Remove duplicates based on values from multiple cells

[Rolf]

Hey,

I am trying to append few datasets. The problem with my data is that there may be completely identical rows in there which i want to remove. At the end i only want to keep the truly unique rows.

Based on the colums it is possible that 4 out of 5 rows contain the same values but only one has a different one. Which column that different value is in differs. Sometimes its colum A, other times its B or C. I only want to delete rows that have the same values in all colums.

An example if my dataset is this:

Time	ID	lat	longit	heading	groundspeed
21-2-2014 13:35	413040020	31,3043	121,788	309	0,1
21-2-2014 13:35	412765420	29,9007	122,256	293	0
21-2-2014 13:35	412044680	29,8915	121,938	0	0
21-2-2014 13:35	412044680	29,8915	121,938	0	0
21-2-2014 13:36	412044680	29,8915	121,938	0	0
21-2-2014 13:36	413040020	29,8915	121,938	0	0
21-2-2014 13:41	413040020	31,3043	121,788	309	0,1
21-2-2014 13:41	412765420	29,9007	122,256	293	0

Sometimes the same id is at the same location for a while. I dont want to remove those rows. Its also possible that we have the few ID's at the same time and place, which is also fine. What i dont want is the same ID's at the same time and at the same place. This is impossible and a true duplicate.

In the data above i only want to remove the third or fourth row. All others are valid.

With the one column remove duplicates method i cant say i only want unique timestamps, ID's or locations. All of these can be duplicates but only if another value in the row differs.

Anyone with some ideas on how to fix this? Manually is not an option as there are thousands of datapoints.

Kind regards,

Rolf.

[waeltken]

Hey All,

Is there a comparable approach to accomplish the multi-column deduplicate in a table filter function in DAX?

Regards,

Henrik

[Vargas]

Thanks a lot for the tip. It works well and I'm now able to have my data closer to the real values. But I'm still struggling to succeed on consolidating one of my tables. 

Same production order with feedback on different dates

I want to consolidate by production order and keep the last feedback date. I have tried group by in different ways, but I'm missing values, therefore the report is not accurate. 

Any idea/suggestion on how to do it?

Enrique

[v-jiascu-msft · Accepted solution]

@Rolf,

Hi Rolf.

It's easy to do it in the Query Editor.

1. Select the columns "ID", "lat", "longit", "Time" at the same time with "Ctrl".

2. Use the function as @Anonymous suggested.

Best Regards!

Dale

[Rolf]

Thanks, must have overlooked that. Did not see that it could take multiple columns in to account.

[Anonymous]

Hi @Rolf

It is possible to delete duplicate rows in the "Query editor" like i showed in the picture below.

Regards,

L. Meijdam