• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Combining duplicate part numbers with different descriptions|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Combining duplicate part numbers with different descriptions|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCombining duplicate part numbers wi…
sp_PrintTopic sp_TopicIcon
Combining duplicate part numbers with different descriptions
Avatar
Danielle Hicks

New Member
Members
Level 0
Forum Posts: 2
Member Since:
November 2, 2015
sp_UserOfflineSmall Offline
1
March 7, 2019 - 5:29 am
sp_Permalink sp_Print

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?

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 7, 2019 - 9:32 am
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Danielle Hicks

New Member
Members
Level 0
Forum Posts: 2
Member Since:
November 2, 2015
sp_UserOfflineSmall Offline
3
March 7, 2019 - 11:03 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 8, 2019 - 12:18 pm
sp_Permalink sp_Print

Aw, just glad I can help 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Dario Serrati, Nada Perovic, Andrew Er
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27279

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.