Forum Discussion
How to merge 2 tables in PowerQuery using substrings?
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) | +------------------------------+----------+
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"
3 Replies
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 nullHere 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"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"
