Forum Discussion
Extract 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
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!
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.
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?
could you provide some sample data along with the desire output?
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.