Comparing Two Data Sets and Finding the differences?
Our take
I have two reports that can be pulled from two systems at work: 1) our normal inventory reporting system and 2) a production software that tracks where in the process a particular part or widget is at; we are in the process of fully implementing this production software and making sure that both reports are integrated with one another (what appears or is entered in inventory as a customer order also appears in the production software and vice versa). In both softwares/systems, I can see the purchase order from the customer, the part number, and the quantity. In the production software, I also have the work order we've assigned internally.
However, my issue is that the two systems record quantities in different ways. The inventory system records the quantity 1-by-1 so one row is 1 unit of measure. The production software groups the quantities by what step in the production process the parts are in for that particular customer order. Sample data below.
Inventory System:
| Customer PO | Part Number | Qty<br type="_moz"> |
|---|---|---|
| 123 | ABC | 1 |
| 123 | ABC | 1 |
| 123 | ABC | 1 |
| 456 | XYZ | 1 |
| 456 | XYZ | 1 |
Production System:
| Customer PO | Part Number | Qty | Work Order<br type="_moz"> |
|---|---|---|---|
| 123 | ABC | 3 | 100 |
| 456 | XYZ | 2 | 200 |
I am needing to reconcile the differences between these two datasets in a cleaner way, so we can identify what orders are missing in what system, or what quantities, etc. For reference, the inventory system has ~15,000 rows and the productions system has ~600. When we've finished implementing the new production software, the totals quantities between the two softwares will match when fully integrated. Just needing to identify the differences so we can track down the particular issue as to why/why not it does not show in the other software.
I compared the two using two PivotTables and an INDEX/MATCH, but am looking for something cleaner and more succinct, almost like a report. I tried feeding the two through PowerQuery, but merging the two datasets results in... a mess.
Any ideas or direction? Open to PowerQuery, PowerBI, whatever. I'm using Office 365.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Cross checking data from two different systems but they export in different formats - how to marry them up?I have a table with two sets of data exported from two different systems that I need to reconcile - names and values. Trouble is they export in different formats so I'm needing partial look-up? The lists are also not exact duplicates so there are names on one that don't appear on the other. Once the names have been matched up I then need to cross check the value to isolate any variances. So two formulas for two columns. thanks submitted by /u/pandoras_picnic [link] [comments]
- Automate list size reductionI'm going to try to lay this out succinctly, let's see how I do: I am trying to build a flexible re-usable sheet to dump two reports into and receive a comparison report. One report has a list of date time stamps for a variable number of work orders (Dt), each with a variable number of entries. The second has a list of date time stamps for a variable number of inspections that also have work orders (Mt), but also have way more work orders, and often are missing inspections for some work orders entirely. What I want out the end is a report for each produced time stamp (Dt) that tells me how separate it is from the most recent (before or after) inspection (Mt). What I do right now is basically a min(abs(Dt-filter( (Mt)*(does Mt work order match Dt work order)))). It works, but oh em gee the computation, because every Dt entry gets compared to EVERY Mt computation. After that I just look at a pivot table of the results to see max time and average. This tells me if people went too long saving parts (Dt time stamp) but NOT measuring parts (Mt). Is there a way to take each of the original lists, have them pushed/pulled to a variable width series of columns (one per Work Order), then have a variable length comparator look at the first set of columns and do that "time to closest inspection" but only with the matching column from the second set of data? I know I could do it if I make them part of a data model and just slowly go through each work order, but I'd prefer it to just spit out the results at the end so I can hand this sheet off to others and not have to manually do the adjustments every day. Let me know if this isn't clear and I can try to set up some example mini-tables or something to better explain my gibber jabber above. e.t.a. uh Version 2603 (Build 19822.20150 Click-to-Run) submitted by /u/NeedIQMSHelp [link] [comments]