Show Query Parameters in your Reports

[DavidKuhry]

This is a description of how to show the current values of your Query Parameters in a Power BI report. 

Why would I want to do this? 

There are probably a number of reasons you may want to display the selected Paramters in a report page such as a "Report Information" page. For me, the reason was that our Semantic Model uses parameters for each query to chose the Server Connection and Database, as well as a paramter for limiting the number of rows to import to make refreshes easier while working in a local copy. I wanted an easy way to show that connection information in a report in the service, so that I could easily display it, without having to go into the Model settings, which only I and 1 other person has access to. 

I've looked for a solution a few times but was never able to find anything simple. I eventually found the solution and thought I'd write it up. Those who are more experienced with Power Query may find this basic, but hopefully it helps someone. 

Solution

Assuming you already have all of your query parameters set up correctly in Power Query, the steps are quite basic. 

1. Create a Blank Query. Name it as desired.
2. From Advanced Editor, you need to create at least one Column.
   1. Source = #table({"Column Name"}, {{#"Parameter Name"}})
3. One you have your first column with a Parameter, you can either continue adding them in Advanced Editor, or use the Custom Column option in the Add Column tab of Power Query. 
4. In my case, I added a total of 3 columns that hold the current values of my 3 Parameters. 
5. Once your preview is working apply and close and return to the Builder. A new table will be added to your data with all a column for each added parameter, and 1 row for the current value. 

Here is my Query

let
    Source = #table({"Selected DWH Server"}, {{#"SQL Server Connection"}}),
    #"Add Database Param" = Table.AddColumn(Source, "Selected Database", each #"Database"),
    #"Add Row Limiting Param" = Table.AddColumn(#"Add Database Param", "Row Limiting", each if #"Number of Rows (0forUnlim)" = 0 then "Unlimited" else #"Number of Rows (0forUnlim)")
in
    #"Add Row Limiting Param"

This is a simple solution that doesn't require any custom visuals like other solutions I've seen. Hopefully someone finds it helpful. 

[Anonymous]

Thank you for all your sharing!

[lbendlin]

or - you could "enable load" for the parameter(s) to convert them from an expression to a partition.

[DavidKuhry]

I did this. While it does bring them in to the report data, the text format is "long text" or somthing like that. It requires a custom visual to use, and that visual was removed from the visual app store. I found the app still hosted on the developers page, but at that point I didn't like the idea of loading a random visual. It's certainly possible I missed something, but that method didn't work for me, and this one worked great. But I'm glad you commented that for future viewers to know there are some options.