Forum Discussion
How to delete cells in one column expect the first one based on another column
Hi
below is a sample of my data:
https://www8.0zz0.com/2022/10/13/03/289351819.png
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
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
https://www11.0zz0.com/2022/10/14/11/611227551.png
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]
https://www11.0zz0.com/2022/10/14/11/427881256.png
https://www11.0zz0.com/2022/10/14/11/209723794.png
https://files.fm/u/3tktf8t66
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
- Thank you a lot,.
