Forum Discussion
Power Query: How to Merge two tables without adding more rows?
Yeah, I just realized my statement is not true. Are you SURE there are not duplicates in the right table? You can try this to be sure:
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"UPC"}, Table.Distinct(VLookup_Combined, {{"UPC, Comparer.OrdinalIgnoreCase}}), {"UPC"}, "VLookup_Combined", JoinKind.LeftOuter)
This makes only the right table distinct.
---Nate
Sorry, I forgot to add the join column in that formula for the first table. Fixed it:
#"Merged Queries" = Table.NestedJoin(Table.Distinct(#"Trimmed Text", {{"UPC", Comparer.OrdinalIgnoreCase}}), {"UPC"}, Table.Distinct(VLookup_Combined, {{"UPC, Comparer.OrdinalIgnoreCase}}), {"UPC"}, "VLookup_Combined", JoinKind.LeftOuter)
---Nate
Anonymous so that took my original data down from 132,127 rows to 3,813.
I need to clarify. When the VLookup is used, the 1st table does have duplicate UPC's (becuase there are different time frames in Column A).
The Lookup Value is the UPC Column in Table1
The Table_Array is the 2nd Table.
Here's the manual VLookup code:
=VLOOKUP([@UPC],Book1!Combined[[UPC]:[NON DAIRY]],3,FALSE)
I don't want to delete the duplicate UPC's in Table1. But I don't understand why a Merge is adding 330 additional rows
Because if there are duplicates in your left table, each match in the right table is going to join to each match in the left table. So if you have ten "Nathan" in left table, and one "Nathan" in the right table, the result of the join is ten Rows of Nathans, one for each match. You cannot have duplicates in eaither table and not have it lead to more rows than you had. Sorry!
Anonymous so based on your anaolgy:
If I have 10 rows of "Nathan" in the left table, and 1 row "Nathan" in the right tabe... I should get a total of 10 rows.
However, when I expand the Merge columns, I'm getting 15 rows of "Nathan" (more or less).
If a merge is not possible, is there another way to perform a VLookup in Power Query?
Regards
Yeah, I just realized my statement is not true. Are you SURE there are not duplicates in the right table? You can try this to be sure:
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"UPC"}, Table.Distinct(VLookup_Combined, {{"UPC, Comparer.OrdinalIgnoreCase}}), {"UPC"}, "VLookup_Combined", JoinKind.LeftOuter)
This makes only the right table distinct.
---Nate