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

[manoolya]

Hi

below is a sample of my data:

 289351819.png (1196×837) (0zz0.com)

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

[#2 · Accepted solution]

Hi @manoolya 

With Power Query:

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

[#3]

Thank you a lot,.

[#4]

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

Results 

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  

data

@L z.

[#5]

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