Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Filter dates in Power Query

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!

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

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"

Vera_33_0-1628162750223.png

 

 

View solution in original post

2 REPLIES 2
Vera_33
Resident Rockstar
Resident Rockstar

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"

Vera_33_0-1628162750223.png

 

 

Anonymous
Not applicable

Thanks 🙂 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors