Power Query Data Merge Issue: Client Project

Hello everyone,

I’m facing a challenge with a data model in Power Query. Here’s a breakdown of the tables I’m working with:

  1. Purchase Orders (Primary Key: PurchaseOrderID)
  2. Purchase Order Lines (Foreign Key: PurchaseOrderID)
  3. Sales Orders (Primary Key: SalesOrderID)
  4. Sales Order Lines (Foreign Key: SalesOrderID)
  5. PurchaseOrderSalesOrder (This table illustrates the relationship between Sales Orders and Purchase Orders. It’s worth noting that some Sales Orders are linked to multiple Purchase Orders.)

My objective is to amalgamate these tables to create a comprehensive master table for subsequent calculations. While I’ve merged these tables in pursuit of a solution, I’ve encountered a specific issue that I need assistance with.

For context, consider a Sales Order associated with two Purchase Orders. When I attempt to merge the Sales Order table to retrieve the corresponding Purchase Orders, the merge process results in the Sales Order being duplicated. This behavior is consistent with a Left Outer Join, yet it also inadvertently doubles the Sales Order Amount due to the duplication.

Given that this pertains to an ongoing client project, I’m unable to share the raw data here. However, I’d be willing to compensate anyone who can offer their expertise on a consultancy basis. Appreciate your help!

I don’t know if I understand very well, but see if it helps.

Try to merge and union the tables

PBIX: ClientProject.pbix (24.0 KB)

Hi,

You mentioned that in your example, one Sales Order is associated with two Purchase Orders. When you join (no matter what type of join) these two tables via ‘PurchaseOrderSalesOrder,’ you will obtain 2 rows for the same Sales Order because it is associated with two Purchase Orders. If you want to summarize information from the Sales Orders without duplicating data, you should divide the Sales Order metrics by the number of rows from the Purchase Orders. Alternatively, you can create an additional column to store the percentage of the total value for each purchase line. Then, you can multiply the total value from the Sales Order table by this percentage