Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

Capture.JPG

 

 

 

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?

 

 

 

1 ACCEPTED SOLUTION

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

16 REPLIES 16
latingraduate07
Frequent 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.

vinay2802
New Member

Hi there,

Can any one help why i am getting duplicate when try to merge two tables? please help me.

vinay2802_0-1638355160878.png

 

thanks, vinay bn

 

once i Expand i am geting duplicate row's. please refre both Images.

 

vinay2802_0-1638355978707.png

thanks, vinay

watkinnc
Super User
Super User

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.


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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 

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

@watkinnc  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?

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 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

@watkinnc I copied and pasted your code, but I'm getting an error message:Capture.JPG

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

@watkinnc 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!

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

@watkinnc 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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

@watkinnc 

That did it!  I needed to make just the right table distinct.  

Thanks!

HotChilli
Super User
Super User

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.

Anonymous
Not applicable

@HotChilli 

I just double checked the 2nd table (VLookup Table), and there are no Blanks or Duplicate references..

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors