topic Re: Extract text (sub) strings from a text string using a pre-specified list in Power Query

Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Thu, 26 Oct 2023 15:14:00 GMT

Hiya good people of PQ,

I have a text column as containing various texts,e.g. xxyyzz, xxxabc, acdfgh.

I want to extract the the texts "x", "c" and "h" for example, using a pre-specified list {"x","c","h"}, if there is a match.

I am currently using if text.contains code, but its starting to get very complicated with every new data refresh adding more complexity to the text column.

If there was a simpler solution with List.accumulate, e..g, then I could just update the list.

Thanks and best regds.,

Re: Extract text (sub) strings from a text string using a pre-specified list

Anonymous — Thu, 26 Oct 2023 18:29:18 GMT

Hi monojchakrab - could you please share an example data and the Power Query code that you are currently using? I think you might need a solution that uses List.ContainsAny. List.Select and List.FindText rather than only Text based functions.

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Fri, 27 Oct 2023 04:12:53 GMT

Hey Anonymous ,

Below is a snapshot of the text column :

And here is the code I am using for extracting the key words like Stevia or Aspartame and I use similar code to extract the pack size of type (like sachets or tablets) :

Table.AddColumn(Custom2, "Ingredient", each if Text.Contains([Title],"50%",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"sugarlo",Comparer.OrdinalIgnoreCase)then "SugarLo" else if Text.Contains([Title],"Stevia",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"Green",Comparer.OrdinalIgnoreCase) then "Stevia" else if Text.Contains([Title],"classic",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"cook",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"zero calorie",Comparer.OrdinalIgnoreCase) then "Sucralose" else if Text.Contains([Title],"original" ,Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"Low Calories", Comparer.OrdinalIgnoreCase) then "Aspartame" else if Text.Contains([Title],"xylitol",Comparer.OrdinalIgnoreCase) then "Xylitol" else null)

This code keeps getting longer and more complex with every refresh as the texts start getting new additions everytime so I have to mody this code post every refresh.

But what remains invariant is the list of actives or the list of pack sizes or the type etc - hence if I can work off a list, it will be a simple task just to update the relevant list

Thanks and appreciate the leg-up

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Fri, 27 Oct 2023 04:30:59 GMT

Table.AddColumn(Custom2, "Ingredient", each List.Skip( {{{"50%","sugarlo"},"SugarLo"}, {{"Stevia","Green"},"Stevia"}, {{"classic","cook","zero calorie"},"Sucralose"}, {{"original","Low Calories"},"Aspartame"}, {{"xylitol"},"Xylitol"} }, (x)=>not List.ContainsAny(x{0},[Title],(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase)) ){0}?{1}? )

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Fri, 27 Oct 2023 10:13:47 GMT

Hey wdx223_Daniel - thanks for stepping in...I will definitely try this out.

But could you help me with the logic here so that I might be able to use the code in other situation also with some modification?

Thanks and really appreciate

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Fri, 27 Oct 2023 11:04:12 GMT

Hey wdx223_Daniel - this is the error I am getting :

I typed in your code verbatim.

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Sun, 29 Oct 2023 23:46:15 GMT

Table.AddColumn(Custom2, "Ingredient", each List.Skip( {{{"50%","sugarlo"},"SugarLo"}, {{"Stevia","Green"},"Stevia"}, {{"classic","cook","zero calorie"},"Sucralose"}, {{"original","Low Calories"},"Aspartame"}, {{"xylitol"},"Xylitol"} }, (x)=>not List.Contains(x{0},[Title],(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase)) ){0}?{1}? )

please change List.ContainsAny to List.Contains

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Mon, 30 Oct 2023 06:07:15 GMT

That worked wdx223_Daniel - but if you could step out the logic for me, I can then use a similar code to extract other strings - like form (tablets, sachets etc), pack size ((100,300 etc) and multipack units (1....6).

Also, is it possible to feed a list prepared separately, nested one as you have coded here into list.skip, so that we dont need to hard code the texts in the curly braces?

But many thanks anyways.

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Mon, 30 Oct 2023 08:10:14 GMT

yes, you can prepare a list separately, for each item in the list, the first item is a list of sub-string you want to search, and the second is the value you what to show.

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Mon, 30 Oct 2023 09:04:21 GMT

In this list :

{"50%","sugarlo"},"SugarLo"},

what is the 1st list nested within the ouuter list doing? which is the item the list is skipping actually?

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Mon, 30 Oct 2023 23:53:43 GMT

each item should like {{"50%","sugarlo"},"SugarLo"}

when all the value in first item's list can not be found in the Title column, then this item {{"50%","sugarlo"},"SugarLo"} will be skipped.

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Wed, 01 Nov 2023 05:24:16 GMT

wdx223_Daniel ,

Sorry to be a pest here but I am havig trouble working thru' the logic of the code. Appreciate your being patient with my dumb questions :

1. Why are we using a nested list - why cannot we use a flat list, e.g. {"50%", "sugarlo", "low calories","Aspartame"} and so on? The reaso I am asking is this is that the [Title] column also has some other information like forms, pack size etc for which we may not need a nested list as in this code.

2. I am also not clear as to how do you skip a nested list

3. In the last part of the code,

(x)=>not List.Contains(x{0},[Title],(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))
                      ){0}?{1}?

I can see you are using the logic for skipping the list, but I did not understand the following :

a. where is variable (x) getting its value from? and why are using the 1st positing by using x{0}?

b. I am not able to understand the 3rd parameter within the list.contains

c. where is variable (y) getting its value from

d. Then in the last part why are we using {0} and then {1} positions and of which list?

Possibly all dumb questions but if I get this logis straight, I can reconstruct the code for extracting the other text strings :

