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:
- Purchase Orders (Primary Key: PurchaseOrderID)
- Purchase Order Lines (Foreign Key: PurchaseOrderID)
- Sales Orders (Primary Key: SalesOrderID)
- Sales Order Lines (Foreign Key: SalesOrderID)
- 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!
