Power Query: Add a Column with a custom Group ID

[jajatidev]

Hi,
In Excel, I used the below function to count the number of rows to assign them a group ID like "G1, G2, G3".
In total, I had 8 groups roughly having 3000 rows each.

=INDEX($A$2:$A$9,MOD(ROWS(D$11:D11)-1,$A$1)+1)

The objective of this was to split table content into 8 groups and execute the extraction separately in SAP to prevent runtime errors.
Please advise how to replicate this in the power query.

 

[GraH]

Maybe try with the code below. The offset is the number of items you want inside a group.

Table.Split(Table, Offset)

 

[jajatidev]

Maybe try with the code below. The offset is the number of items you want inside a group.

Table.Split(Table, Offset)

Hi,
Thanks, for the solution. However, I do not wish to split the table. The exercise is to group the data.
The requirement is to count the number of rows in the data set excluding the header. the in a new column assign Group IDs like G1, G2, G3 etc. I have maintained the sequence from G1 to G8
I wish to replicate the Excel formula in power query to reduce manual intervention.

 

[GraH]

The idea is to add an index to each subtable. Then combine the tables again.
The outcome is you have created groups.

You can add a step where you do a table.rowcount of the source table. Then divide by 8. And roundup. Split source again by the last number.

In my head this is doing the same as you do with the excel formula. Except it is not on each row, it is on each block of data.

 

[jajatidev]

Thanks

 

[GraH]

Glad to assist. Thanks for the mark as solution.