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 query

1. '@parameter'
2.  '"& Parameter &"'

defining the parameter inside the nativequery, nothing has worked for me I need to pass that parameter created in power bi to the query.

 

I'd appreciate your help. I attach an example

 
example1 :
 
let
  Source = Oracle.Databases("localhost"),
  Query =Value.NativeQuery(
      Source,
    "SELECT column1,column2 FROM example1 where column1 between @parameter1 and @parameter2",
[parameter1 = #"paramter1", parameter2 = #"parameter2"])
in
Query
 
 
example 2
 
let
  Source = Oracle.Databases("localhost"),
  Query =Value.NativeQuery(
      Source,
    "SELECT column1,column2 FROM example1 where column1 between '"& Parameter1 &"' and '"& Parameter2 &"'")
in
Query
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

5 Replies

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

      unfortunately friend, I am using an oracle database, I need to use dynamic parameters in native query keeping in mind this database. If you have any additional information that can help me I would appreciate it to be able to pass parameters from power bi dynamics to native query

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        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

  • OndrejVyhnal's avatar
    OndrejVyhnal
    New Member
    1 year ago

    Thanks a lot!

    So looks like the only way to make this work on Oracle is to set every Power BI parameter as text only. 

    I was able to run the query witohur error now, which is great. But a bit woried what other problems it will bring later when integers and dates are set as text and so on...let see I continue with this advanture. 🙂

    Have a nice day!

  • 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