Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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).
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
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
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
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.
Thats a formula for inserting postion, which so in greater than needed.
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.
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).
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |