Guy in a Cube had a great video on using Parameters to dynamically update information pulled in from the database to a Power BI report (view it here). At the time I discovered this video I was looking for something similar. The one difference was I wanted to select multiple items in one report. In other words, in Guy in a Cube’s example, he pulls one StudentID into his report. What if I wanted to pull three, four, or five StudentIDs at once?
Now, I’m going to use Stores in place of students.
- Let’s say I have a Power BI report that can compare Store sales, and let’s say I want to compare stores A and B, and then maybe stores B, C, and D. Now, in Power BI, the Power Query interface does allow for a ton of rows. Let’s say my dataset is still gargantuan and needs to be cherry picked like I said.
The first thing to know is that Power Query is written in M. If you are willing to learn a little bit of M you will be able to make this happen.
Let’s setup the Parameters in Power BI and the Data source we are going to be working with.
In Power BI Desktop select Edit Queries -> Edit Parameters
The Power Query Editor will open and now select Manage Parameters -> New Parameter
The Parameters Box opens and we enter our information accordingly
I named my Parameter “SelectionSet”, and I am calling it out here specifically, because we will re-use that name in our Power Query later. So, whatever you plan to name it “myFavoriteParam”, “JoeWuzHere”, “SteveRox”, whatever floats yer boat. But remember it will be used later.
The description is optional, but helpful when you are reviewing a file you haven’t looked at in six months.
The Required checkbox is important, because it will ensure when the query runs, it passes something and lowers the likelihood of generating an error.
I kept Type as “Any” because I wanted the freedom to pass Alpha-Numeric parameters
I entered default values to enable data testing.
Then I click “OK”
We should now see the screen with the SelectionSet
Next we add our Query. Click New Source -> SQL Server (or whichever data source you are using).
Follow the prompts to enter correct Connection name, Database name.
Choose Import as the Connectivity Mode.
For our SQL Statement, we will include a WHERE Clause that we will update later.
After Clicking OK, we now have two Queries, “Query1” and “SelectionSet”.
Now it’s time to dig into the M Language. Before we change the code, let’s look at two simple M functions we will be working with:
Text.Split() and Text.Combine()
Both do what you imagine they would – Split takes our variable and splits it based on a delimiter. In our case, we have commas separating our StoreID values, so we will split on commas.
Combine, will take multiple text variables and combine them together. We will add some text to run our SQL Select statement properly, and combine the StoreIDs after that.
For Text.Split it will look like this Text.Split(SelectionSet, “,”) – remember to add whatever weirdo name you chose in place of SelectionSet if you did.
For Combine, we will take the new split variables and add “’ OR StoreID = ‘” to the end of each one.
We are assigning the newly combined text to a variable called myDynamicIDSet.
To add the code, Right Click on Query1 and choose Advanced Editor
In the code it will look like this
let Source = Sql.Database("DB_CONNECTION_NAME_HERE", "DB_NAME", [Query="SELECT * FROM TABLE_NAME WHERE (StoreID = 'Store2889')”) in Source
Above the line beginning with “Source” add the following:
myDynamicIDSet = Text.Combine(Text.Split(SelectionSet,",") ,"' OR StoreID = '") as text,
The new code will look like this:
let myDynamicIDSet = Text.Combine(Text.Split(SelectionSet,",") ,"' OR StoreID = '") as text, Source = Sql.Database("DB_CONNECTION_NAME_HERE", "DB_NAME", [Query="SELECT * FROM TABLE_NAME WHERE (StoreID = 'Store2889')”) in Source
The last step is adding the dynamic parameter to the SELECT statement.
Replace the Store2889 text with ” & myDynamicIDSet & ” Be extra careful to:
- Keep the single quotes before and after the variable
- Keep both the open and close parentheses around the clause
- Include the double quotes and ampersands around the variable
The final script will look something like this:
Let myDynamicIDSet = Text.Combine(Text.Split(SelectionSet,",") ,"' OR StoreID = '") as text, Source = Sql.Database("DB_CONNECTION_NAME_HERE", "DB_NAME", [Query="SELECT * FROM TABLE_NAME WHERE (StoreID = '" & myDynamicIDSet & "')”) in Source
One great feature in the Power Query Editor interface is that you can see what the Dynamic variable looks like and troubleshoot it if it’s not showing up the way you expect.
Once the entire query is up and running, try testing it with one, two, three, or more parameters. Check the results to be sure they are what you expect.
That’s all for now, and thanks for reading. Will plan to have more Power BI material soon.