Forum Discussion

monojchakrab's avatar
monojchakrab
Resolver 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 {"x","c","h"}, if there is a match.

 

I am currently using if text.contains code, but its starting to get very complicated with every new data refresh adding more complexity to the text column.

 

If there was a simpler solution with List.accumulate, e..g, then I could just update the list.

 

Thanks and best regds.,

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

     

26 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi monojchakrab - could you please share an example data and the Power Query code that you are currently using?  I think you might need a solution that uses List.ContainsAny. List.Select and List.FindText rather than only Text based functions.

  • monojchakrab's avatar
    monojchakrab
    Resolver III
    2 years ago

    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_Daniel's avatar
      wdx223_Daniel
      Community Champion
      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.ContainsAny(x{0},[Title],(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))
                      ){0}?{1}?
               )
      • monojchakrab's avatar
        monojchakrab
        Resolver III
        2 years ago

        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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    each item should like {{"50%","sugarlo"},"SugarLo"}

    when all the value in first item's list can not be found in the Title column, then this item {{"50%","sugarlo"},"SugarLo"} will be skipped.