Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (2023)

  • Article
  • 9 minutes to read

Finds one or more records in a table.

Watch this video to learn how to use Filter, Search and LookUp functions:

Description

The Filter function finds records in a table that satisfy a formula. Use Filter to find a set of records that match one or more criteria and to discard those that don't.

The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.

For both, the formula is evaluated for each record of the table. Records that result in true are included in the result. Besides the normal formula operators, you can use the in and exactin operators for substring matches.

Fields of the record currently being processed are available within the formula. Use the ThisRecord operator or simply reference fields by name as you would any other value. The As operator can also be used to name the record being processed which can help make your formula easier to understand and make nested records accessible. For more information, see the examples below and working with record scope.

The Search function finds records in a table that contain a string in one of their columns. The string may occur anywhere within the column; for example, searching for "rob" or "bert" would find a match in a column that contains "Robert". Searching is case-insensitive. Unlike Filter and LookUp, the Search function uses a single string to match instead of a formula.

(Video) Working with Search, Filters and Lookup PowerFX in Canvas Apps Power Apps

Filter and Search return a table that contains the same columns as the original table and the records that match the criteria. LookUp returns only the first record found, after applying a formula to reduce the record to a single value. If no records are found, Filter and Search return an empty table, and LookUp returns blank.

Tables are a value in Power Apps, just like a string or number. They can be passed to and returned from functions. Filter, Search, and LookUp don't modify a table. Instead, they take a table as an argument and return a table, a record, or a single value from it. See working with tables for more details.

Delegation

When possible, Power Apps will delegate filter and sort operations to the data source and page through the results on demand. For example, when you start an app that shows a Gallery control filled with data, only the first set of records will be initially brought to the device. As the user scrolls, additional data is brought down from the data source. The result is a faster start time for the app and access to very large data sets.

However, delegation may not always be possible. Data sources vary on what functions and operators they support with delegation. If complete delegation of a formula isn't possible, the authoring environment will flag the portion that can't be delegated with a warning. When possible, consider changing the formula to avoid functions and operators that can't be delegated. The delegation list details which data sources and operations can be delegated.

If delegation is not possible, Power Apps will pull down only a small set of records to work on locally. Filter and sort functions will operate on a reduced set of records. What is available in the Gallery may not be the complete story, which could be confusing to users.

See the delegation overview for more information.

Syntax

Filter(Table*, Formula1 [, *Formula2*, ... ] )

  • Table - Required. Table to search.
  • Formula(s) - Required. The formula by which each record of the table is evaluated. The function returns all records that result in true. You can reference columns within the table. If you supply more than one formula, the results of all formulas are combined with the And function.

Search(Table*, SearchString, Column1 [, *Column2*, ... ] )

  • Table - Required. Table to search.
  • SearchString - Required. The string to search for. If blank or an empty string, all records are returned.
  • Column(s) - Required. The names of columns within Table to search. Columns to search must contain text. Column names must be strings and enclosed in double quotes. However, the column names must be static and cannot be calculated with a formula. If SearchString is found within the data of any of these columns as a partial match, the full record will be returned.

Note

For SharePoint and Excel data sources that contain column names with spaces, specify each space as "_x0020_". For example, specify "Column Name" as "Column_x0020_Name".

(Video) Power Apps Search and Filter Functions on Large SharePoint Lists

LookUp(Table*, Formula [, *ReductionFormula* ] )

  • Table - Required. Table to search. In the UI, the syntax is shown as source above the function box.
  • Formula - Required.The formula by which each record of the table is evaluated. The function returns the first record that results in true. You can reference columns within the table. In the UI, the syntax is shown as condition above the function box.
  • ReductionFormula - Optional. This formula is evaluated over the record that was found, and then reduces the record to a single value. You can reference columns within the table. If you don't use this parameter, the function returns the full record from the table. In the UI, the syntax is shown as result above the function box.

Examples

The following examples use the IceCream data source:

Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (1)

