How do I send data from excel sheet to a Power BI parameter?

Question

After some searching I found a way to create an Excel function to get parameter value upon invoking. Is there a way that the same thing could be done by passing a date from excel cell to the Power BI as a parameter value.This is for making the parameters dynamic and they should change values when the excel cell value is changed. The excel function which I am using is

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

and the excel parameter table has two columns namely 'Parameter'(this is passed to the function) and 'Value'. Can the value from the 'Value' column be used to change the parameter in the Power BI. I have already loaded this excel table into the Power BI model. Any help will be appreciated.

Jenn Jenn · Accepted Answer · 2019-05-20T07:35:29

You can pass a cell value as a parameter by loading it into Power Query. If you have a parameters table, load the table into Power Query, create a reference to the table query, go to the item that should be the parameter, right-click > drill-down, then rename it as your parameter. Then you can replace the value in your m code with the parameter. After you've done that, you can change the value of the parameter by editing the cell on the worksheet.

For example, here is a snippet of a parameter table I have that allows the location of the file and it's other data sources to be dynamic.

When you right-click and drill-down, you are left with only one record, which is now your parameter.

Then you can go to the Advanced Editor and swap out the portion of your code that needs to be variable.

This is the code for a string variable that I use. Perhaps try without the extra steps. Just drill down save and use it.

How do I send data from excel sheet to a Power BI parameter?

1 Answers