cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akamiller
New Member

Filter A Query Based on Parameters

Hi All,

 

I have read all the other posts in here on using parameters in queries and they make sense.  However, in the latest version of Power BI Desktop, these do not seem to be working.  I have created by query link so:

 

let
Source = Sql.Database("Server", "Database",
[Query="
SELECT * FROM ChangeManagementAudit
WHERE CONVERT(datetime,EventDate) >= '" & StartDate & "'
"]),
#"Removed Columns" = Table.RemoveColumns(Source,{"FileName", "EventTime", "AccessType", "DomainName", "Process"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"EventDate", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Solution", "EventDate", "UserAccount", "Project"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"

 

 

The resulting error code I always get is:

Expression.Error: We cannot apply operator & to types Text and DateTime.
Details:
Operator=&
Left=
SELECT * FROM ChangeManagementAudit
WHERE CONVERT(datetime,EventDate) >= '
Right=1/1/2016 12:00:00 AM

 

 

Is there a newer method of implementing the query parameters that I'm not aware of?  All of the form based options are in the desktop either for example there is no icon next to the filter value to change it to a parameterized value.

FilterForm.PNG

 

 

Can someone help me?  

 

 

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

@akamiller

 

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.

View solution in original post

6 REPLIES 6
Eric_Zhang
Microsoft
Microsoft

@akamiller

 

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.

View solution in original post

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
SQLSource

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?

@Bibek

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.

I 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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.