If you have a Power BI file for a while, you realize the model gets bigger and bigger. Not only the file size but also the number of pages, tables, fields. You will get to some steps that it would be really hard to find which tables and fields are useful and which are not. Every time you have to search for something to find it, and you may have tons of duplicate calculations. In this article and video, I’ll explain how simply and quickly you can clean up the Power BI file from unused fields and measures.
Video
Power BI Helper; Download and install for FREE
If you haven’t done it already, download and install the Power BI Helper, it is a free application to use and you can use it for many useful things to do with the development of the Power BI reports as well as the Power BI file clean-up. Here you can download it: https://powerbihelper.org/
Open Power BI Helper as an external tool
After the install, while you have the Power BI file open in the Power BI Desktop, go to the External tools tab and click on Power BI Helper to open it (Alternatively you can open it from the Start and programs on your windows machine);
Connect to Model
Once the Power BI Helper is open, click on Connect to Model (Make sure that the Power BI file is already open in the Power BI Desktop).
This option will detect all the instances of Power BI files open in the desktop, and will list them in a dropdown. you can choose the one you want to document.
If you have only one Power BI file open, the dropdown will just show that one. The rest of the page shows information about the model. Information such as all the tables, the columns and the measures with their expressions and expression trees.
Visualization information
Power BI Helper should automatically find all the visualization information of the selected model, and shows you the information in the Visualization tab.
There are a few cases that Power BI Helper doesn’t show the visualization information automatically (for example; if the Power BI file is just generated and is not yet saved). In those cases, you can use the Select Power BI File option to choose the file from the location it is saved.
Remove Not-used fields
If there is a field not used in any visual, filter, or other calculations, then this is a safe field to be removed. Power BI Help can identify these fields even if they are DAX calculated columns or measures. Using the dependency tree of the measure, it will identify if the field is used in another calculation that is used in a visual or filter.
You can see the fields to remove in here:
As you can see in the above list, there is also a measure that is not used, and no other measures used this measure used in any visuals or filters.
and this is how you can remove them;
You can delete all the fields by using Select all and then delete. Or you can delete them one by one.
Hide technical fields
Technical fields are those fields that are necessary for the model and cannot be deleted, but they are not used directly in visualizations. Examples of these fields are;
- Fields used in relationships
- fields used to sort other columns
- fields used to create a hierarchy
- …
Technical columns are best to be hidden as I explained in this article.
You can see the fields to hide in here:
In the example above, the fields are used in a relationship (that is why they don’t appear in the list to be deleted). Hiding them is recommended if they are not used directly in visuals in Power BI.
and this is how you can hide them;
Sample output
the output will be saved in the same Power BI file that is open in the Power BI Desktop. here you can see the result of the sample file I used;
Here is another example of cleaning a Power BI file using Power BI Helper;
Backup
Power BI Helper, by default, keeps a version of the file before the changes saved as a backup. The backup can be helpful if an unwanted behavior detected.
The backup file settings can be customized in the settings tab of the Power BI Helper.
Sample scenarios that will be considered
- If there is a field that directly used in a visual, then it will not be removed or hidden
- If there is a measure that is not used in visual or other measures, it will be removed
- If there is a measure that is not used directly in a visual, it will be hidden
Not supported at the moment
The usage of fields and measure does not consider across multiple files (meaning if another Power BI file uses the fields from this Power BI file).
Checkout Power BI Helper for other useful things
Power BI Helper can also help you to document the entire Power BI file and report. Power BI Helper also helps you to performance tune, and reduce the size of your Power BI file, clean up your model, export data or metadata of the Power BI. In general, it helps you to have a better Power BI development experience. Check out more about what you can do with this tool. below are just a couple of examples:
- Document the entire Power BI file using Power BI Helper
- Export the data of a table using Power BI Helper
- Reduce the size of Power BI file using Power BI Helper
- Document the Power BI tenant objects; dashboards, reports, workspaces, etc.
- Export Power BI Audit log using Power BI Helper with no code
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.