How to extract the first four occurances

[JeffGrant]

Hi All,

I have a data set of some 45,000 records that looks like this.

Book2
	A	B	C	D
1	name	class	date	position
2	A Big Chance	Class 1	04/08/2021
3	A Big Chance	BM68	28/07/2021	8
4	A Big Chance	Class 1	21/07/2021	3
5	A Big Chance	Class 1	07/07/2021	10
6	A Big Chance	Class 2	02/06/2021	12
7	A Big Chance	Class 1	26/05/2021	9
8	A Fighting Fury	BM58	19/07/2021
9	A Fighting Fury	BM58	19/07/2021	7
10	A Fighting Fury	BM58	05/07/2021	3
11	A Fighting Fury	BM58	22/06/2021	4
12	A Fighting Fury	BM58	07/06/2021	6
13	A Fortunate Lass	Class 3	31/07/2021
14	A Fortunate Lass	Class 3	19/06/2021	4
15	A Fortunate Lass	Class 3	29/05/2021	4
16	A Fortunate Lass	Class 2	16/05/2021	1
17	A Good Chance	BM55	31/07/2021
18	A Good Chance	Class B	08/05/2021	1
19	A Good Chance	Mdn	25/04/2021	1
20	A Good Chance	Mdn	13/03/2021	7
21	A Knight In Paris	BM54	04/08/2021
22	A Knight In Paris	BM54	14/07/2021	9
23	A Knight In Paris	BM54	16/06/2021	10
24	A Knight In Paris	BM62	02/06/2021	3
25	A Knight In Paris	BM54	16/05/2021	6
26	A Krupt Pick	BM58	16/07/2021
27	A Krupt Pick	BM58	06/07/2021	7
28	A Krupt Pick	Class 3	20/06/2021	11
29	A Krupt Pick	Class 3	05/03/2021	8
30	A Land Of	Mdn	25/07/2021
31	A Land Of	Mdn	01/07/2021	4
32	A Land Of	Mdn	22/06/2021	2
33	A Land Of	Mdn	22/05/2021	7
34	A Little Luce	Mdn	07/08/2021
35	A Little Luce	Mdn	23/01/2021	5
36	A Little Luce	Mdn	05/01/2021	5
37	A Little Luce	Mdn	10/07/2020	6
38	A Little Vague	BM65	16/07/2021
39	A Little Vague	BM65	02/07/2021	7
40	A Little Vague	BM65	24/06/2021	12
41	A Little Vague	BM65	28/05/2021	5
42	A Little Witty	Open	02/08/2021
43	A Little Witty	Mdn	27/07/2021	8
44	A Lone Hero	BM58	03/08/2021
45	A Lone Hero	RST58	03/07/2021	1
46	A Lone Hero	RST58	13/06/2021	3
47	A Lone Hero	Class 1	13/05/2021	6
48	A Magic Zariz	BM68	22/07/2021
49	A Magic Zariz	BM68	03/07/2021	2
50	A Magic Zariz	Open	20/06/2021	2
51	A Magic Zariz	BM72	26/05/2021	7
52	A Midnight Shadow	Mdn	04/08/2021
53	A Midnight Shadow	Mdn	29/07/2021	3
54	A Midnight Shadow	Class 1	14/07/2021	16
55	A Midnight Shadow	Mdn	23/06/2021	8
56	A Pinch Of Luck	BM68	17/07/2021
57	A Pinch Of Luck	Open	03/07/2021	9
58	A Pinch Of Luck	BM64	02/06/2021	12
59	A Pinch Of Luck	BM64	14/05/2021	4
60	A Real Wag	Mdn	16/07/2021
61	A Real Wag	Mdn	01/03/2021	6
62	A Real Wag	Mdn	05/02/2021	6
63	A Real Wag	Mdn	22/01/2021	9
64	A Shin Rook	Open	10/07/2021
65	A Shin Rook	Group 3	28/11/2020	16
66	A Shin Rook	Listed	04/07/2020	8
67	A Shin Rook	Open	06/06/2020	7
68	A Tender Lady	Mdn	16/07/2021
69	A Tender Lady	Mdn	18/06/2021	3
70	A Tender Lady	Mdn	31/05/2021	5
71	A Tender Lady	Mdn	07/05/2021	3
72	A Thousand Degrees	BM54	18/07/2021
73	A Thousand Degrees	BM54	18/07/2021	2
Pattern__2

How can I use PQ to extract only the four most recent occurrences on the field name?

The next dilemma is to only keep the four most recent occurrences, where there is a 1, in the position column below the blank cell.

In other words, the sequence I am looking to extract from column D is:

Blank or null
1
any other number
any other number

if the first four occurrences of the name do not follow this sequence, they can filtered out.


Any and all guidance is graciously received.

Thanks

 

[XL Pro]

Irobbo314 -

each if _{0}?[position]? = null

This is basically shorthand for "If Not Exists return null" you can read about it here: Referencing Individual Cell Values From Tables In Power Query
I only needed it on my second row reference, it's unneeded in the part quoted.

Due to that, your query will produce an error row if an entry only has 1 item, so if you change your SR line as such, it will "catch" that error:

 SR = Table.SelectRows(Table.TransformColumns(Group,{{"AllData", each if _[position]{0}=null and _[position]?{1}?=1 then Table.FirstN(_,4) else "x"}}), each _[AllData]<>"x"),

 

[lrobbo314]

Very cool. Thanks for the insight. Love learning new PQ tricks.

 

[JeffGrant]

Thank you all for you contributions. I apologise for the delay in response. I have been away for work and internet connectivity is a bit scarce.

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Combine(List.RemoveNulls(Table.Group(Source, {"name"}, {{"T", each  if  [position]{0}= null and [position]{1}= 1 then Table.FirstN(_,4) else null}})[T]))
in
    Group

 

[XL Pro]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Combine(List.RemoveNulls(Table.Group(Source, {"name"}, {{"T", each  if  [position]{0}= null and [position]{1}= 1 then Table.FirstN(_,4) else null}})[T]))
in
    Group

FYI this solution fails if there is only one record for a name. Changing the if condition will fix it:

= Table.Combine(List.RemoveNulls(Table.Group(Source, {"name"}, {{"T", each  if  [position]{0}= null and [position]?{1}?= 1 then Table.FirstN(_,4) else null}})[T]))