This blog post comes from a question raised at a Power BI training session, and deals with updating data source information, for example moving from development to production, or folder or file locations moving. The question was asked as I was showing the attendees the ‘Load from Folder’ function in Power BI, so you can dump monthly files into a folders and load them together. The question was ‘What if the location changes? do you have to rebuild the report?’. I mentioned that you can edit the M code to change the location, but the users was a bit worried that it was a bit to ‘techy’ to do that, so I suggested a parameter that you can use. They where more than happy with that approach it turns out.
So how to use parameters? Well lets fire up Power BI and enter Power Query, but clicking on ‘Edit Queries’
And then select the New Parameter option, which should bring up the following…
Now, there are a few options here, you can have a list of values, or items based on a query, but for this example, we just need the basic version.
Type = Text
Current Values = The folder location that we are going to use to pull the files in
Click OK and how do we use it when loading a folder?
Select the folder import then select the little drop down for the folder path.
Select the parameter (Or start the process from there, by creating a new one).
Click OK, and away it goes as if you typed in the folder location normally.
So if you need to change the location, you don’t have to edit the M code, just update the parameter, by clicking on the manage parameter selection, where we created the new parameter. Nice and easy, well I think so.
So parameters can be used in a number of data sources, for example SQL Server connections
So switching around DEV, UAT and Live servers should be a slightly less painful process, than editing the M code.