Remove duplicates based on sort order - Power BI Tips & Tricks #22

In this video I will show a trick on how to remove duplicates based on sort order in Power BI / Power Query.
Looking for a download file? Go to our Download Center: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#SUBSCRIBE #CURBAL
► Linkedin: goo.gl/3VW6Ky

[@MattiasGregersen]

I just wanted to say thank you for this - I realize that it is an older video, but jesus I was having headaches on this one.

[@CurbalEN]

🥳 Thrilled to hear that the old videos are still useful and you got the help you needed! Thanks for sharing :) /Ruth

[@adolfocamarillo3550]

This solved the issue I was having. Very well explained. I'll share this trick with my team. Thank you!

[@BalurPoco]

Thank you very much, that was a great help to me, because I can still use this method today. From a CSV of a few million lines, I wanted to discard duplicates after sorting. Based on the video, I succeeded and now the data fits within the 1 million row excel worksheet limit. Thanks again, have a nice day!

[@CurbalEN]

You too!

[@phildanley7285]

Thank you very much Curbal for this video. I was tearing out my hair trying to remove duplicate rows based upon the values in another column. I wasn’t sorting by dates but I was able to create a numerical column and sort that in descending order. Duplicates are gone and the rows I wanted to keep remain. Thank you again!

[@CurbalEN]

Well done!!

¡Esto ha quedado como anillo al dedo! Justamente ayer, necesitaba algo como lo que explicas aquí, porque un modelo de datos sobre inventarios al indexar los últimos cambios de diversos productos duplicaba los registros por la cantidad de cambios de precio. Y al eliminar el código del producto no podía mantener el ultimo cambio, es decir, la fecha. Muy agradecido por tus enseñanzas.

[@CurbalEN]

Muchas gracias José y gracias for compartirlo en twitter! /Ruth

[@sue_bayes]

Perfect, just solved a problem in seconds. Thank you

[@guybowdler2460]

Perfect, thank you! Well explained video, clearly demonstrating the problem which really helps determining whether the solution is applicable. Thank you very much:)

[@MarkCBB86]

So happy I found this Video, I would have assumed the default behaviour would be the same as Excel. Thank you

[@CurbalEN]

You welcome Mark, I thought the same too! On another note, ..,So cool that those older little videos are still useful :) /Ruth

[@ukaszbiaasik1918]

You just saved my and my co-workers life. Thank You !

[@CurbalEN]

🥳🥳🥳

[@bendreessen2809]

Thank you! That video saved me hours and was easy to understand and implement!

[@oscargushiken8310]

Thank you! I was having this specific problem and you gave me the answer!

[@OCCmorrow]

Thank you for this tip - very useful! I will say though, I didn't realize that the power query M function was Table.Buffer. I thought that because your table was named Buffer I should use my table name (which is Products), and I kept getting an error. Not until I tried using Table.Buffer did it work. Just wasn't quite clear in the video. But otherwise, this is a great tip, and fixed my problem.

[@CurbalEN]

Thanks for the clarification! I will pin your post in case others find it useful. /Ruth

[@ruairihourihane6044]

me too!! :) we got there in the end!

[@bc67]

Great one that I haven't seen before. Thank you for sharing.

[@jestinrajan946]

Thank You... This video was a life saver even though it was posted 7 years ago 🎉.

[@CurbalEN]

Power query is evergreen 😃

[@breopardo6691]

Clearly explained, neat trick to actually remove duplicates controlling what duplicates to remove. Thank you!

[@CurbalEN]

You welcome! /Ruth

[@danilochavez4167]

Hi Ruth. I was asking for this solution in the comment section on a different video of yours, and did not realize you made this one! Thanks a ton!

[@CurbalEN]

I have over 500 videos, a tip is to search on my KZbin channel or add Curbal to your google search ;) /Ruth

[@anselmojg]

Thank you (Gracias!) ... This trick allowed to workaround a limitation with PowerApps working with up to 2000 items in SharePoint lists.... (long story... with a happy end thanks to this!)

[@CurbalEN]

