Need to create an index column that is grouped.

[gcongrove]

Hello,  

I'm not sure what the term is for what I need, im calling it a grouped index column.

I just need to create a column that starts with 1 repeats it 5 times, then moves to 2 repeats 5 times etc. 

Can this be added in query editor rather than in DAX? I need to manipulate the data in Query editor after I figure out how to get the grouped index column created.

Thank you!!

Example

Index
1
1
1
1
1
2
2
2
2
2
3
3
3
3
3
4
4
4
4
4

[Greg_Deckler]

OK, try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys9LVYrVIUyXlOcTR2cUpaaSxErLLy0ijhELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.IntegerDivide([Index],6)+1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}})
in
    #"Changed Type1"

[gcongrove]

Greg,

This is helpful I think I can make this work!  How did you get the column to stop at "four"? I'm needing to add this column to an existing table and have the index column repeat until the end of the file.

[Greg_Deckler]

Add an Index colum. Then add a custom column where you likely have a math formula that uses mod and int, hard to remember exactly.