Consulting

Results 1 to 2 of 2

Thread: Pass parameter into SQL statement in Power Query

  1. #1
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    2
    Location

    Pass parameter into SQL statement in Power Query

    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


  2. #2
    Can you show the entire M code of your query?
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •