SOLVED

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

New Contributor

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

4 Replies
best response confirmed by manoolya (New Contributor)
Solution

Hi @manoolya 

 

With Power Query:

_Screenshot.png

 

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

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.

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