Change all dates in column to null

[Buckley]

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!

[Vijay_A_Verma · Accepted solution]

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 · Accepted solution]

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

[Vijay_A_Verma · Accepted solution]

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"})