Combine (merge) two data tables depending on entries in power query

[Anonymous]

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:

• If a Person from Table2 is not available in Table1, then ignore it (do not include any of its entries in Table2 in merging) ("Andy") (=left join?).
• If a Person is available in Table1 and not in Table2, still include all its entries in final table ("Zoe") (=left join).
• If a Person from Table2 is available in Table1, then do full outer join for that Person ("Jack", "Michael", and "Sarah").

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

[Anonymous · Accepted 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.