Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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