Forum Discussion
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...
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"
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"