The following code snippet runs for hours and I do not know if it ever completes because after seeing how long the first run is, I canceled it. Here is the M code and table size is about 250K in import mode:
...
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
new_idx_list = List.Accumulate(#"Added Index"[Index],{0}, (state,current) => if current = 0 then {1} else if #"Added Index"{current-1}[Status] = #"Added Index"{current}[Status] then state & {List.Last(state)} else state & {List.Last(state)+1}),
Add_columns= Table.FromColumns(Table.ToColumns(#"Added Index")&{new_idx_list})
in
Add_columns
If with a small dataset like this, the M engine already struggles to generate 2 index columns, then what other solution is available? There are only 2 categories in this table, ordered by datetime ascending, and the use case is:
1_assign the same index to each category cluster, ie starting with index == 1, if the first 10 rows are of the same category (cat1), then assign index value == 1 to all these rows;
2_if next 5 rows are of category 2 (cat2), then assign index value == 2 to all these;
3_if next rows are of cat1, then assign index value == 3 to these;
4_if next rows are of cat2, then assign index valuue == 4 to all these rows, and so on, effectively assigning the same index value to each group of rows that occur in the table if they belong to the same category AND are contiguous, thus as soon as the next row belongs to the other category, then a new index number (incremented by 1 from the previous index nuumber) will be assigned to this next group, etc.
The goal is to extract the first row from each group, that is, every time we detect a new index number, that is an index different by 1 from the previous, then we know that this is the first row of the group this index number identifies and we want to return a table that contains only these first rows.
I hope this is clear enough but if not, please let me know, and I'll give it another shot.
