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 tried the code above only for ingredients :

Table.SplitColumn(ActiveNewWay.1, "Title", each
            let 
                  fx=(x,y)=>List.RemoveItems(Splitter.SplitTextByAnyDelimiter(x)(y),{"",null}),
                  Ingredient=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},_,(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))
                      ){0}?{1}?
                //   Form=fx(fx(YourFormList,_),_){0}?,
                //   PackSize=fx(fx(YourPackSizeList,_),_){0}?,
                //   MultiPack=fx(fx(YourMulitPackList,_),){0}?
                in {Ingredient}
               {"Ingredient"})

And this is returning the following error :

Am I missing something here?

regds.,

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
2 years ago

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    2 years ago

    this function will be ran in each row of your table.

    it firstly get the value of [Title] in this row

    then, test if it contains any value in the first list-item of each row in your pre-prepared list

    it will skip all the row of which the first list-item did not be found in Title,

    then get the first row of the remain pre-prepared list, and get the 2nd item of the row as the replacement.   

  • 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

     

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

    Thanks wdx223_Daniel - the missing underscore probably is not causing the error since that is commented out. Will try the comma after {Ingredient} and check the result. 

    One question though :

    when you are feeding the variable (x){0} into the list.contains function, will it not always check against the 1st item in the list (which in this case is "SugarLo"? I am slightly confused here. Should not the list.contains check each item in the list within the [Title] column? but (x){0} will always check for the 1st item and not list.contains will always return false, right? I am not able to figure out that if its not a recursive function, then how for each row in column [Title], each item of the list will be checked for true or false?

  • 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!