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
This would replace your "Merge Queries" step. Replace your whole #"Mergered Queries" step with:
#"Merged Queries" = Table.NestedJoin(Table.Distinct(#"Trimmed Text", {{"UPC", Comparer.OrdinalIgnoreCase}}), Table.Distinct(VLookup_Combined, {{"UPC, Comparer.OrdinalIgnoreCase}}), {"UPC"}, "VLookup_Combined", JoinKind.LeftOuter)
Also, I amended the Comparer.OrdinalIgnoreCase; my first post had the parameter applied incorrectly. I tested this to make sure it worked.
Good to go!--Nate
Anonymous I copied and pasted your code, but I'm getting an error message:
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!