Matching and returning the list of matching words

[gupta_diksha]

Hi experts,

I have two columns containing list of different names and these two columns are in two different tables. I would like to compare these columns and find out all the matching words or If some parts of the words are matching in both the lists for example - Georgers SRL or SRL Georgers or Georgers.

Strings containing 3 words, If only one word is matching with the word of other list, then also Its fine. Basically idea is to match two columns and find all the identical words/ parts of word .

I hope you got my point. 

Thanks in advance.

Best Regards,

Diksha

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  Please ensure that the sample data that you share should be representative of your actual data.

[gupta_diksha]

Hi Ashish,

Thank you for replying. 

Data would be somewhat like this. It can be a large dataset instead but this will be the type. If you could please help me to create a query, It would be really helpful.We need to match all the words and If some words contains its parts, then also we want it to be displayed as match.

[Ashish_Mathur]

Hi,

You have still not shown the expected result.

[gupta_diksha]

Hi,

Sorry,  I'm mentioning it below for your reference. 

[Ashish_Mathur · Accepted solution]

Hi,

Try the Fuzzy Lookup feature of the Query Editor.

[gupta_diksha]

Hi Ashish,

Thank you for introducing me to the Fuzzy Matching feature. I tried it but It worked partially.

This is the table which I wanted to match and I wanted to display GUPTA in front of DIKSHA GUPTA as well but It did not happen.

The below attached image was my result

So you can see that GUPTA is not displayed. Why is It like that?

What do I need to do for that.

Thank you

Best Regards,

Diksha

[Ashish_Mathur]

Hi,

Adjust the similarity threshold.  Please read this - Create a fuzzy match (Power Query).

[v-yingjl]

Hi @gupta_diksha ,

You can create a query like this:

let
    tb1 = Table.ExpandListColumn(Table.TransformColumns(#"Table 1", {{"Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name"),
    tb2 = #"Table 2",
    tb = List.Intersect({tb1[Name],tb2[Name]}),
    #"Converted to Table" = Table.FromList(tb, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

table1table2match table

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.