Pass a parameter from an Excel cell into a native SQL Query

[ArjanVos]

Hi all,

I'm trying to pass an Excel cell value as a parameter to a native query (on a Progress database in Power Query in Excel).

I know how to pass an Excel cell value as a parameter to filter a table within Power Query. But we are working with big datasets, so I would like to apply the filter before the data is loaded. In this simple example I would like to replace ib240.cdstatus = ‘O’ by ib240.cdstatus = MyParameter.

let
    Source = Odbc.Query("dsn=Extended", "Select#(lf)    ib240.cdorder,#(lf)    ib240.cdstatus#(lf)From#(lf)    PUB.""ib-240"" ib240#(lf)Where#(lf)    ib240.cdstatus = 'O'")
in
    Source

I have tried using this example https://blog.crossjoin.co.uk/2016/12/11/passing-parameters-to-sql-queries-with-value-nativequery-in-...

My first step was to get this to work without using my parameter, like this:

let
    Source = Odbc.Query("dsn=Extended", "data"),
    Test = Value.NativeQuery(
        Source,
        "Select
            ib240.cdorder,
            ib240.cdstatus
        From
            PUB."ib-240" ib240
            Where
            ib240.cdstatus = 'O'
        ")
in
    Test

But then I'm getting an error:

Can someone help me out here?

[Jimmy801]

Hello @ArjanVos 

You can add your parameter like this

let
    Source = Odbc.Query("dsn=Extended", "Select#(lf)    ib240.cdorder,#(lf)    ib240.cdstatus#(lf)From#(lf)    PUB.""ib-240"" ib240#(lf)Where#(lf)    ib240.cdstatus = '" & MyParameter & "'")
in
    Source

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ArjanVos]

Hi Jimmy, almost a yea further now... Your solution seems to work only for parameters that I have defined within Power Quer but not for parameters that come from a cell value like in this link: https://exceleratorbi.com.au/pass-excel-parameter-power-query/

It then says this (in Dutch):

Freely translated: Query <Name> (step Source) refers to other queries or steps and therefore possibly doesn't have access to a data source. Make this data combination again.

Can we get this to work, so that we can make it as easy as possible for the end user to enter the parameters?

[V-lianl-msft]

Hi @ArjanVos ,

Try checking the symbol in the table name on line 9 according to the error message.

PUB.""ib-240"" ib240

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.