Joining multiple columns in relationship model using all primary keys

[martinnilsson]

Hi,

I am a newbie to Power BI.  I imported two separate data sources, one with sales data and one with open orders data.  I would like to join these two data sources to be able to view open orders data in realtion to the sales data.

In a database environment I would join the two tables using the primary keys (Division, Year, Month, BU, PG, Cust no & Item no) to see the Qty, Amount, Cost, Qty_on_order & Value on the same row.

Can this be done in Power Query or is it a better solution to create the query in my database environment and import the query result to Power BI?

Sales data table

Division	Year	Month	BU	PG	Cust no	Item no	Qty	Amount	Cost
100	2025	1	30	200	1234	ABC	10	100	50
100	2025	1	31	300	9999	ABC	10	100	50
200	2025	2	30	100	1234	XYZ	5	200	150

Open Orders table

Division	Year	Month	BU	PG	Cust no	Item no	Qty_on_order	Value
100	2025	1	30	200	1234	ABC	100	5000
200	2025	2	30	100	1234	XYZ	10	1500

Result table after joining above tables

Division	Year	Month	BU	PG	Cust no	Item no	Qty	Amount	Cost	Qty_on_order	Value
100	2025	1	30	200	1234	ABC	10	100	50	100	5000
100	2025	1	31	300	9999	ABC	10	100	50
200	2025	2	30	100	1234	XYZ	5	200	150	10	1500

Kind regards,

-M.

[Cookistador · Accepted solution]

Hi @martinnilsson 

It is possible, this is how to achieve that

• Select one of your tables (it doesn't matter which one).
• Go to the "Home" tab in the Power Query Editor ribbon.
• Click on the "Merge Queries" dropdown and select "Merge Queries as New" (recommended to keep your original tables intact) or "Merge Queries" (if you want to modify the existing table).
• A "Merge" dialog box will appear.
• Select the Primary Table: The table you selected in the previous step will be at the top.
• Select the Secondary Table: Use the dropdown below to select the other table you want to join (either "Sales data table" or "Open Orders table").
• Select Matching Columns:
  • In both tables, select the columns that form the join keys. Based on your description, these are: Division, Year, Month, BU, PG, Cust no, and Item no.
  • Click on each of these column names in both table previews while holding down the Ctrl key to select multiple columns. The order in which you select them matters. Power Query will try to match them based on the selection order 
  • The dialog box will display the number of matching rows.
  • Below the table previews, you'll see a dropdown for "Join Kind". You need to choose the appropriate join type based on your requirements$

• After clicking "OK", a new column will be added to your first table. This column will contain nested tables from the second table (for the matching rows). The column header will likely be the name of your second table.
• Click on the double-headed arrow (Expand icon) in the header of this new column.

While merging the tables directly will work, it's not the most optimal approach for a well-performing Power BI model. Ideally, you should restructure your data into fact and dimension tables. If that's not immediately possible, consider creating a single combined key column from your join fields in both tables. For better performance during the join, aim to represent this combined key as a number if feasible, rather than text.

[v-venuppu · Accepted solution]

Hi @martinnilsson ,

Thank you for reaching out to Microsoft Fabric Community.

You can achieve the join of your sales data and open orders data within Power Query. Here's some steps to achieve it:

• First, load both your sales data and open orders data into Power BI and click on "Transform Data" which will redirect you to Power Query.
• In Power Query, use the "Merge Queries" feature to join the two tables. You can find this option in the "Home" tab.
• Select the sales data table as your primary table and the open orders data table as your secondary table.
• Choose the columns you want to use as keys for the join. In your case, you would select Division, Year, Month, BU, PG, Cust no, and Item no from both tables.
• Choose the type of join you want to perform. For your requirement, a "Left Outer Join" would be appropriate, as it will include all rows from the sales data table and match rows from the open orders table.
• After merging, you will need to expand the columns from the open orders table to include Qty_on_order and Value in your result table. You can do this by clicking on the expand icon next to the merged column and selecting the columns you want to include.

Please go through the attached files for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Regards,

Rama U.

[Cookistador · Accepted solution]

Hi @martinnilsson 

It is possible, this is how to achieve that

• Select one of your tables (it doesn't matter which one).
• Go to the "Home" tab in the Power Query Editor ribbon.
• Click on the "Merge Queries" dropdown and select "Merge Queries as New" (recommended to keep your original tables intact) or "Merge Queries" (if you want to modify the existing table).
• A "Merge" dialog box will appear.
• Select the Primary Table: The table you selected in the previous step will be at the top.
• Select the Secondary Table: Use the dropdown below to select the other table you want to join (either "Sales data table" or "Open Orders table").
• Select Matching Columns:
  • In both tables, select the columns that form the join keys. Based on your description, these are: Division, Year, Month, BU, PG, Cust no, and Item no.
  • Click on each of these column names in both table previews while holding down the Ctrl key to select multiple columns. The order in which you select them matters. Power Query will try to match them based on the selection order 
  • The dialog box will display the number of matching rows.
  • Below the table previews, you'll see a dropdown for "Join Kind". You need to choose the appropriate join type based on your requirements$

• After clicking "OK", a new column will be added to your first table. This column will contain nested tables from the second table (for the matching rows). The column header will likely be the name of your second table.
• Click on the double-headed arrow (Expand icon) in the header of this new column.

While merging the tables directly will work, it's not the most optimal approach for a well-performing Power BI model. Ideally, you should restructure your data into fact and dimension tables. If that's not immediately possible, consider creating a single combined key column from your join fields in both tables. For better performance during the join, aim to represent this combined key as a number if feasible, rather than text.