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 string.
I tired to replace values using the wild card **/**/*** & ??/??/???? but with no effect. I've also tried this while the data type has been switched to "date". Also with no effect?
Any help you could give would be appreciated!
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
2 Replies
- Vijay_A_Verma
Most Valuable Professional
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"}) - dufoq3
Community Champion
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