Forum Discussion
How to delete cells in one column expect the first one based on another column
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