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) => 
                         Excel.CurrentWorkBook(){[Name=rangeName]}[Content]{0}[Column1]

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