Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
This is a simplifed version of my data.
I have two tables 1 and 2. I want to merge them in a way that will result in table final.
Table1 is a master data table and all of them should appear in the final table.
Table2 is going to be added (merged) to Table1.
The important conditions for merging are:
Table1:
No | Person | Unit | Class | Status_R |
1 | Jack | A | TTA | M |
2 | Jack | A | CPR | O |
3 | Jack | A | PPE | M |
4 | Michael | B | TTA | M |
5 | Michael | B | CPR | O |
6 | Michael | B | PPE | R |
7 | Sarah | C | TTA | M |
8 | Sarah | C | CPR | M |
9 | Sarah | C | PPE | M |
10 | Zoe | D | TTA | O |
11 | Zoe | D | CPR | R |
12 | Zoe | D | PPE | R |
Table2:
No | Person | Class | Status |
1 | Jack | ABS | C |
2 | Jack | TTA | P |
3 | Jack | CPR | D |
4 | Michael | ABS | C |
5 | Michael | XYZ | C |
6 | Sarah | TTA | P |
7 | Sarah | PPE | D |
8 | Sarah | CPR | D |
9 | Andy | ABS | C |
10 | Andy | TTA | C |
Result Table - After combining:
No | Person | Unit | Class | Status_R | Status |
1 | Jack | A | TTA | M | P |
2 | Jack | A | CPR | O | D |
3 | Jack | A | PPE | M | |
4 | Jack | A | ABS | C | |
5 | Michael | B | TTA | M | |
6 | Michael | B | CPR | O | |
7 | Michael | B | PPE | R | |
8 | Michael | B | ABS | C | |
9 | Michael | B | XYZ | C | |
10 | Sarah | C | TTA | M | P |
11 | Sarah | C | CPR | M | D |
12 | Sarah | C | PPE | M | D |
13 | Zoe | D | TTA | O | |
14 | Zoe | D | CPR | R | |
15 | Zoe | D | PPE | R |
Can someone please help in doing this task?
Thanks
Solved! Go to Solution.
I found a solution to this.
I created this table which contains unique info of persons:
Table0:
No | Person | Unit |
1 | Jack | A |
2 | Michael | B |
3 | Sarah | C |
4 | Zoe | D |
Then, merged Table2 with Table0 (right join on Table0) to create a new modified Table2. This removes all extra ones, such as "Andy" entries.
This new table was then full outer joined on Table1.
I found a solution to this.
I created this table which contains unique info of persons:
Table0:
No | Person | Unit |
1 | Jack | A |
2 | Michael | B |
3 | Sarah | C |
4 | Zoe | D |
Then, merged Table2 with Table0 (right join on Table0) to create a new modified Table2. This removes all extra ones, such as "Andy" entries.
This new table was then full outer joined on Table1.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |