Forum Discussion

rmsolfest's avatar
rmsolfest
Advocate II
4 years ago
Solved

PBI Power Query suggestions: Create one table of all Power Query parameters

Idea:  Want to create one table of all Power Query parameters dynamically POWER BI FILES:  I have multiple reports in which I use Power Query parameters to control the datasources, filter date ra...
Tips and Tricks
  • rmsolfest's avatar
    rmsolfest
    4 years ago

    MY RESULTING SOLUTION:

    I decided to hardcode the parameters and there values into a Power Query Parameters table using sample code below.

     

    let
    // Consolidated table of parameter values (type text) for use in DAX measures or PBI visualizations
    Source = #table(
        type table
            [
                #"ParameterName"=text,
                #"Value_text"=text
            ], 
        {
            {"p_ParameterName1", p_ParameterName1},
            {"p_ParameterName2", p_ParameterName2}, 
            {"p_ParameterName3", p_ParameterName3}, 
            ...
            {"p_ParameterNameN", p_ParameterNameN}  
        }
    )
in
    Source


    Although the ParameterNames are hardcoded for inclusion on each row of the table, their values are automatically updated when the parameter values are updated in PBI Desktop or on the Service.

     

    Thanks all responders above for your thoughtful ideas.  Even though I decided to go another way, these ideas were helpful for me to explore the possibilities and others still may use them in their solutions.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi rmsolfest - you could consider the following Power Query function as an example to load you data model, or use a DAX based option if possible.

"Parameter Text " & #"parameter 1" & "#(lf)" &
"Parameter Value " & Text.From( #"parameter 2" ) & "#(lf)" &
"Parameter Date " & Date.ToText( #"parameter 3" , "dd-MMM-yyy" )

 

 

 

Alex_T's avatar
Alex_T
Frequent Visitor
4 years ago

Hi there,

 

I don't know if this helps but starting off from a parameter table, in this case for demo it was created with "Enter Data" but could be anything else i.e. xlsx, csv ...

Create a function to simplify getting a value out of it (a little less typing!)...

(_parameter as text) as any =>
let
    Source = Parameters{[Parameter=_parameter]}[Value]
in
    Source

Invoke the function for each parameter (and rename it from "Invoked Function")...

This is a bit of a pain as this will need to be done for each parameter required within PowerQuery. You could consider substituting the function call e.g. "fnGetParameter("schema")" wherever the parameter is required. You will also need to consider data types as they are all text, and may need conversion.

 

These are also not true parameters, in as much as they won't be available within the Transform Data > Edit Parameters menu option to change or be usable within a deployment pipeline but you will have them to apply within PowerQuery. The table can be maintained outside the report and changes reflected anywhere the table is used.

 

So this may not be a full solution but may give you further ideas. Good luck 🙂

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Alex_T - I like this example, but while this approach will help to display the parameters in the Power BI Report, it will change how the paramaters are maintained.  This table of parameters is not Power BI parameters.  They can't be updated before refreshing the Power BI Dataset.  In this situations the paramaters are Imported from another centralised database or sharepoint list.

    • Alex_T's avatar
      Alex_T
      Frequent Visitor
      4 years ago

      Hi Anonymous , yes I don't disagree! I do say they are not true parameters and my suggestion does come with its own set of disadvantages but unfortunately I'm not aware of any other way to manage values as a table.

       

      I've had a brief look at the suggestions, there doesn't appear to be one about better parameter management, the ability to manage as a table or import/export parameters into a central repository would be rather nice. I might add this one unless yourself or rmsolfest beats me to it 🙂

      • rmsolfest's avatar
        rmsolfest
        Advocate II
        4 years ago

        Please let me know if you add a suggested idea.  I am planning to explore these options a little more.  I will share any results or ideas that I submit.

        Thanks all past and future contributors on this topic!