Power FX is the LowCode language of the Power Platform, originally only in Canvas Apps it is not extending to Dataverse and Power Virtual Agents. Yet because it's based on expressions (like Excel and Power Automate) it is not directly comparable to something like VBA or Python. Like Excel, Power FX is at a base level entwined with components (Excel formulas/functions are entwined with cells/worksheets etc), this means core functionality can be outsourced to components (like timers/delays).
One of the most interesting aspects of Power FX is how it handles loops. Out of the box other expression-based languages don't have loops (Excel none and Power Automate requires actions). Power FX does have a Loop (ForAll), but it is not quite the same as other languages loops. To cover for some of its omissions is the timer component, which due to its repeatability can also be treated as a loop.
ForAll
The ForAll function is most comparable to a ForEach loop, as it requires an array/collection to increment over. There are a couple of key things to be aware of:
- You can't set variables within it
- It Can't update itself (you can't Patch the collection you are looping over)
- The Loop has to complete (no break / stop)
- Always steps one (e.g. can't increment in 2s)
- There is no built-in index
- All internal items are referenced as ThisRecord (making loops within loops hard to reference the correct loop)
If you wish to skip standard uses for the more interesting ones click here
ForAll - Standard Use
So a basic ForAll would look like this:
ClearCollect(array,[1,2,3,5,8,13,21]);
ForAll(array,
Notify(ThisRecord.Value)
)
But as you can see its pretty pointless to use it like this (as there is no delay so you will only see the last notify).
A more useful use would be to create a new collection:
ClearCollect(array,[1,2,3,5,8,13,21]);
ClearCollect(array2,
ForAll(array,
{Value:Value+1}
)
);
In above we create a collection of 2,3,4,6,9,14,22.
We can also do this on Object collections:
ClearCollect(array,[
{fib:1,word:"one",id:1},
{fib:2,word:"two",id:2},
{fib:3,word:"three",id:3},
{fib:5,word:"five",id:4},
{fib:8,word:"eight",id:5},
{fib:13,word:"thirteen",id:6},
{fib:21,word:"twenty on",id:7}
]);
ClearCollect(array2,
ForAll(array,
{Value:Left(word,2)}
)
);
Result
Though quick tip you can use certain functions on collections too, removing the need for a ForAll.
ClearCollect(array2,Left(array.word,2));
The above will give the same results as the ForAll above.
The other standard use it to update another collection with a value from the first collection:
ClearCollect(Objects,
{a:1,b:"A",c:true,ID:1},
{a:11,b:"B",c:false,ID:2},
{a:-11,b:"C",c:false,ID:3},
{a:7,b:"D",c:true,ID:4}
);
ForAll( Objects,
Patch(variables,{ID:ThisRecord.ID},
{LetterNo:ThisRecord.b&ThisRecord.ID}
);
);
Although another tip is to use Drop/Add/RenameColumns to do the same:
ClearCollect(varaibles2,
DropColumns(
AddColumns(Objects,"Letter",b&ID)
,
"a","b","c"
)
)
ForAll - Non-Standard Use
What do I mean by non-standard use, well it's a way to do bypass some of the limitations we mentioned.
Can't Set variables
You may not be able to set variables, but you can update arrays/collections. And a single row collection is pretty much an object, and an object is a group of variables (can you can see where I'm going). So all we need to do is create a collection with a single row with fields you want as variables. Then in the ForAll use an If to Patch the collection values.
ClearCollect(Objects,
{a:1,b:"A",c:true,ID:1},
{a:11,b:"B",c:false,ID:2},
{a:-11,b:"C",c:false,ID:3},
{a:7,b:"D",c:true,ID:4}
);
ClearCollect(variables3,
{ID:1,Field_b:"",
Field_c:false,
Field_a:0}
);
ForAll(Objects,
If(ThisRecord.b="B",
Patch(variables3,{ID:1},
{Field_b:ThisRecord.b}
)
);
If(ThisRecord.c=true,
Patch(variables3,{ID:1},
{Field_c:ThisRecord.c}
)
);
If(ThisRecord.a=-11,
Patch(variables3,{ID:1},
{Field_a:ThisRecord.a}
)
)
)
It Can't update itself
Building on setting variables, all we need to do is use the Select() function, as here we can add the Patch to another button, which when OnSelected uses the variables/collection we have created.
ClearCollect(Objects,
{a:1,b:"A",c:true,ID:1},
{a:11,b:"B",c:false,ID:2},
{a:-11,b:"C",c:false,ID:3},
{a:7,b:"D",c:true,ID:4}
);
ClearCollect(variables4,
{ID:1,Set:0}
);
ForAll(Objects,
If(ThisRecord.b="B",
Patch(variables4,{ID:1},
{Set:ThisRecord.a}
);
Select(buUpdateSelf);
);
)
buUpdateSelf
Patch(Objects,{ID:Index(variables4,1).ID},{
a: Index(variables4,1).Set
}
)
If you are wondering about infinite
loops, sadly you can not create them. I tried changing the button to adding a new row, but looks like the ForAll caches the collection, so any changes happen after it is complete).
buUpdateSelf
Patch(Objects,Defaults(Objects),{
a: Index(variables4,1).Set,
b:"B",
ID:Max(Objects,ID+1),
c:false
}
)
The Loop has to complete & Always Steps one
With the help of an If this is quite easy to overcome:
Stop at 2
ClearCollect(Objects,
{a:1,b:"A",c:true,ID:1},
{a:11,b:"B",c:false,ID:2},
{a:-11,b:"C",c:false,ID:3},
{a:7,b:"D",c:true,ID:4}
);
ForAll( Objects,
If(ThisRecord.ID<3,
Collect(variable,ThisRecord);
);
);
Step 2
ForAll( Objects,
If(Mod(ThisRecord.ID,2)=0,
Collect(variable,ThisRecord);
);
);
One last cool tip is the Index() function, as this allows us to reference other records in the collection. The below gets one from previous, one from current, and one from next:
ClearCollect(Objects,
{a:1,b:"A",c:true,ID:1},
{a:11,b:"B",c:false,ID:2},
{a:-11,b:"C",c:false,ID:3},
{a:7,b:"D",c:true,ID:4}
);
ClearCollect(Objects2,
ForAll(Objects,
Switch(ID,
1,{a:a,b:b,c:Index(Objects,2).c,ID:ID},
Max(Objects,ID),{a:Index(Objects,ID-1).a,b:b,c:c,ID:ID},
{a: Index(Objects,ID-1).a,b:b,c:Index(Objects,ID+1).c,ID:ID}
)
)
)
Timers
The first thought of a timer is it's a delayed action. But because it has the option to repeat it can also be used as a loop. What value does it add over a ForAll, well:
- Its async (non-blocking, so the user can continue interacting with the app)
- It can include a delay
- It does not require an array
- You can step any amount
- You can stop/restart the loop
- Any function can be called in the loop
And what about the negatives:
- Its slow (max speed is 1 millisecond)
- You have to write more code
As you can see, as long as speed isn't critical then a timer is generally the best loop to use.
To turn a timer into a loop you need to set following parameters (vbTimer is just a Boolean variable).
Repeat true
Reset vbTimer
Start vbTimer
Duration 1 (or higher if you want a longer delay)
OnTimerStart or OnTimerEnd YourCode
The following code is an example of a simple ForLoop
StartButton
Set(viCounter,0);
Set(viLimit,100);
Set(vbTimer,true);
OnTimerEnd
If(viCounter<viLimit,
Notify(viCounter);
viCounter=viCounter+1;
,
Set(vbTimer,false);
)
If you want to update items of a collection, you can use the Patch & Index
Set(viCounter,1);
Set(viLimit,CountRows(Objects)+1);
Set(vbTimer,true);
OnTimerEnd
If(viCounter<viLimit,
viCounter=viCounter+1;
Patch(Objects,{ID:viCounter},
{a: Index(Objects,viCounter).a+viCounter}
)
,
Set(vbTimer,false)
)
You can also use it to find a value and then break:
If(viCounter>viLimit,Set(viCounter,1),Set(viCounter,viCounter+1));
If(Index(Objects,viCounter).b="D",
Set(viFind,Index(Objects,viCounter).ID);
Set(vbTimer,false);
)
What's useful is the collection isn't being cached, so you can update the collection and it will be shown in the loop. In the above example the loop will check every value, if it can't find it, then the loop resets and tries every item again. As soon as the collection is updated then it will be read and stop the loop if it matches the condition.
Additionally it can be a straight DoWhile loop:
If(Not(vbTimer),Set(viEndValue,viCounter));
Set(viCounter,viCounter+1);
Although loops can be unintuitive in PowerFX, the full range of functionality is there, it just takes a little out of the box thinking.
Further Reading
Top comments (2)
Great collection of tips and tricks!!!
Btw, you can do Forall(collection as myCol... to avoid the confusion with ThisRecord in nested loops.
Great call out, something I only just discovered and a total life saver