De nada ! Me alegro que te haya ayudado :) /Ruth

[@charl1300]

This helped me fix something I've been struggling with, thank you.

[@me4175]

Never would have figured this out, thanks a lot!

[@CurbalEN]

You welcome and glad it was useful! /Ruth

[@hilmanagung]

Thank you so much Ruth. This is something I have been struggled for several days.

[@tomn6921]

I had this problem collecting MS Forms data and I wanted to accept updated entries and then delete the older rows, and keep the younger. And your video showed me how to do that so BIG thank you.

[@CurbalEN]

Glad it helped you :)

[@Benjamino1987]

Incredibly helpful and elegant solution! Thnx a lot for sharing this!

[@CurbalEN]

My pleasure!! /Ruth

[@modsavant]

Easier way: sort first as needed, then create Index Column. Now remove duplicates. Done. By creating an Index Column, you are overriding the prior back-end Index that was overriding your sort. With this new Index column that you created AFTER your sort, only the first duplicates are kept per your sort order. After removing duplicates, just delete Index column and move on.

[@TayuanRebelo]

Passing by to say you saved the day! Thanks!

[@yaelzi9996]

You saved my day ! Thanks

[@KashifAlvi2009]

Thank you so much!! It was mind boggling, I kept on going back and forth and tried alternating in ascending/descending order (for dates), but whether the date order was descending or ascending, it didn't make a difference to Power BI. Explained with such ease.

[@CurbalEN]

Wonderful! Glad it was useful! :) /Ruth

[@andrewallen8288]

Thank you so much for this. It was exactly what I needed. Thank you!

[@haroldenriquepajueloaraujo7631]

Gracias Ruth, ya no sabia que hacer y recordé que siempre que tengo dudas recorro a tus videos que son excelentes.Gracias desde Perú

[@CurbalEN]

Gracias! /Ruth

[@cynthiahuallanca6310]

Thank you for sharing this super valuable tip!! Spent hours trying to find out why my remove duplicated didn't work after a particular sorting step. Thank you

[@NorbertoVeraReatigaNVR]

Gracias Ruth Muy buen vídeo!, Nos mantiene muy actualizados de contenido para mejorar nuestro desempeño en el manejo de datos... a sido un año muy productivo siguiendo sus excelentes trabajos de divulgación...

[@CurbalEN]

+Norberto Vera Reatiga Muchas gracias Norberto! Todo un verdadero placer poder contribuir en esta comunidad tan increíble y muchas gracias por tu apoyo! Saludos desde España:) /Ruth

[@dilip1able]

Thanks a ton, this helped me to fix the bug. You are awesome.

[@Stray.wanderer]

You are an amazing teacher and I will always be grateful to you for these awesome videos.

[@CurbalEN]

Thanks!! 😊

[@samirvaghasiya9918]

I have this similar scenario. Thank you very much.

[@bensutton7889]

Thank you Much!! You saved me a lot of headache and time!

[@CurbalEN]

This type of feedback is music to my ears! I loooove productivity tips! Congrats! /Ruth

[@stuartmynard]

Great tip. Thank you!

[@nemo9396]

Thanks for being concise :D

[@fabiobecamp]

You resolved my life with this trip, thank you so much :)

[@CurbalEN]

Music to my ears :))) /Ruth

[@zainubnajmudin9181]

Actual life saver

[@JFarr49er]

Really handy video thank you. I'm looking to solve a similar problem, I want to do a distinct count on a visual, based on custom column which is related to dates. I thought that this would be a workaround as removing duplicates would give me the distinct values in date order. I was wondering If i kept the buffer table based on order date, then did a distinct count on a visual, would this work in the same way?

[@barathankannaiah9048]

Thank you Curbal. I need the same in my dataflow, where I can't use table.buffer. Is there any alternative ?

[@purpleisiscat]

Hi. I realise this is an older video but many thanks for the tip! It works great in Desktop but doesn't seem to in the Power BI web service, specifically dataflows. Is there an alternative in this case? Thanks.

[@ruairihourihane6044]

