Power Query Custom Index

[Dicken]

Hi, 
is there any way in power query you can create a repeated , Quotient based, , recurring Modulo based index 
without first have a 'normal' one,   

 so to have { 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,each ; 

Table.TransformColumns(
Table.AddIndexColumn( Source, "dex", 0, 1 ,Int64.Type),
{ "dex" , each Number.IntegerDivide( _ , 5 ) } )

 but you coud have the index increment as 1/5 and just take the the whole or integar part  ?  
I don't think there is, but I keep being wrong. 

Richard. 

[ZhangKun · Accepted solution]

The code you gave is good enough, concise and efficient enough.

The fourth parameter of the Table.AddIndexColumn function can be 0.2, but the result cannot be kept as an integer. Although the fifth parameter can specify an integer type, the set type is a weak constraint, it does not force the type conversion, and will generate an error when uploading to the model (if the fourth parameter is a decimal).

[Dicken]

Anther method, not sure if very effecient but ; 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 =
let rc = Table.RowCount( Source ) ,
mx = Number.IntegerDivide(rc, 5) ,
dex = List.Transform( {0..mx} , (x)=>List.Repeat( {x} , 5 ) ),
dexlist = List.FirstN( List.Combine( dex ) ,rc)
in
Table.FromColumns(
Table.ToColumns( Source) & {dexlist},
Table.ColumnNames(Source) & {"Index"} )
in
Custom1

[SundarRaj · Accepted solution]

Hi @Dicken , in case you need repeated index column, List.Repeat can be used. I'll attach the images below. Let me know If I have understood your query correctly. Thanks!

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
ListIndex = Table.TransformColumns(#"Changed Type",{"Index", each List.Repeat({_},5)}),
Expand = Table.ExpandListColumn(ListIndex, "Index")
in
Expand

[Dicken]

I have come up with this, but I need to check the maths for getting the correct max number 
but the principle works; 

let rc = Table.RowCount( Source ) ,
mx = ( Number.IntegerDivide( rc, 5 ) * 1 ) + rc ,
pos = Number.IntegerDivide( mx, 5 ) in
List.Combine(
List.Transform( {0..pos -1}, (x)=>
List.Repeat( {x} , 5 ) ))

Then 

Table.FromColumns(
Table.ToColumns( Source) &
  repeatedlistabove  } , Table.ColumnNames(Source) & {"Index"} )

Richard. 

[Dicken]

Thats a formula for inserting postion, which so in greater than needed. 

[Dicken]

Doesn't  increase the sisze of the table  upon expanding ?  it could be a staringing point , 
make  a recurring list then tabe to cols,   and tab from cols to put back togethre. 

[ZhangKun · Accepted solution]

The code you gave is good enough, concise and efficient enough.

The fourth parameter of the Table.AddIndexColumn function can be 0.2, but the result cannot be kept as an integer. Although the fifth parameter can specify an integer type, the set type is a weak constraint, it does not force the type conversion, and will generate an error when uploading to the model (if the fourth parameter is a decimal).