List.Alternate ,

[Dicken]

Hi,   I wanted to use List.Alternate to create a list of positions,  so the result wanted is;

Result wanted  = { 5, 12, 19, 26 }
this is my starting point and  numbers;   

[
rc = 23 ,
grupe = 5,
insertN = 2,
mx = (Number.IntegerDivide( rc, grupe) * insertN) + rc ,
maxlist = {grupe..mx} ,
altlist = List.Alternate( maxlist,(grupe + insertN ) -1,1,1)
]

as you can see I started the maxlsit at 'grupe' 5,  but what if i wanted to start  at 0 or 1, 
the best I've can do is to start at -1  ,  but if someone cant come up with a way to start at 0 or 1 and 
get the same result I would like, to know, for practical purposes I would probably use generate, so this is just 
a 'can it be done' question; 

[ 
   rc = 23 ,
   grupe = 5,
   insertN = 2, 
   mx = (Number.IntegerDivide( rc, grupe) * insertN) + rc , 
   maxlist = {-1..mx} ,
   altlist = List.Alternate( maxlist,(grupe + insertN ) -1,1,1) 
]

Ricard.

[v-hjannapu · Accepted solution]

Hi @Dicken,

Thank you for reaching out to the Microsoft fabric community forum.

you can't reliably use List.Alternate to get {5, 12, 19, 26} starting at 0 or 1 because it picks by position, not value. Changing the starting point shifts the positions of your desired values.

If you want to generate {5, 12, 19, 26}, the easier way is:

List.Generate(() => 5, each _ <= 26, each _ + 7)

Starts at 5

Adds 7 each time

Stops when the value is > 26

This directly gives you:

{5, 12, 19, 26} 
List.Alternate picks items based on their position in the list, not the actual values.

Please find the attached screenshot for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team 

[Dicken]

my last word an thanks for the listnumber idea, ; 

 let
    recs =  [ rc = Table.RowCount( Source) , 
    insertN = 2, grupe = 3 ,  
    mx =  Number.IntegerDivide( rc, grupe)  , 
    pos = List.Numbers( grupe, mx, (grupe + insertN)) ,
    check = Number.Mod( rc, grupe) = 0 , 
     rslt = if check  then List.RemoveLastN( pos,1) else pos
           ] 
in List.Accumulate( recs [rslt] , Source, (s,c)=> Table.InsertRows( s, c, inserts) )

[ronrsnfld · Accepted solution]

Not sure what you mean by "same result" in this context. If you mean the same pattern (incrementing by seven (7) but with the sequence starting at 0 or 1), just start maxlist at that number.

eg:

[
rc = 23 ,
grupe = 5,
insertN = 2,
mx = (Number.IntegerDivide( rc, grupe) * insertN) + rc ,
maxlist = {0..mx} ,
altlist = List.Alternate(maxlist,(grupe + insertN )-1 ,1,1)
]

-->

If you mean something else, please be more specific

[Dicken]

I meant same result { 5, 12, 19 ,26} ? 

[ronrsnfld]

Then you need to add one or two nulls to the beginning of the array.

For example, with mxlist starting at zero (0):

altlist = List.Alternate({null} & maxlist,
            (grupe)+1, 1,0)

[v-hjannapu · Accepted solution]

Hi @Dicken,

Thank you for reaching out to the Microsoft fabric community forum.

you can't reliably use List.Alternate to get {5, 12, 19, 26} starting at 0 or 1 because it picks by position, not value. Changing the starting point shifts the positions of your desired values.

If you want to generate {5, 12, 19, 26}, the easier way is:

List.Generate(() => 5, each _ <= 26, each _ + 7)

Starts at 5

Adds 7 each time

Stops when the value is > 26

This directly gives you:

{5, 12, 19, 26} 
List.Alternate picks items based on their position in the list, not the actual values.

Please find the attached screenshot for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team 

[slorin]

Hi @Dicken , @v-hjannapu 

The easier way to generate {5, 12, 19, 26} is 

List.Numbers(5, 4, 7)

Stéphane 

[Dicken]

But thanks anyway, it would not work in the situration I had, as I need to to be based on other numbers.  example if i'm inserting  two rows at   row 7, 12, 19 ,  7 = 7 , 12 = 14, 19 = 23 etc..

[Dicken]

I did not want an easier way I wanted to get better at using Alternate.

[Dicken]

No i did not think so, just wondered if I was overlooking something, the closest I got was 

= let
rc = 23,
grupe = 5,
insertN = 2,
mx = (Number.IntegerDivide(rc, grupe) * insertN) + rc,
maxlist = {0..mx},
altlist = List.Alternate(maxlist,
(  grupe )+1, 1,0)
in
List.Transform( altlist,(x)=> x - 1)

thanks for clearing up,  generate is simpler but I just wanted to try as I don't use alternate much. 

Richard. 

[bhanu_gautam]

@Dicken , Try using

let
rc = 23,
grupe = 5,
insertN = 2,
mx = (Number.IntegerDivide(rc, grupe) * insertN) + rc,
maxlist = {0..mx},
altlist = List.Alternate(maxlist, grupe + insertN, grupe, 1)
in
altlist

[Dicken]

sorry but i don't think that wil  prdcucres {5, 12, 19, 26}  if it does I'll accept,