Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to 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"
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"
Thanks 🙂