Creating a new table from two tables

[EllaDahan]

I have two tables as follows:

1. A list of products (50 rows)

2. A list of locations (10 rows)

I want to create a third table that will have a row for each combination of product and location. I will end up with a table of 50 x 10 = 500 rows. 

What's the best way to do this? 

[EllaDahan · Accepted solution]

I found this online which gave me the solution I wanted: 

https://gorilla.bi/power-query/crossjoin/

[AnalyticPulse]

you can create a relationship between the "Products" table and the "Locations" table and then use the "Merge Queries" feature to create a new table that combines both tables based on this relationship. Here's a step-by-step guide:

1. Load the Tables:

   • Import the "Products" and "Locations" tables into Power BI.

2. Create Relationships:

   • Open the "Relationships" view by clicking on the "Model" icon in the left sidebar.
   • Drag the appropriate field from the "Products" table to the corresponding field in the "Locations" table to create a relationship between them.

3. Create a New Table:

   • Go back to the "Data" view by clicking on the "Data" icon in the left sidebar.

4. Merge Queries:

   • Click on the "Home" tab in the Power BI ribbon.
   • Click on "Transform Data" to open the Power Query Editor.
   • In the Power Query Editor, select the "Products" table.
   • Click on the "Home" tab again and then click on "Merge Queries."

5. Configure Merge:

   • In the "Merge Queries" dialog, select the "Locations" table in the dropdown.
   • Choose the related fields for the merge. These are the fields that create the relationship between the two tables.
   • Choose the type of join (usually "Inner" join is appropriate).
   • Click "OK" to merge the queries.

6. Expand Merged Table:

   • After merging, you will see a new column with a small icon in the header. Click on this icon to expand the merged table.
   • Choose the columns you want to include in the final table.
   • Click "OK" to apply the changes.

7. Load Data:

   • After expanding the table, click on "Close & Apply" to apply the changes and load the new table into your Power BI model.

Now, you should have a new table that contains all possible combinations of products and locations. This table will have 500 rows (50 rows from the "Products" table multiplied by 10 rows from the "Locations" table).

Blog: AnalyticPulse 
Youtube Channel: AnalyticPulse