FormulaDescriptionResult
Filter(IceCream, OnOrder > 0)Returns records where OnOrder is greater than zero.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (2)
Filter(IceCream, Quantity + OnOrder > 225)Returns records where the sum of Quantity and OnOrder columns is greater than 225.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (3)
Filter(IceCream, "chocolate" in Lower(Flavor ))Returns records where the word "chocolate" appears in the Flavor name, independent of uppercase or lowercase letters.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (4)
Filter(IceCream, Quantity < 10 && OnOrder < 20)Returns records where the Quantity is less than 10 and OnOrder is less than 20. No records match these criteria, so an empty table is returned.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (5)
Search(IceCream, "choc", "Flavor")Returns records where the string "choc" appears in the Flavor name, independent of uppercase or lowercase letters.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (6)
Search(IceCream, "", "Flavor")Because the search term is empty, all records are returned.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (7)
LookUp(IceCream, Flavor = "Chocolate", Quantity)Searches for a record with Flavor equal to "Chocolate", of which there is one. For the first record that's found, returns the Quantity of that record.100
LookUp(IceCream, Quantity > 150, Quantity + OnOrder)Searches for a record with Quantity greater than 150, of which there are multiple. For the first record that's found, which is "Vanilla" Flavor, returns the sum of Quantity and OnOrder columns.250
LookUp(IceCream, Flavor = "Pistachio", OnOrder)Searches for a record with Flavor equal to "Pistachio", of which there are none. Because none is found, Lookup returns blank.blank
LookUp(IceCream, Flavor = "Vanilla")Searches for a record with Flavor equal to "Vanilla", of which there is one. Since no reduction formula was supplied, the entire record is returned.{ Flavor: "Vanilla", Quantity: 200, OnOrder: 75 }

Filtering with choice columns

The following example uses the Account table in Microsoft Dataverse as data source. This example shows how to Filter list of accounts based on selected Combo box control values:

Step by step

  1. Open a blank app.

  2. Add a new screen by selecting the New Screen option.

  3. On the Insert tab, select Gallery and then select Vertical.

    (Video) Learn to use the PowerApps Filter function

  4. On the Properties tab of the right-hand pane, open Data Source and then select Accounts.

  5. (Optional) In the Layout list, select different options.

  6. On the Insert tab, select Input and then select Combo box. Repeat the step to add two more combo box controls.

  7. For each combo box control, on the Properties tab of the right-hand pane, open Data Source and then select Accounts. Select Edit next to Fields option and then select the Primary text and SearchField values. The Primary text should be the choices column you want to add to the combo box. Repeat the step for other two combo box controls.

    Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (8)

  8. Now select Gallery control and set the Items property to the following formula:

    Filter(Accounts, 'Industry' = ComboBox3.Selected.Industry||IsBlank(ComboBox3.Selected.Industry), 'Relationship Type' = ComboBox2.Selected.'Relationship Type'|| IsBlank(ComboBox2.Selected.'Relationship Type'), 'Preferred Method of Contact' = ComboBox1.Selected.'Preferred Method of Contact'|| IsBlank(ComboBox1.Selected.'Preferred Method of Contact'))

    Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (9)

Search user experience

The following examples use the IceCream data source:

In many apps, you can type one or more characters into a search box to filter a list of records in a large data set. As you type, the list shows only those records that match the search criteria.

(Video) Power Apps Search and Filter Function with SharePoint + Workarounds

The examples in the rest of this topic show the results of searching a list, named Customers, that contain this data:

Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (10)

To create this data source as a collection, create a Button control and set its OnSelect property to this formula:

ClearCollect(Customers, Table({ Name: "Fred Garcia", Company: "Northwind Traders" }, { Name: "Cole Miller", Company: "Contoso" }, { Name: "Glenda Johnson", Company: "Contoso" }, { Name: "Mike Collins", Company: "Adventure Works" }, { Name: "Colleen Jones", Company: "Adventure Works" }) )

As in this example, you can show a list of records in a Gallery control at the bottom of a screen. Near the top of the screen, you can add a Text input control, named SearchInput, so that users can specify which records interest them.

Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (11)

As the user types characters in SearchInput, the results in the gallery are automatically filtered. In this case, the gallery is configured to show records for which the name of the customer (not the name of the company) starts with the sequence of characters in SearchInput. If the user types co in the search box, the gallery shows these results:

Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (12)

To filter based on the Name column, set the Items property of the gallery control to one of these formulas:

(Video) PowerApps Search Function

FormulaDescriptionResult
Filter(Customers, StartsWith(Name, SearchInput.Text) )Filters the Customers data source for records in which the search string appears at the start of the Name column. The test is case insensitive. If the user types co in the search box, the gallery shows Colleen Jones and Cole Miller. The gallery doesn't show Mike Collins because the Name column for that record doesn't start with the search string.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (13)
Filter(Customers, SearchInput.Text in Name)Filters the Customers data source for records in which the search string appears anywhere in the Name column. The test is case insensitive. If the user types co in the search box, the gallery shows Colleen Jones, Cole Miller, and Mike Collins because the search string appears somewhere in the Name column of all of those records.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (14)
Search(Customers, SearchInput.Text, "Name")Similar to using the in operator, the Search function searches for a match anywhere within the Name column of each record. You must enclose the column name in double quotation marks.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (15)

