Log In
Register
Home
December 8, 2016
Offline
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.
See Answer
July 16, 2010
Offline
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
December 8, 2016
Offline
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?
Answers Post
July 16, 2010
Offline
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
December 8, 2016
Offline
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
July 16, 2010
Offline
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
December 8, 2016
Offline
Okay - trying again. See attached desired result.
July 16, 2010
Offline
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
New Member
January 10, 2020
Offline
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.
July 16, 2010
Offline
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
1 Guest(s)
