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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors