How to Reduce the Size of Power BI file in a few Steps

how to reduce the size of Power BI file in a few steps

One of the performance considerations of a Power BI file is to reduce the size of it as much as possible. This also helps with the speed of development, because smaller files loads faster and easier in the Power BI Desktop. One way to reduce the size significantly is to find which columns are consuming the most size and remove them. In this article and video, I’ll explain how you can easily do that using Power BI Helper.

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 checking the memory space each column takes. 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.

Steps to reduce the size

My sample Power BI file has a size of 72MB. I want to find out what columns are consuming the most space in this model;

Power BI file size before tuning

When I open Power BI Helper and connect to that model, under the Modeling Advise tab, I can see how big each column is when it is in the Power BI model.

Checking the memory space consumed for each column in a Power BI file

The dictionary_size column is in MB, and it is the size of the column when expanded it the memory. This might not be necessary the size of the file when stored in file storage. For example, the SalesOrderNumber column doesn’t take 52MB of the 72MB size of the file, but it is definitely a big portion of it.

Step 1: Remove columns with big memory space consumption as much as possible

Once you identified columns that are taking much space, if you don’t need those columns, you can go to the file in the Power BI Desktop and remove them;

remove columns that are consuming a lot of space and not useful for analysis

Step 2: Turn off the Auto Date/Time

In the Modeling Advise report, you can also see evidence of having a default date table in Power BI used.

evidences of usage of the default date table in a Power BI file

The default date table can be helpful. However, if you have too many date fields in your data model, and the range of dates is very wide, then the ideal would be to have a custom date table and disable the default date tables. Read my article here to understand that fully.

You can go to the Power BI Desktop and File > Options and setting > Options, and uncheck the setting below;

turn off the default date table in Power BI file

Uncheck the option for Auto date/time under Time Intelligence. After doing the changes, and saving the file, here is the model size reduced 30+% of size.

Power BI file size reduced more than 30% after tuning

Step 3: Remove un-used columns

In a lot of Power BI reports, it might not be easy to find out which columns are not really used anywhere in the file. in visualizations, in relationships, filters, or even other calculations.

Power BI helper can help you identify which columns are not used (not in visualization, not in other calculations that are used in visualization too). You can check that by going to the Visualization tab in the Power BI Helper;

Delete the columns not used in visualizations or other calculations

This option will simply delete all the columns not needed. in this example, the file before and after size is showed.

Power BI file size reduced more than 80%

In the example above the size reduced more than 80%.

Checkout Power BI Helper for other useful things

Power BI Helper can also help you to document the entire objects in the Power BI service and 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.

4 thoughts on “How to Reduce the Size of Power BI file in a few Steps

  • Thanks so much for sharing this tool! One question. If your visuals are in separate files from your model and there are multiple, can you use power bi helper to search multiple report files to validate the columns are not in use?

    • Hi Shannon
      At the moment, Power BI Helper doesn’t search for visuals in other PBIX files that uses data fields in another PBIX file. That is, however, in our backlog. a big feature to be implemented though.
      Cheers
      Reza

  • Hi,
    Thanks for sharing this tool. I am trying to reduce the size of a model and following your instructions.
    In the Visualisations tab , I do not see anything even though I m connected to the model. Why do you think is that ?

    • Hi.
      As mentioned in the post. there are sometimes that the Power BI Helper cannot detect the PBIX file location to open it in the visualization tab. In cases like that open the pbix file in the visualization tab.
      CHeers
      Reza

Leave a Reply

%d bloggers like this: