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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]SiteVisit
[/TD]
[TD]Person
[/TD]
[TD]CustomColumn
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]TL-IP
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]DH
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]RR
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]TL-GQ
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]AR
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]TH
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]TL-MN
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]JJ
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]TL-PF
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]KK
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]TL-ST
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]BM
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

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"