Forum Discussion
remove string patterns within a column
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
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
