Merging wildcard data between two tables

[crossover]

Hello! I have a data merge challange that I'm looking to solve with PowerQuery. Objective is to get data from Excel and return there too (rather than Power BI), but expecting techniques to be the same. I have Table1 full of comments which may and may not include references to products in Table2 (on the right).

I'm looking to link each Table1 comment to a product from Table2, but not really sure how to accomplish this. I have used the help of INDEX + wildcard MATCH formula in Excel to create a link that I could use in order to merge tables in PowerQuery, however although functional the clear limitation with this approach is only being able to link the first match.  

Eventually I'm looking at something like this as a returned result. It's just a simple demo dataset, there are many more products to join, so just hand picking some strings for "split column" + merge table functionality doesn't seem viable. Just realizing my demo dataset lacks an example of a Table1.Comment not having any match in the Table2.product section, but that can also be the case.

Any suggestions are highly appreciated!

[AlB]

Hi @crossover 

I believe a combination of search and sumproduct (possibly with array formulae) would do the job in Excel.

In any case, here is a possible solution in PQ. The "#Added custom" step is the relevant one. See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpKTVFILCjISS1WUIrViVYyAoqlVhSk5hVnlqUqFKQmFhWDxY2B4skZqUVFlQpJOfnFxfm5SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Comment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Comment", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.SelectRows(ProductT, (inner)=> Text.Contains([Comment], inner[Product] ))[Price]), Int64.Type)
in
    #"Added Custom"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.