Forum Discussion

Mohan128256's avatar
Mohan128256
Helper IV
3 years ago
Solved

Replace multiple DATE columns values with null based on a condition

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 dat...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    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"

Mohan128256's avatar
Mohan128256
Helper IV
3 years ago

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's avatar
m_dekorte
Resident Rockstar
3 years ago

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!