Column Split by Case Insensitive Delimiter

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.