Creating a Power BI data model from a flat file source hosted on SharePoint or Azure Data Lake Store can be cumbersome. I recently found this out trying to create a simple data model for a demo, which involved files sized over 3 gigabytes. Every time I performed a transformation in Power Query and hit Close and Apply, I had to wait for 15 to 30 minutes for the data to be pulled down from the cloud and for the processing to complete on my local machine.I soon figured that this is not a viable solution as it would take days and the better part of my sanity to make this work. This is where Power BI Parameters came to my rescue, which allows users to create parameters in Power BI Desktop and reference them for just about anything such as data source, database reference, and filter references, etc. My solution was to parameterize the data source in Power BI Desktop. When working on my local machine, I work with a “dev” data source with a subset of the files which amounts to a few megabytes worth of data. Once I publish my Power BI dataset to the Power BI service on the cloud, I can change this parameter from the web to point to the “prod” data source which has several gigabytes worth of data files. Once I change the parameter in the service and refresh the dataset, things happen much faster due to the following reasons:
- Power Query no longer needs to copy all the data from the cloud to my laptop through my local ISP to process data.
- Power Query now uses the power of the Microsoft cloud to process data.
This is a simple but elegant solution which saved me countless hours, and I hope this helps someone out there too. The following steps will give you an understanding of how this is done. 1.Open Power BI Desktop and create the parameter.
2. Reference the parameter in the Power Query step to filter data source/transformation. Note: You must have this option enabled from the Power BI Desktop > Options tab.
3. Go to the Power BI service datasets Settings
4. Set parameter in Power BI service.
Author:
Stefan Outschoorn
Consultant – Business Intelligence
Stefan has been working with business intelligence for 7+ years; with beginnings in SAP and Oracle, and some Infor BI on the way, his expertise now centers on Microsoft technologies. He is particularly interested in Application Lifecycle Management and DevOps of business intelligence.
