Replace multiple DATE columns values with null based on a condition

[Mohan128256]

Hello All,

I have data as below where i am trying to repalce multiple(almost 40 columns) date columns values with nulls where the dates are 1900-01-01 and 2000-01-01 else it should return the dates.

I have tried the below power query but not successful.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK0NDDQNTAEIiAnEIgNlGJ1opWcgCwjAySpcJBasJQzWMoQKGWsa2QM5LjCpVzAUkbGuoa6qMY5QiSAZpnoGpiiGIdhkytEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column2", "Column5"),
    Custom3 = List.Accumulate( {"Column2","Column5"}, #"Duplicated Column", (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "1900-01-01" then null else if Text.From( Record.Field( _, a)) = "2000-01-01" then null else _, Replacer.ReplaceValue, {a} ))
in
    Custom3

It returning as below which is not right.

Anyone please correct what i am doing wrong here.

Thanks,

Mohan V.

[ThxAlot · Accepted solution]

Simple enough, no need to specify any column name.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY4xCoAwDEXvkrmBpNXBsWoP4ORQev9r+FMhtiAkw3888lMrZQqkmwiLYhAurMywhUo7UJTBu82ZoXlHRwqUOCaE4p5D886OYmLlsdWROfkFuL2wrFOnw9/fil/7fmsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type date}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,null,(x,y,z) => if List.Contains({#date(1900,1,1),#date(2000,1,1)},x) then null else x,Table.ColumnNames(Source))
in
    #"Replaced Value"

[m_dekorte]

Hi @Mohan128256 

Give this a go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK0NDDQNTAEIiAnEIgNlGJ1opWcgCwjAySpcJBasJQzWMoQKGWsa2QM5LjCpVzAUkbGuoa6qMY5QiSAZpnoGpiiGIdhkytEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column2", "Column5"),
    Custom3 = List.Accumulate( {"Column2","Column5"}, #"Duplicated Column", (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "1-1-1900" then null else if Text.From( Record.Field( _, a)) = "1-1-2000" then null else Record.Field( _, a), Replacer.ReplaceValue, {a} ))
in
    Custom3

with this result

Ps. If this helps solve your query please mark this post as Solution, thanks!

[Mohan128256]

@m_dekorte  thanks for the reply. I really appreciate your time.

I tried what you have given but i dont see any changes from my end.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK0NDDQNTAEIiAnEIgNlGJ1opWcgCwjAySpcJBasJQzWMoQKGWsa2QM5LjCpVzAUkbGuoa6qMY5QiSAZpnoGpiiGIdhkytEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column2", "Column5"),
    Custom3 = List.Accumulate( {"Column2","Column5"}, #"Duplicated Column", (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "1-1-1900" then null else if Text.From( Record.Field( _, a)) = "1-1-2000" then null else Record.Field( _, a), Replacer.ReplaceValue, {a} ))
in
    Custom3

Am i Missing anything here.

Please correct.

[m_dekorte]

Hi @Mohan128256,

Works on my end, most likely a system- or regional setting... try this instead.

List.Accumulate( {"Column2","Column5"}, #"Duplicated Column",
    (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a),
        each if Date.From( Record.Field( _, a)) = #date(1900, 1, 1) then null
        else if Date.From( Record.Field( _, a)) = #date(2000, 1, 1) then null
        else Date.From( Record.Field( _, a)), Replacer.ReplaceValue, {a} ))

Ps. If this helps solve your query please mark this post as Solution, thanks!