Help with code to pass parameter in report

[danielcarreira1]

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

[Anonymous · Accepted 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

parameters to SP or Sql query

Best Regards

[lbendlin]

[Query="execute [1test] " & Text.From(Date)]

But you likely still won't be able to run that. What date format is the SP expecting?

[danielcarreira1]

Hi Ibendlin,

It didn't worked, the SP is expecting a date format

Best Regards,

Daniel

[lbendlin]

which exact data format ? yyyy-MM-dd  ?

[danielcarreira1]

Hi Ibendlin,

The date format is dd/mm/yyyy

Best Regards,

Daniel