Forum Discussion
vlookup for keyword in power query
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
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)
Super UserHere 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