Forum Discussion
Limiting value filter to certain date range
I need to exclude certain values, but only after a certain date range. I cannot figure out how to do this in M. What I am trying to accomplish basically looks like this:
Table.SelectRows(#Table, if [Date] > xx/xx/xxxx then each [Item] <> "Excluded Value")
What is tripping me up here is the if statement requires an else and I don't know what to put there. Another way to do it I guess would be to append an unfiltered table before the date and a filtered one after, but I want to see if I can do it in one line of code.
Hi Anonymous
You can add a custom column and use the following:
if Date.IsAfter( [Date] , #date ( 2023 , 1 , 1 ) ) or not List.Contains ( {"Excluded Value"} , [Item] ) then "Keep" else "Exclude"
Just change the date based on YYYY , MM , DD values.
Hopefully this helps.
Theo
3 Replies
Hi Anonymous
You can add a custom column and use the following:
if Date.IsAfter( [Date] , #date ( 2023 , 1 , 1 ) ) or not List.Contains ( {"Excluded Value"} , [Item] ) then "Keep" else "Exclude"
Just change the date based on YYYY , MM , DD values.
Hopefully this helps.
Theo
Anonymous that is good you achieved your outcome!
If you're working with large amounts of data / records, just be conscious that creating two sub queries and then appending them is probably not the most effective approach to apply. Although it can achieve what needs to quite easily with smaller datasets, etc., as you expand and grow the size and complexity of your data model, it is far more likely to cause inefficiencies and other complications.
All the best in your BI journey and thank you for letting providing an update on the post.
If you're content with any of the responses, including your own, please feel free to accept a solution and close the post.All the best.
Theo