For example, a Desktop Monitor has been ordered twice, as have Backup Tapes for the Server. A sample of this data can be seen in the following picture:Īs you can see in this example, there are several examples where the Product and Part Ordered are repeated. We need to identify how often each spare part is being ordered for each machine. Our data table lists orders of spare parts for several computers.

This lesson uses an example of a product order table. I am a beginner with power query, your inputs will be appreciated.Excel offers a number of ways to find rows that contain duplicate values. However, most of them focus on finding rows where the value in just one column is duplicated. In this lesson, we look at how to use the COUNTIFS function to find rows with duplicate entries in two or more columns. We then use the COUNTIFS function in combination with Excel's Conditional Formatting feature to highlight duplicate and triplicate rows. However when i started entering comments and refreshing again a few other rows along with the previus culprot began to double again. This stopped the doubling and removed the duplicates. I managed to solve this by editing the query and adding remove duplicates to the end. However one sales order# which had 2 ship by dates and hence appeared twice in the table started doubling every time i hit refresh. The final product looked ok, and the comments are dynamic as i expected. I expanded the tables to merge the 3 new columns to 'summary' I edited 'summary' and merged it with 'self ref'Ĩ. I made a connection of the table which includes the 3 new columns. I added 3 columns to the right hand side of the table called 'comments', 'actions' & 'updates'.Ħ. I pivoted the data based on Sales order# and Price to get a unique list of Sales orders and aggregate price.Ĥ. I deleted some columns that i did not need.ģ. I created a query from an excel table which was an existing query.Ģ. I'm getting duplicate rows every time i refresh my data.

Excel for Decision Making Under Uncertainty Course.

Excel for Customer Service Professionals.

How to merge duplicate rows in excel