Here's the disconnect in plain terms: you have two systems that should tell the same story, but they speak in different dialects. One counts every single unit like individual footsteps; the other groups those steps into stages. That gap isn't just a formatting headache, it's a visibility problem. When your inventory system shows 15,000 rows of individual units and your production system compresses that into 600 grouped records, you're not seeing mismatches, you're seeing two different languages for the same workflow. The real task isn't making the numbers match; it's building a translation layer that lets you spot where the story breaks.
Your pivot table and INDEX/MATCH approach got you partway there, but it's brittle. Every time you run a fresh report, you rebuild the same logic. PowerQuery was the right instinct, but merging on customer PO and part number alone collapses the detail you need. The production system's work order is the key you're leaving out. That internal identifier ties each grouped quantity back to the individual units in inventory. Without it, a merge becomes a guessing game. Instead of a straight merge, try this: in PowerQuery, unpivot the inventory rows so each unit becomes its own record, then group them by customer PO, part number, and work order (if you can derive that from the production system's context). Then join on those three fields. That gives you a clean comparison at the grouped level, exactly what you need to flag missing orders or quantity mismatches.
What you're really after is a reconciliation report, not a merged dataset. Power BI can handle this elegantly: load both tables, create a summary table in DAX that groups inventory by PO and part number, then build a visual that shows unmatched records side by side. The production system's work order becomes a cross-reference column. When the totals don't align, you trace back to the work order and check that specific batch. This isn't about making the systems identical, it's about giving yourself a single pane of glass where the differences are obvious. Once you identify the root cause for each mismatch, you can fix the integration piece by piece.
The endgame is a workflow where you don't have to reconcile at all. But until then, your job is to make the gap visible without reinventing the wheel every week. A grouped reconciliation report in Power BI, built once and refreshed with a click, turns 15,000 rows of noise into a focused list of exceptions. That's the report you take to your team. That's how you close the loop.