Forum Discussion
remove string patterns within a column
Hello,
I have a text column in PowerQuery that contains strings with the pattern like below. I want to replace all such cases
s:xxx: where xxx can be any number from 0 to 999
Examples s:1:, s:34:, s:677:
The simplest thing to do, if there are not too many different codes, would be to right-click and Replace Values. Do that multiple times for each different code. It's simple but maybe not appropriate
--
There is no built-in regular expression functions in power query but you can make web calls to do this from within PQ. It's not going to be quick but it does open up the whole power of regex. Here's an example thread
--
Alternatively, you can define a list of all possible codes to search for, then use that list in a function to clean the column:
1 Make a list {1..999}
2 Transform it to codes : List.Transform(Source, each Text.Combine({"s:", Text.From(_), ":"}))
then add a column to the original query using :List.Accumulate( SearchList, {[Column1], ""}, (old, current) => let CreateNew = Text.Replace(old{0}, current,""), CreateSubstringsRemoved = try Text.Combine (List.Difference(Text.ToList (old{0}), Text.ToList(CreateNew) ) ) otherwise "" in {CreateNew, if CreateSubstringsRemoved <> "" and old{1} <> "" then old{1} & ", " & CreateSubstringsRemoved else if old{1} <> "" and CreateSubstringsRemoved = "" then old{1} else CreateSubstringsRemoved } ) {0}I didn't write that originally, it's by a contributor who used to be on the forum
Jimmy801
5 Replies
More detail please. Show your data and the desired result please
Thanks for the answer. I've attached an example below. The idea is to simply replace all instances of s:xxx: with "".
Data:
Column name:ABC
Example of Column Value:
This is a sample text with s:1: embedded. Another example: s:34: can be found in this text as well. s:677: appears at the end of this string. No pattern in this one. s:123: is just another instance.
Expected result after transformation:
Column name:ABC
Example of Column Value after transformation:
This is a sample text with embedded. Another example: can be found in this text as well. appears at the end of this string. No pattern in this one. is just another instance.
Hi, ryland91 change "txt" to your column name (as well as use correct your_table reference).
let Source = your_table, numbers = {0..999}, delimiters = List.Buffer(List.Transform(numbers, (x) => "s:" & Text.From(x) & ":")), z = Table.TransformColumns(Source, {"txt", (x) => Text.Combine(Splitter.SplitTextByAnyDelimiter(delimiters)(x), "")}) in z
The simplest thing to do, if there are not too many different codes, would be to right-click and Replace Values. Do that multiple times for each different code. It's simple but maybe not appropriate
--
There is no built-in regular expression functions in power query but you can make web calls to do this from within PQ. It's not going to be quick but it does open up the whole power of regex. Here's an example thread
--
Alternatively, you can define a list of all possible codes to search for, then use that list in a function to clean the column:
1 Make a list {1..999}
2 Transform it to codes : List.Transform(Source, each Text.Combine({"s:", Text.From(_), ":"}))
then add a column to the original query using :List.Accumulate( SearchList, {[Column1], ""}, (old, current) => let CreateNew = Text.Replace(old{0}, current,""), CreateSubstringsRemoved = try Text.Combine (List.Difference(Text.ToList (old{0}), Text.ToList(CreateNew) ) ) otherwise "" in {CreateNew, if CreateSubstringsRemoved <> "" and old{1} <> "" then old{1} & ", " & CreateSubstringsRemoved else if old{1} <> "" and CreateSubstringsRemoved = "" then old{1} else CreateSubstringsRemoved } ) {0}I didn't write that originally, it's by a contributor who used to be on the forum
Jimmy801
Very nice. I had a look for something to feed a list into. Couldn't find it!
