Power Query Parameters Selected Value in a Table / Report View

[Emma_]

Hi everyone,

I am using a parameter in Power BI to control from which data source I load the data before loading it into the model (for example: Source Environement Preprod vs Prod). This works fine for loading, but I would like the selected parameter value to be visible in the report view so that testers can always see where the data is coming from.

In other words:

• I choose the data source via a parameter in Power Query.
• I then load the data into the model.
• I want to display somewhere in the report which data source was selected in that parameter when the data was last refreshed.

Is there a way to:

• Transform the selected parameter value into a column or table that is loaded into the data model or
• Otherwise make the parameter value available in a measure or table so it can be shown on the report?

Any examples (Power Query M or DAX) or best practices to achieve this would be appreciated.

Thank you.

[Emma_]

It is really amazing, thank you so much for your quick response and a great solution !

[jennratten]

You are very welcome!

[rohit1991]

Hii @Emma_ 

The selected Power Query parameter value can be exposed in the report by creating a small query that returns the parameter as a table and loading it into the model. In Power Query, create a new blank query and use M like:

= #table({"Environment"}, {{ParameterName}})

Load this query to the model and create a card, table, or measure in the report to display it. After every refresh, the table will show the currently selected parameter value (e.g., Preprod or Prod), allowing users to clearly see which data source the report was loaded from.

check the link below:-

https://learn.microsoft.com/en-us/power-query/power-query-query-parameters 

[jennratten · Accepted solution]

Hello @Emma_ - thanks for posting in the Fabric Community.  

Below is one way you can get the outcome you are expecting.  Please let me know if you have any questions.

In this example I am creating a table which displays the email addresses from the parameter paramTesterEmails split into separate rows (one per email), with two additonal columns populated by the parameters paramTesterRoleId and paramClientEntityId.

Parameters in Power Query:

Create a new function:

For your use case you can simply replace the parameter names with your real parameter names as well as what you'd like your columns to be named.

let
    fn = ( ) =>
let
    Source = Text.Split(paramTesterEmails,","),
    ListToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"UPN"}, null, ExtraValues.Error),
    ChangeTypes = Table.TransformColumnTypes(ListToTable,{{"UPN", type text}}),
    AddId = Table.AddColumn(ChangeTypes, "TesterRoleId", each paramTesterRoleId, Int64.Type),
    ClientEntityId = Table.AddColumn ( AddId, "ClientEntityId", each paramClientEntityId, Int64.Type )
in
    ClientEntityId
in
    fn

 Then invoke the function to produce the table:

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User