Remove duplicates without losing any info | Excel Power Query
HTML-код
- Опубликовано: 6 окт 2024
- Learn to easily merge and group duplicates in Excel using Power Query's built-in function. Say goodbye to losing important information and hello to a cleaner, more organized dataset in this step by step tutorial for beginners. Maximize your data's potential with Power Query.
If you enjoyed this video, please like and subscribe to my channel.
Also, if you have any tutorial requests, leave a comment below!
![Chicken P - People's Favorite (Remix) [Feat. 42 Dugg] (Official Video)](http://i.ytimg.com/vi/a6Bn8FEtxmY/mqdefault.jpg)
Subscribe for more Excel Power Query tutorials!
opened a 4th chakra in my brain and I finally managed to do what I wanted to after 2h of struggle, thank you sir
This video saved me after many hours of struggling with power query - thank you!!
I’m so happy I could help!
I was searching for this almost for a week.Thank You
Awesome! Using that trick to fool PQ into summing Emails then edit the code. Glad to subscribe. 👏
Thanks for the sub!
Excellent video well explained, I appreciate that Paolo!
Graaacias me ayudaste un montón!
I am trying this to sum overtime sheets containing duplicated dates. Thanks.
Very helpful indeed.... Thanks alot.
This was great...thank you so much!
It works! Thank you
This video very helpful. Thank you!
Thanks for watching!
good knowledge
This is awesome, thanks! What about if you have multiple duplicates, like email addresses AND phone numbers?
Thanks for watching. You can add another field/aggregation when grouping and follow the same approach. Add as many fields as you want.
that was amazing.
Thanks a lot!
This was extremely helpful as I had duplicate contact emails and didn’t want to lose any.
I also have numbers I’m trying to combine but Text.Combine doesn’t work on numerical values. Any tips?
Try converting the field type to text before merging.
@@excelwithpaolo This is a very helpful video!!! thank you, I am having a "dataformat error: we could not convert Number". I read where you can remove the "Changed Type" portion of query, but i am not sure what if anything should replace it. When you simply remove it it gives a "Expression.SyntaxError: Invalid identifier". For back ground - i am doing a similar contact merge as you did, I have 18K rows. Lots of the information is Null, which could be part of my problem, but i am not sure. I did convert the data to text on the original excel (source). Any suggestions would be very helpful.
Thanks
Thank you :))))
Awesome...How about adding email id in new column rather than separating it through ;? Also how about if we have numbers & not text?
Can always split it out into columns using Split.
@@excelwithpaolo I have 3 columns in the duplicate line which I want to move like you had for email id. One column is in characters. So if I choose 'SUM' in GROUP BY option it works. My other 2 columns have numbers so if I choose 'SUM' in GROUP BY option it simply sums it & does not work. Is there any other option I should choose than 'SUM'? Later on I will use 'Text To Columns' to split my values.
nice
Hi, i have an issue though, what if i want all the names merged as one name and then emails as one email, considering that my data has the same name and emails, the name on the left and mails on the right but some names are not paired to their mails,but are paired to their mails as u scroll down. I don’t know if my explanation is clear enough. Thanks
Pls make a video in power bi desktop power query if we have 2 tables and i need only the date column from other table, but while performing merge my dataset is getting duplicated. When i hav many to many in both the tables how to perform merge.
Thank you. How would I rather than combine but put the 2nd email into another column under the same name/row?
john doe(row 1), $xxxx (column b) $xxx (column c)
john doe (row 2), $xoxoxo (column b), $oyoyoy (column c) I want to move row 2 (because it is the same name), column b & C up to column D & E row 1. I have hundreds of rows some have two or more duplicate names. I am trying to create a mail merge to send out email payment reminders and only want to send one email with all their payments listed. Thanks.
Combine and then split based on the delimiter would be one way.
What if you want to do the same but instead of different emails, its client ID numbers? Is there a different code I can use?
If it’s a number, try converting it to a string/text before using the approach in the video. I don’t think Text.Combine will work on numbers unless they are converted.
Is there a way to add a new source of data, but have the query "bypass" any row that would cause a duplicate?
Not sure I understand what you mean by bypass, but Power Query follows the steps in order. So if you append a new data source after the group by steps, they will not be merged with the other rows.
@@excelwithpaolo bypass as in skip over. Imagine a master sheet that has two columns named widget and notes. and in row a1 is widget 1, a2 widget 2, b1 - some message. Now I have a new sheet/table that has widget 1, widget, widget 3. Is there a way to tell Power Query to only update the row with new data (Widget 3) and skip (widget 1 and 2).
@computerdaddymultimedia281 It's a little tricky. You could first consolidate all your files, then take the oldest record for each widget (assuming you have a date stamp for each record which is easy to add), and then merge duplicates. This would merge the data from the older records and ignore the newer ones.
@@excelwithpaolo Thx you very much for your help and time. After some testing, it looks like if I sort by file (Mainfile , then update file) and then execute a remove duplicates I was able to achieve the results I wanted.
Hello. But how can I remove duplicates from only 1 column?
In that case, when you're removing duplicates, only select the column you want to base the removal on vs. all the columns.