Really Really great. Simple, and exactly the problem i was having issues with! would say that "Buffer etc" is exactly what you paste and doesnt depend on the name of your table. great Video though, thanks

[@jonashva7704]

Great, just what I was looking for. Thanks!

[@CurbalEN]

🎉🎉🎉 /Ruth

[@MrJoaomcs]

Your video saved my project! Thank you!

[@CurbalEN]

My pleasure!!

[@Akirmax]

Simply what I need to know...Thank you a lot!

[@JordivanKaam]

Do you also have an explanation on how to execute this for a directquery in Power BI?

[@andresantos1123]

I love you. You saved me! Thanks

[@angus6095]

Genius! Just what I needed. Thank you!

[@CurbalEN]

Yey!! Happy friday! /Ruth

[@exiexijtzoe3564]

omg... just what I needed, remove duplicates was working fine in one report and not in the other.... yeah for ""buffer.table" ;) awesome Ruth, thx.

[@CurbalEN]

👏👏 /Ruth

[@TheChouchoune75]

Great and simple trick. Thx a lot!

[@CurbalEN]

You welcome !! :) /Ruth

[@lumitic774]

LIFE SAVER AS ALWAYS CURBAL! life saver!

[@CurbalEN]

Yey! /Ruth

[@lumitic774]

@@CurbalEN I was trying to figure it out with multiple formulas and nothing. Such a simple solution! any feedback in processing needs with this buffer code? because this is for a table with sharepoint connection with 1000 new lines/week?

[@CloudhoundCoUk]

Sadly removing duplicate data entries is an essential skill and this excellent video presentation explains in an easy to understand way just how you can do that.

[@CurbalEN]

+Cloud Hound Thanks Christopher :)

[@Edgard422]

Excellent explanation, thank you very much.

[@CurbalEN]

My pleasure :)

[@PedroCabraldaCamara]

cool trick Ruth! Thank you so much

[@kabis4572]

Fab! thank you so much for this. Well explained.

[@CurbalEN]

Yey!!

[@ngocnamnguyen8096]

Thank you for sharing. I have another case related Remove Duplicate - The data before and after I doing Remove Duplicate is not matching ( I see it when I using filter: If I do Remove Duplicate first then Filter so the data show missing some rows, if I do Filter first then do Remove Duplicate, it's ok (but if I do like this, I can not looking for another if I need ). After apply Table.Buffer, it's no help. Do you have any ideas?

[@emilio6117]

Its really helpful!!!! Thank you so much for this!

[@manttone]

Thanks. Just what I was looking for.

[@CurbalEN]

Awesome :) /Ruth

[@ivanmenendez5389]

Thanks for sharing... min 4:00

[@B4H4MuTT]

Great solution. I've seen other solutions in the community waaaay longer and uglier. Congrats!

[@CurbalEN]

Wonderful! Glad it was useful :) /Ruth

[@alexandresuberbielle7446]

Thanks a lot! Simple and smart!

[@CurbalEN]

Wonderful :)

[@sonetmollick4630]

Awesome, it works for me. Thanks :)

[@sugarcube7984]

Very helpful...thanks

[@MrBlets]

Thank you Ruth. Works like a charm

[@CurbalEN]

Music to my ears!! :) /Ruth

[@pocbuyd]

ur a lifesaver

[@CurbalEN]

😊 /Ruth

[@RTAEON]

Awesome, thank you!

[@CurbalEN]

You welcome Rolf! /Ruth

[@piacastro7233]

Awesome!!! Thank you so much!

[@gustavovillelarodrigues927]

Thank you so much! It helps a lot

[@CurbalEN]

Wonderful!

[@uweseidel8784]

Woohooo! This is awesome. Thanks a lot for this one

[@evansjam316]

Amazing video, thank you very much :)

[@CurbalEN]

Thanks for the feedback !

[@ziondiaz7126]

HI there, This Works but Why does the Buffer Table change the sort format?

[@SuperYanab]

Thank you! It was mind boggling indeed!

[@CurbalEN]