You can expand your search to include the Company column and the Name column:

FormulaDescriptionResult
Filter(Customers, StartsWith(Name, SearchInput.Text) || StartsWith(Company, SearchInput.Text) )Filters the Customers data source for records in which either the Name column or the Company column starts with the search string (for example, co). The || operator is true if either StartsWith function is true.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (16)
Filter(Customers, SearchInput.Text in Name || SearchInput. Text in Company)Filters the Customers data source for records in which either the Name column or the Company column contains the search string (for example, co) anywhere within it.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (17)
Search(Customers, SearchInput.Text, "Name", "Company")Similar to using the in operator, the Search function searches the Customers data source for records in which either the Name column or the Company column contains the search string (for example, co) anywhere within it. The Search function is easier to read and write than Filter if you want to specify multiple columns and multiple in operators. You must enclose the names of the columns in double quotation marks.Filter, Search, and LookUp functions in Power Apps (contains video) - Power Platform (18)

FAQs

How do I filter a lookup field in Power Apps? ›

How to Filter LookUp Column in Power Apps
  1. Brands(id, name, category(lookup column))
  2. Category(id, name)
  3. Filter(brandsList,(drop_Category.Selected.ID) in CategoryId.Value)
  4. Filter(brandsList, CategoryId.Value = (drop_Category.Selected.ID))
  5. Filter(brandsList, 'Category: Id'= (drop_Category.Selected.ID))
Jun 8, 2022

What is filter function in Power Apps? ›

PowerApps Filter Function

Definition. The Filter function in Power Apps used to find the records of the table that adhere to a formula. You can use Filter Function to find a set of records that meet the criteria so If the condition you apply is met, so the records will be displayed; otherwise, they are discarded.

How do I find the lookup value in Power Apps? ›

LookUp function in PowerApps finds the first record in a table that matches a formula. LookUp can be used to find a single record that matches one or more criteria. If the Lookup function matches the formula, either it returns single row from the table or a single value.

How do I filter a lookup field? ›

In the Lookup Filter section, click Show Filter Settings. to select the Field, and fill in the filter details. Ensure Required is selected next to Filter Type. Leave everything else as is and click Save.

How do I set a lookup filter? ›

Create a custom field with a lookup filter.
  1. From Setup, click the Object Manager tab.
  2. Click Case, then click Fields & Relationships.
  3. Click Contact Name, then click Edit.
  4. In the Lookup Filter section, click Show Filter Settings.
  5. Click the lookup icon. ...
  6. Choose equals as the operator.
  7. In the Value/Field menu, select Field.

What is use of lookup () method? ›

Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column. For example, let's say you know the part number for an auto part, but you don't know the price.

How do you do a power query lookup? ›

You can use POWER QUERY to match two-column and get values (By using Merge Option).
...
Steps to Perform VLOOKUP with Power Query
  1. Create queries (connections) for the both of the tables.
  2. Choose the column which is common in both of the tables.
  3. Merge them and get the column you want.

What is the function of lookup tab? ›

Lookup tables provide a way to add extra information in an event. A lookup table consists of a list of keys and values. You define a lookup table using the table function, and access the table using the lookup function. You can create a lookup table in the rules file or in a separate file.

What are the types of filters available in power? ›

Compare filter types
Filter typeEditSort
Include/Exclude filtersNY
Drill-down filtersYN
Cross-drill filtersNN
Drillthrough filters (Invokes drillthrough)YN
5 more rows
Nov 30, 2022

How does filter function work? ›

The FILTER function in Excel is used to filter a range of data based on the criteria that you specify. The function belongs to the category of Dynamic Arrays functions. The result is an array of values that automatically spills into a range of cells, starting from the cell where you enter a formula.

What is filter and its function? ›

A filter is a circuit whose transfer function, that is the ratio of its output to its input, depends upon frequency. There are three broad categories of filter which are widely used: Low-pass filters allow any input at a frequency below a characteristic frequency to pass to its output unattenuated or even amplified.

Is LookUp faster than first in Power Apps? ›

