Power Query M - Filtering to Max Date per Group

[wmarques]

Very much a novice here so apologies if this query seems a bit basic.

I have a table where one of the columns holds nested tables:

Financial Year	All Rows
2021/22	Table
2022/23	Table
2023/24	Table

The nested tables hold a large number of columns and rows which will be manipulated after they're expanded but before that, I need to filter each table to contain only the records that match the latest [Source.DateCreated]. This date would be different for each [Financial Year].

Example of Nested Table contents:

Source.DateCreated	Forecast Sales	Team
1/3/2021	564	Research
3/3/2021	657	Research
3/3/2021	200	Research
3/3/2021	354	Manufacturing
2/3/2021	784	Manufacturing

My desired result for the above example:

Source.DateCreated	Forecast Sales	Team
3/3/2021	657	Research
3/3/2021	200	Research
3/3/2021	354	Manufacturing

I know that I can filter by the latest date for a table but I'm clueless on how to do this for a nested table.

Does anyone know how I would go about doing this?

[Anonymous]

use this scheme if your dataset is not huge

[Anonymous · Accepted solution]

you should use the add.column function using some criteria like:

Table.SelectRows([#"All Rows"], let latest = List.Max([#"All Rows"][Source.DateCreated]) in each [Source.DateCreated] = latest)