How to create group index with Power Query or R

It is possible to generate a group index (unique row ids) by one or many groups in Power Query. It comes handy when you have to detect the first or last record in each group. Here is 2 solutions: group index with M language and group index with R.

Group index in Power Query with M language.

1. Sort data in the order that you want to index.

2. Group your data by columns and return all rows in a new column named “Rows”.

3. Go to Advanced editor and add a new line of code that can generate indices in all the previously grouped tables.

Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})

4. Click on the expand icon and check all columns that are needed.

Here is all the M code that you can put in a blank query.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZQ9TwMxDIb/S+cO8VfijAUJBmamqsOpOsEJaKW7MvDvSdITLZePmzz4UfzGr+39frP7ni5j9zl0m+3mYfjopxDRGAkByHjDbnPYVjEbAwijSgtzMXh2Tn0L0xA0Vs2gx8/z5X04vd3EISuuYFEcCCGscEmdWqAVLspjsIWO7I7HfprO43DfPiS7DkaN5EjyL2dolCmONP9Ohv5v5OtL7i16EA/FdJQUcqKumI4yAAWMmmI+1gZgFbW3fO6fs6K1fDIu1OAakDSwQVMDkghFuGtA0SNQsrbBpPkWe6+k6AsRcKtWGh3i2bunsTsd+8LCqRGHVSQZE+pwHYlavDplU0XSbDiDuHilYJER02KiHu+ttpjrtLD3LSi5xTLvyx9UdEzZyQqWxsd7pRUu7b14XXtP047yfEee+/GrO/0UVorCfZM6k+xDQGq8k9plwuUAU4f0ConO1/QG5R6ycdqEkok2013cN09NKuoKRx4XUNFG8m4pvuxj8Gf5gYqRhLwGJietj3fx8As=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, Category = _t, Year = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Category", type text}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "Category"}, {{"Rows", each _, type table [Country=text, Category=text, Year=number, Value=number]}}),
Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Year", "Value", "GroupIndex"}, {"Year", "Value", "GroupIndex"})
in
#"Expanded Rows"

Group index by running R script in PowerQuery.

If you have experience with R, then this task will be easy for you, because there is rowid function in data.table package. You can choose one or many columns to group data and create unique row ids within each group.

1. Make sure that R script works (i prefer to test it first in RStudio) and your dataset is sorted in a way that you want to make indices.

2. Go to Transform -> Run R script

Paste your R script, use “dataset” to get your data and record result in a new dataset (you can name it as you like).

library(data.table)

dataset$GroupIndex <- rowid(dataset$Country, dataset$Category)

WithGroupIndex <- dataset

3. Press OK.

Make sure that data sources are set to public and if you need to publish that please check if R library is available in Power BI service. In this case library data.table is available.

Here is all the M code that you can put in a blank query.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZQ9TwMxDIb/S+cO8VfijAUJBmamqsOpOsEJaKW7MvDvSdITLZePmzz4UfzGr+39frP7ni5j9zl0m+3mYfjopxDRGAkByHjDbnPYVjEbAwijSgtzMXh2Tn0L0xA0Vs2gx8/z5X04vd3EISuuYFEcCCGscEmdWqAVLspjsIWO7I7HfprO43DfPiS7DkaN5EjyL2dolCmONP9Ohv5v5OtL7i16EA/FdJQUcqKumI4yAAWMmmI+1gZgFbW3fO6fs6K1fDIu1OAakDSwQVMDkghFuGtA0SNQsrbBpPkWe6+k6AsRcKtWGh3i2bunsTsd+8LCqRGHVSQZE+pwHYlavDplU0XSbDiDuHilYJER02KiHu+ttpjrtLD3LSi5xTLvyx9UdEzZyQqWxsd7pRUu7b14XXtP047yfEee+/GrO/0UVorCfZM6k+xDQGq8k9plwuUAU4f0ConO1/QG5R6ycdqEkok2013cN09NKuoKRx4XUNFG8m4pvuxj8Gf5gYqRhLwGJietj3fx8As=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, Category = _t, Year = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Category", type text}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
#"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)library(data.table)#(lf)#(lf)dataset$GroupIndex <- rowid(dataset$Country, dataset$Category)#(lf)#(lf)WithGroupIndex <- dataset",[dataset=#"Changed Type"]),
#"""WithGroupIndex""" = #"Run R script"{[Name="WithGroupIndex"]}[Value]
in
#"""WithGroupIndex"""