Successfully run a query with a blank parameter
Hi guys. Today’s post is just a quick tip, that you may find useful!
Imagine you wanted to create a template for users to pull data into a PowerBI report. For example, it could be using a custom connector or API, and you want to create a blank template where users can add their own connection information.
So, you’ve setup your template with a dynamic query using a parameter (if you are struggling with this, allow me to recommend this excellent blog post on this topic (spoiler - it’s one of mine) and you are ready to share the template. But you don’t want to leave your connection information / credentials in the template of course. This is the trap - if you clear the parameter you can’t successfully run the query, so you can’t apply the change.
If this (admittedly quite niche) problem is giving you trouble - don’t worry! Here’s a simple solution
Let’s break this code down.
Source = contains my web contents query which uses a parameter call GetURL for the target URL, then we pass through some headers like client_id (ci) and client_secret (cs), as well as getting a refresh token - we don’t need to worry too much about that. But in this example file my ci and cs are fed to the query through a parameter, as you can see. Obviously, it’s not really best practice to do this, but it’s just an example!
Then we have converted to table and expanded the column - this is the usual code PBI generates automatically for you when you interact with the data in PowerQuery - standard stuff.
Finally - the cool bit!
The check step is where the magic happens.
And actually it’s just a simple IF statement. What we are doing here is checking if the GetClient_id is equal to whatever text value you want to substitute in your parameter. You could also just set it to blank if you wish - in my example I used XXX.
Either way, we will evaluate that logical statement. If the text does equal whatever placeholder you opted for, then the query actually will generate a new table from a list, which is the contents of the {“Message”}. Whatever text you put in there will be what is returned if the query is run using the placeholder text. If you are sharing this as a template file you could put this measure in a card or something to help guide your users to fill in their own details. Of course, if they went to the dataview to check the data, they will see a single row table as per the screenshot below.
If however the expression evaluates and the parameter is not equal to your placeholder text, then it simply refers to the previous step in your calculation and continues, essentially ignoring itself. If this happens then your query will load as if this step didn’t exist and the result will be whatever output your query returns. In our example you can see that if the parameter is blank we return the message or else we return the “Expanded Column 1” step which is the final step in my query.
While this possibly isn’t that advanced of a tip, in terms of the methodology it employs, when I was faced with this problem I certainly didn’t solve it right away, as this is a pretty unusual usecase for PowerQuery. So, I hope if you are struggling with something like that, this blog has helped!
A big thanks to Torr Wylder for helping come up with this solution! Thanks Torr!
