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
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}?
)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