5

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 ?

4 Answers 4

4

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: Creating Table from Range
  2. Make sure that your cursor is within the table, on the "Data" tab, click "From Table/Range" Using Table as a Data Source for Power Query
  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". Pivoting the parameters
  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" Creating reference from an existing query
  5. Right click on the value of your parameter and click "Drilldown" Drilling down to a specific value
  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

1
  • 1
    PowerQuery is so difficult to do such simple things. I am still using MS Query. It works.
    – Fandango68
    Commented Oct 29, 2019 at 5:08
3

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: Creating Table from Range
  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/

2
  • 1
    You need to concatenate the query text and the function call for this to work: Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '" & fnGetParameter("STN1") & "'") Commented Dec 17, 2021 at 16:38
  • Fixed, thanks! Guess I figured it's like PHP in which you can use "a string with a $parameter inside it".
    – LWC
    Commented Dec 18, 2021 at 10:59
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:
Exemple of the parameter table

Example of the query edited in the advanced editor:
Example of the query edited in the advanced editor

0

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.

enter image description here

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .