Forum Discussion
Date Error in M query editor
Hello i keep getting this message:
Expression.Error: We cannot apply operator - to types DateTime and Date.
Details:
Operator=-
Left=1/1/2024 12:00:00 πμ
Right=1/1/2016
Here is the code at M query editor
let
StartDate = StartDate,
EndDate = Today,
Today = Date.EndOfYear(DateTime.LocalNow()),
Duration = Duration.Days(Duration.From(Today-StartDate))+1,
Dates = List.Dates(StartDate,Duration,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterNo", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter", each "Q"&Text.From([QuarterNo])),
#"Inserted Month" = Table.AddColumn(#"Added Custom", "MonthNo", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type)
in
#"Inserted Day"
Thank u in advance
Yes, the answer is in this line which is dynamic for both start and end (one year back in time (-1), and end of this year:
Source = {Number.From(#date(Date.Year(DateTime.LocalNow()) - 1,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}You can set it relative to both start or end (-1 is -1 year).
Or with fixed start and dynamic end:Source = {Number.From(#date(2016,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}Or with fixed start and end:
Source = {Number.From(#date(2016,01,01))..Number.From(#date(2023,12,31))}
3 Replies
Try this code:
let Source = {Number.From(#date(Date.Year(DateTime.LocalNow()) - 1,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}, Convert =Table.FromList(Source,Splitter.SplitByNothing(),null,null,ExtraValues.Error), #"Converted to Table" =Table.TransformColumnTypes(Convert,{{"Column1",type date}}), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterNo", each Date.QuarterOfYear([Date]), Int64.Type), #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter", each "Q"&Text.From([QuarterNo])), #"Inserted Month" = Table.AddColumn(#"Added Custom", "MonthNo", each Date.Month([Date]), Int64.Type), #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text), #"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type) in #"Inserted Day"First of all thank u for your response the code worked but the start date is from "1/1/2022" and i want it to start from "1/1/2016".
Any ideas?
Thank you again!
Yes, the answer is in this line which is dynamic for both start and end (one year back in time (-1), and end of this year:
Source = {Number.From(#date(Date.Year(DateTime.LocalNow()) - 1,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}You can set it relative to both start or end (-1 is -1 year).
Or with fixed start and dynamic end:Source = {Number.From(#date(2016,01,01))..Number.From(#date(Date.Year(DateTime.LocalNow()),12,31))}Or with fixed start and end:
Source = {Number.From(#date(2016,01,01))..Number.From(#date(2023,12,31))}
