Calling System-i Stored Procedure as Direct Query

[chinma]

Hello Team,

We are tyring to call DB2 (System-i) stored procedure as Direct Query in Power BI Query Editor but not sure how to use dynamic parameters.  Here is the snapshot of the error message we are getting:

Any help would be appreciated.

Thank you.

[BA_Pete]

Hi @chinma ,

Not sure if this will solve your problem exactly as I don't know whether this works for calling Stored Procs, but here's how to use dynamic parameters against a DB2 source:

-1- Create parameters in Power Query/Dataflow. Lets call them 'Parameter1' and Parameter2'.

-2- Add parameter placeholders to source code:

let
    Source = DB2.Database("SERVER", "DATABASE"),
    NativeQuery = Value.NativeQuery(
        Source,
        "SELECT Items, Values
        FROM Table
        WHERE
            Field1 <= ?  -- Parameter1 placeholder
            AND Field2 > ?  -- Parameter2 placeholder",
        {Parameter1, Parameter2}  -- List of Parameter names in the order used in SELECT statement
    )
in
    NativeQuery

Now, when you run the DB2 query, the parameter values will be picked up and inserted into the DB2 query before sending to the source.

Pete