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.
Hi all,I have made a date parameter for a t-sql stored procedure and tried to insert the name of the parameter on the code, but it doesn't work, see below code from advanced editor and also the error message, appreciate if someone can tell me what i am doing wrong.
let
Source = Sql.Database("sap", "entie", [Query="execute [1test] "&Date]),
#"Extracted Date" = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB")[Date]), "Date", "Balance", List.Sum)
in
#"Pivoted Column"
ERROR MESSAGE:
Expression.Error: We cannot apply operator & to types Text and Date.
Details:
Operator=&
Left=execute [1test]
Right=01/01/2020
Best Regards,
Daniel
Solved! Go to Solution.
Hi @danielcarreira1 ,
Please update the applied codes in your Advanced Editor as below, the part with red font is updated one. Please note that the Date with blue font is date parameter name....
let Source = Sql.Database("sap", "entie", [Query="exec [1test] '"& Date.ToText(Date) & "' #(lf)#(lf)#(lf) #(lf) "]) , #"Extracted Date" = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB")[Date]), "Date", "Balance", List.Sum) in #"Pivoted Column" |
In addition, you can also refer the following links to update the codes...
Execute SQL Server Stored Procedure With User Parameter In Power BI
Best Regards
Hi @danielcarreira1 ,
Please update the applied codes in your Advanced Editor as below, the part with red font is updated one. Please note that the Date with blue font is date parameter name....
let Source = Sql.Database("sap", "entie", [Query="exec [1test] '"& Date.ToText(Date) & "' #(lf)#(lf)#(lf) #(lf) "]) , #"Extracted Date" = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB")[Date]), "Date", "Balance", List.Sum) in #"Pivoted Column" |
In addition, you can also refer the following links to update the codes...
Execute SQL Server Stored Procedure With User Parameter In Power BI
Best Regards
[Query="execute [1test] " & Text.From(Date)]
But you likely still won't be able to run that. What date format is the SP expecting?
Hi Ibendlin,
It didn't worked, the SP is expecting a date format
Best Regards,
Daniel
which exact data format ? yyyy-MM-dd ?
Hi Ibendlin,
The date format is dd/mm/yyyy
Best Regards,
Daniel
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
6 | |
6 | |
3 | |
2 | |
2 |