Forum Discussion
filter previous 2 years power query help
Hi all,
I have a table that is currently looking at data from 2021 in column TRANSACTION_DATE.
I would like to filter this in Power Query M to Transaction_Date is >= 2 years previous (which will change next year to 2024, 2025, 2026 etc), so would see 2023, 2024 and 2025 this year.
Cannot figure out how to do this within the applied steps formula.
Your help would be appreciated.
Many Thanks
Alex
Managed to find the solution....
Table.SelectRows(#"Changed Type", each [TRANSACTION_DATE] >= DateTime.Date(Date.AddYears(DateTime.LocalNow(),-2)))
Hi villa1980 ,
In power query, please add a new custom column using this M code to get the year:
Date.Year([TRANSACTION_DATE])Now in APPLIED STEPS, right click in last step and inser step after with this M code:
= Table.SelectRows(#"Added Custom", each [Custom] >= Date.Year(DateTime.LocalNow()) - 2)This will dynamically adjust each year and include data for the last two years plus the current year.
3 Replies
Hi villa1980 ,
In power query, please add a new custom column using this M code to get the year:
Date.Year([TRANSACTION_DATE])Now in APPLIED STEPS, right click in last step and inser step after with this M code:
= Table.SelectRows(#"Added Custom", each [Custom] >= Date.Year(DateTime.LocalNow()) - 2)This will dynamically adjust each year and include data for the last two years plus the current year.
Managed to find the solution....
Table.SelectRows(#"Changed Type", each [TRANSACTION_DATE] >= DateTime.Date(Date.AddYears(DateTime.LocalNow(),-2)))
Hi Bibiano_Geraldo ,
Just found this query because I need to do the same and saw the solution that you have proposed. I'd like to know what is the difference between that solution and the following one:
Table.SelectRows(FilteredYears, each Date.IsInPreviousNYears([Estimated delivery date], 2))
That is created in a step in Power Query, by going to the filter of the column "Estimated delivery date" ---> Date Filters --> Is in the previous --> 2 yearsThanks in advance!
