Log In
Register
Home
August 23, 2016
Offline
Dear Friends. There is very good example, how to compare two lists (https://www.myonlinetraininghu.....-two-lists). Is the any way or guidance how to manage Full Outer Join in case there are some duplicated items available in both table (left table,right table)? i have an issue, if one items repeated 3 time in left table and same item available only 1 time in right table the result will be incorrect using Full Outer Join: 3 items (in LT) = 3 items (in RT). Its incorrect cause i should get 3 - 1 (other 2 items in right table should be blank).
have an idea to create couples of merge queries. 1st one: to merge exactly all items where qty will be only 1. 2nd one: to group by as per main ID, Name, Qty... Will appreciate any feedback!
July 16, 2010
Offline
Hi Alex,
Your example file has 2 items in left table and 1 item in right table. The result when you don't group the tables prior to merging is what you describe as your desired result.
i.e. Your second merged table shows 2 items from the left table and 1 time from the right table, with a blank row for the duplicated item in table one that's not present in the right table.
From your description this is what you want, except your left table example only has 2 duplicates, instead of 3.
What am I missing?
Mynda
August 23, 2016
Offline
Mynda Treacy said
Hi Alex,Your example file has 2 items in left table and 1 item in right table. The result when you don't group the tables prior to merging is what you describe as your desired result.
i.e. Your second merged table shows 2 items from the left table and 1 time from the right table, with a blank row for the duplicated item in table one that's not present in the right table.
From your description this is what you want, except your left table example only has 2 duplicates, instead of 3.
What am I missing?
Mynda
Dear Mynda,
sorry for confusion.
my expected results is second merged table (Your second merged table shows 2 items from the left table and 1 time from the right table, with a blank row for the duplicated item in table one that's not present in the right table.)
August 23, 2016
Offline
Problem is solved. To each Tables (1, 2) need to add Index to be able to see unique number for duplicated (all items).
then, need to apply Full Outer Join by Site ID-PO-Item-Index
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"
1 Guest(s)
