Forum Discussion
Solved
Grouping issue
Hi Community
I have a table like this one below
| Required Action | Input Date | Test Grade | Overall Action | Unit Reference | Estate Test |
| NONE | 06/07/2023 | D | N | 65335 | Cleaning |
| NONE | 23/06/2023 | U | N | 65335 | Grounds Maintenance |
| NONE | 06/07/2023 | D | N | 65335 | Grounds Maintenance |
| NONE | 01/06/2023 | C | N | 65335 | Cleaning |
| NONE | 01/06/2023 | C | N | 65335 | Grounds Maintenance |
| NONE | 07/07/2023 | C | N | 65369 | Cleaning |
| NONE | 09/06/2023 | U | N | 65369 | Cleaning |
| NONE | 30/06/2023 | U | N | 65369 | Cleaning |
| NONE | 30/06/2023 | U | N | 65369 | Grounds Maintenance |
| NONE | 19/06/2023 | U | N | 65369 | Grounds Maintenance |
| NONE | 09/06/2023 | U | N | 65369 | Grounds Maintenance |
| NONE | 07/07/2023 | C | N | 65369 | Grounds Maintenance |
| NONE | 02/06/2023 | C | N | 65369 | Cleaning |
| NONE | 02/06/2023 | C | N | 65369 | Grounds Maintenance |
| NONE | 02/06/2023 | U | N | 65385 | Cleaning |
| NONE | 09/06/2023 | U | N | 65385 | Grounds Maintenance |
| NONE | 19/06/2023 | U | N | 65385 | Grounds Maintenance |
| NONE | 02/06/2023 | U | N | 65385 | Grounds Maintenance |
| NONE | 30/06/2023 | U | N | 65385 | Cleaning |
| NONE | 09/06/2023 | U | N | 65385 | Cleaning |
| NONE | 19/06/2023 | U | N | 65385 | Cleaning |
| NONE | 07/07/2023 | C | N | 65385 | Cleaning |
| NONE | 07/07/2023 | C | N | 65385 | Grounds Maintenance |
| NONE | 30/06/2023 | U | N | 65385 | Grounds Maintenance |
I would like to group it in powert query so that the maximum Input date line is displayed.
The example below is how I would like to result to look like.
| Unit Reference | Estate Test | Input Date | Test Grade | Required Action |
| 65335 | Cleaning | 06/07/2023 | D | NONE |
| 65335 | Grounds Maintenance | 23/06/2023 | U | NONE |
| 65369 | Cleaning | 07/07/2023 | C | NONE |
| 65369 | Grounds Maintenance | 07/07/2023 | C | NONE |
| 65385 | Cleaning | 07/07/2023 | C | NONE |
| 65385 | Grounds Maintenance | 07/07/2023 | C | NONE |
I have tried this method
but the results are wrong
Richard
You can use this as your Group step
= Table.ExpandRecordColumn(Table.Group(#"Changed Type", {"Unit Reference", "Estate Test"}, {{"Input Date", each List.Max([Input Date])}, {"All", each Table.Max(_, {"Input Date"})[[Test Grade], [Required Action]]}}), "All", {"Test Grade", "Required Action"}, {"Test Grade", "Required Action"})
2 Replies
You can use this as your Group step
= Table.ExpandRecordColumn(Table.Group(#"Changed Type", {"Unit Reference", "Estate Test"}, {{"Input Date", each List.Max([Input Date])}, {"All", each Table.Max(_, {"Input Date"})[[Test Grade], [Required Action]]}}), "All", {"Test Grade", "Required Action"}, {"Test Grade", "Required Action"})Works perfectly thank you😀
