Add an index column in groups of three. 1,1,1,2,2,2,3,3,3, etc

[laganlee]

Hi

I need an extra column that is like an index column, but groups in threes:

INDEX Column

1

1

1

2

2

2

3

3

3 etc

I need this after my Column59:

However, I don't want use the usual 'group by' solution as the table is more complicated than that and it wouldn't work!

Many thanks for looking!

[laganlee]

Thanks a lot Pete, that is awesome!  

Also very easy to do 🙂

Much appreciated!

[BA_Pete · Accepted solution]

Hi @laganlee ,

You can use Table.Split instead of Table.Group:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZNRkgIxCETvMt8OAw2BcBbL+19jYxwd3bXQqv0kPDrQJOfzAoYKL6dFeGPZruEIVnCQ+nI5TUI4xiFjG9AIr/iq/bsspjp8V8fMSqOwBzHrJfd6mUT60/23DjEI3DVErZNGRUA7iVWEGejo9A2RKWSlhDQlryTEkGR52CG72TjsYCeXikCMWyo7EI26/M8vpY4iHy0J5UrGoJFVkzrsrgDPIKnMNDeKcmHmxNUUwk8v712PEPJWCQSh3DdcKatVpDWqXPg8wyejVY2yAn65MAfvG+v9/5u219fwh1gfyOUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [period = _t, dtTrans = _t, value = _t]),

    splitTable = Table.Split(Source, 3),
    convToTable = Table.FromList(splitTable, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    addIndex = Table.AddIndexColumn(convToTable, "Index", 1, 1, Int64.Type),
    expandNestedTables = Table.ExpandTableColumn(addIndex, "Column1", {"period", "dtTrans", "value"}, {"period", "dtTrans", "value"})

in
    expandNestedTables

Pete