The M Code Behind Power BI Parameters

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:

image

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:

image

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:

"Monday" 
meta 
[
IsParameterQuery=true, 
List={"Monday", "Tuesday", "Wednesday", 
"Thursday", "Friday", "Saturday", "Sunday"}, 
DefaultValue="Monday", 
Type="Text", 
IsParameterQueryRequired=true
]

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:

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"

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.

17 thoughts on “The M Code Behind Power BI Parameters

  1. 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.

  2. 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. 🙂

  3. 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.

  4. 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?

      • 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.

  5. 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s