Use a Cell Value for a Paramater Value using in Power Query

Question

I am using a query like below in power query Excel 2016:

let
   Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '03'  ")
in
    Source

I want to replace '03' with a value form cell AD2

Is this possible to do ?

Answer 1

I think there's a better and friendlier approach to both other answers, which is both dynamic and doesn't create extra entries other than the parameters themselves:

In Excel:

1. Create a table of parameters:
2. Go to the Table Tools tab and rename the table to Parameters.

Then in Power Query:

1. Choose From Other Sources => Blank Query
2. Go to View => Advanced Editor
3. Replace all the code with the following:

 

(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

Finally, click Done and rename the function to fnGetParameter.

From then on you can use it - in this specific case fnGetParameter("STN1") - anywhere like:

let
   Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '" & fnGetParameter("STN1") & "'")
in
    Source

Reference: https://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

Answer 2

Rajesh S' answer can already satisfy your requirement. However, the weakness on his answer is that your parameter is dependent on its location on the table. I am suggesting a better solution:

1. Just as Rajesh suggested, create a table with proper headers:
2. Make sure that your cursor is within the table, on the "Data" tab, click "From Table/Range"
3. Right now, you'll be on the power query editor. First make sure that the "Values" column is of "Text" data type. Next, Click the "Parameter Name" column, and Under the "Transform" tab click "Pivot Column". Use the "Value" column as Values. make sure that you expand the "Advanced options" tab and select "Don't Aggregate" as "Aggregate Value Function".
4. At this point, you will now have different columns with the parameter name as the column name. Right click the "Parameters" query and click "Reference"
5. Right click on the value of your parameter and click "Drilldown"
6. You will now have a Query that can be used as a variable from your sheet. go ahead and use it in your ODBC Query:

let Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '"&STN1"' ") in Source

I know my steps looks tedious, but I am very forgetful so I need use descriptive variable names to easily remember what my Power Query Does. You can also do a "Change Type" step after you pivoted the parameters if you want to use cell values for calculations with other queries. Here is my reference

Answer 3

This is now much easier to do with newest version of Excel (O365): you can simplify declare a variable in the PowerQuery request using advanced editor, indicate that the variable value has to be fetched from an Excel table, then refer to the variables in the following steps.

For example : myVar = Excel.CurrentWorkbook(){[Name="MyParameterTable"]}[Content]{0}[MyColumnName]

So using a parameter table having key / value columns, here is how I would make dynamic the directory and file name of the .csv file to be imported

let
    directy = Excel.CurrentWorkbook(){[Name="Param"]}[Content]{0}[Value],
    newDirectory = if Text.EndsWith(directy,"\") then directy else directy&"\",
    filePath = Excel.CurrentWorkbook(){[Name="Param"]}[Content]{1}[Value],
    Source = Csv.Document(File.Contents(newDirectory&filePath),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

Example of the parameter table:

Example of the query edited in the advanced editor:

Answer 4

No, directly you can't use the cell reference. What you need to do is to create a separate Data Range and convert it to Table.

Now convert this Range into Table and assign a proper name like ParaTAB or ParameterTAB, then you command should be like this,

let
   Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 =  ParaTAB[Value]{2}  ")
in
    Source

Here Power Query will pick the third value or third row which is ADM, since it counts from 0.

NB: Remember B6 and A2 are cell references.