Swede_21
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) =>
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 :)
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 :)