Repeated indexing

[cmcreynolds]

Hello everyone! I'm in a new position where I'm allowed to use PQ and PP again.

As always, I apologize if I am not asking this using the best terms:

I need to create a column within Power Query that indexes according to the column "SiteVisit".
The TL (Team Lead) is present in every site visit, but sometimes there are three persons, so a simple IF/Then statement won't work.

SiteVisit	Person	CustomColumn
9	TL-IP	1
9	DH	2
9	RR	3
10	TL-GQ	1
10	AR	2
10	TH	3
11	TL-MN	1
11	JJ	2
12	TL-PF	1
12	KK	2
13	TL-ST	1
13	BM	2

<tbody>
</tbody>

Glad to be back and have your help again.

Thanks for your patience, too!
Cliff

 

[RudiS]

Here is some 'M'.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"SiteVisit"}, {{"Data", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Add Index", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Add Index" = Table.ExpandTableColumn(#"Removed Columns", "Add Index", {"Person", "Index"}, {"Person", "Index"})
in
    #"Expanded Add Index"