filter previous 2 years power query help

[villa1980]

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

[villa1980 · Accepted solution]

Managed to find the solution....

Table.SelectRows(#"Changed Type", each [TRANSACTION_DATE] >= DateTime.Date(Date.AddYears(DateTime.LocalNow(),-2)))

[J_S_A]

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 years

Thanks in advance!

[Bibiano_Geraldo · Accepted solution]

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.

[villa1980 · Accepted solution]

Managed to find the solution....

Table.SelectRows(#"Changed Type", each [TRANSACTION_DATE] >= DateTime.Date(Date.AddYears(DateTime.LocalNow(),-2)))