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

[Anonymous]

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

[v-jingzhang · Accepted solution]

Hi @Anonymous 

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.

[Anonymous]

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

[v-jingzhang]

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

Jing