Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Search Keyword return next row (VLOOKUP + SEARCH) on Power Query

Hi everyone this is my first post and probably not the last one,

I have been struggling to find a solution to my problem.
I hope you can help me with this simple problem.

I have a table with Keywords, Categories and Subcategories like below:

KeywordsCategorySubcategory

Carrot

VegetableLow
AppleFruitHigh
BeefMeatMedium

 

Then I have a table with all the data like below:

 

Order numberIngredients
5156Apples, Carrot
5158Beef, Pasta, Flour

 

I would like to search for the keywords on the column "Ingredients" (2nd table), if found look at the 1st table and return either the Category or Subcategory.

 

Now I have done some research on my own, I'm not asking you to do all the work.

 

I found 2 solutions but they are only part of the solution, here are the links below:

 

1st link is about a search function that will search for the keyword but return them if found:
https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-query#comments

2nd link is about a vlookup like function, but I'm unable to use it in this situation:

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/#comment-19516

 

NOTE: For the 1st link I used the "List Found Substrings - Case Insensitive" solution

 

If possible could you explain to me what you have done or how it works, I don't have much experience in coding, in fact, I want to learn more and this is the perfect opportunity to learn more.

 

Sincerely,

 

Pierre Reynaud

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello Anonymous 

     

    the code works probably fine - we saw this in my first solution. M

    aybe there is something wrong with your masterdata. Possible error could be what your columname of your keywords table is not the same as in my code... maybe with some spaces in it at the end or beginning. The same could be for your keywords like " Paypal Payment ".

    So without seeing that is impossible to help you anymore - sorry.

    What you can do on your side is to try this code (i canceled the error handling) and check out the error it gives you.

    let
    Helper =
    let
        Source = Excel.CurrentWorkbook(){[Name="Helper"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Category", type text}, {"Subcategory", type text}})
    in
        #"Changed Type",
    Data = 
    let
        Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text},{"Date", type text},{"Type", type text},{"Details", type text}, {"Amount Out", type text},{"Amount In", type text},{"Balance", type text},{"Class", type text}})
    in
        #"Changed Type",
    
    AddCategory = Table.AddColumn(Data, "Categories", each let 
            GetListOfDetails = List.Transform(Text.Split([Details], ","), each Text.Trim(_)),
            GetCategory = List.Transform(GetListOfDetails, (item)=> Table.SelectRows(Helper , each Text.Upper([Word]) = Text.Upper(item))[Category]{0}),
            CombineCategories = Text.Combine (GetCategory, ", ")
        in 
            CombineCategories)
in
    AddCategory

    I hope you are able to solve it with this


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    There are different ways. I want to understand below questions, so the approach will be different:

     

    the [Ingredients] always have the same delimiter: ","?

     

    the [Subcategory] is based on [Category], like always Fruit-High, Meat-Medium?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

      Thank you for replying 🙂

      Actually, I'm using the ingredient as an example because the data is sensitive, the "Ingredients" in this case would the details of the bank transactions like "Amazon ..."

      The subcategory is not based on the category, so it could be Fruit-Medium or Fruit-Extra, it doesn't follow a rule.

      I hope this helps 🙂

      I'm hoping this solution can be applied to future projects instead of creating a whole new code for this type of problem.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

         

        Then Ingredient might have multiple values in the lookup table [Keywords], how to determin which Category to put?

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello Anonymous 

     

    you can add a new column and use this formula. "Keywords" has to be step or the query where you stored the keywords. If your tables are big, then we might enhance a little bit to get a better performance

    let 
            GetListOfIngredients = List.Transform(Text.Split([Ingredients], ","), each Text.Trim(_)),
            GetCategory = List.Transform(GetListOfIngredients, (item)=> try Table.SelectRows(KeyWords, each [Keywords] = item)[Category]{0} otherwise "/"),
            CombineCategories = Text.Combine (GetCategory, ", ")
        in 
            CombineCategories

     Here the complete example (including both of your tables)... but be aware Apples is not Apple

    let
    KeyWords =
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sKsovUdJRCktNTy1JTMpJBbJ98suVYnWilRwLCsB8t6LSTJAaj8z0DLCEU2pqGpDvm5pYAqZSMktzlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keywords = _t, Category = _t, Subcategory = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Keywords", type text}, {"Category", type text}, {"Subcategory", type text}})
    in
        #"Changed Type",
    Orders = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU0NVPSUXIsKMhJLdZRcE4sKsovUYrVActYAGWcUlPTdBQCEotLEnUU3HLyS4uUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, Ingredients = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order number", Int64.Type}, {"Ingredients", type text}})
    in
        #"Changed Type",
    
    AddCategory = Table.AddColumn(Orders, "Categories", each let 
            GetListOfIngredients = List.Transform(Text.Split([Ingredients], ","), each Text.Trim(_)),
            GetCategory = List.Transform(GetListOfIngredients, (item)=> try Table.SelectRows(KeyWords, each [Keywords] = item)[Category]{0} otherwise "/"),
            CombineCategories = Text.Combine (GetCategory, ", ")
        in 
            CombineCategories)
in
    AddCategory

    Outcome

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Jimmy801 

      Thanks for your reply, I have some difficulties using your solution.

      The table above was just an example, the table that I'm working on has multiple columns and it won't recognise the column with the data, here is the code after I tried modifying it to my data:

       

      let
    Helper =
    let
        Source = Excel.CurrentWorkbook(){[Name="Helper"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Word", type text}, {"Category", type text}, {"Subcategory", type text}})
    in
        #"Changed Type",
    Data = 
    let
        Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Name", type text},{"Date", type text},{"Type", type text},{"Details", type text}, {"Amount Out", type text},{"Amount In", type text},{"Balance", type text},{"Class", type text}})
    in
        #"Changed Type",
    
    AddCategory = Table.AddColumn(Details, "Categories", each let 
            GetListOfDetails = List.Transform(Text.Split([Details], ","), each Text.Trim(_)),
            GetCategory = List.Transform(GetListOfDetails, (item)=> try Table.SelectRows(Word, each [Word] = item)[Category]{0} otherwise "/"),
            CombineCategories = Text.Combine (GetCategory, ", ")
        in 
            CombineCategories)
in
    AddCategory

       

      Also here are the original data column headers:

      (tried multiple times to post the table but something is wrong with the HTML)

       

      Source.Name-Date-Type-Details-Amount Out-Amount In-Balance-Class-Category-Subcategory

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello Anonymous 

         

        as I don't know your real data, I had to simulate it, using your dummy data. For sure you have to connect to your real data, meaning integrate my code somehow. If you have other column names, you have to pay attention to that to. What I can see in your code the variable "Word" is not existing. Then at least you should have another query named word, that has the column you are referencing (Column "word" and "Category" is needed)

        But as long as I don't see your exact queries, I cannot help you


        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy