Power Query Compare two tables containing duplicates in both

[Anonymous]

Dear Excel Guru. Would appreciate for any support or idea how to compare 2 different tables in Power Query if in both tables can be duplicated values When i apply Full Outer Join i got some result which I was not expected to get. Its match every item 

Table1

Site ID	PO	Item	amount
sh001	h006	rru	10
sh001	h006	rru	10
aw001	14032	tower	150
bg001	h007	grid connection	100

Table2

Site ID	PO	Item	amount
bg001	h007	grid connection	110
sh001	h006	rru	10
we023	h006	unit 1	19

 Result of Full Outer Join below:

Site ID	PO	Item	amount	Site ID.1	PO.1	Item.1	amount.1
sh001	h006	rru	10	sh001	h006	rru	10
sh001	h006	rru	10	sh001	h006	rru	10
bg001	h007	grid connection	100	bg001	h007	grid connection	110
aw001	14032	tower	150				
				we023	h006	unit 1	19

But my expected result is below:

Site ID	PO	Item	amount	Site ID.1	PO.1	Item.1	amount.1
sh001	h006	rru	10	sh001	h006	rru	10
sh001	h006	rru	10				
bg001	h007	grid connection	100	bg001	h007	grid connection	110
aw001	14032	tower	150				
				we023	h006	unit 1	19

hereis the simple code in Power Query which i used:

let
    Source = Table.NestedJoin(Table1,{"Site ID", "PO", "Item"},Table2,{"Site ID", "PO", "Item"},"Table2",JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Site ID", "PO", "Item", "amount"}, {"Site ID.1", "PO.1", "Item.1", "amount.1"})
in
    #"Expanded Table2"

i have found one post https://community.powerbi.com/t5/Desktop/Merge-two-tables-without-missing-any-rows-and-without/td-p/... seems like for the same issue but i couldnot understand solution and it was done in Power BI.

Many thanks in advance for any support!

[Anonymous · Accepted solution]

Thanks for good people! Problem solved by adding index row number for each Table 1 and Table 2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Site ID", "PO", "Item"}, {{"Index", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
    #"ExtendedIndex" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"amount", "Index"}, {"amount", "Index"})
in
    #"ExtendedIndex"

then Merge Full Outer

let
    Source = Table.NestedJoin(Table1,{"Site ID", "PO", "Item", "Index"},Table2,{"Site ID", "PO", "Item", "Index"},"Table2",JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Site ID", "PO", "Item", "amount"}, {"Table2.Site ID", "Table2.PO", "Table2.Item", "Table2.amount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Index"})
in
    #"Removed Columns"