In this article, we are going to understand the Power Apps Lookup function, how and where you can use it, what are the limitations, and some examples related to the Lookup function.
Note:
- The Lookup function only returns the first record from the source database/table after it matches the filter criteria.
- If the Lookup function matches the formula, either it returns single row from the table or a single value.
- If no record is found, Power apps lookup function returns blank
- Lookup is not case-sensitive, so if the string provided in formula matches then it will return the result even if it doesn’t match the case of the value.
Power Apps Lookup Function Syntax
LookUp(Table, Formula, ReductionFormula)
- Table: This is required. This is the source DB where the search is performed.
- Formula: This is The formula to be applied on the source DB/Table and it will return a single record, rather the first record which matches the formula.
- ReductionFormula: This is Optional. If you specify any column name, the Lookup function will return the value with that column only, i.e. single value. If you don’t specify anything, the function will return a single row as a return value.
Power Apps Lookup Function Examples
We will use the below table for our examples.
Table Name: Weather
PowerApps lookup function single condition
- Even if the condition satisfies the multiple records, it will pick only the first record, and it will provide the value of the column specified.
- Outlook is the column name.
LookUp(Weather, Humidity > 40 , Outlook)
Output: Sunny
PowerApps lookup function multiple conditions
- You can also provide multiple conditions in LookUp function and narrow down the output.
LookUp(Weather, Humidity < 40 && Temperature > 80, Outlook)
Output: Overcast
PowerApps lookup function without column
- As specified in the syntax section above, the third parameter is optional in the LookUp function.
- The third option is the column name of the Table/Source provided in the first column.
- When you don’t provide the column name, the output will be single DataRow.
- The advantage over here is that you can use any column from the table unlike when you provide column name, you can use only that column value.
LookUp(Weather, Temperature = 70)
Output:
You get the entire row in the result. You can directly assign this to a DataTable in PowerApps.
PowerApps lookup max value
- In this example, we will see how we can use Max function inside the PowerApps LookUp function.
- If we check the Formula part, it is comparing the column with the Maximum value of that column.
- Here, the maximum value of the Humidity is 77, so it will pick that Row and display the Outlook column value.
LookUp(Weathers, Humidity = Max(Humidity), Outlook)
Output: Sunny
PowerApps lookup last record
- If there are multiple records that match the filter, then the LookUp function will pick the first record only.
- But what if you want to pick the last record of the result table?
- Here, we have used Sort function along with LookUp.
- LOGIC:
- We are simply going to reverse the order of the table rows.
- So, if the table is sorted by XYZ column in ascending order, we are just going to apply a descending sort on the same column (XYZ column)
- That way our last row becomes the first and we pick that row using LookUp function. Simple!!
- Sort function takes
- Datasource (First argument - Weathers)
- Expression (Second argument – Id/Modified Date - - Pick the column on which you want to apply Sorting)
- Order (Third argument – Ascending or Descending)
LookUp(Sort(Weathers, ModifiedDate, Ascending), Humidity <> 0, Outlook)
Output:
- If we had not used sorting, then on above table output would be – Sunny
- But as we reversed the order of the table, the output will be – Overcast
Let us know if you have any difficulties using the LookUp function, we will try to cover them here.