Forum Discussion
Filter A Query Based on Parameters
The report is as the error says "Expression.Error: We cannot apply operator & to types Text and DateTime."
Change the StartDate to text rather than date/time. Then it works in my test. Regarding date type conversion, in many databases, a formated string "20160804" or 2016-08-04" can be implicitly converted to date correctly. No worry about the conversion.
The report is as the error says "Expression.Error: We cannot apply operator & to types Text and DateTime."
Change the StartDate to text rather than date/time. Then it works in my test. Regarding date type conversion, in many databases, a formated string "20160804" or 2016-08-04" can be implicitly converted to date correctly. No worry about the conversion.
Thank you, I made these changes
I the database stored query leave variable as date and i power BI query editor put variables as text after that working properly
let
SQLSource = (Param1 as text, Param2 as text) =>
let
param1 = Date.ToText(Param1, "MM")&"/"&Date.ToText(Param1, "dd")&"/"&Date.ToText(Param1, "yyyy"),
param2 = Date.ToText(Param2, "MM")&"/"&Date.ToText(Param2, "dd")&"/"&Date.ToText(Param2, "yyyy"),
Source = Sql.Database("10.58.211.97,49461", "PPL_KPI", [Query="EXEC [PPL_KPI].[dbo].[Completed_kpi] @init_date = '"& Param1 &"', @final_date = '" & Param2 & "'"])
in
Source
in
SQLSourceI will try that. I guess the part that threw me off was that the LEFT side was my entire SELECT query up to the & and not just the EventDate.
- Hi Eric_Zhang
I'm trying to pass date as parameters and have changed it to text. However, when i pass the value like 4/4/2017 , I'm getting incorrect syntax near '/' . Could you kindly help?
Bibek wrote:
Hi Eric_Zhang
I'm trying to pass date as parameters and have changed it to text. However, when i pass the value like 4/4/2017 , I'm getting incorrect syntax near '/' . Could you kindly help?Since this thread is old and closed, for your question, could you please raise a new thread?
Eric_Zhang No issues. I have found the solution. However, I have another query for which I'll raise a new thread.
