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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

how to only keep a certain number of duplicates in a table

The table contains sensitive information so I will summarize what's happening.

I have a table that contains project records - the 3 columns I care about are 'Name', 'Client', and 'Allocation'. There are multiple rows that have the same values in the 'Name' column and I want to only keep the first (at most) 3 rows for each name.

Is there a simple way to do this? I am quite new to Power BI so any direction would be helpful.

1 ACCEPTED SOLUTION
otravers
Community Champion
Community Champion

You can group and index data like described in this post, then filter out rows with index > 3:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

View solution in original post

3 REPLIES 3
otravers
Community Champion
Community Champion

You might have to use Table.Buffer to make sure the sorting order is actually applied before you cut off the unwanted rows. Read this for reference:

https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
otravers
Community Champion
Community Champion

You can group and index data like described in this post, then filter out rows with index > 3:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
Anonymous
Not applicable

Many thanks - I decided I also wanted it sorted on date ascending so I sorted the table and added an index column before following the steps in the post you linked. Worked like a charm

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.