cancel
Showing results for 
Search instead for 
Did you mean: 
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

15 REPLIES 15
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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!