Forum Discussion
Post ProdigyPower 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.
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
6 Replies
ZhangKunSuper 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).
- SundarRaj
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
DickenDoesn'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.- 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
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