Column Split by Case Insensitive Delimiter

[anmattos]

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 mispelled differently with letters in different cases. For example, the delimiter

"Method of Compliance" may be spelled as "method OF compliance" or something like this.

PowerQuery text split function by text delimiter is case sensitive.

I've already tried a replace function to replace all erroneous delimiters, but the replace function in Power Query is case sensitive as well and the alternatives I found in forums does not seem to work to replace strings in the middle of a larger text.

Any suggestion in how to split those columns with case insensitive delimiters OR replace those key words with the correct ones in a case insensitive replace?

Thank you,

[m_dekorte]

Hi @anmattos,

Give this custom splitter a go, you can copy all the M code into a new blank query, to see how it works.

let
    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,
    Source = Table.FromColumns( {{"xxxxxxxxxxxxxxx Method of Compliancexxxxxxxxxxxxx", "xxxxxxxxxxxxxxxxxmethod OF compliance xxxxxxx", "xxxxxxxxxxxxxx", "xxxxxxxxxxxxxxx Method of Compliancexxxxxxmethod OF compliance xxxxxxx" }}, type table [String=text]),
    NewCol = Table.AddColumn(Source, "Split", each fxSplitter([String], "Method of Compliance") )
in
    NewCol

It yields this result on my dummy data.

I hope this is helpful

[anmattos]

Hello,

Thank you for your reply and for all the effort. I'll see if a simpler solution comes up. I'm not familiar on how to apply this function to my query.

But it just amazes me how Power Query doesn't have a replace function that can be case insensitive. Very insensitive from the developers (pun intended). This would solve the problem neatly.

[m_dekorte · Accepted solution]

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.

[anmattos]

Thank you very much. I'll try.