Finding text from a set of keyword phrases

[Dave8055]

Hi Guys,

I am quiet new to power query. I am trying to find a solution to a problem that I am facing.

I have two tables as below, one with sentences and another one with keywords & category.

Table A

Title

Cat eat moon

Ben is a man

John is from London

Banana is Yellow

Table B

Keywords	Category
Cat_Moon	Animal
Man_is_Ben	Human
London	Place
Yellow_on_Banana	Food

 What I want here is to find and search for keywords from the Table A values. Eg: The 2nd row in Table B shows "Man_is_Ben". It should be split based on the _ and then look for those values in Table A. The correct match for this keyword will be row 2 on Table A. It has Man, Ben and is in the same cell even though the order is different.

Expected Output

Title	Keyword	Category
Cat eat moon	Cat_Moon	Animal
Ben is a man	Man_is_Ben	Human
John is from London	London	Place
Banana is Yellow	<NO MATCH since on is missing>	NONE

Hope you guys understood my ask here. Any help or suggestion on this is much appreciated.

[Anonymous]

Hi @Dave8055 

When I look at the data you passed in the example, I see that the rulesets are not correct in your mapping table. For example, you want to match "Yellow_on_Banana" with "Banana is Yellow".
For this reason, I want you to know that your joins will not work well and will not get a hundred percent result. In this regard, you should go into detail and increase your words in mapping.

The rule set is not correct, but if you use the fuzzy match method in the Merge Queries transaction, you can get results from certain rows, there is no other method you can use other than Fuzzy match in the two tables you forwarded. I am sharing the process steps in detail below, in order to achieve the highest result in the lines you have forwarded, I have set the "Similarity Threshold" as 0.5 in the fuzzy match process.

you can also see that the London row does not appear from the screeshot, for this you can detail words like "from london" in the mapping. I also shared the result when the row changes.

Best regards,

Ezgi Naz Aslankara

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[wdx223_Daniel]

NewStep=let a=List.Buffer(Table.ToList(TableB,each {Text.Split(_{0},"_")}&_)) in Table.FromRows(List.Transform(Table.ToRows(TableA),each let b=List.Skip(a,(x)=>List.RemoveItems(x{0},Text.Split(_{0}," ")<>{}){0}? in if b=null then _&{null,null} else _&List.Skip(b)),Table.ColumnNames(TableA)&Table.ColumNames(TableB))