Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago

Problem running query with dynamic parameters in direct mode

Good morning

I'm using Power BI Desktop, version 2.136.1478.0 64-bit (September 2024). I perform a direct query of a stored procedure that receives a parameter:

let
Source = Sql.Database(Servidor, Base),
Periodo = 110,
Data = Value.NativeQuery(Source, "PA_XXXXXXXX " & Text.Combine({"'",Number.ToText(Periodo),"'"}) & "")
in
Data

The query runs smoothly, but when I try Apply Changes and Close, it won't let me because of a syntax error

Microsoft SQL: Incorrect syntax near '110'.

I will appreciate guidance in this regard. Best regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    HI Syndicate_Admin ,
    Power BI's stricter parsing of Value.NativeQuery parameters in DirectQuery mode may result in incorrectly formatted SQL statements being passed, for example:
    (1) There may be a problem with the SQL syntax during parameter splicing (e.g., redundant or missing quotes).
    (2) The parameter passing format after the procedure name PA_XXXXXXXXXX does not conform to the standard procedure calling format of SQL Server.

     

    let
    Source = Sql.Database(Servidor, Base),
    Periodo = 110,
    Query = "EXEC PA_XXXXXXXX @Period = " & Number.ToText(Periodo),
    Data = Value.NativeQuery(Source, Query)
in
    Data

     

    If you need to adjust parameters dynamically, you can add query parameters in Power BI and pass the parameter values to the M code.

     

    let
    Source = Sql.Database(Servidor, Base),
    Query = "EXEC PA_XXXXXXXX @Period = " & Number.ToText(PeriodParameter),
    Data = Value.NativeQuery(Source, Query)
in
    Data

     

    By adjusting the procedure call format and M-code, the problem should be solved. If you still have problems, you can provide more detailed information.
    Value.NativeQuery - PowerQuery M | Microsoft Learn

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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