Indexing based on two columns

[mboulos11]

Hello guys,

I have this table in power query, and I need to do indexing based on "UnitID" & "Attribute" columns.

I want to index to start every time the Attribute is equal to "Installer", then start from the beginning everytime the UnitID is changed. For example:

UnitID	Attribute	Value	Index
dslk322	Installer	Team 1	1
dslk322	Room	Livingroom	1
dslk322	...	...	1
dslk322	...	...	1
dslk322	Installer	Team 2	2
dslk322	Room	Bedroom 1	2
dslk322	...	...	2
dslk322	...	...	2
lkj32091	Installer	Team 2	1
lkj32091	Room	Bedroom 1	1
lkj32091	...	...	1
lkj32091	...	...	1

Can you help me pls?

[Jakinta · Accepted solution]

You can try with steps below after the step your table looks like on an image.

let
    #"Added Index" = Table.AddIndexColumn( PreviousStepName, "Index", 0, 1, Int64.Type),
    Custom1 = Table.AddColumn(#"Added Index", "Extra_Index", each if [Index] = 0 then 1 else if #"Added Index"{[Index]}[Attribute] = "Installer" then 1 else 0),
    Grouped = Table.RemoveColumns ( Table.Group(Custom1, {"UnitID"}, {{"A", each  Table.FromColumns (Table.ToColumns(_) & {List.Skip(List.Accumulate(_[Extra_Index],{-1}, (s , c) => s & {List.Last(s) + c }))}, Table.ColumnNames(_) &{"i"}), type table }}), {"UnitID"} ),
    Expanded = Table.ExpandTableColumn (Grouped, "A", Table.ColumnNames ( Grouped [A]{0})),
    FINAL = Table.RemoveColumns(Expanded,{"Index", "Extra_Index"})
in
    FINAL