Thanks Yana! /Ruth

[@elfridhasman4181]

Thank You Very Much

[@piscisknight]

THANKS! Awesome tip

[@CurbalEN]

🥳🥳

[@DrDrooo]

Thank you so much!

[@CurbalEN]

You welcome!!

[@John-bo2ut]

Thank you

[@jptolledo]

Thank you. You helped me a lot

[@CurbalEN]

Wonderful!

[@robedias31]

Thank you very much for this amazing trick.

[@CurbalEN]

Glad it was helpful! /Ruth

[@KirtiKumarNagar]

Thanks a lot.

[@edgarcr21]

Great video. Thanks for sharing. I was wondering what would happen if, let's say, I have multiple records on the last month that I want to keep. So, apparently, this will remove *all* duplicates, including the ones that i have on the last month, leaving me with just one single record from the latest month. Check it out: records dates r1 mar-2020 r1 mar-2020 r2 feb-2020 r1 jan-2020 after #buffer: records dates r1 mar-2020 r2 feb-2020 Any ideas how can i keep both instances of r1 from mar-2020? Now, obviously, from the "records" column perspective these 2 records are indistinguishable but there could be other columns that could make it worth keeping them.

[@ImranAmiKh4n]

thank you!

[@namkce]

Thankyou!! this was very helpful! :D

[@CurbalEN]

Yey! /Ruth

[@mehdihammadi6145]

Thank you Ruth very helpful

[@CurbalEN]

You welcome ! /Rutj

[@cparker4486]

Fantastic.

[@victornzemeke1763]

Thanks!!

[@lucasamorim2420]

Thank you!!!

[@CurbalEN]

You welcome! /Ruth

[@mountainmonk2]

Love it thanks !!

[@darlyngtrejo6800]

Thank u! This trick was really useful :)

[@CurbalEN]

Yey!!🥳🥳

[@Grant094]

super helpful! thank you so much!

[@CurbalEN]

You welcome Grant! /Ruth

[@oscarcampos3054]

Great trick!!! Thank you so much.

[@CurbalEN]

You welcome! /Ruth

[@edgarabgar4633]

Thanks a lot

[@AjayKumarparmar]

problem is if you like to remove alternate rows and before that let us say you have sorted the table by serial numbers like 1,1,1,2,2,2,2,3,3,3,3 ,,,,,so i you trying to remove alternate rows starting from 5th row and remove 4 rows and keeping 4 rows, it is removing different rows...not the ones which are sorted in previous step even if you use table.buffer...thats a challenge now....dear Ruth.. removing alternate rows does not work if you have sorted the data...it changes the indexes ...even if you have used table buffer...i wanted to use such kind of a function which can keep latest table in memory.....but alas, it could not work for me

[@lorennabastos7765]

Thanks a lot Ruth!

[@japarell]

Super! thanks a lot!

[@CurbalEN]

Yey!! 🥳🥳

[@emekandiasor3468]

Quite helpful. Thanks alot

[@CurbalEN]

You welcome :) /Ruth

[@martinskoglund2664]

Great but I have a simular problem the only diffrence is that i don´t have dates. I was thinking power query could keep track of when the data was important and save the duplicate that was imported last. We have daily reports that I put in an folder that in its turn is merged with another report that hav comments on the buissnies cases. An order changes status but i don´t have the date of change date in the data extraction flow.

[@luisalbertorocham8419]

Ruth, you are the best :) thank you

[@CurbalEN]

Thank you!! :) /Ruth

[@matsingh9490]

thanks a lot. helped it

[@CurbalEN]

All I need to hear :)

[@jonathanacosta3362]

Really usefull! Thank you!

[@CurbalEN]

Yey!! Happy it worked :) /Ruth

[@johnlevengood]

You’re amazing

[@CurbalEN]

You too :)

[@bluex5115]

thanks

[@thaisjustidetoledo130]

thank you!!!!!

[@CurbalEN]

🥳🥳🥳

[@DCal1999]

Awesome

[@CurbalEN]

Thanks! :) /Ruth