Problem running query with dynamic parameters in direct mode

[Syndicate_Admin]

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

[v-xingshen-msft]

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.