You are an AMAZING person! You have saved me at least 2 days of stress, headaches, work, and have provided the company with potentially hundreds of thousands of dollars of owed back pay as I am dealing with 190k lines of transaction records.
That is awesome. I'm glad it could help. Yeah, it took me a while to find this solution for a customer and I knew someone else would benefit from the online community. Nice to have a fellow Algebra teacher in this realm as well.
This was literally exactly what I needed. I'm just breaking into the Power Query scene, and this solved exactly what I was hoping it would. Thanks for the help!!!
Man thank you so much. I stayed up 4 hours past my bedtime to fix this. I thought the error was in the data and never thought Power Query would screw it up
I just bumped into your vid and found the answer (not only the solution but also the reasoning behind it) to what was happening to my data. And now i can finally get some sleep haha. Thanks a lot, you're the best!
Thanks SO much for explaining this in an an easy way to follow! Yours was the 3rd clip I watched and the easiest to understand hands down - Have now subscribed!
This is great information. Is there a way to get the most recent as you show but also create a new column with data from a cell of the duplicate being removed?
That is great! But, what if you have two different tables with two different latest date of different activities that you want to get the latest activity status date. How do you solve for it?
That's nice and all but I have a similar problem which I can solve in pandas but not in power BI. Suppose I have 2 tables A and B, B being the newest. I append these tables and in case there are duplicate e-mails/primary keys I want to keep only the records coming from table B which are supposedly the latest. There is no date column to take advantage of sorting gimmicks. How would you solve that? For example in pandas there is a first and last attribute that can solve this.
Thanks for the information, it did work for me. PS: Just insane to have to add a command to have Power Excel actually functioning like Excel (which takes the first sorted line and discard the others). If i would'nt have thoroughly checked the data's results (final output being 17k beneficiaries out of 660k based on criteria) using the remove duplicates function in Power Excel versus Excel I would'nt have known. Bottom line: no reason to change the order just to speed up internal refreshing, it could have been a box that the user would be checking if they didn't care about the order.
This is wonderful method! However,why is it that latest updates with null values cant replace the preceding records..null cant update null or update a records?
What if the status update is modified on the same date. Let's say their recent update is on Jan 01, and again they modified that update on Jan 01 itself but at a later time in day. Then which one will it choose if we don't know the time at which they got changed?
when I apply Table.Buffer in a query with a large volume of data, powerquery just keeps processing, taking a while to return the table.buffer command. Why does it happen? Is anyone going through this too?
I attempted this but my computer freezes. My table has about 20m records. I am trying to remove duplicates and be left with only records whose invoice date is close to the last employee roster entry (date field) any ideas how to resolve this?
I used "table.buffer" in the same way but when i wanted to apply changes and close the transform data section, it takes like forever. I tried to do it for days but i gave up. There must be a problem :( Does anyone have an idea about that?
Ohh soo good solution, but what if I do not use data directly after Table.Buffer apply, lets assume I keep continue adding some more steps. Do I need on each step use Table.Buffer ?
@@TheAlgebraTeacherPOWERStoBI Currently I have issue where I prepare where sorting is involved and then merged with another table. After the merge all goes wrong and data is totally mixed up.
2024 and you're still saving lives, google and the Microsoft forums only conjured up convoluted solutions.
Thanks!
You are an AMAZING person! You have saved me at least 2 days of stress, headaches, work, and have provided the company with potentially hundreds of thousands of dollars of owed back pay as I am dealing with 190k lines of transaction records.
3 years later and I still reference this video from time to time. So much that I actually bookmarked it.
dude, you just solved it!! Spent 1.25 days on this....by the way I'm also a former Algebra teacher, now working as an analyst....you rock! Jim
That is awesome. I'm glad it could help. Yeah, it took me a while to find this solution for a customer and I knew someone else would benefit from the online community. Nice to have a fellow Algebra teacher in this realm as well.
This was literally exactly what I needed. I'm just breaking into the Power Query scene, and this solved exactly what I was hoping it would. Thanks for the help!!!
Same here! Thanks Matthew!
Man thank you so much. I stayed up 4 hours past my bedtime to fix this. I thought the error was in the data and never thought Power Query would screw it up
Sweet! Glad I could provide a fix.
Thank you, this just fixed 2 days of pain trying to find a solution.
I just bumped into your vid and found the answer (not only the solution but also the reasoning behind it) to what was happening to my data. And now i can finally get some sleep haha. Thanks a lot, you're the best!
I was making the exact same mistakes you were making, and you saved me a lot of frustration. Thank you!!! Subscribed!
Glad I could help!
Super useful trick! You just solved my 1 hour frustration of the day with Power BI. Thanks 🙌🏻🙌🏻
This problem was driving me nuts so great to come across this solution. Thank you very much
My Bookmark,
Equivalent of SQL Partition by Row_Number() order by- 4:40 onwards
Thanks, i lost many time to solved this issue till find you here
This was brilliant. i just tried it, and it seems to be working. I will know more as i audit my data.
This is exactly what i was looking for .. and it's easy to be comprehensive.
Thanks SO much for explaining this in an an easy way to follow! Yours was the 3rd clip I watched and the easiest to understand hands down - Have now subscribed!
Wow, I cannot tell you how much this speeded up what i was working on.
Thx a lot, finally i understand the reason of this weird behavior
brilliant sir! The simplest while smartest solution so far!
the buffer detail was to step I need, thank you very much!
I LOVE YOU!!! It was killing me why it wouldn't remove my duplicates and keep the latst!!
Thank you so much. You have been a saviour. None other video explained the sorting action in detail. This helped to solve big bug.
Glad it helped!
I have (had!) a similar problem. This showed me the approach - worked a treat!!
Just back here to say that this is the most perfect answer to this question
This is great information. Is there a way to get the most recent as you show but also create a new column with data from a cell of the duplicate being removed?
That is great! But, what if you have two different tables with two different latest date of different activities that you want to get the latest activity status date. How do you solve for it?
I've been struggling with this for the last day. Thank you verrrry much for the fix!
Happy to help!
That's nice and all but I have a similar problem which I can solve in pandas but not in power BI. Suppose I have 2 tables A and B, B being the newest.
I append these tables and in case there are duplicate e-mails/primary keys I want to keep only the records coming from table B which are supposedly the latest.
There is no date column to take advantage of sorting gimmicks. How would you solve that? For example in pandas there is a first and last attribute that can solve this.
excellent really appreciate this video and your clear presentation
I've been struggling with this for the last 8 hours. Thank you for the fix!
Glad to help ;)
Thanks for the information, it did work for me.
PS: Just insane to have to add a command to have Power Excel actually functioning like Excel (which takes the first sorted line and discard the others). If i would'nt have thoroughly checked the data's results (final output being 17k beneficiaries out of 660k based on criteria) using the remove duplicates function in Power Excel versus Excel I would'nt have known. Bottom line: no reason to change the order just to speed up internal refreshing, it could have been a box that the user would be checking if they didn't care about the order.
Table.Sort is wrapped in Table.Buffer 5:39
thanks for the video, learned something new today.
Excellent. We want more Power query Magic Sir.
Looking forward to providing you more ;)
Agree 😀
Thank you very much for you video. Keep up the good work
This is wonderful method!
However,why is it that latest updates with null values cant replace the preceding records..null cant update null or update a records?
Wonderful! just what I needed, thank you!
Same exact frustration i experienced
Great work around
This was amazing,,,, And crazy simple as well. Thank you.
You solved my problem. Thanks Matt!
Great trick. I should try the same in Excel PQ :)
Excellent, this is exactly what I was looking for. Thank you so much.
What if the status update is modified on the same date. Let's say their recent update is on Jan 01, and again they modified that update on Jan 01 itself but at a later time in day. Then which one will it choose if we don't know the time at which they got changed?
thank you very much, you have no idea how much you helped me with this video!!
Awesome video. Was struggling with this for a while and I didn't know you could force it to do that!
Just the thing i needed - thank you
Thank you a lot for this solution! It helped me a lot!
Thank you so much, this is exactly what I needed!!!
when I apply Table.Buffer in a query with a large volume of data, powerquery just keeps processing, taking a while to return the table.buffer command. Why does it happen? Is anyone going through this too?
how do I undo the removal up dulicate rows/columns?
Thaaanks!!! this is exactly what I was trying to doo!!
Awesome video and explanation, thank you very much!
Thanks sooo much!!!!! It helped me a lot!
Brilliant, this is really helpful
That did the trick for me, thank you!!!
Many thanks
I was looking for this solution
Group by with aggregate Max on Date?
I attempted this but my computer freezes. My table has about 20m records. I am trying to remove duplicates and be left with only records whose invoice date is close to the last employee roster entry (date field) any ideas how to resolve this?
thank you! your saved my mental healthy
Thanks, your video helped me alot.
Hey man, you saved me!
Thanks!
Thanks so much for the tutorial
Thank you SO MUCH. Saved me a lot of time!
you are a genius sir
It was really helpful, thanks so much
Worked for me thanks a lot ❤
I used "table.buffer" in the same way but when i wanted to apply changes and close the transform data section, it takes like forever. I tried to do it for days but i gave up. There must be a problem :( Does anyone have an idea about that?
Hi I need similar solution in dataflow, because I can't able to use table.buffer in dataflow. Any alternative solution for this issue
Ohh soo good solution, but what if I do not use data directly after Table.Buffer apply, lets assume I keep continue adding some more steps. Do I need on each step use Table.Buffer ?
You do not. You've already limited what rows to return and thus your subsequent transforms will be applied to those rows.
@@TheAlgebraTeacherPOWERStoBI Currently I have issue where I prepare where sorting is involved and then merged with another table. After the merge all goes wrong and data is totally mixed up.
I have been looking for this
easy and efficient
Amazing 👍👍👍👍👍👍👍👍👍👍👍👍👍👍
Thank you..This is brilliant
I’m glad you enjoyed!
Thank you very much for this.
Great tip!
Thanks! I thought PQ was lying to me :)
this is so great! thanks
Good to know this! thanks.
Hi Sir, I need the same result using SQL query. Could you help me?
Always great❤❤
Thank you!
Can I get a sample file
Brilliant !!!
great tip👍
saving lives here!!!
You're a goddamn hero!
Awesome !!!
Thank you soo much
tank you so much !
thankyou very much
Yep, sorting/distinct has been fooling people for years. Would be nice if it was built in behavior.
I agree. It fooled me once, won’t fool me again.
LOL very complex
Thank you!