Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Merging tables: First match condition (avoiding duplicates)

Hi everyone!

A simple task at first, turned out to be a "mission impossible"!

 

I have two tables (please see attachment), and I simply want to merge them into one, with only one conditon - get data only for the first match like in the screenshot below

e.g.

is there a way to do so in PQ?

thanks in advance!

 

 

sorry, I couldn't find attach button

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    I'm assuming that you're merging on [Country] and [City] in both tables. If you're merging on different columns, just reflect those columns in my instructions:

     

    In your first table (the one with duplicates) add a grouped index:

    1) Group by [Country] and [City] and use the All Rows operator for your aggregated column. Call the new column "data"

    2) Add an index column to your nested tables by putting this as a custom step:

     

    = Table.TransformColumns(
    previousStepName,
    { "data", each Table.AddIndexColumn(_, "Index", 1, 1) }
)

     

    3) Click on the two outward pointing arrows at the top of your [data] column to reinstate all the rows we previously grouped together.

     

    Now, just add a custom column to your second table, with the code '= 1' and call this column "mergeIndex""

    Merge your tables on Table1 [Country], [City], [Index] = Table2 [Country], [City], [mergeIndex]

     

    Pete

    Now accepting Kudos!

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

     

    I'm assuming that you're merging on [Country] and [City] in both tables. If you're merging on different columns, just reflect those columns in my instructions:

     

    In your first table (the one with duplicates) add a grouped index:

    1) Group by [Country] and [City] and use the All Rows operator for your aggregated column. Call the new column "data"

    2) Add an index column to your nested tables by putting this as a custom step:

     

    = Table.TransformColumns(
    previousStepName,
    { "data", each Table.AddIndexColumn(_, "Index", 1, 1) }
)

     

    3) Click on the two outward pointing arrows at the top of your [data] column to reinstate all the rows we previously grouped together.

     

    Now, just add a custom column to your second table, with the code '= 1' and call this column "mergeIndex""

    Merge your tables on Table1 [Country], [City], [Index] = Table2 [Country], [City], [mergeIndex]

     

    Pete

    Now accepting Kudos!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      hi BA_Pete ,
      your solution works perfectly fine, thanks 👍

      and I accept your solution, fair and square

       

      However, I wonder if there is a more elegant solution with less steps involved, especially when your data is massive like mine (I've got close to a million rows)

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

         

        I agree, it's not the most elegant solution 😂

        I had assumed that you need to keep all the rows in Table1 but, if you don't, you could just filter Table1 on [Vol] <> null?

        You could also reference Table1, disable load on it, perform the above filter on it, then use it for the merge instead of your full Table1 with the duplicates?

         

        Pete