Forum Discussion
Adding Recuring Index
Hi,
is it possibel to replacate ;
QUOTIENT(SEQUENCE(25,1,0,1),3)+1 in power Query without first addin an actual index ;
so I've got ;
Table.TransformColumns(
Table.AddIndexColumn(Source, "Index", 0,
1), { "Index", each Number.IntegerDivide(_, 3 ) + 1 } )
but you can put 1 /3 as the increment for index but not Int div which = 0 ? and rounding does not seem to work either .
If it can't be done I can stop trying.
Richard
AFAIK, you can't do to directly get your desired output. But you could do something like:
= Table.AddIndexColumn(Source, "Index", 2/3,1/3) or = Table.AddIndexColumn(Source, "Index", 2/3,1/3, Int64.Type)and then, at the next step, change the data type to Int64.Type (whole number).
I would have thought is would do the conversion in the same step, but it does not.
10 Replies
Dicken I don't believe so, no. You can add the Index column, then add your calculated column and then remove the Index column.
Thanks I'll leave it open, never knwo someone might have ideas. I'm still not sure why
and increment of 1/3 works but Number.IntegerDivide( 1,3) just results in zero's.Not sure exactly what you want.
To create the same sequence (List) of values you can use the List.Accumulate function
List.Accumulate( {0..24}, {}, (s,c)=>s & {Number.IntegerDivide(c,3)+1})If you want something else, please be more specific.
Yes also used
= let alist = {0..Table.RowCount( Source)-1 } ,
index = List.Transform( alist, (x)=>
Number.IntegerDivide( x, 3 ) + 1 ) in index
and then Table from / to columns , to get result.
I was more interested in if there was a way to manipulate add indexAFAIK, you can't do to directly get your desired output. But you could do something like:
= Table.AddIndexColumn(Source, "Index", 2/3,1/3) or = Table.AddIndexColumn(Source, "Index", 2/3,1/3, Int64.Type)and then, at the next step, change the data type to Int64.Type (whole number).
I would have thought is would do the conversion in the same step, but it does not.
You can apply both in one step of adding an index column, and you have to do it into two steps
If you start the Index at 1, but then integer/divide by 3, your result is not as desired. Note that it starts with "0,0,1" instead of the desired "1,1,1"
