Forum Discussion
Resolver IIIExtract text (sub) strings from a text string using a pre-specified list
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
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.
so you get a blank list {}, so {}{0} is error, then {}{0}{1} is error.
can add a ? to tolerate the error, when there is no item on the index you want, it will give a null
Resolver III
Community ChampionTable.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
- 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
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!
- wdx223_Daniel
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.
- monojchakrab
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?