Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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.
User | Count |
---|---|
73 | |
72 | |
39 | |
25 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
43 | |
42 |