Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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))
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
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!
I had the same problem, it was caused by a manually added stop-date (used by incremental refresh) with a non exsisting date: 31.06.2023 00:00:00
Because June only has 30 days, I changed it to 30.06.2023 00:00:00
It solved the problem.
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
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??
There is no function called Date.FixedLocalNow() which is why you get an error. DateTime.FixedLocalNow() is the name of that function.
I cant really tell whats wrong, can you post the complete query code?
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!
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))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
97 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |