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

microsoft excelodbcparameterspower-query

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

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

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

Is this possible to do ?

Best Answer

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

Related Question