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

[Anonymous]

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:

Keywords	Category	Subcategory
Carrot	Vegetable	Low
Apple	Fruit	High
Beef	Meat	Medium

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

Order number	Ingredients
5156	Apples, Carrot
5158	Beef, 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-que...

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/#comme...

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

[Jimmy801]

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]

@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]

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

[Anonymous]

Hi Jimmy, (sorry difficulties posting on this forum, had post a blank reply then edit it, take a screenshot of my reply and post as an image)

[Jimmy801]

Hello @Anonymous 

when you are using this code, what error gives to 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)=> try Table.SelectRows(Helper , each [Word] = item)[Category]{0} otherwise "/"),
            CombineCategories = Text.Combine (GetCategory, ", ")
        in 
            CombineCategories)
in
    AddCategory

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]

Hi @Jimmy801,

No error but the new "Categories" column has nothing inside, also I would require a "Subcategory" column too.

Here is a screenshot:

The column is only showing: "/"

[Jimmy801]

Hello @Anonymous 

this can have multiple reasons. To undestand that i need your keywords-table too. Be aware that power query is case sensitive. In your case the value "PAYPAL PAYMENT" has to be written exactly in your helper-table in the column "Word"

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]

Hello @Jimmy801,

Would it be possible to make it non-case sensitive?
Here is my Helper Table:

[Jimmy801]

Hello @Anonymous 

okay, then this is the reason. But you should always post your original data, because in your scenario there this request was not needed. 

I tried to adapt your code. Hopefully it works, because I cannot test it

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)=> try Table.SelectRows(Helper , each Text.Upper([Word]) = Text.Upper(item))[Category]{0} otherwise "/"),
            CombineCategories = Text.Combine (GetCategory, ", ")
        in 
            CombineCategories)
in
    AddCategory

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]

Hi @Jimmy801,

Sorry but I can't really post this information to the public.
I can share it with you in private, if that's okay with you?

The solutio above doesn't seem to work, smae result as before 😞

[Jimmy801 · Accepted solution]

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

[Vera_33]

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]

Hi @Vera_33 ,

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.

[Vera_33]

Hi @Anonymous 

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

[Anonymous]

Hi @Vera_33,

It would show both Categories.

[Vera_33]

Hi @Anonymous 

It is another way to lookup values, the Fuzzy Match you can add Threshold, the LookupTable I put here is just for your conveinece, you can delete it and refer to your actual look up table.

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}}
  ),
  #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
  LookupTable = 
    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",
  #"Added Custom" = Table.AddColumn(
    #"Added Index",
    "Custom",
    each Table.TransformColumnTypes(
      Table.FromList(Text.Split([Ingredients], ",")),
      {{"Column1", type text}}
    )
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "new",
    each [
      a = Table.FuzzyNestedJoin(
        #"Added Custom"[Custom]{[Index]},
        {"Column1"},
        LookupTable,
        {"Keywords"},
        "LookupTable",
        JoinKind.LeftOuter,
        [IgnoreCase = true, IgnoreSpace = true]
      ),
      b = Table.ExpandTableColumn(
        a,
        "LookupTable",
        {"Category", "Subcategory"},
        {"Category", "Subcategory"}
      ),
      c = Table.SelectColumns(b, {"Category", "Subcategory"})
    ][c]
  ),
  #"Expanded new" = Table.ExpandTableColumn(
    #"Added Custom1",
    "new",
    {"Category", "Subcategory"},
    {"Category", "Subcategory"}
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Expanded new", {"Index", "Custom"}),
  #"Grouped Rows" = Table.Group(
    #"Removed Columns",
    {"Order number", "Ingredients"},
    {
      {"Category", each Text.Combine(_[Category], ", ")},
      {"Subcategory", each Text.Combine(_[Subcategory], ",")}
    }
  )
in
  #"Grouped Rows"

[Anonymous]

Hi @Vera_33 ,

Thank you, I know it might be too much to ask but I'm not very experienced in Power Query, would it be possible for you to map it to the tables that I provided to Jimmy below?

Thanks 🙂

[Vera_33]

Hi @Anonymous 

I actually don't quite understand...I saw your mapping table, but what is your actual data looks like?

If you paste the code to the Advanced Editor

You can click the steps on the right to see what is happening, this one is to split your sample fake data

This is where I said Fuzzy match, so you can specify Threshold and select case insensitive while look up values in your other table

If you can share a sample file, I can work on it

[Anonymous]

@Vera_33 

Sure 🙂 I will give you a piece of my data, I randomise the values and dates.