Forum Discussion
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.
- Open up the Advanced Editor
- Place your cursor after the let clause on line 1 and press enter
- 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.
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
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.
Hi anmattos,
I get that it may look daunting, but here's the trick to implementing this solution into your own query.
- Open up the Advanced Editor
- Place your cursor after the let clause on line 1 and press enter
- 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.