We can call the LookUp function, or use a combination of the First and Filter functions. The First/Filter pattern is more inefficient because Power Apps can potentially retrieve up to 2,000 rows when we just want to take a single record. This unnecessary data transfer will reduce the performance of an app..

Can we have multiple lookups in Power Apps? ›

With multi-table lookups, you can perform a lookup on 2 or more tables at the same time and locate the record you want from the referenced tables.

What should I include in LookUp value? ›

Lookup_value can be a value or a reference to a cell. The range of cells in which the VLOOKUP will search for the lookup_value and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references. The first column in the cell range must contain the lookup_value.

How do I create a lookup field in power app? ›

Add a lookup column (Many-to-one relationship)
  1. On powerapps.com, expand the Data section, and then select Tables on the left navigation pane.
  2. Select an existing table, or Create a new table.
  3. Select the Relationships area.
  4. Select Add relationship, and then select a relationship type, such as Many-to-one.
Jun 22, 2022

What is a lookup filter? ›

Lookup filters are administrator settings that restrict the valid values and lookup dialog results for lookup, master-detail, and hierarchical relationship fields.

What is a lookup field example? ›

A lookup field can display a user friendly value that is bound to another value in the source data table. For example, you want to record the customer for an order in the Orders table. However, all customer information is tracked in the Customers table.

What are the two types of lookups? ›

There are two forms of Lookup: Vector and Array. The vector form of the LOOKUP function will search one row or one column of data for a specified value and then get the data from the same position in another row or column.

What is the difference between lookup and filter? ›

Use Filter to find a set of records that match one or more criteria and to discard those that don't. Lookup function in Microsoft POWER APPS :The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.

What is possible using lookup filters on an object? ›

Salesforce Lookup Filter limits which records can be associated within an object relationship. It can be applied to Lookup, Master Detail, and Hierarchical Relationship Fields.

Which function is best used to lookup? ›

VLOOKUP Function
  • It looks down the left column of a range, to find a value.
  • Then, it returns a value from another column in the range, from the row where the matching value was found.
Dec 8, 2022

Why do we use lookup transformation? ›

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.

What is the return type of lookup () method? ›

The parameter types of the method handle will be those of the constructor, while the return type will be a reference to the constructor's class. The constructor and all its argument types must be accessible to the lookup class.

Is Power Query better than VLookup? ›

Power Query can easily overcome these challenges of VLookup and offers much more flexibility in data sourcing/extraction. I would describe more Power Query features in my future articles but lets focus here on performing VLookup using Power Query! VLookup is essentially merging of data from two Tables/Sheets.

What is the alternative to VLookup in Power Query? ›

The LOOKUPVALUE function is a VLOOKUP function to fetch the data from other tables in Power BI. The “Power Query” “Merge” option is the alternative way of fetching data from different tables. The LOOKUPVALUE function is a DAX function.

Can we do VLookup in Power Query? ›

VLOOKUPs in Power Query are joins between 2 tables. If you've been using Power Query for a while, I'm pretty sure that you know about that. With all that said, let's go check out VLOOKUP in Power Query.

What are the types of lookup function? ›

There are two forms of LOOKUP in Excel: Vector and Array.

What is lookup and types of lookup? ›

There are two forms of Lookup: Vector and Array. The vector form of the LOOKUP function will search one row or one column of data for a specified value and then get the data from the same position in another row or column.

What is lookup and reference function? ›

LOOKUP function. Looks up values in a vector or array. MATCH function. Looks up values in a reference or array.

What are 3 types of filters? ›

Four Major Types of Filters

The four primary types of filters include the low-pass filter, the high-pass filter, the band-pass filter, and the notch filter (or the band-reject or band-stop filter).

What are the 5 types of filters? ›

The 5 Types of Filters
  • Mechanical Filters.
  • Absorption Filters.
  • Sequestration Filters.
  • Ion Exchange Filters.
  • Reverse Osmosis Filters.

How does a filter work with data? ›

Data filtering is the process of choosing a smaller part of your data set and using that subset for viewing or analysis. Filtering is generally (but not always) temporary – the complete data set is kept, but only part of it is used for the calculation.

Why is filtering is important? ›

Filtration is extremely important to keep things like water, chemicals, and pharmaceuticals clean, pure and free of contaminants. If it wasn't for filtration, we might not have safe drinking water, because it plays a crucial role in eliminating sediment, sand, gravel, carbon and other suspended particles.

