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

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'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: