Combing two Data list together

[tqn626]

I'm trying to merge two tables together. One is a customer list and another is a product list. I want to combine the two and expand the customer list with the product list. How would I do this in power query.

Customer List
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product List[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Consumer[/TD]
[/TR]
</tbody>[/TABLE]

Product List
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product List[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Food[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Food[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Consumer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Product 3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Consumer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Product 4[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Consumer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Product 5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Consumer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Product 6[/TD]
[/TR]
</tbody>[/TABLE]

Result

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product List[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD]Food[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD]Food[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Consumer[/TD]
[TD]Product 3[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Consumer[/TD]
[TD]Product 4[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Consumer[/TD]
[TD]Product 5[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Consumer[/TD]
[TD]Product 6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

I'm not familar with power query: I assume it will be the same though as normal Excel queries.

SELECT C.Customer, C.[Product List], P.Product
FROM Customer_List C, Product_List P
WHERE C.[Product List] = P.[Product List]

which can also be written as

SELECT C.Customer, C.[Product List], P.Product
FROM Customer_List C INNER JOIN Product_List P ON C.[Product List] = P.[Product List]

 

[sandy666]

try

[SIZE=1]let
    Source = Table.NestedJoin(Table10,{"Product List"},Table11,{"Product List"},"Table11",JoinKind.LeftOuter),
    #"Expanded Table11" = Table.ExpandTableColumn(Source, "Table11", {"Product"}, {"Product"})
in
    #"Expanded Table11"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Table10[/td][td][/td][td][/td][td]Table11[/td][td][/td][td][/td][td]Result[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Customer[/td][td=bgcolor:#5B9BD5]Product List[/td][td][/td][td=bgcolor:#5B9BD5]Product List[/td][td=bgcolor:#5B9BD5]Product[/td][td][/td][td=bgcolor:#70AD47]Customer[/td][td=bgcolor:#70AD47]Product List[/td][td=bgcolor:#70AD47]Product[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Customer1[/td][td=bgcolor:#DDEBF7]Food[/td][td][/td][td=bgcolor:#DDEBF7]Food[/td][td=bgcolor:#DDEBF7]Product 1[/td][td][/td][td=bgcolor:#E2EFDA]Customer1[/td][td=bgcolor:#E2EFDA]Food[/td][td=bgcolor:#E2EFDA]Product 1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Customer 2[/td][td]Consumer[/td][td][/td][td]Food[/td][td]Product 2[/td][td][/td][td]Customer1[/td][td]Food[/td][td]Product 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Consumer[/td][td=bgcolor:#DDEBF7]Product 3[/td][td][/td][td=bgcolor:#E2EFDA]Customer 2[/td][td=bgcolor:#E2EFDA]Consumer[/td][td=bgcolor:#E2EFDA]Product 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Consumer[/td][td]Product 4[/td][td][/td][td]Customer 2[/td][td]Consumer[/td][td]Product 4[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Consumer[/td][td=bgcolor:#DDEBF7]Product 5[/td][td][/td][td=bgcolor:#E2EFDA]Customer 2[/td][td=bgcolor:#E2EFDA]Consumer[/td][td=bgcolor:#E2EFDA]Product 5[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Consumer[/td][td]Product 6[/td][td][/td][td]Customer 2[/td][td]Consumer[/td][td]Product 6[/td][/tr]
[/table]

 

[tqn626]

This works great, thank you!

 

[sandy666]

You are welcome