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

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-power-query-and-power-bi/

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?