My company is in the middle of changing our ERP system and I am trying to merge data from our old and new systems into one gross margin report for the year. Half of the year will be info from our old system and half from our new system. I have everything formatted the same and it is all working correctly, but the only problem is this... the descriptions for our part numbers have changed slightly from one system to the other. When I summarize all the data into a pivot table, I am getting two lines for the same part number because of the different descriptions and the duplicate line doesn't show the part#. Example:
Part Number Description Qty List Price Sales Price Discount Cost GRM GMP
953450210 Roc C18 32 3009.3 2985.3 24 2655.34 329.96 11.05%
Roc C18 10 120 96 20 0 82.98 13.02 13.56%
If I collapse these in my pivot table, it will combine both lines and show the part number and not the description. I need one of the descriptions to show (it doesn't matter which one). I tried to shorten the text for the description in Power Query to 12 characters, which eliminated most of the issues, but not all, as evidenced above. Is there anything else I can do in Power Query to get these descriptions to align?
Hi Danielle,
Create a new query that references the first. Remove everything except the part number and description columns. Since you don't mind which description gets used, select the part number column and 'Remove Duplicates'. This will leave you with a distinct list of part numbers and one description for each.
Now merge this query with your original one (at the top of the merge dialog box) using a 'Left Outer Merge', with the part number as the matching columns.
Expand the 'New Column' which will bring in the description and then you can remove the original description column that contains different descriptions for each part.
Mynda
That worked perfectly! Thank you so much! My boss and co-workers think I'm some kind of Excel wizard, but I always tell them that you taught me all the good stuff! Thanks, again!
Danielle
Aw, just glad I can help 🙂
