Forum Discussion
Group By - Filter by latest date within weekending
Hi dd_au ,
You're correct, using MAX aggregators in the Group By isn't going to help you here - you need to group to a nested table then select the row of data which has the MAX of [date].
Try this:
1) Group By [week ending] and [item]. For the aggregate column, choose the 'All Rows' operator and call this column 'data'.
2) Now you have your nested table column, add a new custom column like this:
Table.Max([data], "date")You will now have a nested Record column that contains the full row of data for the max [date] value.
3) Expand the nested Record column, choosing whichever columns you want to reinstate back to the table.
Pete
Super UserHi dd_au ,
You're correct, using MAX aggregators in the Group By isn't going to help you here - you need to group to a nested table then select the row of data which has the MAX of [date].
Try this:
1) Group By [week ending] and [item]. For the aggregate column, choose the 'All Rows' operator and call this column 'data'.
2) Now you have your nested table column, add a new custom column like this:
Table.Max([data], "date")You will now have a nested Record column that contains the full row of data for the max [date] value.
3) Expand the nested Record column, choosing whichever columns you want to reinstate back to the table.
Pete
Thanks Pete! This worked a treat. This will be pretty handy in the future.
Cheers,
DD
