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

[monojchakrab]

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.,

[wdx223_Daniel · Accepted solution]

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

[wdx223_Daniel · Accepted solution]

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.

[wdx223_Daniel · Accepted solution]

[monojchakrab]

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

Really appreciate the help. This will help me immensely.

[monojchakrab]

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.

[wdx223_Daniel]

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.

[monojchakrab]

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?

[monojchakrab]

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

[Daryl-Lynch-Bzy]

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.