Replacing Values based upon a second date value

[Anonymous]

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

[Anonymous · Accepted solution]

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.