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?
@@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.
In power query there is a remove rows feature. How does it decide what rows to cut? In excel under data if you did a remove duplicates it would leave the top entry and all subsequent dupes would be removed. I’m not seeing this same functionality in the remove rows. Regardless of how I sort prior to remove rows the removal seems random.
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
@@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.
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.
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.
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.
Subscribe for more Excel Power Query tutorials!
Super helpful. I'd never used Power Query before.
I was searching for this almost for a week.Thank You
Thank you, brief and to the point and it resolves my issues. Have added my subscription!
This video saved me after many hours of struggling with power query - thank you!!
I’m so happy I could help!
opened a 4th chakra in my brain and I finally managed to do what I wanted to after 2h of struggle, thank you sir
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.
This was great...thank you so much!
Very helpful indeed.... Thanks alot.
It works! Thank you
This video very helpful. Thank you!
Thanks for watching!
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.
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.
good knowledge
In power query there is a remove rows feature. How does it decide what rows to cut? In excel under data if you did a remove duplicates it would leave the top entry and all subsequent dupes would be removed. I’m not seeing this same functionality in the remove rows. Regardless of how I sort prior to remove rows the removal seems random.
that was amazing.
Thanks a lot!
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
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.
Thank you :))))
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.
Thanks
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.
How can you do it for phone numbers?
Try converting the phone number column to a string first.
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.
nice
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.