Power BI Cleaner

Edit: I am retiring support for this tool for now, as you can find an alternative here: Measure Killer | Brunner BI.
Please feel free to use this tool as it is, but I am not fixing any bugs or provide further help for it. Power BI Cleaner is now published with ‘Power BI Sidetools’ by Didier Terrien: https://github.com/didierterrien/Power-BI-Sidetools/releases Didier may provide help in some cases. Please post issues here:  https://github.com/didierterrien/Power-BI-Sidetools/issues

In this post I’m sharing a new version of my Power BI Cleaner tool. Besides from some bug fixes it can now automatically identify and analyze reports that are connected to an Analysis Services Model in tabular mode.

Edit: Currently it is not available to analyze “thin reports” in the service du to missing authentication feature. The generation of the token via the Power BI Rest API sites is temporarily out of service currently and will be restored soon.

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. I might include a feature to pre-select datasets in a future release if there is interest. So please add comments if you want to see this feature.

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. It covers most use cases from visual fields, titles or format properties over filters on all levels, calculation groups, roles, incremental refresh policies…

As 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.

Basic Power BI Cleaner 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.

I’m always using DAX Studio to grab the port number, but there are other methods described in this article as well. Please use only the numeric part (not “localhost”):

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:

If this prompt is not appearing and privacy levels are still enabled, you might get an error message (ending: “…. Please rebuild this data combination”). You then have to ignore them via the menu: File -> Options and settings -> Options -> Privacy (under: CURRENT FILE) -> Ignore the Privacy Levels..:

 

Also, depending on your settings in the global options, you might be prompted with a couple of warning screens about native database queries:

This is to inform you that you’re executing some custom code against a database. This database is the local instance of SQL Analysis Services that is the basis for Power BI Desktop and automatically running in the background. These warning screens will pop up for all the 15 DMV queries unfortunately and you can only disable the warnings in the global settings of Power BI. Which is something I usually don’t recommend for security reasons, as you can run scripts against a DB that alters or deletes records. So this could open doors for malicious code or simply screw things up if you don’t know what you’re doing. So one should handle this very carefully.

Also, if you’re running the pbit for the first time, the refresh might not succeed at the first time and you might see these symbols at the top of the screen:

Simply hit “Apply changes” again and the results will finally show up. I recommend to save the new pbix as your personal sample and reuse that to avoid having to refresh the file twice in the future.

Main report

Overview page

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.

DAX Expressions page

The last page “DAX Expressions” simply holds a table with all DAX expressions in the model as a reference of to quickly check items. You can also use the search box to find strings in DAX expressions.

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 or use a live connection to an Analysis Services Tabular model (on prem or Azure).

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 will occupy resources and Excel might also become unresponsive for some time.

For these queries to run, you have to disable the privacy settings on file-level:

Then you have to select the modus:

Single Power BI Desktop File

If you choose “PBI_Desktop_pbix” the following fields will show up:

In cell B8 you have to enter the port number just like in the PBI version. In B37 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

Then you can simply copy the code, open Tabular Editor (LinkToProVersion, LinkToFreeVersion) and paste the code into the “Advanced Scripting” window. Click run, save the file and the cleanup is done:

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 “ReportFile (s)” from cell B37 onwards 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”:

On the “Instructions” page of the xlsx choose modus “PBI_Live_Connection”. Paste the copied authentication figures into row 30/31:

Also fill in the port number into B25 and the dataset ID into B26. 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 B27 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 B26, 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)

Reports connected to an Analysis Services model

If you select modus “Analysis_Server” the following dialogue will appear:

Enter the path to the SQL server into B15, or “localhost” if it’s running you your local machine. The database name goes into B16 and the credential in B19/B20 follow the same mechanics than described above under “Directly from the service”.

Optionally, you can add links to local files if they should be analyzed as well.

Just have in mind, that these methods can take quite a while, because in order to identify all reports in the Power BI service that use the specified datasets, Power Query has to retrieve metadata from all datasets and all data sources that live in your tenant (and you have access to).

Areas covered

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
• Relationships
• Filters (visual-, page- and report level)
• Visuals (incl. conditional formatting)

 

Limitations

• 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.
• Fields used in connected Excel reports are not covered by this solution.
• Detection will not work when sensitivity label are applied.
• Fields in custom visuals might not be properly detected. If you find problems there, please report.
• Certain special characters in measures will lead to false negatives (meaning they will be shows and unused/can be deleted). So far, I could only identify the triangle (∆), but I cannot rule out that other characters can also be affected. The following special signs seem to work alright: |, (, ), \, “.
  What you can do instead, is to rename a measure with the triangle sign, once used in the visual. This will be picked up correctly.

Warning

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.

Feature requests

Are there additional functionalities that you’d like to see in this tool?
Post it in the comments, please.

Potential Problems

Run-time error ‘1004’

This error message might indicate the your token isn’t valid any more and you have to refresh it:

… please rebuild the data combination – error

If you’re getting a warning that data sources cannot be combined, you must disable privacy settings like so:

 

If you experience further problems with the Power BI Cleaner tool, please post them in the comments or send me an email and I will do my best to get them sorted.

Current version: 2022-Jan-8: V7 (bugfixes)

Download files

PBIT: Power BI Version Cleaner Gen2

Excel version with macros: Cleaner Gen2 Excel-Version with Macros

Enjoy and stay queryious 😉