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

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.

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;

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.

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;

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.

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;

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.

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;

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

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:

• Document the entire Power BI file using Power BI Helper
• Export the data of a table using Power BI Helper

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 12 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, Power BI Summit, and Data Insight Summit.
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.