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 ranges, sent environment variables, etc.  Some of these parameters are often in common across the Power BI files, but not always.  

Additionally, I sometimes need to view the parameters for DAX measures and need to Enable Load within Power Query.  This ends up producing a single column, single row table inside PBI Desktop for each parameter.  

 

OPTIMISTIC SOLUTION OUT THERE?
It seems possible to me to have Power Query dynamically create one table of Parameter and Value columns that would have a row for each parameter.  This would flatten the parameter queries that I need to load into one table for DAX to reference.

Does anyone have any suggestions that could help me towards my goal of simplifying the parameters with one Parameters table?

Thanks in advance for your thoughts and assistance!

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.

6 Replies

  • rmsolfest's avatar
    rmsolfest
    Advocate II
    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.