Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
Hi,
I'm trying to remove rows based on duplicate values in a column. I know how to use the Query Editor to do this. However, the duplicate values in that column has different values corresponding to it in the table. I only want to keep the record with the highest value in the other columns, and get rid of the duplicates with have lower values.
When I do the regular remove duplicates step, it always seems to keep the lowest value. I want to do the exact opposite. Anyone know if there is some flexibility with this option where I can achieve this?
Thanks!
Solved! Go to Solution.
I've had this problem before. I'm using datesThe way I found around it was 4 steps:
Step #1. Use group by to find the max value. Add all rows to the bottom.
Step #2. Expand all the rows. Basically this adds a new column with the max values down the side of the table
Step #3. Add a custom column like this: if date_applied = Max Date then true else false
Step #4. Filter down to just true.
Hope this helps.
I've had this problem before. I'm using datesThe way I found around it was 4 steps:
Step #1. Use group by to find the max value. Add all rows to the bottom.
Step #2. Expand all the rows. Basically this adds a new column with the max values down the side of the table
Step #3. Add a custom column like this: if date_applied = Max Date then true else false
Step #4. Filter down to just true.
Hope this helps.
very interesting way, thank you @Beckham
Helped me a lot.
My problem seemed very specific but hopefully some other people like you managed to solve it.
that's great.
thanks
Hi all,
I know a better way (the easiest one, for sure !) which is explained here in a video made by a clever Power BI specialist known as Curbal :
https://www.youtube.com/watch?v=rqDdnNxSgHQ&index=22&list=PLDz00l_jz6zzttb28XH8GHZNL6vvpBlkQ
You are Welcome @Anonymous !
That's GREAT!!! Thanks for the tip!
Is this the only way to do this? Looks like my data might be too large so it's not loading.
Even if you didn't add the extra test, the grouping would by default eliminate the non-grouped subset. At least that's what I'm seeing with my dataset. I have a group of users who purchased items at different dates. I wanted to look for their earliest order date, so I grouped a "Min" on the order date and was left with that.
How do you expand all the rows?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |