Let me set up the scenario for you. Recently, our team was working on a report which used excel files from a local system as its data source. The excel files were eventually going to reside on the client’s local computer which they would be updating daily. The report would be refreshed using Power BI’s Gateway.
During the development, the requirement was such that the latest files (report & excel) needed to be sent to multiple developers. A developer would get the latest files, make the required transformations, build reports & then forward the updated files to the next person in the team. During this cycle, we were also required to publish the report in versions that the clients would test and suggest modifications or more additions.
Hence I decided it was best to make the source dynamic to overcome the rather very time-consuming task of changing the source location of every different excel file or folder every time the files are shared across developers. Another use of making the source dynamic is that you can publish the report while it points to the files in your system, but after it’s published you can change the source to point to the system which has the gateway installed. Therefore, you will not need to transfer the pbix file to the remote system to change the source whenever it’s needed to be published.
In this blog, I will be showing how even you can implement this trick. So, let’s get started!
Here’s how the blog is structured:
- Connect to Excel in Power BI
- Create Parameters
- Make the source dynamic!
- For individual files
- For folders
- Switch the excel file directly from Power BI Service
Connect to Excel in Power BI:
Currently, there are two ways in which you can connect your Excel/CSV file (or any other similar file format) in Power BI. I have already discussed in detail about these methods in one of my previous blogs.
Create Parameters:
I have also covered parameters in detail in many of my previous blogs. Still, I will go through it again as this whole technique is going to be based on parameters.
Step 1: In Power Query Editor, from the Home ribbon click on Manage Parameters->New Parameter
Step 2: Fill in the details in the ‘Manage Parameters’ Dialog box. Give a name & then change the suggested values to ‘List of values’ & enter the path of the file or folder. Select the default & current value of your choice. Click on Ok.
Make the source dynamic – Files:
In the case of an individual excel file, in the ‘List of values’ you can specify the locations of the file in your system, as well as the location of the file in the system with the gateway. You will need to create a separate parameter for every other excel file present in the report.
Once parameters are created, follow the steps below:
Step 1: In Query Editor, select any table which uses Excel/CSV as the source & then select ‘Advanced Editor’ from the Home ribbon. The M script will look something like this:
Step 2: In ‘Source’, replace the actual path between quotes & replace it with the parameters name.
Here’s how the script will look after you’ve made the change:
Step 3: Click on Done.
Make the source dynamic – Folder:
In the case of folders, in the ‘List of values’ you can specify the locations of the folder in your system, as well as the location of the folder in the system with the gateway. You will need to create a separate parameter for every other folder present in the report (or folders that you want to change & close before publishing).
Once parameters are created, follow the steps below:
Step 1: In Query Editor, select any table which uses Folder as the source & then select ‘Advanced Editor’ from the Home ribbon. The M script will look something like this:
Step 2: In ‘Source’, replace the actual path between quotes & replace it with the parameters name. Also, notice that the source of the folder is also specified in the 2nd line next to ‘ #”Folder Path”= ‘. In this case, we need to replace the path with the parameter’s name here as well.
Here’s how the script will look after you’ve made the changes:
Step 3: Click on Done.
Switch the location directly from Power BI service:
If you have created the report in your system & then want to publish the report with the report connected to excel files that resides in someone else’s local system which also has a gateway associated to refresh them, there is no other choice than to transfer the report to that local system -> apply the changes -> then Publish.
Once you have made the sources dynamic, you can publish the report with the source connected to excel files in your system & then change the parameters after the report is published. The only issue you will face here is that currently, power bi service does not allow you to select the list of values (you had specified while creating parameters). Instead, you will have to enter the locations manually.
Karan Nair
Team Lead – Data Analytics
Addend Analytics