Forum Discussion

monojchakrab's avatar
monojchakrab
Icon for Resolver III rankResolver III
2 years ago
Solved

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

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 ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    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.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    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

     

monojchakrab's avatar
monojchakrab
Icon for Resolver III rankResolver III
2 years ago

wdx223_Daniel - I was trying out the code in a smaller bit as follows :

1. I have prepared a list called [strings} as follows :

let
    Source = {
{{"SugarLo","50%"},"SugarLo"},
{{"Original", "Low Calories"},"Aspartame"},
{{"Classic","cook","zero calorie"},"Sucralose"},
{{"Stevia","green"},"Stevia"},
{{"Xylitol"},"Xylitol"}
}
in
    Source

2. Then I am trying out your code on this list as follows :

 List.Skip(Strings, (x)=>not List.Contains(x{0},"SugarLo"))

Since the condition is false it returns all the 5 lists and if we use {0}{1} on this list it correctly returns "SugarLo"

3. the problem starts when I change "SugarLo" to "Aspartame" e.g. - 

List.Skip(Strings, (x)=>not List.Contains(x{0},"Aspartame"))

which returns an empty list and hence the {0}{1} does not work and returns an error.

Am I missing something in the logic here? I am really sorry but I am not able to work out how will (x){0} work on the list recursively?

can you help?

thanks and really appreciate and apologies for the bother!

 

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
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