Search code examples
powerbietlpowerquerypowerbi-desktopm

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

I am in this screen: enter image description here

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:

enter image description here

I have tried many ways with no success.

Solution

  • You can't do that from that screen but you can achieve what you want with a two step split. e.g.

    enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WiolJLE7JSIuJKUoty4yJCcnMNdJx9I6JcfSOd3TxDfDQcczJyS/XccusUIrVgSpPKzZEUq+j4JWfkafjFOrpE+LpB9SZkpuZl1lcUpRYkl9UDNXvl5+XqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByAnyDelimiter({",BUILTIN", ",AK"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}})
in
    #"Changed Type1"