Power Query , get record with last data and 1 more criteria

[Wishmaster89]

Hi All

I've got some data which has duplicate ID's(uln). I want to get the last row based on the max date and the standard description. I know how to get the last date by doing group by ID and then getting the max date(col B) for each but how do i add in additional criteria please?

So , ID 2 should have a record for music and history ect.

Min ifs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	ULN	MaxDate	All1.ULN	All1.Academic_Year	All1.Completion_Status	All1.Learning_Outcome	All1.Framework_Name	All1.Standard_Code	All1.Standard_Description	All1.Learning_Start_Date2	All1.Learning_Start_Date	All1.Learning_End_Date3
2	2423208986	04/11/2021 00:00	2423208986	2017	1	9	English	60	English	20170502	02/05/2017 00:00	15/10/2021 00:00
3	2423208986	04/11/2021 00:00	2423208986	2018	2	1	English	60	English		01/09/1989 00:00	04/11/2021 00:00
4	2423208986	04/11/2021 00:00	2423208986	2020	2	1	Geography	60	Geography		02/05/2017 00:00	07/05/2021 00:00
5	2	19/10/2022 00:00	2	2017	1	9	Music	60	Music	20170502	02/05/2017 00:00	09/08/2020 00:00
6	2	19/10/2022 00:00	2	2018	2	1	Music	60	Music		10/03/1989 00:00	11/09/2021 00:00
7	2	19/10/2022 00:00	2	2020	2	1	History	60	History		02/05/2017 00:00	19/10/2022 00:00
8	3	10/10/2022 00:00	3	2010	1	9	Maths	60	Maths	20170502	02/05/2010 00:00	22/10/2012 00:00
9	3	10/10/2022 00:00	3	2013	2	1	IT	60	IT		10/03/2013 00:00	04/05/2015 00:00
10	3	10/10/2022 00:00	3	2022	2	1	Music	60	Music		02/05/2022 00:00	10/10/2022 00:00
example_24

M language ( with data filtered by max date)

let
    Source = Excel.CurrentWorkbook(){[Name="example_24"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ULN", Int64.Type}, {"Academic_Year", Int64.Type}, {"Completion_Status", Int64.Type}, {"Learning_Outcome", Int64.Type}, {"Framework_Name", type text}, {"Standard_Code", Int64.Type}, {"Standard_Description", type text}, {"Learning_Start_Date2", Int64.Type}, {"Learning_Start_Date", type datetime}, {"Learning_End_Date3", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ULN"}, {{"MaxDate", each List.Max([Learning_End_Date3]), type date}, {"All1", each _, type table [ULN=nullable number, Academic_Year=nullable number, Completion_Status=nullable number, Learning_Outcome=nullable number, Framework_Name=nullable text, Standard_Code=nullable number, Standard_Description=nullable text, Learning_Start_Date2=nullable number, Learning_Start_Date=nullable datetime, Learning_End_Date3=nullable datetime]}}),
    #"Expanded All1" = Table.ExpandTableColumn(#"Grouped Rows", "All1", {"ULN", "Academic_Year", "Completion_Status", "Learning_Outcome", "Framework_Name", "Standard_Code", "Standard_Description", "Learning_Start_Date2", "Learning_Start_Date", "Learning_End_Date3"}, {"All1.ULN", "All1.Academic_Year", "All1.Completion_Status", "All1.Learning_Outcome", "All1.Framework_Name", "All1.Standard_Code", "All1.Standard_Description", "All1.Learning_Start_Date2", "All1.Learning_Start_Date", "All1.Learning_End_Date3"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All1", each true),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"MaxDate", type datetime}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([MaxDate]=[All1.Learning_End_Date3] ))
in
    #"Filtered Rows1"

 

[lrobbo314]

Is this the output you're looking for?

WM89
	A	B	C	D	E	F	G	H	I	J	K	L
1	ULN	MaxDate	All1.Standard_Description	All1.ULN	All1.Academic_Year	All1.Completion_Status	All1.Learning_Outcome	All1.Framework_Name	All1.Standard_Code	All1.Learning_Start_Date2	All1.Learning_Start_Date	All1.Learning_End_Date3
2	2423208986	11/4/2021	English	2423208986	2018	2	1	English	60		9/1/1989	11/4/2021
3	2423208986	11/4/2021	Geography	2423208986	2020	2	1	Geography	60		5/2/2017	5/7/2021
4	2	10/19/2022	Music	2	2018	2	1	Music	60		3/10/1989	9/11/2021
5	2	10/19/2022	History	2	2020	2	1	History	60		5/2/2017	10/19/2022
6	3	10/10/2022	Maths	3	2010	1	9	Maths	60	20170502	5/2/2010	10/22/2012
7	3	10/10/2022	IT	3	2013	2	1	IT	60		3/10/2013	5/4/2015
8	3	10/10/2022	Music	3	2022	2	1	Music	60		5/2/2022	10/10/2022
Table1 (2)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ULN", "MaxDate", "All1.Standard_Description"}, {{"Data", each _, type table [ULN=number, MaxDate=datetime, All1.ULN=number, All1.Academic_Year=number, All1.Completion_Status=number, All1.Learning_Outcome=number, All1.Framework_Name=text, All1.Standard_Code=number, All1.Standard_Description=text, All1.Learning_Start_Date2=nullable number, All1.Learning_Start_Date=number, All1.Learning_End_Date3=number]}}),
    SR = Table.TransformColumns(Group,{{"Data", each Table.PromoteHeaders(Table.Transpose(Record.ToTable(Table.Max(_,"All1.Academic_Year"))))}}),
    Expand = Table.ExpandTableColumn(SR, "Data", {"All1.ULN", "All1.Academic_Year", "All1.Completion_Status", "All1.Learning_Outcome", "All1.Framework_Name", "All1.Standard_Code", "All1.Learning_Start_Date2", "All1.Learning_Start_Date", "All1.Learning_End_Date3"}, {"All1.ULN", "All1.Academic_Year", "All1.Completion_Status", "All1.Learning_Outcome", "All1.Framework_Name", "All1.Standard_Code", "All1.Learning_Start_Date2", "All1.Learning_Start_Date", "All1.Learning_End_Date3"})
in
    Expand