Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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
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
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.
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
Oh gee! I was checking for that and just couldn't see it. Thank you!
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |