Forum Discussion

anmattos's avatar
anmattos
Advocate I
2 years ago
Solved

Column Split by Case Insensitive Delimiter

Hello,   I have a column with a large text that contains some keywords as text delimiters that I use to split the text in different columns.   The problem is that the keywords are sometimes mispe...
Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi anmattos,

     

    I get that it may look daunting, but here's the trick to implementing this solution into your own query.

    1.  Open up the Advanced Editor
    2.  Place your cursor after the let clause on line 1 and press enter
    3.  Paste in the fxSplitter function, seen here:

     

        fxSplitter = (string as text, substring as text) as list => 
        let
            s = Splitter.SplitTextByPositions( 
                {0} & Text.PositionOf(string, substring, Occurrence.All, Comparer.OrdinalIgnoreCase)
            )(string),
            r = {List.First(s)} & List.Transform( List.Skip(s), each Text.RemoveRange(_, 0, Text.Length(substring)))
        in  r,

     

        4. Select the query step in the Applied Steps pane, where you'd like to implement this.

        5. On the Add Column tab select Custom Column, when prompted to insert a step, confirm.

        6. Inside the formula section of the dialog box enter: fxSplitter( [String], "Method of Compliance") 

    Note that you have to replace [String] with an available column from the list on the right hand side, just double click to insert it, and update the text string in accordance to your delimiter.

     

    I hope this is helpful.

dufoq3's avatar
dufoq3
Community Champion
10 months ago

Hi anmattos, another solution:

 

You have to define your delimiters here:


Output:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7BboMwEER/ZcS5l9JbbyglTaQSosSHVoiDgzfBEtiRWST4+5pFiXJc7XszU1XJ2fcEpolRELfewF+x8f29s9o1BH1p8J5+JPVblWjnuaWwgLsHWBx/VnCJEIpbG4yKV0FqV36h3L7kxTgjlKHO9pYppGt548eDOs2wA26BMoVpmsb5V9hLrMVTGET4lGXRchxm7M/4PuWaoZ2B7gaPIpfyZWMZN+6zwyZfmwS5aevQlKNT4Q9W7IzlnyZ1/Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Delimiters = List.Buffer({"Method of Compliance", "country is great"}),
    Ad_Splitter = Table.AddColumn(Source, "Splitter", each 
        List.Accumulate(Delimiters, [Column1], (st1,cur1)=>
            [ a = List.Transform(Text.PositionOf(st1, cur1, Occurrence.All, Comparer.OrdinalIgnoreCase), (x)=> Text.Range(st1, x, Text.Length(cur1))),
              b = List.Accumulate(a, st1, (st2,cur2)=> Text.Replace(st2, cur2, "||"))
            ][b]), type text),
    Ad_Splitted = Table.AddColumn(Ad_Splitter, "Splitted", each List.Transform(Text.Split([Splitter], "||"), Text.Trim), type {text})
in
    Ad_Splitted