When you try to import large sets of data in Power BI Desktop (think 25 GBs or larger), it can often consume a lot of time and in certain scenarios, the import may actually fail due to limitations of your desktop machine.Â
The good news is that you can use Parameters in Power BI to work around some of these limitations.Â
In this blog, we’ll walk through how to load large datasets in Power BI Desktop using parameters.
How to Load Large Datasets in Power BI Desktop Using Parameters
To load large datasets in Power BI Desktop using parameters, you first need to create parameters, filter data in the date column of the table being imported, and finally, change parameters in Power BI Service to load the entire data.
Step 1: Create Parameters
In this task, use Power Query Editor to create two Parameters with default values. The parameters apply only when filtering the data to be loaded into the model in Power BI Desktop. Since we are wanting to limit our data load to a smaller data set to make the initial load easier, choose values for your parameters that will parse down your data set appropriately.
1. In Power BI Desktop, click Transform data to open Power Query Editor.
2. Click Manage Parameters > New Parameter.
3. In Manage Parameters > Name, type Parameter1, then in Type, select Date/Time, and then in Current Value enter a start date/time value. Click OK.
4. Create a second parameter named Parameter2. In Type, select Date/Time, and then in Current Value enter an end date/time value. Click OK.
Make sure that the difference between the start date/time value and the end date/time value is small as we are limiting our initial data load in Desktop to save time. Now that you created two parameters (Parameter1 and Parameter2), next you’ll need to filter the data to be loaded into the model based on those parameters.
Step 2: Filter Data
With Parameter1 and Parameter2 parameters defined, apply a filter based on conditions in the Parameter1 and Parameter2.
1. In Power Query Editor, select the date column you want to filter on, and then click the filter icon > Date/Time Filters > Custom Filter.
2. In Filter Rows, to specify the first condition, select is after or equal to then select Parameter, and then select Parameter1. To specify the second condition, select is before or equal to, then select Parameter, and then select Parameter2.
Note:
- If you are using ‘is after or equal to’ as the first condition and ‘is before or equal to’ as the second condition, Power BI will load all the records between Parameter 1 and Parameter 2 and also the records which includes Parameter 1 & 2 dates.
- If you are using ‘is after’ as the first condition and ‘is before’ as the second condition, Power BI will only load the records between Parameter 1 and Parameter 2 and not the records which includes Parameter 1 & 2 dates.
- If you are using ‘is after or equal to’ as the first condition and ‘is before’ as the second condition, Power BI will load all the records between Parameter 1 and Parameter 2 and also the records which include Parameter 1 date but the records containing Parameter 2 date will not be loaded.
Click OK to close.
3. In Power Query Editor, click Close & Apply. Power Query will then load data based on the filters defined in the parameters, and any other filters you defined.
Step 3: Changing Parameters in Power BI Service
After loading data based on the filters defined in the parameters, save the file and publish to Power BI Service. Now go to the workspace where the .pbix file is published. Go to dataset settings and change the parameters to include the larger dataset and refresh the data manually or schedule refresh.Â
Now your large dataset should refresh on Power BI Service without you having to load the entire dataset up to Service with Desktop.
Conclusion
Loading large datasets in Power BI Desktop using parameters is a simple and time-saving task. All you need to do is create parameters, filter data in the date column of the table being imported, and finally change parameters in Power BI Service to load the entire data.Â
Following the process covered in this blog will save a lot of import time and prevent failures while importing data due to limitations of your desktop machine or low network bandwidth.Â
If you need additional help or are curious about how to better leverage Power BI, reach out to the experts at phData today for help, guidance, and best practices.Â
