Today I’m very excited to share with you my first version of a complete rework of my Power BI Cleaner tool. It is way faster the the initial version, overcomes some bugs and limitations of the old version and doesn’t require creating additional vpax files.
On top of that, I’ve created an Excel-version, that adds some very convenient additional features: The option to analyze thin reports and to generate scripts that delete unused measures and hides unused columns automatically.
Basic Power BI version
Fill in parameters
First you have to open the Power BI file whose data model you want to analyse. 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.
When you try to refresh the data, you will be prompted with a dialogue about how to connect to your local Power BI model:
Just accept the default dialogue (“Use my current credentials”).
Next comes a prompt for Privacy Levels. Click on “Ignore Privacy Levels” in there:
On the Overview page, you see a matrix with table and column names in the row section and usage information in the columns. 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). So fields in here can also be deleted from the model. But depending on when you delete them, you might break some measures or calculated column temporarily. But those are the ones that can also be deleted at the end. 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).
In the next column you see where the used fields are actually used (3). So these fields should stay in the model. The next column is just a summary, indicating if the field can be deleted or not.
To the left there are some filters that you might find useful. For example, you can select all fields that can be deleted by choosing the first box in the “Can be deleted”-filter (6).
If you expand the fields in the row section, you will first see the DAX expression of the field. After that, you see where this field has been used throughout the report.
The last 4 pages of the Excel file (DMV_Measures, DMV_Columns, DMV_CalculationGroupItems, DMV_Hierarchies), just hold a list of all DAX expressions in your model as a reference.
Delete or hide unused items automatically: Excel version
The Excel-version can also generate scripts for you that can delete unused measures or hide unused columns automatically. 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.
To start you have to fill in the port number into B9 just like in the Power BI version. In B12, you must fill in the path to the pbix like in the Power BI version or the path to a pbit version. Both will work.
Click on “Refresh main result” will refresh the data and move you over to the “Result” page. This looks just the same than in the Power BI version.
The next page “Measures_Delete” holds a table to the left with one row for each measure that is not used. Based on it, the table to the right will generate a code that can be used to delete all the measures using the Tabular Editor tool.
Before using this functionality, I strongly recommend to make a copy of your Power BI report, as the following actions are not officially supported by Microsoft. So if they break your report, they will not help you out on it.
The left table contains a list of detected measures that are not used anywhere in the report. If you want to keep some of the unused measures from there, simply delete the “Delete”-entry in column “Action” and it will be excluded from the code. Once you’re happy with the selection, click the button “Create and copy code”. This will refresh the table to the right that contains the code that would automatically delete all the listed measures if used in Tabular Editor.
Use Tabular Editor to delete or hide unused items
In your PBIX-model, the measures should be gone now.
The same principle goes for the columns, that can be hidden with the tables from sheet “Columns_Hide”.
Analyze thin reports
Based on files
Also in the Excel-version, there is a table where you can add all the files to analyze that are using the currently opened dataset. Yes: You can analyze “thin reports” with it that don’t have an own data model, but are connected to a centrally published model instead. Simply add the path to the pbix or pbit-versions into table “ReportFiles” on the “Instructions”-sheet. The model that holds the central dataset must be open in Power BI Desktop and its port number entered into B11 of the “Instructions” page.
In the pivot table on the “Result”-sheet, the last column level field is “Report File(s)” and there you can see in which (thin) report each element is used.
Directly from the service
And to make it even more convenient, you can also analyze your reports from the Power BI service directly. This requires an app registration that provides the access to this data. This article provides a guide on how to register such an app. The app needs to have “Workspace.ReadAll” and “Report.ReadAll” permissions. To authenticate with this service from Excel, you can create and grab a token from one of the API endpoint-pages, like: Datasets – Get Datasets – REST API (Power BI Power BI REST APIs) | Microsoft Docs. There you must click on “Try it”:
Next the authentication will start and you have to login to the account that has access to the app. On success, you’ll see a window like so that displays the token. Click on “Copy”:
Paste it into B7/B8 of the Excel sheet.
Also fill in the dataset ID into cell B2. You find the dataset ID in the URL if you edit the dataset details in the service like so for example:
2 different connection modes
If you have admin rights in Power BI, the queries can fetch details from all workspaces in your tenant. Therefore you have to switch the value in cell B4 to “As Admin”. Otherwise, keep the default entry “As User”. In that case, only the details of those reports will be displayed, where you have access to.
Please note, that even measures that have been created in the thin reports (report level measures) will be detected as well. Just have in mind, that they will not be covered by the automatic
As long as there is an entry for the DatasetID in cell B2, Excel will try to use the dataset and the corresponding token. After a while, the token will become invalid. To generate a new one, simply refresh the page where you’ve grabbed it from. If you haven’t already, now it’s time to vote to enable the usage of custom connectors in Excel so this cumbersome process can be omitted: Add support for custom data connectors (Power Query M extensions) – Welcome to Excel’s Suggestion Box! (uservoice.com)
This has been a frequent request by many users and I’m really happy for being able to provide this now. Just don’t forget please, that this analysis doesn’t include fields used in Excel-reports that are connected to these central datasets.
The following areas of field usage are covered in these versions:
- DAX expressions (measures, columns, hierarchies, tables, report level measures, roles, bins, calculation items and title expressions)
- Incremental refresh policies
- Filters (visual-, page- and report level)
- Visuals (incl. conditional formatting)
- When upgrading your workspace, your reports might be rebound to the dataset. In that case, the API will not return any data.
- Automatic retrieval by the API will also not work for reports in the old workspace format.
I cannot guarantee that this all works 100% correct, so you’re using it on your own risk. Don’t forget to make a copy of your file before starting to throw out your measures and columns. If you find any bugs, please mention them in the comments.
Are there additional functionalities that you’d like to see in this tool?
Post it in the comments, please.
If you’re getting a warning that data sources cannot be combined, you must disable privacy settings like so:
Excel version with macros: Cleaner Gen2 Excel-Version with Macros
Enjoy and stay queryious 😉