Recently, the awesome Nathalie Leenders posted this quandary on Twitter and the Index function came to her rescue. So, I thought I’d put this article together to briefly go over some examples of using the First, Last and Index functions in Power Fx.
First, Last and Index functions retrieve a row in a table of data, which is returned as a record value. Once we have this record, we can then access a value from a specific column.
As per Microsoft’s guidance, none of these functions are delegable, so worth bearing in mind if you try to use any of these with massive data sets.
Example data
Here is a collection I’ve built for the purpose of this article:
ClearCollect(
colAlcoholicDrinks,
[
"Beer",
"Gin",
"Rum",
"Vodka",
"Wine",
"Whiskey"
]
)
Index vs First or Last
To get the first or last row from a collection/table of data, we can use either the Index or First functions.Â
In our example collection, we have 6 rows. If we used Index to get the first or last row, this would be our Power Fx syntax:
Index(colAlcoholicDrinks,1).Value
Index(colAlcoholicDrinks,6).Value
That’s fine of course, but note we are using hardcoded values of 1 and 6. What if our collection of data has a varying number of records? In this instance, the First and Last functions come into their own:
First(colAlcoholicDrinks).Value
Last(colAlcoholicDrinks).Value
As you can see, no hardcoded values to target the row. As such these are more dynamic options if the number of rows you or your users are working with is variable.
Index vs Last/FirstN
Where I personally prefer using Index is for getting values between the first and last record. Let’s say we want to get the fourth record from our collection. We can attack this in 2 ways.
Before the Index function, we would use a combination of Last and FirstN. With FirstN, we specify the number of rows from row 1 that we want to iterate through. In our example, we want to get the fourth record, so our starting point would be:
FirstN(colAlcoholicDrinks, 4)
That would get the first 4 records from our collection. We’d then embed this within a Last function to get the bottom row. This would therefore get row number 4 from the 6 available in colAlcoholicDrinks:
Last(
FirstN(
colAlcoholicDrinks,4)).Value
I love that Microsoft tools always give us more than one way to skin a cat. A more streamlined approach to this scenario uses the Index function:
Index(colAlcoholicDrinks,4).Value
Not a huge, groundbreaking moment. But I’m all for simplicity especially if you using these scenario’s with bigger blocks of Power Fx.
Using Index with galleries
The above examples are nice and easy, working with collections means the data is already saved locally. But we might have a scenario like Nathalie’s, whereby we’ve added an additional control to our gallery and want to target that value instead.
I’ve added a drop down box to the gallery that’s showing the items of colAlcoholicDrinks. The dropdown box has the following items:
["3%","5%","7%","9%","11%"]
There is currently no direct connection between the drop down and the collection, so how can still use Index to retrieve specific values?
Well, in exactly the same way – only instead of referencing the collection, we target the gallery. In the example below, I want to get the selected % volume for Rum, which is line 3:
Index(
gllDrinks.AllItems,3
).dpdAlcohoStrength.Selected.Value
We are accessing the ‘All Items’ property of the gallery, as that’s exactly what we need to retrieve first to then find the 3rd row. Once we have the row, we can select any control – then the relevant properties from it. For a drop down, we’d need the ‘selected’ option:
To retrieve outputs of other controls, you’d need to work with their specific properties. For example, if it was a text input control you were trying to target instead, the syntax would be in accordance with the available text input properties:
Index(
gllDrinks.AllItems,3
).txtInput.Text
Can you still do the same with Last/FirstN? Of course you can:
Personally though, I prefer to use the more streamlined approach where I can; it makes code easier to read and digest too, especially if others will be taking on or supporting your development later on.
Knowing when to use First, Last or Index is very useful knowledge for day-to-day Power Fx scenario’s. Hopefully the above examples of what to use and when will give you some additional depth to your Power Apps weaponry!
Hi, I can using the index fuction to a dropdown?
I want detect when is the first item.;
It would likely be better to perform the index function against the collection or data source that’s being used for the dropdown. A dropdown control will typically only allow you to get information of the selected item.
thank you