Forum Discussion
Power Query M - Filtering to Max Date per Group
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?
- Anonymous5 years ago
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)
2 Replies
- AnonymousNot applicable
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) - AnonymousNot applicable