How do you use Splitter.SplitTextByAnyDelimiter??

[jilbobagins]

Hi All, I've got a particularly messy column of data that need splitting by virtually all of the delimiters (and Special Characters). Can the Splitter.SplitTextByAnyDelimiter function handle all of this? Anyone have any experience with it?

Thanks

 

[Nick Burns]

So you have a column of data that looks something like this:
<data1>|<data2>.<data3>:<data4>,<data5> and you want 5 columns (in this example) of each piece of data?

Then this should work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.SplitColumn(Source, "Column", Splitter.SplitTextByAnyDelimiter({"|",".",":",","}, QuoteStyle.Csv))
in
    Custom1

 

[jilbobagins]

Thanks for responding! I'll give that a try now.

 

[jilbobagins]

Hi, It only applies the split to the "Column" and doesn't create multiple new columns?? any ideas?

 

[jilbobagins]

I manually added the additional columns on the end and it works a treat then!

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column", "Column1"}}),
    Custom1 = Table.SplitColumn(#"Renamed Columns", "Column1", Splitter.SplitTextByAnyDelimiter({"/", " ", "&", ","}, QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2", "Column1.1.3"})
in
    Custom1