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

Customer	Product List
Customer1	Food
Customer 2	Consumer

<tbody>
</tbody>

Product List

Product List	Product
Food <tbody> </tbody>	Product 1
Food <tbody> </tbody>	Product 2
Consumer <tbody> </tbody>	Product 3
Consumer <tbody> </tbody>	Product 4
Consumer <tbody> </tbody>	Product 5
Consumer <tbody> </tbody>	Product 6

<tbody>
</tbody>

Result

Customer	Product List	Product
Customer1	Food	Product 1
Customer1	Food	Product 2
Customer 2	Consumer	Product 3
Customer 2	Consumer	Product 4
Customer 2	Consumer	Product 5
Customer 2	Consumer	Product 6

<tbody>
</tbody>

 

[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]

Table10			Table11			Result
Customer	Product List		Product List	Product		Customer	Product List	Product
Customer1	Food		Food	Product 1		Customer1	Food	Product 1
Customer 2	Consumer		Food	Product 2		Customer1	Food	Product 2
			Consumer	Product 3		Customer 2	Consumer	Product 3
			Consumer	Product 4		Customer 2	Consumer	Product 4
			Consumer	Product 5		Customer 2	Consumer	Product 5
			Consumer	Product 6		Customer 2	Consumer	Product 6

 

[tqn626]

This works great, thank you!

 

[sandy666]

You are welcome