How to simplify an auto incremented value by a condition

Clow47 · ‎05-03-2023

Basically I'm trying to "simplify" a specific query for two reasons:

1) It makes the transformation too long to execute

2) I'm not able to merge the table with a copy of it, and I'm guessing that query is the reason why.

The query in question would be the following:

= Table.AddColumn(#"Added Index", "Custom.2", each List.Count(List.Select(List.FirstN(#"Added Conditional Column"[Table Index Identifier],[Index]+1), each _ = "Table")))

And the logic I'm trying to address, is something like this:

I've multiple mini tables in the same file, and I need a index to recognize each row of each table. The index must be the same for each row of each table. So, let's say we have something like this:

Then we'll need to have the first two rows with 1, the next ones with 2, etc. If you see the number at the left (the 1s and 2s) they are the identifier if it's the first row of that table.

So, after some steps, I managed to get something like this:

Where I unpivoted some columns (Col_1 to Col_4) and add a column (Table index identifier) to "recognize" the first row of each mini-table.

With that information, what I need is a logic to add the previously mentioned index. The logic I though of was something like "An incremental index that increments each time a "Table" is found in the "Table index identifier" column.

I've searched for similar cases and that's how I came to the query I've today. But as I said before, it's giving me a hard time when loading, and when trying to merge this table with a duplicate of itself, so I was wondering if there's any other way to aproach this case!