Forum Discussion
Help with code to pass parameter in report
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
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
5 Replies
[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 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
