Merge columns based on words contains

[Simon_29]

I have one more question. I will give a little more precise data.
I created the ISSUE_CLEANED column from the Issue (already without words in the STOP_WORDS column), but now I needed to merge the following using some contains function:

To the new table with the Issue column from tables no. 1 in picture no. 1 assign the values from tables no. 2 in picture no. 2 so that if the words from the ISSUE_CLEANED column are in the Issue column, match the Issue_Group_Type column.

Could you help me with this? Thank you very much 

[v-yanjiang-msft]

Hi @Simon_29 ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @Simon_29 ,

According to your description, I create a sample.

Table1:

Table2:

Issue column in Table1 and ISSUE_CLEANED column in Table2 are not totally matching, for example "mbo goals attainmet is wrong" in Table1 and "mbo goals attainmet wrong" in Table2. In my understanding, you want to get the corresponding Issue_Group_Type in Table1 from Table2.

You can use the fuzzy match. Select the "Use fuzzy matching to perform the merge" when merge quries. Here're two notes:

1.For the Similarity threshold option, we should tweak the value to get the expected matching.

2.As there're many similar rows in your sample, to get the most suitable one, we should enter 1 in the option "Maximum number of matches".

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Simon_29]

Hi 🙂

Thank you very much for your reply.

This would be a partial solution, but the point is that redundant words like "and", "of" and similar are deliberately removed in ISSUE_CLEANED. Therefore, it was just some contains function that will say that if there are ISSUE_CLEANED in the Issue (that is, phrases cleaned of redundant words), then find the intersection and assign Issue_Group_Type. Is there any such function/possibility to do it?

Fuzzy works but not quite well, for example with Issue: missing order from attainment Could not find link to ISSUE_CLEANED: missing order attainment and did not assign Issue_Group_Type.. however, they do not match in only one word.

[v-yanjiang-msft]

Hi @Simon_29 ,

There is a function Text.Contains  can partially work, it only works when the find text is continuous in the within text. For example "missing order attainment" contains "missing order", but "missing order from attainment" doesn't contain "missing order attainment", because there is a from in the middle.

By my test, when set the Similarity threshold to 0.6, "missing order attainment" will matchs "missing order from attainment".

Best Regards,
Community Support Team _ kalyj

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