Power BI File Clean-up In a Few Steps

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);

Open Power BI Helper from the Power BI Desktop

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

Connect to the model from Power BI Helper

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.

Select the Power BI file in the Power BI Helper

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.

Power BI Helper automatically shows the visualization information after selecting the model

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:

Fields not used in Power BI detected using Power BI Helper

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;

Delete un-used fields in Power BI

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:

detecting fields in Power BI that can be hidden from the report view

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;

Hide Power BI fields in a few clicks using Power BI Helper

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;

Cleaned Power BI file

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.

Auto backup of Power BI file before changes

The backup file settings can be customized in the settings tab of the Power BI Helper.

Backup file settings in 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:

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply