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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Delete duplicates in directquery

Hey Community, 

So i'm using directquery mode And I need to delete rows that are (almost) identical, keeping the last row. Example below:

 

Source:

ID, Type , Type2,

1, A, a

1, A, b

1, B, a

 

Result:

1, A, b

1, B, a

 

So I delete One of the first two rows because ID And Type Are equal.

 

Any ideas How to get this Done? 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,@Anonymous 

You can use a measure as visual level filter to meet your needs:

 

 

Create a measure like this after you got the sample:

Measure = RANKX(ALLEXCEPT('Table','Table'[ID],'Table'[Type]),CALCULATE(MAX('Table'[Type2])),,DESC)

2020-12-22.png

 

 

Then you can put the measure in your visual to filter what you want.

 

Screenshot 2020-12-22 143253.png

Hope it helps.

 

Best Regards,

Caitlyn Yan

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi,@Anonymous 

You can use a measure as visual level filter to meet your needs:

 

 

Create a measure like this after you got the sample:

Measure = RANKX(ALLEXCEPT('Table','Table'[ID],'Table'[Type]),CALCULATE(MAX('Table'[Type2])),,DESC)

2020-12-22.png

 

 

Then you can put the measure in your visual to filter what you want.

 

Screenshot 2020-12-22 143253.png

Hope it helps.

 

Best Regards,

Caitlyn Yan

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , You can delete data in Direct Query. In Visual Take max of Type2 and other two as not summarized

 

measure = Max(Table[Type2])

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.