Bulk "Replace" Multiple columns in Power Query that has date older than certain value

[lotus22]

I would like to bulk replace multiple columns in PowerQuery with dates older 1/1/2000 to Blank

Is this possible?

Date1	Date2	Date3	Date4
9/6/1993	5/6/1993	4/1/2025	8/1/2019
4/1/2019	4/1/2019	1/1/1991	4/6/1992
5/23/1992	5/23/2023	1/1/2011	5/4/1994
8/1/2022	8/1/2019	4/1/2019	9/1/2019

[v-yiruan-msft]

Hi @lotus22 ,

You can achieve it by Unpivot column and adding a custom column in Power Query Editor besides the method as suggested by @DataZoe , you can copy and paste the below applied codes in your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7LDcAwCEN34YxE+VVlFpT91yglUZveni0bkwlBJ3GEAoJ/aMQkh3jh1cgBA3P5JXBHLqwit9s3pNNOolPh5DqpK19VbtuehHV+Tonsq7+heH8ZNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t, Date3 = _t, Date4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Date1", "Date2", "Date3", "Date4"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if [Value]<#date(2000,1,1) then null else [Value]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Best Regards

[DataZoe · Accepted solution]

Edit: I realized I could do it in one step

@lotus22 You could try this in your PowerQuery:

let

fChange = (x as date) =>
let
result = if x <= #date(2000,1,1) then null else x
in
result,

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BDoAwCAT/wpkEodTI3V80PXjwB/4/Ulq1nnbYLCylwH5cJwOGytA0VKFiAaOV2KyZ+UMlJlkkO26BbJHWZ8AZ2dEXOdy4IZHOJKlP2NlPppH3VQ5bW6L/0qtE5tZfkb2/1Bs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}}),
Transform = Table.TransformColumns(#"Changed Type1",{{"Date1", each fChange( _ )},{"Date2", each fChange( _ )},{"Date3", each fChange( _ )},{"Date4", each fChange( _ )}})
in
Transform