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

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, thanks for @tackytechtom 's solution, one more thing I would like to add. When we divide a column by delimiter in the result the delimiter is lost. In your sample, you want to divide the column by “,AK” and “,BUILTIN” and also keep them in the result, so here's my solution.

1.Right click the column and select Replace Values.

2. Add something special before the “,AK” and “,BUILTIN”, here I add a string "Replace".

3.Select the column then click Split Column, enter the string "Replace" I add in the previous step.

4.Split the new column by delimiter comma.

Remove blank columns and get the correct result.

Here's the whole M syntax:

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]),
    #"Replaced Value" = Table.ReplaceValue(Source,",AK","Replace,AK",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",BUILTIN","Replace,BUILTIN",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Column1", Splitter.SplitTextByDelimiter("Replace", QuoteStyle.Csv)),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3", "Column1.2.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Column1.2.1"})
in
    #"Removed Columns"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tackytechtom]

Hi @ovonel ,

I suggest 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 Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

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/