Grouping issue

[cottrera]

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

[cottrera]

Works perfectly thank you😀

[Vijay_A_Verma · Accepted solution]

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"})