Power Query: How to Merge two tables without adding more rows?

[Anonymous]

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?

[vinay2802]

Hi there,

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

thanks, vinay bn

[vinay2802]

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

thanks, vinay

[watkinnc]

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.

[watkinnc]

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 

[Anonymous]

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

[watkinnc]

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]

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

[watkinnc]

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]

@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

[watkinnc]

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]

@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

[watkinnc · 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

[Anonymous]

@watkinnc 

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

Thanks!

[HotChilli]

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]

@HotChilli 

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