Merging tables: First match condition (avoiding duplicates)

[Anonymous]

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

[BA_Pete · Accepted solution]

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]

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]

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

[Anonymous]

Yes, you are right, this is the catch, I need to keep all the rows in Table1