Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Dicken
Responsive Resident
Responsive Resident

Power Query Custom Index

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. 

2 ACCEPTED SOLUTIONS
ZhangKun
Super User
Super User

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).

View solution in original post

SundarRaj
Super User
Super User

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!

SundarRaj_0-1745516309657.png

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

Sundar Rajagopalan

View solution in original post

6 REPLIES 6
Dicken
Responsive Resident
Responsive Resident

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
Super User
Super User

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!

SundarRaj_0-1745516309657.png

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

Sundar Rajagopalan
Dicken
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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

Dicken
Responsive Resident
Responsive Resident

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
Super User
Super User

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).

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.