Use power bi parameters in a query through native query

[Shiroe]

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

[Bryn_]

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  

[OndrejVyhnal]

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!

[amitchandak]

@Shiroe , check if this can help.

https://www.fourmoo.com/2020/10/08/power-query-m-passing-parameters-dynamically-to-a-sql-server-quer...

But this may be limited to SQL server

[Shiroe]

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 · Accepted solution]

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