View Full Version : Pass parameter into SQL statement in Power Query

09-22-2019, 08:57 AM
Hello everyone,

The situation I am facing is the following: I am attempting to retrieve data from an Oracle Database into an Excel spreadsheet by passing simple SQL statements (see below) through Power Query. This works perfectly fine. Now I want to be able to pass the value of a cell to the SQL statement(or the M-code, whichever works) and make the query parametrized. This is where I am stuck.

My progress so far: I read that it is not possible to pass cell values directly to a SQL statement but it can be achieved by using a named range and the following named blank custom function in Power Query(say called TestFunction for now)

(rangeName) =>

So with a named cell value called TestValue, its cell value is given by TestFunction(TestValue). ​This works in PQ - I can Invoke Function and get the desired value.

But how do I pass the reference in the SQL statement? I have attempted with both of these two attempts without luck.

SELECT * FROM TABLE WHERE number_ID=Excel.Workbook(File.Contents(TestFunction("TestValue")))

SELECT * FROM TABLE WHERE number_ID=Excel.CurrentWorkbook(){[Name="TestValue"]}[Content]{0}[Column1]

I appreciate all the help I can get.

Many thanks :)

Jan Karel Pieterse
09-24-2019, 06:03 AM
Can you show the entire M code of your query?