Working with large data volumes in Power BI desktop can be tricky if you don’t have a high spec machine. When you are using Import mode to create models in Power BI desktop, it loads all the data to memory and use CPU to process any changes you do to model. In that case, if you have millions of records, there is a high possibility that your PC will struggle for resources and sometimes Power BI desktop application can crash as well. In this post I’m going to show a simple trick which will help you to reduce no of records in power bi model while keeping it all the records in Power BI Service. That way you don’t have to worry about your PC configurations and keep doing the development in a low-end PC.
To show that, I have used Contoso Retail DW and have created a simple star schema using Fact Sales and related dimensions. Fact Sales table have close to 3.5 million records and I’m struggling to load all records into my PBIX file in my development PC as there is not enough memory and CPU power available.
Therefore, what I’m trying to do is minimize the no of records I work in PBIX file and I try to make to work without any issue in Power BI service once deployed to the service. First, I’m going to create a parameter called “RecordCount” in Power Query Editor and set the value as 2000. Note that I have set Type as Text.
Then I opened the Fact Sales table in Power Query editor and click on the top left corner to open the menu for transformations. In the menu click “Keep Top Rows” and set Number or Rows to 2000. This will filter the table and only load the top 2000 records to the PBIX file.
Next right clicks the FactSales table on the left hand Quires panel and click “Advanced Editor”. This will open up Advanced Editor where I modify the Keep First Row step. In here, I change it to a If statement. The logic is if the value of the parameter is “ALL” then it should get all the records in the fact sales table, if not get the FirstN records based in the value set for the parameter “RecordCount”. check the highlighted part of the query.
Next important thing I want to check is whether the change I did affect the query folding in Power Query. Therefore, I right click on the “Kept First Rows” and check the Native Query. It seems no issue with native query.
When I check the row count from the Power BI visual side, I can verify that it only loads the row count I specified in the Parameter. Then I can modify the model and create required measures/columns and visuals without Power BI desktop consuming high resources in my PC.
Once all the development is done in Power BI desktop, I published the Power BI file to service. However, in service I don’t want to see 2000 rows from Fact Sales table, I want all the records in the fact table. For that, click in 3 dots in the dataset in service and select settings in the menu.
it will open up the settings are of the data set and under that there is an option called “parameters”. Change the value of the parameter to “ALL”. Remember the IF statement I used in advanced editor, that will allow me to get all the data from the fact table when I refresh the data set now.
When I refresh the dataset in Power BI service and check the row count now, it shows me that model now have all the source data, not 2000 records as in Power BI file in your PC.
This way you can create Power BI models with large data volumes without worrying about configuration of your PC. Thanks for reading and stay safe! Cheers.