Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
danielcarreira1
Regular Visitor

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

yingyinr_0-1644902996079.png

Best Regards

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

yingyinr_0-1644902996079.png

Best Regards

lbendlin
Super User
Super User

[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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.