Forum Discussion
Extract text (sub) strings from a text string using a pre-specified list
- 2 years ago
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
- 2 years ago
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.
- 2 years ago
- 2 years ago
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
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
- wdx223_Daniel2 years ago
Community Champion
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}? )- monojchakrab2 years ago
Resolver III
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
- monojchakrab2 years ago
Resolver III
- wdx223_Daniel2 years ago
Community Champion
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