Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
commited2020
Frequent Visitor

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

1 ACCEPTED SOLUTION

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))}

 

View solution in original post

3 REPLIES 3
tt_and
Helper I
Helper I

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))}

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.