Forum Discussion
Solved
Change all dates in column to null
Hi all, hope you can help! I need to remove all dates within the below column. The idea is, that if I can remove the dates I can then pivot the column out to produce new columns for each text str...
Use this as your next step to replace date with null
Table.ReplaceValue(Source, each [WFM Activity], each if (Value.Is([a = Text.Split([WFM Activity],"/"), d = #date(Number.From(a{2}), Number.From(a{1}), Number.From(a{0}))][d], type date)) then null else [WFM Activity], Replacer.ReplaceValue, {"WFM Activity"})Hi Buckley, what about this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorVgRIGxvoGhvpGBkYmYG5IamKuglNRZmoaRNYERdYltSw1J78gNzWvRCEkMzcVLOibmlqSmZcOZhuaItTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WFM Activity" = _t]), Replace = Table.ReplaceValue(Source, null, null, (x,y,z)=> if (try Date.From(x, "sk-SK") otherwise x) is date or Text.Trim(x) = "" then null else x, //you can delete or Text.Trim(x) = "" if you want to replace only dates {"WFM Activity"} ) in Replace
Hi Buckley, what about this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorVgRIGxvoGhvpGBkYmYG5IamKuglNRZmoaRNYERdYltSw1J78gNzWvRCEkMzcVLOibmlqSmZcOZhuaItTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WFM Activity" = _t]),
Replace = Table.ReplaceValue(Source,
null,
null,
(x,y,z)=> if (try Date.From(x, "sk-SK") otherwise x) is date or Text.Trim(x) = "" then null else x, //you can delete or Text.Trim(x) = "" if you want to replace only dates
{"WFM Activity"} )
in
Replace