IF function custom column power query referencing previous row

Question

I am trying to get a helper value that changes if certain values change from the prior row. For instance, if the Site, Room or Cubicle value changes from the previous row, I want the helper value to increase by one. I am utilizing this method to separate specific data by row fill color utilizing conditional formatting. In a general workbook I can reference cells so a simple IF statement such as the following works.

=IF(AND(A3=A2, B3=B2, C3=C2),$E2, $E2+1)

Example of need Current workbook query

I am very new to utilizing power query and have no idea of how to get similar results. Below is an example of my first attempt, which clearly did not work.

Power query attempt

I tried referencing the column-1. Additionally, as I cannot start with a value (1 in the prior example) in the first helper value cell, I cannot increase the value by one. Can anyone please assist?

Answer 1

Another method in Powerquery uses Table.Group with GroupKind set to local to increment an index any time Site/Room/Cubicle changes

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Special={"Site","Room","Cubicle"},
#"Grouped Rows" = Table.Group(Source, Special, {"data", each _, type table},GroupKind.Local),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
ColumnsToExpand =List.RemoveItems(List.Distinct(List.Combine(List.Transform(#"Grouped Rows"[data], each Table.ColumnNames(_)))),Special),
#"Expanded data" = Table.ExpandTableColumn(#"Added Index", "data", ColumnsToExpand ,ColumnsToExpand )
in #"Expanded data"

Answer 2

here is a workaround for you.

1. you can reference to duplicate the table

2. remove duplicates

3. add an index column

4. merge two tables , press ctrl to select three columns and make sure the numbers beside the column name are matching

5. at last , expand the new column