What is an example of a filter? ›

Filtration Examples

The most common example is making tea. While preparing tea, a filter or a sieve is used to separate tea leaves from the water. Through the sieve pores, only water will pass. The liquid which has obtained after filtration is called the filtrate; in this case, water is the filtrate.

What is lookup filter option where it is available? ›

Lookup filters are administrator settings that restrict the valid values and lookup dialog results for lookup, master-detail, and hierarchical relationship fields.

How do you filter a lookup field in dynamics? ›

Go to Settings–>Customizations–>Customize the System, then expand the “Entities” section, and navigate to the Opportunity entity. Select “Forms” to open up the form you want the lookup field filtering to apply to: 2. Double click the Lookup field you want to apply the related record filter to.

How do I filter a lookup column in SharePoint? ›

yes it is possible to apply a filter on the lookup column to filter the SharePoint list items,
  1. Go to the SharePoint Online list and select the lookup column for which you want to apply the filter.
  2. Expand the Lookup column and Select the Filter by option.
May 19, 2022

What is Filter lookup access? ›

Filtering is a useful way to see only the data that you want displayed in Access databases. You can use filters to display specific records in a form, report, query, or datasheet, or to print only certain records from a report, table, or query.

Why filters are used in search? ›

The use of a search filter can save time and effort, in both the creation of the overall search strategy and in significantly reducing the number of records that researchers need to screen.

What are the search filters? ›

Search filters are pre-tested strategies that identify the higher quality evidence from the vast amounts of literature indexed in the major medical databases.

What is a benefit of using a lookup filter instead of a validation rule? ›

Validation rules and lookup filters achieve similar ends, but offer different advantages. Use a lookup filter: To improve user efficiency by limiting the number of available options in a lookup search dialog. To improve user efficiency by automating filters on lookup search dialogs that your users manually set.

How do you use a lookup field? ›

Create a lookup field
  1. In the Access desktop program, open the table in Design view.
  2. In the first empty row in the list of fields, type a name for the new lookup field and choose Lookup in the Data Type column. ...
  3. Click I want the lookup field to get the values from another table or query.

Can we use lookup field in workflow? ›

its not possible in the workflow but we can done this by using the trigger but here am using Process builder to acheive this because it is a cross refernce object i.e, contact is a lookup field in the Account. The below steps are i did to achieve my Task.

How do I create a lookup column in a list? ›

In the list where you want the Lookup column, select Add column > Lookup. Under Select list as a source, select the source list to get information from. Under Select a column from the list above, select what information you want to display from the source list in this new column in the target list.

How do I apply a filter to a specific column? ›

Filter for a specific number or a number range

Click a cell in the range or table that you want to filter. On the Data tab, click Filter. in the column that contains the content that you want to filter. Under Filter, click Choose One, and then enter your filter criteria.

What is the purpose of a lookup check? ›

The Lookup Check processor allows you to check for records in a set of Reference Data that are related to those that you are currently working with, for example, data from another table in a relational database, or related data in a separate system.

How do you create a lookup? ›

Create a lookup field in Design View

In the first available empty row, click a cell in the Field Name column, and then type a field name for the lookup field. Click in the Data Type column for that row, click the arrow and then, in the drop-down list, select Lookup Wizard.

Videos

1. Power Apps LookUp Function
(Shane Young)
2. Filter, LookUp, and Search Functions In PowerApps | Delegation Function in powerapps
(InfoTech Nest)
3. How To Use Lookup Columns In Power Apps
(Daniel Christian)
4. Noobie Learning PowerApps E05: Filter, Search, LookUp with a bit of Delegation
(MS Power Platform Tutorials)
5. Filter and Search Records in Power Apps
(Dhruvin Shah)
6. PowerApps Multiple Filters on Gallery
(Reza Dorrani)
Top Articles
Latest Posts
Article information

Author: Kieth Sipes

Last Updated: 02/16/2023

Views: 6551

Rating: 4.7 / 5 (67 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Kieth Sipes

Birthday: 2001-04-14

Address: Suite 492 62479 Champlin Loop, South Catrice, MS 57271

Phone: +9663362133320

Job: District Sales Analyst

Hobby: Digital arts, Dance, Ghost hunting, Worldbuilding, Kayaking, Table tennis, 3D printing

Introduction: My name is Kieth Sipes, I am a zany, rich, courageous, powerful, faithful, jolly, excited person who loves writing and wants to share my knowledge and understanding with you.