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

Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime" - please help

I have the following M code in Query Editor from power BI:

 

let
    Source = Sql.Database("DB", "DB", [CommandTimeout=#duration(0, 1, 0, 0)]),
    dbo_Calls = Source{[Schema="dbo",Item="Calls"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Calls,{{"Completion Date", type date}}),
    EndDate = Date.From(DateTime.FixedLocalNow()),
    StartDate = Date.AddDays(Date.AddMonths(EndDate,-6),1),
    #"Filtered Rows" = Table.SelectRows(dbo_Calls,each [Completion Date]>=StartDate 
    and [Completion Date]<=EndDate)

in
    #"Filtered Rows"

However this results in the below error:

 

Expression.Error: We cannot apply operator < to types Date and DateTime.
Details:
    Operator=<
    Left=26/12/2018
    Right=16/05/2017 00:00:00

Any ideas how I can get around this?

 

Many thanks for all help

3 ACCEPTED SOLUTIONS

Accepted Solutions
Gordonlilj Established Member
Established Member

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&qu

Hi,

 

You could either change the datetime column to date or the other way around.

 

Or you could try adding DateTime.From() to the date columns.

I don't know which one is the datetime columns so i added the function it to all of them.

#"Filtered Rows" = Table.SelectRows(dbo_ANSAPBICalls,each DateTime.From([Completion Date])>=DateTime.From(StartDate) 
    and DateTime.From([Completion Date])<=DateTime.From(EndDate))

View solution in original post

tex628 New Contributor
New Contributor

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&qu

You are comparing Date format with Datetime format. Change the format from datetime to date on your start and enddate before the filterstatement and it should work fine!

/J

View solution in original post

ansa_naz Member
Member

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&am

Its fixed now. I changed all Date functions to DateTime. I also amended the Filtered Rows step so it was using SelectRows from #ChangedType step, not from the original data source:

 

let
    Source = Sql.Database("DB", "DB", [CommandTimeout=#duration(0, 1, 0, 0)]),
    dbo_Calls = Source{[Schema="dbo",Item="Calls"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Calls,{{"Completion Date", type date}}),
    EndDate = Date.From(DateTime.FixedLocalNow()),
    StartDate = Date.AddDays(Date.AddMonths(EndDate,-6),1),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type",each DateTime.From([Completion Date])>=DateTime.From(StartDate) 
    and DateTime.From([Completion Date])<=DateTime.From(EndDate))
in
    #"Filtered Rows"

Thats done the trick, cheers for all the help!

 

View solution in original post

6 REPLIES 6
Gordonlilj Established Member
Established Member

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&qu

Hi,

 

You could either change the datetime column to date or the other way around.

 

Or you could try adding DateTime.From() to the date columns.

I don't know which one is the datetime columns so i added the function it to all of them.

#"Filtered Rows" = Table.SelectRows(dbo_ANSAPBICalls,each DateTime.From([Completion Date])>=DateTime.From(StartDate) 
    and DateTime.From([Completion Date])<=DateTime.From(EndDate))

View solution in original post

tex628 New Contributor
New Contributor

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&qu

You are comparing Date format with Datetime format. Change the format from datetime to date on your start and enddate before the filterstatement and it should work fine!

/J

View solution in original post

ansa_naz Member
Member

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&am

Thank you both

I have amended my M code to below:

 

EndDate = Date.From(Date.FixedLocalNow()),
StartDate = Date.AddDays(Date.AddMonths(EndDate,-6),1),
#"Filtered Rows" = Table.SelectRows(dbo_Calls,each Date.From([Completion Date])>=Date.From(StartDate) 
and Date.From([Completion Date])<=Date.From(EndDate))

However I now get below error:

 

Expression.Error: The name 'Date.FixedLocalNow' wasn't recognized.  Make sure it's spelled correctly.

Am I doing something daft here??

 

 

tex628 New Contributor
New Contributor

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&am

I cant really tell whats wrong, can you post the complete query code?

Gordonlilj Established Member
Established Member

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&am

There is no function called Date.FixedLocalNow() which is why you get an error. DateTime.FixedLocalNow() is the name of that function.

ansa_naz Member
Member

Re: Keep getting "Expression.Error: We cannot apply operator < to types Date and DateTime&am

Its fixed now. I changed all Date functions to DateTime. I also amended the Filtered Rows step so it was using SelectRows from #ChangedType step, not from the original data source:

 

let
    Source = Sql.Database("DB", "DB", [CommandTimeout=#duration(0, 1, 0, 0)]),
    dbo_Calls = Source{[Schema="dbo",Item="Calls"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Calls,{{"Completion Date", type date}}),
    EndDate = Date.From(DateTime.FixedLocalNow()),
    StartDate = Date.AddDays(Date.AddMonths(EndDate,-6),1),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type",each DateTime.From([Completion Date])>=DateTime.From(StartDate) 
    and DateTime.From([Completion Date])<=DateTime.From(EndDate))
in
    #"Filtered Rows"

Thats done the trick, cheers for all the help!

 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)