cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_Norman
Helper I
Helper I

How can you create a parameters table in Power BI, similar to Power Query with fnGetParameters?

In Power Query I upload the following table and call it fnGetParameters and change the Mcode to the following and then I can invoke custom functions and change pathnames, thanks

 

Table:

Parameter             Value
Name of Model    Performance Dashboard
User                      J Norman
Date Range          06/03/2021
Today's Date        30/04/2021
Start Date             12/03/2021
Deadline               12/04/2021
Time range start    07:00
Time range end     05:00
Max hours             20:00
Midnight Trigger   00:00
Path name             C...
File name               Photo Model

 

 

 

 

 

 

M Code used for above table
(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @J_Norman 

 

In Power BI, you can click on Home > Transform data to launch the Power Query Editor, and create the fnGetParameters function similar to what you do in Excel's Power Query Editor. 

 

If you want to store the parameters in an Excel file, you can connect to it through New source > Excel and get its code like below.

let
    Source = Excel.Workbook(File.Contents("C:\.....\New Microsoft Excel Worksheet (2).xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Parameter", type text}, {"Value", type any}})
in
    #"Changed Type"

 

Then modify above code into below one to create a custom function.

(ParameterName as text) =>
let
Source = Excel.Workbook(File.Contents("C:\.....\New Microsoft Excel Worksheet (2).xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
ParamRow = Table.SelectRows(Table1_Table, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

 

Now you can invoke this function in Power Query Editor just like what you do in Excel's Power Query Editor.

 

If you want to store the parameters in other types of files or data sources, you can connect to the file first, then copy the codes before "Changed Type" step and paste them between the "let" and "ParamRow" steps in the custom function. Modify table and step names accordingly. 

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @J_Norman 

 

In Power BI, you can click on Home > Transform data to launch the Power Query Editor, and create the fnGetParameters function similar to what you do in Excel's Power Query Editor. 

 

If you want to store the parameters in an Excel file, you can connect to it through New source > Excel and get its code like below.

let
    Source = Excel.Workbook(File.Contents("C:\.....\New Microsoft Excel Worksheet (2).xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Parameter", type text}, {"Value", type any}})
in
    #"Changed Type"

 

Then modify above code into below one to create a custom function.

(ParameterName as text) =>
let
Source = Excel.Workbook(File.Contents("C:\.....\New Microsoft Excel Worksheet (2).xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
ParamRow = Table.SelectRows(Table1_Table, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

 

Now you can invoke this function in Power Query Editor just like what you do in Excel's Power Query Editor.

 

If you want to store the parameters in other types of files or data sources, you can connect to the file first, then copy the codes before "Changed Type" step and paste them between the "let" and "ParamRow" steps in the custom function. Modify table and step names accordingly. 

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

Hi V-Jingzhang,

 

Works absolutely perfectly, many thanks for taking the time out to reply. It now allows me to have all my parameters in an excel table for users. 

 

Thanks

 

Jasmin

Glad it helps. I also learn from your idea on how to use the parameters. Thank you. 

 

Jing

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors