FIltering Records based on ID and max value

[chergh]

Hi,

I am trying to filter a data set shown in the dataset below.

What I want to do is return rows for an optionID but only for the highest LoadID associated with that option ID. So for the example below I would return any rows with a LoadID of '2' for OptionID "abc" and rows with a LoadID of '5' for OptionID "efg"

I am trying to do this with power query but haven't been able to figure it out.

Thanks in advance for any help.

 

[JamesCanale]

Just a regular excel worksheet solution, but what have you got to loose?

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1						Solution					Formulas broken apart to see what is happening
2	Load ID	OptionID	SubOption	Value		Load ID	OptionID	SubOption	Value		abc	2	abc2	2	abc	x	6
3	1	abc	x	4		2	abc	x	6		efg	5	efg5	2	abc	y	6
4	1	abc	y	4		2	abc	y	6					2	abc	z	6
5	1	abc	z	4		2	abc	z	6					5	efg	g	3
6	2	abc	x	6		5	efg	g	3					5	efg	d	3
7	2	abc	y	6		5	efg	d	3					5	efg	f	3
8	2	abc	z	6		5	efg	f	3
9	2	efg	d	3
10	2	efg	f	3
11	5	efg	g	3
12	5	efg	d	3
13	5	efg	f	3
Sheet14

Cell Formulas
Range	Formula
K2:K3	K2	=UNIQUE(B3:B13)
L2:L3	L2	=MAXIFS(A3:A13,B3:B13,K2#)
M2:M3	M2	=K2#&L2#
N2:Q7	N2	=FILTER(A3:D13,NOT(ISNA(MATCH(B3:B13&A3:A13,M2#,0))))
F3:I8	F3	=FILTER(A3:D13,NOT(ISNA(MATCH(B3:B13&A3:A13,UNIQUE(B3:B13)&MAXIFS(A3:A13,B3:B13,K2#),0))))
Dynamic array formulas.