Filter dates in Power Query

[Anonymous]

Hello,

Before I load my data, I want to transform and filter my dataset based on a condition between two date columns. I have a datecolumn A, and I want to filter my dataset knowing that datecolumn A can't go any further than 2 years after today's date.

Example 1:

Datecolumn A  | Datecolumn B (Today)  

23/04/2019      | 05/08/2021          

This is valid because 23/04/2019 is a date before 05/08/2023 (Today + 2 years)

Example 2:

Datecolumn A  | Datecolumn B (Today)  

23/04/2025      |  05/08/2021

This is what I want to filter and remove in Power Query because 23/04/2025 is two years after today.

Any help? Thank you!

[Vera_33 · Accepted solution]

Hi @Anonymous 

You are using the date from ColumnB or a fixed Today? Here is one from ColumnB

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLWNzDRNzIwtDy0QAGClHSUDEz1DSyAokaGcFEwOrRAKVYHocnIlFhNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <= Date.AddYears( [Column2] ,2)))
in
    #"Filtered Rows"

[Anonymous]

Thanks 🙂