Forum Discussion

Mei_Jiang_Wu's avatar
Mei_Jiang_Wu
Copper Contributor
Nov 29, 2023

Help on Power Query Merging

Hello,

I have cleaned 5 tables in power query:

 

  Table 1 - contains student name and course 1 name columns

  Table 2 - contains student ID and course 2

  Table 3 - contains student name, student email and course 3

  Table 4 - contains student email and course 4

  Table 5 - contains student name, ID, email, instructor, helper 1 and helper 2

 

Table 1-4 contain other school students. Table 5 generated by school HR 

 

I am looking for a help on a solution - how to get a final report in the format of student name, ID, email, course 1, course 2, course 3, course 4, instructor, helper 1 and helper 2 columns. One student in one row shows all the courses with instructor and helpers (other school students will not have instructor and helpers since they are not on HR list.

 

Thanks,

Mei

 

BI & Data Analysis
excel
  • tauqeeracma's avatar
    tauqeeracma
    Steel Contributor
    Dec 16, 2023

    Hi Mei_Jiang_Wu 

     

    With the help of Power Query(PQ), you can easily merge all of your five tables.

     

    Since you can only merge two tables at a time in PQ, you need at least four steps:

     

    1. Using the Name column, merge Table 3 with Table 1 and give it a name as Course_1&3.

    2. Using the Email column, merge Course_1&3 with Table 4 and name as Course_1&3&4.

    3. Using the ID column, merge Table 5 with Table 2 and name as Course_2.

    4. Using the Email column, merge Course_2 with Course_1&3&4 and name it All_Courses.

     

    Points to note:

    I assume that all of your tables have information for all students, so use Inner as Join kind.

     

    For simplicity purposes, use the "Merge Queries as New" option.

     

     

    Please let me know if it works for you.

     

    Thanks

    Tauqeer

Resources