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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Shelley
Continued Contributor
Continued Contributor

Limit dateset by timeframe in Query Editor

Hi All, Man, I just don't get the M Language. Looking for help please.

I want to limit our dataset by the create date. I want everything five years backwards from today. The built-in selections don't seem to do this. The previous five years, doesn't include any of this year's data. I want today minus five years - every time the data is refreshed. I tried this:

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > (Date.AddYears(DateTime.LocalNow, -5)) as datetime)

 

I'm sure I've got something boogered up, but I'm not sure what it is. The error I'm receiving is: 

Expression.Error: The Date value must contain the Date component.
Details:
Function

 

Thanks! @ImkeF is this an easy one for you to answer?

1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

This error message usually means that you're missing a closing parenthesis 🙂

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -5) )

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Community Champion
Community Champion

Agree that this should be easier:

 

Date.AddYears(Date.From(DateTime.LocalNow()), -5)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Shelley
Continued Contributor
Continued Contributor

Thanks @ImkeF!

 

I still have something wrong. Here's the whole advanced editor window. Now I'm receiving a different error:

Expression.SyntaxError: Token Comma expected.

 

I don't see where there should be a comma. Do I still have something written wrong in the code?

 

let
Source = Sql.Database("csmdataservice.cloudapp.net", "csm_master"),
dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
#"Added Custom" = Table.AddColumn(dbo_RepairTransaction, "Custom", each if Text.Start([Contract_Number],1)="0" then Text.TrimStart([Contract_Number],"0") else [Contract_Number]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Contract_Number"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Contract_Number"}}),
#"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns1", "Key", each Text.Combine({[End_Customer_Key], [Contract_Number]}, "|"), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"List_Price", Currency.Type}, {"Net_price", Currency.Type}, {"Calculated_List_Price", Currency.Type}, {"Unit_Received_Date", type date}, {"Order_Submit_Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -5)
in
#"Filtered Rows"

 

Thanks! You're awesome.

 

ImkeF
Community Champion
Community Champion

This error message usually means that you're missing a closing parenthesis 🙂

 

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Order_Submit_Date] > Date.AddYears(Date.From(DateTime.LocalNow()), -5) )

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Shelley
Continued Contributor
Continued Contributor

Oh gee! I was checking for that and just couldn't see it. Thank you!

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.