Removing duplicates but keeping the records with the most information in

[boa]

Hello

I need some help with cleaning my data. I have a lot of duplicates in my table. I want to keep the record which contains the most data.

This is how my table looks like:

Records with the same 'key intervention' are duplicates. In the perfect scenario the records have no 'null' in 'time call', 'time arrival' and 'time end'. I want to keep the records in blue and remove the other records. 

Help is really appreciated!

[v-yangliu-msft · Accepted solution]

Hi  @boa ,

Here are the steps you can follow：

1. Go to Power Query – Transform – Group By – Advanced – Set as follows.

2. Select the new column – click the expand flag -- check [time call], [time arrival], [time end].

3. Add Column – Custom Column – Enter the following code.

if [Count] = [time call] then 1 else 0

4. Select [Custom] – select only 1.

5. Check [Count], [Custom] – Remove Columns.

6. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[boa]

Thank you for your help and excellent explanation! 🙏

[sudhav]

go to transform data and goto power query editor and there select time arrival column click on down arrow and deselect null values, then you will get.

[boa]

Hello sudhav

If I deselect "null" values in the 'time arrival' column (=remove empty?) I remove also records that I want to keep... If the arrival time is empty and the end time is not empty/or empty, I want to keep it as well