Both Excel and Power BI have options to create parameters for use in the power query editor. Unfortunately in Excel, the parameter value is not easily updated. If your user doesn't have any experience with Power Query, this can be quite frustrating. This post will take you through the steps of creating dynamic parameters in Power Query with Excel, and also how to deal with that pesky Formula.Firewall error/warning.
Normally, you would create a parameter directly in the Power Query editor. However, a dynamic parameter can be created on the spreadsheet itself. If you are working with a data set that has potentially multiple parameters, you can create a tab specifically for the purpose of managing parameters. This makes it easy for any user to access and change as needed. For this example, will create a 'Markup' parameter for our data set. I'll enter it in cell B1:
Note that I have also named this cell, 'markUp', in the name box right under the clipboard. Next, we can simple add a new data source using the 'From Table/Range' feature on the Data Tab.
Clicking on this will open up the Power Query editor and automatically create a query named 'markUp'.
In its current state, it's just a table with 1 row and 1 column - unusable as a parameter.
We just need to extract the value from this table. As of the time of this writing, there is no way to do this by way of the Power Query UI. So we need to dive into the Advanced Editor. Don't worry though, it's quite simple, and we only need to write one line of code. Open the advanced editor. The line of code we need is:
myValue= Source{0}[Column1]
'Source' refers to our 1 x 1 table. We specify the row number in {} and the column name in [].
Before inserting that line of code, we need to modify a couple of things in the script. First, add a comma after the last line (right before the 'in' line). Then we can add our line of code below, after the comma. The code should look like this:
// Dynamic Parameter From Range let
Source = Excel.CurrentWorkbook(){[Name="markUp"]}[Content],
myValue= Source{0}[Column1]
in
myValue
Click 'Done' and your query should update to reflect the single value parameter.
"But wait! Now I'm getting a Formula.Firewall error!!"
[Formula.Firewall: Query 'Query1' (step 'Your Step') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.]
This bothered me for the longest time. Here's how to remove this pesky error:
In the Query Editor, go to File -> Options and Settings -> Query Options.
Under the Privacy tab, select "Ignore the Privacy Levels and potentially improve performance".
You can now use the query 'markUp' as an effective parameter in your other M scripts!
Enjoy!
Building an Access Database? Check out my ebook on Amazon!
