Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Feb 24, 2025
Solved

What delimiter shall I use?

Dear Experts,                     Could you please help me with what Delimiter shall I use in below case? So , I have data like in Column2, and want to split the column on the basis of " , " , mark...
excel
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 24, 2025

    That's as Patrick2788 suggested, or if you prefer with formula, practically the same could be

    =TRIM( TEXTSPLIT(SUBSTITUTE( <line>, "), (", "),:(" ), ",:") )

    Above for single row, how to split texts in 2D you may find patterns, few of them.  But again, that will be spill, not table.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 24, 2025

My first thought was to replace all occurrences of "), " with "): " and then split by ": ", but PQ somehow wouldn't let me. Don't really understand why, as the parentheses and spaces are 'regular' characters.

So, if you can somehow substitute "), " with "): " in the source file, PQ should handle it correctly. I tried by taking your data and use the SUBSTITUTE function in Excel. Then connected PQ to that table and could do the split.

 

Edit: I wrote my earlier comments when working on a Mac and it wouldn't let me. Now trying on a PC and PQ lets me replace "), " with "): " and then split based on ": " without any issues.

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 24, 2025

    Riny_van_Eekelen , strange, it works in my case

    let
    Source = Excel.CurrentWorkbook(){[Name="l1low_bfweight_Study"]}[Content],
    SelectColumns = Table.SelectColumns(Source,{"Column1", "Column2"}),

    SetDelimiter = Table.ReplaceValue(SelectColumns,"),","),:",Replacer.ReplaceText,{"Column2"}),

    names = List.Transform(
        {1..List.Max( List.Transform( SetDelimiter[Column2], (q) => List.Count( Text.Split(q, ":") ) ) ) },
        (n) => "Pair" &  Number.ToText(n, "00") ),

    SplitColumns = Table.SplitColumn(
        SetDelimiter, "Column2",
        Splitter.SplitTextByDelimiter(",: ", QuoteStyle.Csv), names),
    TrimTexts = Table.TransformColumns(
        SplitColumns, List.Transform( names, (q) => {q, Text.Trim, type text} ) )
in
    TrimTexts
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Feb 24, 2025

      Indeed, very odd. I edited my earlier response to explain I was using a Mac, and can't get it to work there. On a PC, no problems.

Resources