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

 

[alansidman]

Using your example, please mock up manually what your expected results should look like. I don't quite understand what you are looking to do.

 

[JeffGrant]

Hi Alan, hope you are well.

From the above 73 rows, the only results that meet the criteria would look like this:

name	class	date	position
A Good Chance	BM55	31/07/2021​
A Good Chance	Class B	08/05/2021​	1​
A Good Chance	Mdn	25/04/2021​	1​
A Good Chance	Mdn	13/03/2021​	7​
A Lone Hero	BM58	03/08/2021​
A Lone Hero	RST58	03/07/2021​	1​
A Lone Hero	RST58	13/06/2021​	3​
A Lone Hero	Class 1	13/05/2021​	6​

Cheers

 

[JeffGrant]

Hi Alan,

just thought I would let you know that I managed to achieve the result with two passes of some VBA code. Thanks anyway.

 

[alansidman]

For the benefit of others, please post your code.

 

[JeffGrant]

Good Idea Alan.
1. Sort the data by Name.
2. On the first pass of the code, using the code below, I counted out the number of occurrences

Sub AddCount()
count = 1
For rw = 3 To LastRow
    If Cells(rw, 1) = Cells(rw - 1, 1) Then
        Cells(rw - 1, 5) = count
        count = count + 1
        Else
        Cells(rw - 1, 5) = count
        count = 1
    End If
Next rw
MsgBox "Done!"
End Sub

3. Then off to PQ and filtered the results to return counts of 1, 2, 3 and 4.
4. Then on second pass, using this code.....

Sub Find1stPlace()
count = 1
For rw = 2 To lastRow

    If Cells(rw, 4) = "" And Cells(rw + 1, 4) = 1 Then
        Cells(rw, 5) = 1
        Cells(rw + 1, 5) = 2
        Cells(rw + 2, 5) = 3
        Cells(rw + 3, 5) = 4

    End If
Next rw
MsgBox "Done!"
End Sub

Looked for the criteria of blank and 1, and reassigned the count or index for lack of a better word.

5. Back in PQ to do a transpose as described in an earlier post

And ended up with the results I required. Didn't take long at all.

Cheers

 

[JGordon11]

a PQ solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    nmbrRows = Table.RowCount(Source),
    ChangeType = Table.TransformColumnTypes(Source,{{"name", type text}, {"class", type text}, {"date", type date}, {"position", Int64.Type}}),
    
    lstL1 = List.Generate(()=>0, each _ < nmbrRows -3, each _ +1, 
       each ChangeType[position]{_} = null and 
            ChangeType[position]{_+1} = 1 and 
            ChangeType[position]{_+2} <> null and 
            ChangeType[position]{_+3} <> null),

    AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1, Int64.Type),
    AddCustom = Table.AddColumn(AddIndex, "Custom", each lstL1{[Index]}),
    ReplaceErrors = Table.ReplaceErrorValues(AddCustom, {{"Custom", false}}),
    
    AddConditionalColumn = Table.AddColumn(ReplaceErrors, "TF", each if 
            ReplaceErrors[Custom]{[Index]} = true or 
            ReplaceErrors[Custom]{[Index]-1} = true or 
            ReplaceErrors[Custom]{[Index]-2} = true or 
            ReplaceErrors[Custom]{[Index]-3} = true 
    then true else false),
    
    RemoveColumns = Table.RemoveColumns(AddConditionalColumn,{"Index", "Custom"}),
    ReplaceErrors1 = Table.ReplaceErrorValues(RemoveColumns, {{"TF", false}}),
    FilterRows = Table.SelectRows(ReplaceErrors1, each ([TF] = true)),
    RemoveColumns1 = Table.RemoveColumns(FilterRows,{"TF"})
in
    RemoveColumns1

 

[XL Pro]

Here's another PowerQuery Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"name"}, {{"AllData", each _, type table [name=text, class=text, date=text, position=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Position Condition", each if [AllData]{0}[position] = null and [AllData]{1}[position]= 1 then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Position Condition] = true)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "FirstFour", each Table.FirstN([AllData],4)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"FirstFour"}),
    #"Expanded FirstFour" = Table.ExpandTableColumn(#"Removed Other Columns", "FirstFour", {"name", "class", "date", "position"}, {"name", "class", "date", "position"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded FirstFour",{{"name", type text}, {"class", type text}, {"date", type date}, {"position", Int64.Type}})
in
    #"Changed Type"

 

[XL Pro]

With a little more advance PQ, you can combine the logic and First 4 rows into the Grouping, then it's just a matter of filter, remove unneeded columns and expand

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"name"}, {{"AllData", each Table.FirstN(_,4), type table},{"Condition" ,each if _{0}?[position]? = null and _{1}?[position]? = 1 then true else false, type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Condition] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"AllData"}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Removed Other Columns", "AllData", {"name", "class", "date", "position"}, {"name", "class", "date", "position"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded AllData",{{"name", type text}, {"class", type text}, {"date", type date}, {"position", Int64.Type}})
in
    #"Changed Type"

this also catches errors if there's only 1 row in the grouping

 

[lrobbo314]

Interesting. Never seen this kind of syntax before.

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

Here was my take on it. Just a bit different.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"name"}, {{"AllData", each _, type table [name=text, class=text, date=date, position=nullable number]}}),
    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"),
    Expand = Table.ExpandTableColumn(SR, "AllData", {"class", "date", "position"}, {"class", "date", "position"}),
    Types = Table.TransformColumnTypes(Expand,{{"name", type text}, {"class", type text}, {"date", type date}, {"position", Int64.Type}})
in
    Types