Performing a FOR loop in PowerApps

[EricLott]

Until FOR loops are added in PowerApps, I have a workaround that I've been using and it's really come in handy.

I start by adding a collection in my App's OnStart property (I'll show at the end how to do this in Flow, which I prefer).

I've attached a template for this from 0-1000 (For.txt at the bottom)

ClearCollect(Loop,
{Index:0},
{Index:1},
{Index:2},
{Index:3},
...
{Index:100}
)

 You can now perform a FOR loop by filtering this collection. For example, if I want to get a collection of dates that are 7 days from a selected date, I can do this:

Clear(NextDates);
ForAll(Filter(Loop,Index<=7),
Collect(NextDates,
 {
     Date:DateAdd(DatePicker1.SelectedDate,Index,Days),
     DaysSincePickedDate:Index,
     DayOfWeek:Weekday(DateAdd(DatePicker1.SelectedDate,Index,Days))
}
))

I get this result:
\

Remember that the numbers don't have to be hardcoded. You can use CountRows(),a numeric input, or any other integer to drive this code.

The shell of the FOR loop looks like this:

Set(i,0);
ForAll(
Filter(Loop,Index<=7,Index>=i),
//Code
)

This would be the equivalent to something like this:

for (i = 0; i <= 7; i++)
{
 //code
}

To clean up the Loop collection, I use Flow. Here's what that solution looks like:

I can now use this code to get my Loop collection (I like to rename the column to Index, since Value is the generic term from Flow)

ClearCollect(Loop,RenameColumns(For.Run(1000),"Value","Index"));

That will return a collection of 1000 rows without all the manual coding! This Flow is pretty slow, so you can also setup this logic to only load it once for each user (this saves the collection to memory on mobile):

LoadData(Loop,"Loop", true );
If(CountRows(Loop)=0,
  ClearCollect(Loop,RenameColumns(For.Run(100),"Value","Index"));
  SaveData(Loop,"Loop")
  )

[EricLott · Accepted solution]

Marking solution

[CarlosFigueira · Accepted solution]

This is a pretty good workaround to the lack of a 'for' construct in PowerApps. I've been using something similar, and those are some things that you can do to make your code a little faster and/or smaller:

• To create the 'Loop' collection, you can use the abbreviated array syntax ([...]), and rename the column to 'Index':
  • ClearCollect(LoopMaster(RenameColumns([0,1,2,3,4,5,6,7,8,9,...,999],"Value","Index"))
    • Attached a file with the expression to create that loop master
  • This is pretty fast; I have apps with collections like this created with 5000 items, no noticeable performance impact (I even measured in a few apps, and the time it takes is usually around 10-20ms
• Once you have this (large) loop master collection, you can use the FirstN function to create a loop collection of the size that you want. For example, if you want to loop over a collection MyColl, you can use this expression:

ForAll(
    FirstN(LoopMaster, CountRows(MyColl)),
    // Code
)

• If you don't want to have a very large expression, you can trade off code length by performance. The expression below is equivalent to the one above, but in my tests it takes ~1-5 seconds to execute:

ClearCollect(LoopMaster,{Index:0}); Clear(LoopMaster);
ForAll(
    RenameColumns([0,1,2,3,4,5,6,7,8,9], "Value", "Hundreds"),
    ForAll(
        RenameColumns([0,1,2,3,4,5,6,7,8,9], "Value", "Tens"),
        ForAll(
            RenameColumns([0,1,2,3,4,5,6,7,8,9], "Value", "Ones"),
            Collect(LoopMaster, {Index: Hundreds * 100 + Tens * 10 + Ones}))))

• If you want to create your own loop, using the FirstN function will probably be faster than filtering - especially if your 'master loop' is a large expression. For example, this is equivalent to your example:

Clear(NextDates);
ForAll(
    FirstN(LoopMaster, 8),
    Collect(
        NextDates,
        {
            Date:DateAdd(DatePicker1.SelectedDate,Index,Days),
            DaysSincePickedDate:Index,
            DayOfWeek:Weekday(DateAdd(DatePicker1.SelectedDate,Index,Days))
        }))

• One more performance hint: if you're using ForAll to add elements to an empty collection using the index, you may want to look into using ClearCollect and the AddColumns function to add the elements, followed by the RemoveColumns to remove the index (if you want to do so). In my empirical testing I've found that this is 5-10 times faster than using ForAll (ForAll is not a very performant function). The example above would look like this:

ClearCollect(
    NextDates,
    DropColumns(
        AddColumns(
            FirstN(LoopMaster, 8),
            "Date", DateAdd(DatePicker1.SelectedDate,Index,Days),
            "DaysSincePickedDate", Index,
            "DayOfWeek", Weekday(DateAdd(DatePicker1.SelectedDate,Index,Days))),
        "Index"))

Hope this helps, and thank you for sharing your ideas!

[EricLott]

That's an awesome piece of syntax I didn't know about! I also didn't even think to use FirstN().
Thank you for sharing!

[Craig_Humphrey]

Hey @CarlosFigueira,

this is such a useful tip, but I can't for the life of me get the RenameColumns([0,1,2,3,4,5,6,7,8,9,...,999] to work.  I always get a squiggle under the ...  "invalid use of '.'"

Is there some preview feature I need to turn on to get this?

Thanks

Craig

[EricLott]

The '...' is just to denote numbers between 9 and 999, it's not actually part of the formula.

[Craig_Humphrey]

Seriously?  So you have to type in all 1000 numbers? (or script out somewhere else and paste in)

There has to be a better way!

[EricLott]

There are a handful of other ways, but typing it out is the fastest (to process, that is). You could setup an excel document with maxed out rows of consecutive integers and use that, which would probably be the most efficient.

[Anonymous]

You don't need 1000 "numbers" you need 1000 rows. It can be anything. I just did this:

Collect(aLongTable, [" ", " ", " ", " ",

Then copy and pasted:

" ", " ", " ", " ",

to get

Collect(aLongTable, [" ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ",

Then copy and paste 

" ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ",

etc...

Took me about 30 seconds to make a table of 1000's of rows. Now you can use this to run your ForAll on your FirstN(aLongTable, NumberOfLoopsYouWant)

It's not exactly elegant and I still don't know a great way to increment a variable inside a ForAll but I'm sure there's a workaround for that too.

[Craig_Humphrey]

Yeah, I get that, but still, YUCK!

The value of putting numbers in the rows is that you can then compare and use partial ranges to get subsets of values - smaller numbers of items.