For me the most exciting new feature in Power BI in a long while is the appearance of Query Parameters for data loading. We have been promised an official blog post explaining how they work (although they are very easy to use) and in fact Soheil Bakhshi has already two very good, detailed posts on them here and here. What I want to do in this post, however, is look at the M code that is generated for them and see how it works.
Consider the following parameter built in Power BI Desktop that has, as its possible values, the names of all of the days of the week:
The first thing to notice is that parameters are shown as a special type of query in the Queries Pane, but they are still a query:
This means that you can open up the Advanced Editor and look at the M code for the query. Here’s the code for the query shown above:
[sourcecode language=”text” padlinenumbers=”true”]
"Monday"
meta
[
IsParameterQuery=true,
List={"Monday", "Tuesday", "Wednesday",
"Thursday", "Friday", "Saturday", "Sunday"},
DefaultValue="Monday",
Type="Text",
IsParameterQueryRequired=true
]
[/sourcecode]
From this you can see that the value returned by the parameter query is just a single piece of text – it’s the value “Monday” that is set as the Current Value, that’s to say the value returned by the parameter itself. The interesting stuff is all in the metadata record associated with the value. I blogged about metadata here, so you may want to read that post before going any further; it’s pretty clear that the fields in the metadata record correspond to the values set in the UI. All of the fields in the metadata record can be edited in the Advanced Editor if you want.
When the parameter is used in another query it is referenced like any other query value. For example, if you load the DimDate table from the Adventure Works DW sample database and use the parameter above to filter the EnglishDayNameOfWeek column then the code generated in the UI looks like this:
[sourcecode language=”text” highlight=”10″]
let
Source =
Sql.Databases("localhost"),
#"Adventure Works DW" =
Source{[Name="Adventure Works DW"]}[Data],
dbo_DimDate =
#"Adventure Works DW"{[Schema="dbo",Item="DimDate"]}[Data],
#"Filtered Rows" =
Table.SelectRows(dbo_DimDate,
each [EnglishDayNameOfWeek] = Day)
in
#"Filtered Rows"
[/sourcecode]
The filtering takes place in the #”Filtered Rows” step and you can see where the name of the parameter – Day – is used in the Table.SelectRows() function to filter the EnglishDayNameOfWeek column. This is nothing new in terms of the language itself because you have always been able to return values of any data type from a query, not just tables, and you have always been able to reference queries in other queries like this – in fact you can see me write the same kind of code manually in this video. What is new is that there is now a UI to do this and there’s no need to write any code.
Personally, I think the Power BI team have done a great job here in terms of usability and clearly a lot of thought has gone into this feature. It doesn’t do everything I would want yet though: the ability to bind the list of available values to the output of another query and the ability to select multiple parameter values at the same time are obvious missing features (and ones that would be needed to match the parameter functionality in SSRS). However I would not be surprised to see them appear in a future version of Power BI.
After seeing the code, I wondered whether I could edit the code in the parameter query to make it do more interesting things. For example, even if the UI doesn’t support data-driven lists of available values for a parameter, it looks as though it should be possible to replace the hard-coded list with a list of values returned by another query. Unfortunately this does not work: any changes I tried to the parameter query code were either ignored or removed completely. A bit of a disappointment but again, hopefully this will be possible in a future version.
Was also disappointed when I tried to replace the hard-coded list, hopefully they’ll add this soon!
Do you know whether the user can enter/choose the parameter value in the Power BI cloud portal, I can’t see to find how. Perhaps this isn’t its intended use?
I can’t see it there either. I assume it will be available though – what’s the point of having parameters if the user can’t set them in the web portal before refreshing?
I was trying to figure that out myself. Unless it is just for the report author those doesn’t know how to parameterise queries through M-code?
It’s a bit off topic of course, but I was kind of hoping I could populate the parameters from a list and then link to a logged-in username so that we get user-targeted content on Power BI online. I’ve taken a quick look at Row Level Security and Roles, not sure how/whether I can use username() in the DAX filter expression there so was checking out parameters.
Hey Chris,
Curious if you’ve tested this to see how it affects query folding. That’s always been the bugbear with using parameterised queries in the past. So if you use a parameter to filter your table to a specific value, then do more actions, will query folding still continue or does it still break it?
Hi Ken, just double-checked it and for the filtering scenario query folding does happen. I suspect that some of the current limitations of parameters are a result of needing to make sure that query folding happens.
Now that is very cool. We need this in Excel too. If we could hook an official parameter to an excel named range/table, and query folding would still work, that would be magic. 🙂
Just a quick update, for now parameters work only in Power BI Desktop and are intended to deliver the ability to save a pbix file as a template (which strips out all the data) and then when a user opens, they are prompted to supply each of the parameters thus delivering the contents according to their selection. They are intending to extend this to the cloud service, no timescales given.
Interesting, thanks.
Unfortunately once I’ve included parameters in the dynamic file names, changing any other parameter, even one that has nothing to do with the filenames forces refresh of all the datasets. So to change a desired date range parameter means not 5 seconds of change and done, but an additional 10 minutes of reading all the fairly static historical fact tables in from the LAN.
Is there any chance we’ll be able to distinguish at some point, in order to prevent this?
This is in Power BI Desktop, right? Can you tell me how you are doing the refresh?
Power BI Desktop. What I’ve seen is that if I go to edit parameters, changing any of them, even if one not used in the file paths, forces a refresh of all the queries, including several large static files of historical data. There is no distinction as to which parameter was changed or not.
If I go instead to edit queries, and then select and change one of the parameters not involved in the source statements, and close and apply, then only the selected parameter is applied and I do not force a full 15 minute refresh of all the files.
I’m fine with that distinction, but my user base isn’t going to carefully follow that second path, and I’ll be taking a lot of flak about how awful parameters are. Long term I think it would be good to add logic to the refresh process determining which parameters changed. Short term finds me looking for an alternate way to add common filters.
OK, I see it happening too and I agree this isn’t good. I recommend you post something on the Power BI community forum; if you do, please let me know the link and I’ll try to make sure the dev team see it.
Done. https://ideas.powerbi.com/forums/360879-issues/suggestions/15046173-using-edit-parameters-forces-full-refresh-of-sou And thank you!
I’ve just tested this in the June release, and it looks it has been fixed already!
Question for you guys…
If I have a query in PowerQuery that returns a single value, can I use the value from that query to the “Parameter: Current Value”?
I have a parameter that needs to be updated annually; If I could automate this parameter, that would automate the job and be tremendously helpful. Thanks!
Query Name: [valueFiscalYearForecastBeginning]
let
Source = Oracle.Database(“GmenuProdDB:1521/gprod”, [Query=”select * from calendar#(lf)where CAL_TYPE=’D'”]),
#”Sorted Rows” = Table.Sort(Source,{{“CAL_DATE”, Order.Ascending}}),
#”Filtered Rows” = Table.SelectRows(#”Sorted Rows”, each Date.IsInCurrentDay([CAL_DATE])),
KeepColumn_FiscYear = Table.SelectColumns(#”Filtered Rows”,{“YEAR”}),
YEAR = KeepColumn_FiscYear{0}[YEAR]
in
YEAR
Parameter Name: [paramFiscalYearForecastBeginning]
2017 meta [IsParameterQuery=true, Type=”Number”, IsParameterQueryRequired=true]
I’ve just had a quick look and is seems that it is possible to get a parameter to use the result of another query as the Current Value, but you have to edit the M code manually and this breaks the UI. However, why do you need to create a parameter here? You could just reference the output of the query you already have, although it would again mean you couldn’t do all of the nice things the UI lets you do with parameters. Do you expect users to ever change the value of this parameter manually, or will it always be set to the output of the query above?
Hi Chris. Thanks for pointing out that which I somehow overlooked. True, the [valueFiscalYearForecastBeginning] query value can passed in replace of the parameter. This does provide the results I was seeking.
If the PowerQuery Parameter interface would let you assign the Value to a query result– This would allow the PQ developer to create a solution that is more transparent, I think.
Thanks for your advice!