how to to split a csv on multiple delimiters in PowerQuery?

[Anonymous]

I am on this screen:

The data looks like this:

\\asdhf\revi\Tim2,AK\AK_ADMPH,Allow,Fix

\\asdfs1\revi\Tim, John,BUILTIN\Administrators,Allow,None

I want to use “dynamic” delimiters… I want the delimiters to be:

1st delimiter: “,BUILTIN” and “,AK”

Then: the usual comma.

The end result should be:

\\asdhf\revi\Tim2	AK\AK_ADMPH	Allow	Fix
\\asdfs1\revi\Tim, John	BUILTIN\Administrators	Allow	None

How can I achieve this? I have tried different ways with no luck

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this:

Before:

After:

It's a two step approach. First, we split the column with your special requirement and afterwards we split the residual column by comma.

Here the code in M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WigGCxOKUjLSYmKLUssyYmJDMXCMdR++YGEfveEcX3wAPHcecnPxyHbfMCqVYHbiGtGJDJB06Cl75GXk6TqGePiGefkC9KbmZeZnFJUWJJflFxVAT/PLzUpViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({",AK",",BUILTIN"}, QuoteStyle.None), {"Column1", "Column2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3"})
in
    #"Split Column by Delimiter1"

Let me know, if this helps 🙂

/Tom

https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/