Forum Discussion
Power Query: How to Merge two tables without adding more rows?
Hi All,
I am attempting to Merge a second table into an existing table, using `Merge Queries`, (not Merge Queries as New) to perform a V-Lookup.
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)
My original table has 132127 rows.
When I Merge 2 Columns from the 2nd table, using `JoinKind.LeftOuter`, I get an exact match
However, after I expand the Merge columns, it jumps up to 132,457 rows.
How to I keep the Merge from adding additional 330 rows?
**Edit: I checked for Blanks and Duplicates on the 2nd table.
let
Source = Table.Combine({#"4 Wk Data", #"13 Wk Data", #"26 Wk Data", #"52 Wk Data", #"YTD Wk Data"}),
#"Trimmed Text" = Table.TransformColumns(Source,{{"UPC", Text.Trim, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"UPC"}, VLookup_Combined, {"UPC"}, "VLookup_Combined", JoinKind.LeftOuter),
#"Expanded VLookup_Combined" = Table.ExpandTableColumn(#"Merged Queries", "VLookup_Combined", {"NON DAIRY", "BRAND"}, {"NON DAIRY", "BRAND"})
in
#"Expanded VLookup_Combined"
If a Merge is not possible, is there another way to perform a VLookup in Power Query?
- Anonymous5 years ago
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
16 Replies
- HotChilliCommunity Champion
I think it suggests there are multiple row matches in the 2nd table to some UPC values in the 1st table. So it's not the 'adding 1 column' that increases the row count, it's the row matches.
- AnonymousNot applicable
I just double checked the 2nd table (VLookup Table), and there are no Blanks or Duplicate references..
- AnonymousNot applicable
This will fix that; add Table.Distinct to both tables like Table.NestedJoin(Table.Distinct(#"Trimmed Text", Ordinal.Ignore), {"UPC"}, Table.Distinct(VLookup_Combined, Ordinal.Ignore), {"UPC"}, "VLookup_Combined", JoinKind.LeftOuter)
Good to go!--Nate
- AnonymousNot applicable
Anonymous where do I place this in the code? After I merge the tables, or place the code you provided in each table before I merge them?
- AnonymousNot applicable
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
- AnonymousNot applicable
By the way, even though the dialog says that it matches 132127 of 132127 rows doesn't mean that there are not duplicates, it just means that for each value in one column, there is a match in the other. But it could match 10 rows for each value and still match 132127 of 132127 rows.
- AnonymousNot applicable
Hi there,
Can any one help why i am getting duplicate when try to merge two tables? please help me.
thanks, vinay bn
- AnonymousNot applicable
once i Expand i am geting duplicate row's. please refre both Images.
thanks, vinay
- latingraduate07Frequent Visitor
Hi there! I am having the same issue and am unable to get the query to NOT add additional rows to my file. Before conducting the Vlookup (merging query), I have a row count of 3757, however, after conducting my FIRST Vlookup to a file, my row count increases to 73,161 and after a 2nd vlookup to a different file/query it jumps up to 76,282. All I need is for the vlookup to insert the located value in its respective column, no additional rows should be added. Please further assist, if can.