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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Solution Supplier
Solution Supplier

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
Solution Supplier
Solution Supplier

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

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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