Replacing Values based upon a second date value

Anonymous — Fri, 10 Jun 2022 11:38:55 GMT

I have similar posts in this forum, but cannot make them work for my query.

I have columns with data which I want to make blank or null if a date value in another column (Service End Date) is older than 6 years (Today - 2190 is fine). I have messed around trying to make any date work (less than today) but no luck. I need to repeat this across multiple columns, so don't know if I can replace these all in 1 step, or whether it needs to be repeated 1 step per column. Can anyone help?

Staff Summary Person ID	First Name	Last Name	Service End Date
3000001	Bob	Jones	null
1065563	Steve	Williams	null
4536300	Tracey	Smith	5/31/2013, 12:00:00 AM
5685276	David	Patel	8/31/2022, 1:00:00 AM
1009361	Sarah	Thomas	5/31/2019, 12:00:00 AM

Re: Replacing Values based upon a second date value

Anonymous — Wed, 15 Jun 2022 06:20:54 GMT

Hi Anonymous ,

I have built a data sample for test:

1.Select all date/time columns and choose "Unpivot columns".

2. Add a new custom column, and delete the Value column

if [Value]< Date.AddYears( DateTime.LocalNow(),-6) then null else [Value]

3. Select Attribute column, choose "Pivot column":

Here is the whole M syntax:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBCoMwEER/pew5kGxiovbW0lOhUFDoQTykbcBAVFAr9O+78WRbQ8gcdt5kkqoCJeJCYHDs73Se+86NpN0rBJKEY8qlQLlDuReC9u5wgZpVgMJobRR5isnNjvTmQ/C2XdGGS7VNJ1oZupo85WAf7h1jWj81pJorjJBiXxSDjGMeB8lfmjaZlqkhz8nO/kl6tZMLC7OESUlh6yxEjjoOxMbDRK5M/JHCDjY2Kpu+teOqWv5bDer6Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Staff Summary Person ID" = _t, #"First Name" = _t, #"Last Name" = _t, #"Service End Date" = _t, #"Date 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Staff Summary Person ID", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Service End Date", type datetime}, {"Date 2", type datetime}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Staff Summary Person ID", "First Name", "Last Name"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value]< Date.AddYears( DateTime.LocalNow(),-6) then null else [Value]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Staff Summary Person ID", Int64.Type}, {"First Name", type text}, {"Last Name", type text}, {"Date 2", type datetime}, {"Service End Date", type datetime}}) in #"Changed Type1"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Replacing Values based upon a second date value in Power Query

Replacing Values based upon a second date value

Re: Replacing Values based upon a second date value