• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Preventing Duplicates when merging tables with multiple matching rows|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Preventing Duplicates when merging tables with multiple matching rows|Power Query|Excel Forum|My Online Training Hub

vba course banner

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 QueryPreventing Duplicates when merging …
sp_PrintTopic sp_TopicIcon
Preventing Duplicates when merging tables with multiple matching rows
Avatar
Cheryl
Michigan, USA
Member
Members
Level 0
Forum Posts: 44
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
1
January 4, 2019 - 6:51 am
sp_Permalink sp_Print

I have two tables to merge which list student courses - multiple courses (from different schools) per student on each list .  My goal is to be able to list each course once for the student in question.   I am matching on the students college ID number.  The query results in multiple matching rows (each unique row on one table brings multiple matching rows from the other table for each student ID).  What type of merge could I use to prevent getting duplicates when the two tables are merged.  I don't need all students from each list - only one list contains all of the students I want to report out.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
January 4, 2019 - 7:41 am
sp_Permalink sp_Print

Hi Cheryl,

Thanks for attaching your file. I presume it contains the two tables you want to merge. Can you also provide the desired result you'd like to see as I don't know which list "contains all of the students you want to report on", or how you'd like the final data to appear as there are different columns in the two lists.

Thanks,

Mynda

Avatar
Cheryl
Michigan, USA
Member
Members
Level 0
Forum Posts: 44
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
3
January 5, 2019 - 12:07 am
sp_Permalink sp_Print sp_EditHistory

The result I would like should list the student, and each course in the a column that indicates the term that the student took the course.  (school year and term mean the same thing in the different lists).  If I could figure out  how to pivot the columns to get that result it might work, but when I do that with all of my data , I get an error.  I am wondering if it would be best to create two queries that I could append instead of trying to merge?

sp_AnswersTopicAnswer
Answers Post
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
January 5, 2019 - 1:13 pm
sp_Permalink sp_Print

Sorry Cheryl, but I need a more explicit 'desired result' example. Your data is familiar to you, but it's not to me.

When I look at the 'desired result' you've provided, my first question is where does the 'Term' data come from. What does it look like. Then I wonder what sheet the 'classname' comes from and what column. Then I wonder if you're only expecting to see one row per student, or could some students have multiple rows.

Please show me a complete example of how you want to see the final data for the two students listed in your source sheets in the 'desired result' sheet. I can then map the source and understand what goes where and I can answer the above questions from your example.

Thanks,

Mynda

Avatar
Cheryl
Michigan, USA
Member
Members
Level 0
Forum Posts: 44
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
5
January 9, 2019 - 1:07 am
sp_Permalink sp_Print

Thank you - Sorry for not being more clear.  Unfortunately, the data I am trying to merge comes initially from so many confidential files containing personal protected data that I don't think I can prepare an example that I can share publicly. 

The Term data is listed as Term on the "All Students" worksheet and "SchoolYear" 

Classname and CourseTitle are contain the same data, but different names per sheet.  

After more consideration, I need to prepare queries that can be appended instead of merging.  I am going to create queries that contain the same header names for these two compiled sources of data and attempt to append the files, instead of merging them.  From there I should be able to remove duplicates and potentially group by the students' college_id/Valley No. and course name (which also represent the same data).  This may still result in multiple rows per student.  And I would love to not have those rows duplicated, but not sure how to pivot them to get one row over multiple columns (for each different term) since students take more than one course per year.

If this is inadequate information to allow you to help me with this, please just delete my topic if you like.  I cannot provide a better sample.

Cheryl

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
January 9, 2019 - 4:41 pm
sp_Permalink sp_Print

Hi Cheryl,

I don't need to see the confidential data. The example file is fine, the only part missing is how you want those two sheets to look in the final desired result. If you can do a mock up of the desired result using the example data in the attached file then I can trace how the data gets from the two source sheets to the final result you want.

It's much easier to see what you want, than it is to visualise it from a description, so I'll wait for the example file.

Mynda

Avatar
Cheryl
Michigan, USA
Member
Members
Level 0
Forum Posts: 44
Member Since:
December 8, 2016
sp_UserOfflineSmall Offline
7
January 10, 2019 - 5:50 am
sp_Permalink sp_Print

Okay - trying again.  See attached desired result.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
January 25, 2019 - 11:14 am
sp_Permalink sp_Print

Hi Cheryl,

Sorry for my slow reply. I've been on vacation. Thanks for providing the sample file. It helps a lot.

I can see how most of the columns map from the two source tables to the desired result, however it's not clear how to map the SchoolClassCodes to the Course Titles. e.g. how can I tell that CULI 01 1S is for The Science of Food and CULI 02 2S is for Culinary & Food Industry Math? I need some values present in both tables that map the SchoolClassCodes to the Course Titles.

Mynda

Avatar
Rya Osmon

New Member
Members
Level 0
Forum Posts: 1
Member Since:
January 10, 2020
sp_UserOfflineSmall Offline
9
January 10, 2020 - 2:18 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mynda,

Hope all is well with you.

I have a similar issue. I have lookup tables. Dim A, Dim B merged and become Dim C. I have no issue with this. Unfortunately, it seems that because Dim A is consider incomplete, some of the data did not show up in the summary  after we merged transactions table with Dim C. so, we realized that we have to append Dim A with Dim D and then merge the appended table with Dim B to get DimE.

We had no issue with earlier steps. But once we merge the transactions table with DimE, we get multiple matching rows. Since we have to Sum the end result, this in return give us wrong calculation. 

For example, on 10th July, there are 3 matching rows. so, in Transaction II, we get 400, 400, 400 and -50, -50, -50. What we really want is

10/7/2019     400 -50

10/7/2019     (blank)

10/7/2019     (blank)

When power query returns duplicate result, is there a way to just get the first match and blanks for the rest of matching rows? 

Thank you.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
January 10, 2020 - 9:53 pm
sp_Permalink sp_Print

Hi Rya,

In future, please start a new thread for a new dataset. If your question is similar to another then you can reference it in your question.

Dimension table B shouldn't be merged with A and D. I would just merge A and D, then create a proper date dimension table, which is what Dimension table B is. 

Tables A and D aren't dimension tables since there are multiple batches on a single date, e.g. 10 July 2019. I don't see how batch number 265 is duplicated since it's present in both table D on 8th July and 10th July. Not sure what you were expecting here.

I hope that points you in the right direction.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti, Misael Gutierrez Sr.
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
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.