cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rolf
Frequent Visitor

Remove duplicates based on values from multiple cells

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:

TimeID lat longit heading groundspeed
21-2-2014 13:3541304002031,3043121,7883090,1
21-2-2014 13:3541276542029,9007122,2562930
21-2-2014 13:3541204468029,8915121,93800
21-2-2014 13:3541204468029,8915121,93800
21-2-2014 13:3641204468029,8915121,93800
21-2-2014 13:3641304002029,8915121,93800
21-2-2014 13:4141304002031,3043121,7883090,1
21-2-2014 13:4141276542029,9007122,2562930

 

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.

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

@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.Remove duplicates based on values from multiple cells.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
waeltken
Frequent Visitor

Hey All,

 

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

 

Regards,

 

 

Henrik

 

 

Vargas
Regular Visitor

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 datesSame 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
Microsoft
Microsoft

@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.Remove duplicates based on values from multiple cells.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Anonymous
Not applicable

Hi @Rolf

 

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

 

Capture444.PNG

 

Regards,

 

L. Meijdam

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors