Power BI / Power Query - M language - playing with data inside group table

Question

Hello M language masters!

I have a question about working with grouped rows when the Power Query creates a table with data. But maybe it is better to start from the beginning.

Important information! I will be asking for example only about adding an index. I know that there are different possibilities to reach such a result. But for this question, I need an answer about the possibility to work on tables. I want to use this answer in different actions (e.g table sorting, adding columns in group table).

In my sample data source, I have a list of fake transactions. I want to add an index for each Salesman, to count operations for each of them.

Sample Data

So I just added this file as a data source in Power BI. In Power query, I have grouped rows according to name. This step created form me column contained a table for each Salesman, which stores all his or her operations.

Grouping result

And now, I want to add an index column in each table. I know, that this is possible by adding a new column to the main table, which will be store a new table with added index:

Custom column function

And in each table, I have Indexed. That is good. But I have an extra column now (one with the table without index, and one with a table with index).

Result - a little bit messy

So I want to ask if there is any possibility to add such an index directly to the table in column Operations, without creating the additional column. My approach seems to be a bit messy and I want to find something cleaner. Does anyone know a smart solution for that?

Thank you in advance. Artur

Answer 1

Sure, you may do it inside Table.Group function:

= Table.Group(Source, {"Salesman"}, {"Operations", each Table.AddIndexColumn(_, "i", 1, 1)})

P.S. To add existing index column to nested table use this code:

= Table.ReplaceValue(PreviousStep,each [index],0,(a,b,c)=>Table.AddColumn(a,"index", each b),{"Operations"})