none
Splitter.SplitByEachDelimiter - Bug? RRS feed

  • Question

  • Hi all,

    I'm wondering why this doesn't work:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSswpTknL0k0EkkqxOkA+kKED4cUCAA==", 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.SplitTextByEachDelimiter({",", "-"}, QuoteStyle.Csv, false), {"1", "2", "3"})
    in
        #"Split Column by Delimiter"

    Only the first delimiter in the list of delimiters will be considered. All others are ignored. I would expect both rows to be split.
    Is there an error in my syntax or is this a bug?

    Thanks!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, May 27, 2020 8:08 PM
    Moderator

Answers

  • Hi Imke!

    It's look like Splitter.SplitTextByEachDelimiter function performs splitting exactly in the order of the specified delimiters:

    let
        Source = #table(1,List.Zip({{"a,b-c","a,b,c","a-b,c","a-b,c-d"}})),
        dupl = Table.DuplicateColumn(Source, "Column1", "temp"),
        split = Table.SplitColumn(dupl, "temp", Splitter.SplitTextByEachDelimiter({",", "-"}, QuoteStyle.Csv, false), {"1", "2", "3"})
    in
        split


    Wednesday, May 27, 2020 9:04 PM

All replies

  • Hi Imke!

    It's look like Splitter.SplitTextByEachDelimiter function performs splitting exactly in the order of the specified delimiters:

    let
        Source = #table(1,List.Zip({{"a,b-c","a,b,c","a-b,c","a-b,c-d"}})),
        dupl = Table.DuplicateColumn(Source, "Column1", "temp"),
        split = Table.SplitColumn(dupl, "temp", Splitter.SplitTextByEachDelimiter({",", "-"}, QuoteStyle.Csv, false), {"1", "2", "3"})
    in
        split


    Wednesday, May 27, 2020 9:04 PM
  • Thanks Aleksei,

    that's a good sample: Check out the last row: It seems that the non-first delimiters will only work on the non-first columns. So the second delimiter ("-") will not be applied in the 3rd row and in the 4th row only on the element that stood to the right of the delimiter.

    At the end I figured out that Splitter.SplitByAnyDelimiter does what I was actually expecting. 

    let
        Source = #table(1,List.Zip({{"a,b-c","a,b,c","a-b,c","a-b,c-d"}})),
        dupl = Table.DuplicateColumn(Source, "Column1", "temp"),
        split = Table.SplitColumn(dupl, "temp", Splitter.SplitTextByAnyDelimiter({",", "-"}, QuoteStyle.Csv, false), {"1", "2", "3", "4"})
    in
        split


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!



    Thursday, May 28, 2020 6:08 AM
    Moderator
  • Hi Imke :-)

    I came across this problem a few years ago and my conclusions were similar to Aleksei.
    Since then I have distinguished Each from Any in Splitter function ;-)))

    Cheers

    Thursday, May 28, 2020 8:49 AM
  • Imke,

    Not sure you've expressed same idea by other words, so I clarify my opinion. As far as I understand, the order of delimiters is essential for Splitter.SplitByEachDelimiter function. Thus, the second delimiter ("-") may be applied only after the first one (","). So, if PQ engine can not find the first delimiter in the text value, then it will not be splitted, regardless, how many other delimiters are specified.

    Thursday, May 28, 2020 10:35 AM
  • Hi Bill and Aleksei,

    It's clearly good to have both of these functions - once you've understood and remember to distinguish between them ;)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, May 28, 2020 6:28 PM
    Moderator