Introduction
You have probably created various auxiliary tables, columns or metrics during report development. Before the final deployment of the report into production, it is necessary to delete these auxiliary objects. How to easily check that there are no unused objects in the report? Various paid and freely available tools are used for this purpose. We will take a look at one such tool and talk about its advantage over the others.
Installation
Power BI Cleaner does not need to be installed. Just download it from the Power BI Cleaner website, where is also a detailed guide, how to use this tool.
After downloading the tool, you may notice that it is a PBIT file that contains all the functionality.
Testing report
I created a test report for testing whether the tool can correctly recognize unused objects.
As you can see the tool doesn’t recognize column used for conditional formatting properly and marked it for delete. Let’s try to investigate the root cause of the issue and fix it.
Technical background of the tool
The functionality of the tool is quite simple. Power BI Cleaner parses JSON, which describes how the report should look.
If we open Power Query Editor we see all objects used for parsing and collecting data for visualization of (un)used objects in report. In our case we need identify, where the information about columns used in conditional formatting is collected. There is table UsedFields1_CondFormatVisual.
This table is empty in original version of the tool.
Structure of JSON was probably a little bit changed since last version of Power BI Cleaner.
Parsing of JSON is not so difficult and fix was made quickly.
After the fix we can see that table contains information about all columns used in conditional formatting.
If we apply changes and return to Power BI Desktop, we can check that the bug was fixed successfully.
Conclusion
If you want to have tool where you can tune functionality yourself, Power BI Cleaner can be good starting point.
The advantage of this tool:
- All available transformations are available directly in PBIT file
- No installation of additional tool is required
- Transformations are written in Power Query and don’t require any additional programming knowledge
- You can add the missing functionality yourself
- You can adjust the existing functionality according to your preferences
I am a long time IT fan focusing on databases and data processing. I am interested in both on-premises and cloud technologies. Currently I am working on increasing my knowledge in Azure Synapse Analytics and Databricks.
TOMÁŠ JURKE
Data Engineer