Joining multiple columns in relationship model using all primary keys

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.

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.