Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.