Forum Discussion
Merge Two Tables based on Qualification
Hello,
I have two tables. Table 1 shows the School and the different Standards they have. Table 2 shows the teachers and their qualifications based on the three Standards. Table 3 shows the possible lists of teachers that can be accepted based on the three standards. Example School A had 7 possible Teachers wherein it doesn't have any Standard yet therefore all teachers can apply in School A. In School B had only Nadia since she's the only one who has passed Standard 1 and Standard 3. How can I ahieve this list in PowerBI?
Table 1. Student
School | Standard1 | Standard2 | Standard3 |
A | 0 | 0 | 0 |
B | 1 | 0 | 1 |
C | 0 | 1 | 0 |
D | 1 | 1 | 1 |
E | 1 | 1 | 0 |
Table 2. Teacher
Teacher Name | Standard1 | Standard2 | Standard3 |
Gretchen | 0 | 1 | 1 |
Nadia | 1 | 1 | 1 |
Inca | 0 | 1 | 0 |
Michelle | 0 | 0 | 1 |
Melina | 1 | 0 | 0 |
Gail | 0 | 1 | 1 |
Tessa | 1 | 1 | 0 |
Table 3. Qualified
School | Teacher |
A | Gretchen |
A | Nadia |
A | Inca |
A | Michelle |
A | Melina |
A | Gail |
A | Tessa |
B | Nadia |
C | Gretchen |
C | Nadia |
C | Inca |
D | Nadia |
E | Tessa |
E | Nadia |
Thank you very much,
Alvin
Hey,
first make the names of the Standards in both tables distinct, like S_S1, S_S2, and S_S3 for the Standards in the school table and T_S1 ... in the teachers table
Assuming that your tables are called School and Teacher ...In PowerQuery (meaning no DAX) add a new custom column to the school table like so:
Basically this will create a cartesian product of both tables ...
Expand the table "Teacher"
Now create 3 custom columns called Q_S1, Q_S2_, and Q_S3 (Q means qualifies), here is the formula for Q_S1:
if [S_S1] = 0 then 1 else if [T_S1] = 1 then 1 else 0
This creates the value 1 if both columns (School and Teacher) have a value of 1 or the school value is 0, otherwise the formula returns 0.Adjust the column references for the columns Q_S2 and Q_S3 accordingly.
Create another custom column and call it "Qualified" or something similar and use this formula:
if [Q_S1] + [Q_S2] + [Q_S3] = 3 then "qualified" else "not qualified"
Now you have a "School" table that also contains the "Teacher" content and a column called "Qualified" that you can use to filter.
Hopefully this gives you an idea.
Regards,
Tom
5 Replies
Can you post that data as something that can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
I apologize for this. Will correct it.
Thank you very much
Hey,
first make the names of the Standards in both tables distinct, like S_S1, S_S2, and S_S3 for the Standards in the school table and T_S1 ... in the teachers table
Assuming that your tables are called School and Teacher ...In PowerQuery (meaning no DAX) add a new custom column to the school table like so:
Basically this will create a cartesian product of both tables ...
Expand the table "Teacher"
Now create 3 custom columns called Q_S1, Q_S2_, and Q_S3 (Q means qualifies), here is the formula for Q_S1:
if [S_S1] = 0 then 1 else if [T_S1] = 1 then 1 else 0
This creates the value 1 if both columns (School and Teacher) have a value of 1 or the school value is 0, otherwise the formula returns 0.Adjust the column references for the columns Q_S2 and Q_S3 accordingly.
Create another custom column and call it "Qualified" or something similar and use this formula:
if [Q_S1] + [Q_S2] + [Q_S3] = 3 then "qualified" else "not qualified"
Now you have a "School" table that also contains the "Teacher" content and a column called "Qualified" that you can use to filter.
Hopefully this gives you an idea.
Regards,
Tom
