Forum Discussion

Shiroe's avatar
Shiroe
Icon for Helper I rankHelper I
4 years ago
Solved

Use power bi parameters in a query through native query

Dear partners, Kindly request your help, I am trying to pass parameters to a query that uses native query, but it does not recognize the parameter of power bi I have tried appending it to the sql ...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Shiroe 

    You can refer to the solution proposed by kailas684  in this thread.

    Step#1: Create Parameter pID [text parameter and keep default value]

    Step#2: On Dataset that is pulled on Query Editor, right click and go to Advanced Editor. Edit below code as per your database and table name.

    let
        pID=ID,
        Source =
        Oracle.Database("your database name",
        [HierarchicalNavigation=true,
        Query="SELECT * FROM MYtableName#(lf) where DA_LOAN_ID='"&pID&"'"])   
    in
        Source

    Best Regards,
    Community Support Team _ Eason

Bryn_'s avatar
Bryn_
Regular Visitor
1 year ago

I'm late to this post. The answer from v-easonf-msft will work ok, but it's not what Shiroe was asking for and is clunky and limited for the exact reasons cited by OndrejVyhnal .

 

Value.NativeQuery works as expected on an Oracle database if you use the Oracle parameter syntax (which uses ':' to start a parameter name). For example:

 

let
    Source = Oracle.Databases("localhost"),
    Query = Value.NativeQuery(
        Source,
        "SELECT column1, column2 FROM example1 WHERE column1 BETWEEN :parameter1 AND :parameter2",
        [parameter1 = Value1, parameter2 = Value2])
in
    Query