a. Form - "tablets","jars","pouch","sachets"

b. Pack size - "100","300","500","50","100","150" etc

c. Multi pack - "1","2","3","4","5","6"

Appreciate the leg-up and apologies for the long-winded question!

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Wed, 01 Nov 2023 06:18:30 GMT

1 if use flat list, you can not get the replacement when more than one substrings be assigned a replacement. the list can be re-write as this

{"50%","SugarLo"}, {"sugarlo","SugarLo"}, {"Stevia","Stevia"}, {"Green","Stevia"}, {"classic","Sucralose"}, {"cook","Sucralose"}, {"zero calorie""Sucralose"}, {"original","Aspartame"}, {"Low Calories","Aspartame"}, {"xylitol","Xylitol"}

and re-write the second arguement as

(x)=>Text.Contains([Title],x,Comparer.OrdinalIgnoreCase)

I just intergerate the same replacement into one item.

2 (ItemFromSupportingList)=>not List.Contains(ItemFromSupportingList{0},[Title],(1stArguement,2ndArgument)=>Text.Contains(2ndArgument,1stArguement,Comparer.OrdinalIgnoreCase))
){0}?{1}?

{0} is select the first value of the list after skiping all the items not match the condition. this value will be in the structure as {list,text}, then {1} to get the "text" as the replacement.

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Wed, 01 Nov 2023 06:37:51 GMT

Thanks a lot wdx223_Daniel for the clear explanation.

Suppose the text string also contains sub-strings like 50,500 and 5 - is there a way we can extrat the "5" from the single "5", but not from "50 and "500"? That along with a remodification of this code can help extract the pack sizes and selling units from the [Title] string?

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Wed, 01 Nov 2023 07:24:26 GMT

could you provide some sample data along with the desire output?

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Wed, 01 Nov 2023 07:54:21 GMT

The source data remains same...

The code I am using to extract the form is as below :

Table.AddColumn(Ingredients, "Form", each if Text.Contains([Title],"tablets",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"tablet",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"500 pcs",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"330",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"440",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"550",Comparer.OrdinalIgnoreCase) then "Tablets" else if Text.Contains([Title],"sachet",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"sachets",Comparer.OrdinalIgnoreCase) then "Sachets" else if Text.Contains([Title],"500 g",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"500g",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"xylitol",Comparer.OrdinalIgnoreCase) then "Pouch" else if Text.Contains([Title],"80",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"150g",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"150 g",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"150gm",Comparer.OrdinalIgnoreCase) or Text.Contains([Title],"150 GRAM",Comparer.OrdinalIgnoreCase) then "Jar" else "Tablets" )

The code I am using to extract the sizes is as below :

a. First I remove all text from the [Title] string :

Table.AddColumn(MPU, "Remove Text", each Text.Remove([Title],{"A".."Z","a".."z"}))

b. This returns a column as below :

c. from this column now, I feed a pre-prepared list :

into the following code to extract the matching strings :

Table.AddColumn(RemoveTextFromTitle, "Pack", each List.Select(PackList, (x)=> Text.Contains([Remove Text],x,Comparer.OrdinalIgnoreCase) ){0}?)

d. This returns a column as below :

e. I now clean up the above the column, by feeding another list with corrected packlist :

by using the following code :

List.Accumulate({0..Table.RowCount(PackSize)-1}, PackPosition, (x,y)=> Table.ReplaceValue(x, PackSize[OldValue]{y}, PackSize[NewValue]{y}, Replacer.ReplaceValue, {"Pack"}) )

Little long-winded, but I could not find a simpler way to get to this in fewer steps.

Any help appreciated.

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Wed, 01 Nov 2023 08:56:39 GMT

it looks like more complicated.

i can not get the whole story depending on what you showed

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Wed, 01 Nov 2023 09:24:09 GMT

wdx223_Daniel ,

Sorry I confused you unintentionally. let me try and simplify this for you :

1. We are starting with the same column as above :

2. This text string, contains terms like sachets, tablets and jars - which are the forms. I want to extract these sub-strings from this column and the desired output should be to add a column headed "Form" with the strings as below :

3. Next if you notice, the text string column also includes number strings as - 100,300, 500, 80,50 etc. These are pack sizes. The desired output would be to extract these strings into a separate column headed "pack Size" :

Not sure if that has simplified things a bit and helps you better with the solution I am seeking.

Re: Extract text (sub) strings from a text string using a pre-specified list

wdx223_Daniel — Wed, 01 Nov 2023 23:57:34 GMT

Table.SplitColumn(Custom2, "Title", each let fx=(x,y)=>List.RemoveItems(Splitter.SplitTextByAnyDelimiter(x)(y),{"",null}), Ingredient=List.Skip( {{{"50%","sugarlo"},"SugarLo"}, {{"Stevia","Green"},"Stevia"}, {{"classic","cook","zero calorie"},"Sucralose"}, {{"original","Low Calories"},"Aspartame"}, {{"xylitol"},"Xylitol"} }, (x)=>not List.Contains(x{0},_,(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase)) ){0}?{1}?, Form=fx(fx(YourFormList,_),_){0}?, PackSize=fx(fx(YourPackSizeList,_),_){0}?, MultiPack=fx(fx(YourMulitPackList,_),){0}? in {Ingredient,Form,PackSize,MultiPack} {"Ingredient","Form","PackSize","MultiPack"})

Re: Extract text (sub) strings from a text string using a pre-specified list

monojchakrab — Thu, 02 Nov 2023 03:59:46 GMT

Thanks wdx223_Daniel - will definitely try this out and let you the result.

Really appreciate the help. This will help me immensely.