Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

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.

Ali_Shakh_0-1658826880358.png

is there a way to do so in PQ?

thanks in advance!

 

 

sorry, I couldn't find attach button

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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!



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

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!



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

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)

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.