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

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.