It will occupy resources and Excel might also become unresponsive for some time.įor these queries to run, you have to disable the privacy settings on file-level: I strongly recommend to close all other Excel files when working with this solution, especially when you’re analyzing thin reports (Analysis Services or PBI Live Connection). It also allows to analyze thin reports: Meaning Power BI reports that don’t have an own data model but instead use a live connection to a central data model in the service or use a live connection to an Analysis Services Tabular model (on prem or Azure). The Excel-version can also generate scripts for you that can delete unused measures or hide unused columns automatically. Delete or hide unused items automatically: Excel version You can also use the search box to find strings in DAX expressions. The last page “DAX Expressions” simply holds a table with all DAX expressions in the model as a reference of to quickly check items. After that, you see where this field has been used throughout the report. If you expand the fields in the row section, you will first see the DAX expression of the field. For example, you can select all fields that can be deleted by choosing the first box in the “Can be deleted”-filter (6). To the left there are some filters that you might find useful. The next column is just a summary, indicating if the field can be deleted or not. So these fields should stay in the model. In the next column you see where the used fields are actually used (3). The decision to delete a column in the source or hide it in the report might depend on the column size, which is also given at the end of the matrix (5). But those are the ones that can also be deleted at the end. But depending on when you delete them, you might break some measures or calculated column temporarily. So fields in here can also be deleted from the model. Upon readers requests I’m distinguishing between fields that are not used at all in the report (1) and those who are used in DAX expressions, but the fields it has been used in, have not been used in the report at the end (2). On the Overview page, you see a matrix with table and column names in the row section and usage information in the columns. Power BI Cleaner main report Overview page Then open the Cleaner Power BI template file and fill in the local port number and file path of the file to analyze into the 2 mandatory parameters.
Basic Power BI Cleaner version Fill in parametersįirst you have to open the Power BI file whose data model you want to analyse. It covers most use cases from visual fields, titles or format properties over filters on all levels, calculation groups, roles, incremental refresh policies…Īs before, there are 2 versions: A basic Power BI version and an Excel-version, that adds some very convenient additional features: The option to analyze thin reports (connected to Analysis Services or a shared Power BI dataset) and to generate scripts that delete unused measures and hides unused columns automatically. If you are new to the tool: It shows you which DAX expressions are used where in your reports and also indicates which elements can be deleted or hidden. So please add comments if you want to see this feature. I might include a feature to pre-select datasets in a future release if there is interest. Just a warning that the live connections to Analysis Service or shared Power BI dataset might be slow, as Power Query will retrieve the data from all datasets that are available for your from the service. The generation of the token via the Power BI Rest API sites is temporarily out of service currently and will be restored soon. Besides from some bug fixes it can now automatically identify and analyze reports that are connected to an Analysis Services Model in tabular mode.Įdit: Currently it is not available to analyze “thin reports” in the service du to missing authentication feature. In this post I’m sharing a new version of my Power BI Cleaner tool. Power BI Cleaner is now published with ‘Power BI Sidetools’ by Didier Terrien: Didier may provide help in some cases.
Please feel free to use this tool as it is, but I am not fixing any bugs or provide further help for it. Edit: I am retiring support for this tool for now, as you can find an alternative here: Measure Killer | Brunner BI.
0 kommentar(er)
