Forum Discussion
Power Query Compare two tables containing duplicates in both
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/170090 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!
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"
1 Reply
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"