Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rmsolfest
Advocate I
Advocate I

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!

1 ACCEPTED SOLUTION
rmsolfest
Advocate I
Advocate I

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.

View solution in original post

6 REPLIES 6
rmsolfest
Advocate I
Advocate I

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.

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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" )

 

 

 

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

Alex_T_0-1644418746965.png

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

Alex_T_1-1644418837681.png

(_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")...

Alex_T_2-1644419244920.png

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 🙂

 

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

Hi @Daryl-Lynch-Bzy , 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 🙂

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors