How to merge 2 tables in PowerQuery using substrings?

[WillianLopes]

I have a sold items table. In this list, in addition to the name of the product, there are other expressions required by law - all on a single line.

In the second table I have the name of the products.

I need to merge the product name from the second table with the items from the first table. However, as the items in the first table are in the middle of a sentence, something like "product" == "%product%" would be needed.

How to do this merge in PowerQuery?

Example tables

+------------------------------+
|Sold items                    |
+------------------------------+
|Yellow bananas - consume soon |
|A box of small apples         |
|Blue and yellow pencil set    |
|A box of oranges              |
+------------------------------+

+---------+
|Products |
+---------+
|Bananas  |
|Apple    |
|Juice    |
|Pencil   |
+---------+

Expected outcome:

+------------------------------+----------+
|Sold items                    | Products |
+------------------------------+----------+
|Yellow bananas - consume soon | Bananas  |
|A box of small apples         | Apple    |
|Blue and yellow pencil set    | Pencil   |
|A box of oranges              | (null)   |
+------------------------------+----------+

[WillianLopes · Accepted solution]

On another site I was given a great solution that I will share here to document.

First add a custom column:

Table.SelectRows(Table2, (x)=> Text.Contains ([Sold items], x[Products], Comparer.OrdinalIgnoreCase) )

Now, expand the column.

The code will be:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sold items", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Table2, (x)=> Text.Contains ([Sold items], x[Products], Comparer.OrdinalIgnoreCase) )),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Products"}, {"Products"})
in
#"Expanded Custom"

[JW_van_Holst]

Here you are:

let
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckrMA8JipVidaCXHgoKcVDDLqzQzGcIKSM1LzsxRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Products = _t]),
    listOfProd = Source2[Products],
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYzLCYBADAVbeexZi9AuZPEQNYqQTRajqN37u8och5kYQ8MitqMjvXGU6E19Sww309AWMVTo7ICN8EQioJyF/TW1bAzSAec3yaz9LHBe/0JbSKenbC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sold Items" = _t]),
    #"Added Custom" = Table.AddColumn(Source1, "Product", (x)=>  
        let
            attempt =  try List.Select(listOfProd, each Text.Contains(x[Sold Items], _, Comparer.OrdinalIgnoreCase) ){0},
            result =    if attempt[HasError] then
                            null
                        else
                            attempt[Value]
        in
            result
    )
in
    #"Added Custom"

[Vijay_A_Verma]

You can use this in a custom column named Products

= try TableB[Products]{List.PositionOf(TableB[Products],[Sold items],0,(x,y)=>Text.Contains(y,x,Comparer.OrdinalIgnoreCase))} otherwise null