vlookup for keyword in power query

[vinayaka123]

Hi all,

This is my first time posting, pls help me

i have imported two tables into power query table1 field1 has string/sentence table2 has keywords and categorization. I need to add an another column as categorization which should take value from table2, categorization based on any word matches in keywords list.

eg. Table1

Field1(String)                 categorization (expected to pick from table 2)

sitting chair for office     Furniture

table2

keywords                      categorization

Chair                             Furniture

Table                              Furniture

[Omid_Motamedise · Accepted solution]

Initially load table 1 into power query

and also table 2 as categories

then select table 1, from add column tab, select custom column anduse this formula in it.

List.Accumulate(Table.ToRows(Table2),"",(a,b)=> if Text.PositionOf(Text.Lower([Field]),Text.Lower(b{0}))>=0 then a & b{1} else a)

it result in what you whant as below.

download the solution from here

https://docs.google.com/spreadsheets/d/1V-gvjQwTghDNqB3l2x3bDOfHP2y3tNLY/edit?usp=sharing&ouid=10517...

[vinayaka123]

Thank you so much it works perfectly!! just made a small change.. as it was showing picking two times same item. but not sure what is a and b in this..  List.Accumulate(Table.ToRows(keyword),"",(a,b)=> if Text.PositionOf(Text.Lower([merge text]),Text.Lower(b{0}))>=0 then b {1} else a)

[Omid_Motamedise]

Here I bring a short description, but for depth explanation, please chek my video on youtube:
https://youtu.be/G8PRbWuDcmQ?si=V_dmzlSdw3r8WVT4


generaly (a,b)=> described a custom function with two arguemnt (input parameters) and in the third argument of List.Accumulate, we have to defie a custom function with two arguments.

in this example, the result of Table.ToRows(keyword) will be {{"Chair","Furniture"},{ "Table","Furniture"}} and [merge text] for the first row is equal to "sitting chair for office", so 

List.Accumulate(Table.ToRows(keyword),"",(a,b)=> if Text.PositionOf(Text.Lower([merge text]),Text.Lower(b{0}))>=0 then b {1} else a)

by replacing these values, the formula can be rewriten as:

List.Accumulate({{"Chair","Furniture"},{ "Table","Furniture"}},"",(a,b)=> if Text.PositionOf(Text.Lower("sitting chair for office"),Text.Lower(b{0}))>=0 then b {1} else a)

if we imaging all the texts on both the table are in lower case, to simplify the formula, we can remove Text,Lower on the formula and rewrite it as:

List.Accumulate({{"Chair","Furniture"},{ "Table","Furniture"}},"",(a,b)=> if Text.PositionOf("sitting chair for office",b{0})>=0 then b {1} else a)

in this formula, List.Accumulate make a loop over the items in the first input.
so in hte first iteration a="" (initial value of a is the second argument in the function) and b is equal to first item in the list inserted in the first argument and equal to  {"Chair","Furniture"}, so b{0} "Chair" and b{1} is "Furniture".

so 
if Text.PositionOf("sitting chair for office",b{0})>=0 then b {1} else a
for the first itteration is equal to 

if Text.PositionOf("sitting chair for office","Chair")>=0 then  "Furniture" else "".

as the condition of this if statemnt is true, so it return "Furniture".
the result of this stattement will be consider as a new value for a in the next iteration.
so in the second iteration a is "Furniture" and b is { "Table","Furniture"}

this loop will be continue

[jgeddes]

I have attached a file that shows one way of doing this. 
Starting with tables...

and ending up with...

[AlienSx]

@vinayaka123 

1. Create 2 lists in your code (and buffer them!): table2[keywords] and table2[categorization]

2. Add custom column to your table1 with a function that looks up a position of keyword in keywords list (List.PositionOf with option Occurrence.First and comparison Text.Contains) and use that position to grab an item from categorization list.