Compare Two Lists

[AllisterB]

I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the web.
I need to be able to know for each Person (First name +las tname) at a Branch, the differences in the Role and Date Appointed between the two Lists.
It is possible for a Branch that the Person is on one List but not on the other.
[Once the differences are identified the user will check what is the correct Information and make changes to the lists to get them to match]

I have tried using PQ Merge but couldn't get this to work.

Has anyone got any ideas

Thanks

Allister

Book2
	E	F	G	H	I	J	K	L	M	N	O	P	Q
4	List 100							List 200
5	Branch	Role	Appomited	First Name	Last Name			Branch	Job	First Name	Last Name	Appomited
6	A	Worker	1/03/2020	Mickey	Mouse			A	Worker	Mickey	Mouse	1/03/2020
7	B	Manager	3/06/2019	Donald	Duck			A	Manager	Goofy	Brown	14/03/2018
8	A	Manager	14/03/2018	Goofy	Brown			A	Worker	Jasmin	Orange	18/03/2012
9	A	Worker	1/06/2007	Jasmin	Orange			B	Worker	Harry	Hall	1/04/2017
10	B	Worker	30/08/2011	Popey	Sailor			C	Manager	Jonny	Appleseed	31/03/1945
11	B	Worker	1/04/2017	Harry	Hall			D	Worker	Sally	Trout	30/12/2018
12	C	Manager	31/03/1945	Jonny	Appleseed
13	C	Worker	11/11/2011	Daffy	Tailor
14
List 2

 

[shaowu459]

Creat Two tables: List100 and List200

Merge Queries as New:

the M code:

let
    Columns={"Branch", "First Name", "Last Name"},
    Source = List100&
                     Table.NestedJoin(
                                      List200, Columns,
                                      List100, Columns,
                                      "List100",
                                      JoinKind.LeftAnti
                                      )[[Branch],[First Name],[Last Name]],
    res = Table.ExpandTableColumn(
                                  Table.NestedJoin(
                                                   Source,Columns,
                                                   List200,Columns,
                                                   "n", JoinKind.LeftOuter
                                                  ),
                                  "n",
                                  {"Job", "Appomited"},
                                  {"Job", "Appomited.200"}
                                 )
in
    res

final result as below:

Book1.xlsx
	A	B	C	D	E	F	G
13	Branch	Role	Appomited	First Name	Last Name	Job	Appomited.200
14	A	Worker	2020/3/1	Mickey	Mouse	Worker	2020/3/1
15	B	Manager	2019/6/3	Donald	Duck
16	A	Manager	2018/3/14	Goofy	Brown	Manager	2018/3/14
17	A	Worker	2007/6/1	Jasmin	Orange	Worker	2012/3/18
18	B	Worker	2017/4/1	Harry	Hall	Worker	2017/4/1
19	B	Worker	2011/8/30	Popey	Sailor
20	C	Manager	1945/3/31	Jonny	Appleseed	Manager	1945/3/31
21	C	Worker	2011/11/11	Daffy	Tailor
22	D			Sally	Trout	Worker	2018/12/30
Sheet5