How to delete cells in one column expect the first one based on another column

New Contributor


below is a sample of my data:

 289351819.png (1196×837) (

For each city (example ABHA and AL HASA) for each day i want to delete the redundant day

only keep the day with first entry of column time 


I tried delete duplicate for column date, its woks but it also deletes the rest

4 Replies
best response confirmed by manoolya (New Contributor)

Hi @manoolya 


With Power Query:



Group By [station_name] & [observation_date], aggregation = Max of [observation_time]


Thank you again for introducing me to the power query. I appreciate your help


I tried:

 Group By [station_name] & [observation_date], aggregation = Max of [observation_time]

and it works well



But when I add the rest of column, it did not summarize the result 

 Group By [station_name] & [observation_date] & [latitude] & [longitude] & [wind_direction_angle] & [wind_speed_rate] &[sky_ceiling_height] & [visibility_distance] & [air_temperature] & [air_temperature_dew_point] &[atmospheric_sea_level_pressure] , aggregation = Max of [observation_time]


group by multiple column  


Results 2  





@L z.

Hi @manoolya 


(I don't have WinRar and won't install it). Quickly looking at your pictures and what you explain it seems the Grouping to to granular


In the attached file, a different approach for 2 scenarios:

- Your data are Sorted DESC on [observation_time] (as shown in your initial picture) => FirstRecordDataSorted

- Your data are not sorted => FirstRecordDataNotSorted


In both cases we Group By [station_name] & [observation_date2] only. Then inside each group we keep the 1st record only when data are sorted OR Sort [observation_time] DESC then keep the 1st record only when the data were not sorted. After that we combine the nested tables


Hope this helps & makes sense