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