Forum Discussion
Remove text between delimiters
Hi,
I’m looking for a way to remove the text between two delimiters. I’ve tried these two solutions but unfortunately they do not work for me.
https://community.powerbi.com/t5/Desktop/Remove-text-between-delimiters/m-p/303937
https://community.powerbi.com/t5/Desktop/Power-Query-remove-all-text-between-delimiters/m-p/460854
The problem is as follows.
I have a table with 3 million rows of product information:
Product input |
Bla Bla Bla 7LITER |
bla 5LITER text text |
Blab la text |
4LITER text bla |
Bla 4.0LITER text |
Text bla LITER50 bla text |
Bla bla 233.3LITER text bla 10LITER text text |
As you can see the product information can contain the volume of the product, but sometimes it will not contain that info. The position of the volume information (LITER) is different for each row, this is only a small example of some of the types of ways the information is being displayed. Next to that sometimes the LITER precedes the amount, and sometimes the amount will be shown before the LITER, and sometimes the amount of Liters will have a decimal in the number.
I want to remove the volume information from all rows so that the output looks like this:
Product input | Product output |
Bla Bla Bla 7LITER | Bla Bla Bla |
bla 5LITER text text | Bla text text |
Blab la text | Blab la text |
4LITER text bla | text bla |
Bla 4.88LITER text | Bla text |
Text bla LITER50 bla text | Text bla bla text |
Bla bla 233.3LITER text bla 10LITER text text | Bla bla text bla text text |
A solution would be to simple do a search and replace for all possible LITER combinations, but that will result in hundreds of search and replace queries, which has a big impact on refresh time. How can I get to the output with the least amount of processing power?
Hi Anonymous ,
You can create a column with the bolded code - if you paste the full code into the advanced editor you'll have a complete solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspJVIBhcx/PENcgpVidaKUkINcUzFUoSa0oARNgCaC6JAWgJFzABEkVUBdMkYKJngFCBiwaAlWiABY3NQCzkQ0GCxgZG+sZo5qpYGiA4ZRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product input" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product input", type text}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each Text.Combine( List.Select( Text.Split([Product input], " "), (x) => not Text.Contains(x, "liter", Comparer.OrdinalIgnoreCase)), " ")) in #"Added Custom".. unfortunately the new editor doesn't let me highlight parts of the code anymore - this is the code that goes into the "Table-Add-column-Window":
Text.Combine(
List.Select(
Text.Split([Product input], " "),
(x) => not Text.Contains(x, "liter", Comparer.OrdinalIgnoreCase)),
" ")
4 Replies
Anonymous ,
Perhaps it will be either a function that removes any word that "contains" Liter, or inserting a delimiter around any word that contains the same. Calling on the M language superheroes. ImkeF KenPuls .
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielHi Anonymous ,
You can create a column with the bolded code - if you paste the full code into the advanced editor you'll have a complete solution:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspJVIBhcx/PENcgpVidaKUkINcUzFUoSa0oARNgCaC6JAWgJFzABEkVUBdMkYKJngFCBiwaAlWiABY3NQCzkQ0GCxgZG+sZo5qpYGiA4ZRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product input" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product input", type text}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each Text.Combine( List.Select( Text.Split([Product input], " "), (x) => not Text.Contains(x, "liter", Comparer.OrdinalIgnoreCase)), " ")) in #"Added Custom".. unfortunately the new editor doesn't let me highlight parts of the code anymore - this is the code that goes into the "Table-Add-column-Window":
Text.Combine(
List.Select(
Text.Split([Product input], " "),
(x) => not Text.Contains(x, "liter", Comparer.OrdinalIgnoreCase)),
" ")
