I have two sets of data. Some of their column headers are the same while some of them are not. I apply either "Combine & Transform" or "Transform" to a query, but the data for the 2nd set (CA-CF) do not appear even though they have the same column headers.
Please view the video below:
https://www.loom.com/share/ab0fbc65e81548baba4d166794c04037
The first set of data: Columns T-X:
| Last Name | 2nd Child Grade | 2nd Child Gender | 2nd Child Start Date | 2nd Child School Attended |
The second set of data: Columns CA-CF
| First Name | Last Name | 2nd Child Gender | 2nd Child Grade | 2nd Child email (Gmail only) | 2nd Child School Attended |
| Rohan | Vadlamani | Male | 6th | Windemere Ranch Middle School |
Hi Jim,
Because your dataset has duplicate column names, Power Query renames the duplicates 'First_Name_2', 'First_Name_3' and so on. It looks like the renaming of the duplicate columns do not match in Power Query and therefore aren't being matched when appending.
I recommend you rename the column names in your source files to make them unique and match as you'd expect.
Mynda
Mynda,
I edit the column names for both sets of data so the column headers of the children's last names and first names are now unique, but the data of the 2nd child on the first set of data still don't appear:
1. Row three on the 2nd data set.
2. Row 53 after Power Query.
1st set of data: Columns S-X
| First Name (2nd child) | Last Name (2nd child) | 2nd Child Grade | 2nd Child Gender | 2nd Child Start Date | 2nd Child School Attended |
2nd set of data: Columns CA-CF
| First Name (2nd child) | Last Name (2nd child) | 2nd Child Gender | 2nd Child Grade | 2nd Child email (Gmail only) | 2nd Child School Attended |
I found that it works if I reverse the order of two data sets
1. What does it mean to me? What is the best way to accomplish this task?
2. How can use Power Query to combine all column headers if some column headers from two data sets are different? I don't want to use "Append Tables".
Hi Jim,
File 2022-After_School_(in-person)2022-09-02_19_34_34.csv only has 37 columns. The second child names in 2022_Fall_Online_Classes2022-09-02_19_35_29.csv starts in column 79, but only 37 columns of the second file are being imported.
You can import these files as separate queries (get data > from csv file), then merge them using Full Outer join type which brings in all columns from both tables.
Or you can choose the 2022_Fall_Online_Classes2022-09-02_19_35_29.csv as your sample file.
Mynda
Mynda,
I use Merge Query to merge two queries: Online & In-person. However, it doesn't work as expected. It creates additional columns to hold data from the 2nd Query instead of adding them up under the same column headers. I have made all column headers unique.
| Submission Date | First Name | Last Name | Cell Phone #1 | Cell Phone #2 | Email #1 | Email #2 | First Name (1st child) | Last Name (1st child) | 1st Child School Attended | 1st Child Grade | 1st Child Gender | First Name (2nd child) | Last Name (2nd child) | 2nd Child Grade | 2nd Child Gender | 2nd Child School Attended | First Name (3rd child) | Last Name (3rd child) | 3rd Child Grade | 3rd Child Gender | 3rd child Start Date | 3rd Child School Attended |
My bad, I should have said Append, not Merge. No need to get files from folder, just get each CSV file using Get Data > From Text/CSV. See attached - be sure to edit the source settings to point at the files on your PC.
