Merging two unrelated tables conditionally

[Anonymous]

Hi guys,

I currently have two tables that haven't got any fields in common, as follows.

Product ID	Product
123	Item A
234	Item B

Sales ID	Product Description
45678	Description [Item A] Description
56798	Description [Item B] Description

I want to be able to join in the products from table 1 based on the product description on table 2, but not sure how to merge the two tables conditionally based on whether the product description contains the product name. I want to add the product ID and product name to the matched row on the second table.

Hope this makes sense?

Thanks in advance!

[Greg_Deckler]

@Anonymous - Try Power Query fuzzy matching with Merge queries. I also invented a fuzzy matching calculation in DAX. https://community.powerbi.com/t5/Quick-Measures-Gallery/Fuzzy/td-p/1352914

Attached latest PBIX for convenience. 

[Anonymous]

I cannot rely on fuzzy match, I need to know that the data is matching accurately. There will be a lot of free text in the description column and it will lead to incorrect matches. 

[Greg_Deckler · Accepted solution]

@Anonymous - Then use FIND, SEARCH or CONTAINSSTRING, maybe like:

Column = 
  VAR __Table = 
    ADDCOLUMNS(
      'Table1',
      "Found",IF(FIND([search],[description],,0)>0,[search],BLANK())
    )
RETURN
  MAXX(FILTER(__Table,NOT(ISBLANK([Found]))),[Found])

[Anonymous]

but the tables aren't related so it's not letting me look one up in the other and join it on?

[Greg_Deckler]

@Anonymous, sorry, hit the button accidentally check updated post above, create that column in Table2 with description field.

[Anonymous]

No worries, thank you it worked!!