How to Filter Data table in Power Apps?

While working with a Power Apps requirement, I needed to filter a Power Apps Data table control based on various SharePoint columns. Also, it’s quite easy to achieve this.

In this Power Apps article, I will explain to you how to filter data table in Power Apps using various scenarios like:

• Filter Power Apps Data table based on SharePoint Text column
• Filter Power Apps Data table based on SharePoint Choice column
• Filter Power Apps Data table based on SharePoint Multiple columns
• Filter Power Apps Data table based on ComboBox control

How to Filter Data table in Power Apps

So, let’s proceed with this one by one.

Example-1: (Filter Power Apps Data table based on SharePoint Text Column)

We will discuss filtering Power Apps data table control based on a SharePoint Text field.

The data table will filter and display the specific item information whenever a user searches for any device name in the search box.

To work around this, follow the steps below:

• Below is a SharePoint list called Products. This list contains two columns:
  • Title = Single line of text
  • Vendor = Choice [APPLE, SAMSUNG, HP, DELL, LENOVO]

• On the Power Apps screen, insert a Text input control where users can enter or search any Title like Mobile, Laptop, etc. Rename the text input control to txtSearchBox. Also, remove the text from its Default property.
• Insert a Data table control and set its Items property as:

Items = Filter(
    Products,
    Title = txtSearchBox.Text
)

Where,

1. Products = SharePoint List name
2. Title = Specify the SharePoint field name the user will search in the text box
3. txtSearchBox = Text input control name

• Now save and publish the app. Search any title value (like Mobile) in the text box; then, the specific item details will be displayed in the data table.

Example-2: (Filter Power Apps Data table based on SharePoint Choice Column)

Next, we will discuss filtering Power Apps data table control based on a SharePoint choice column.

• As discussed in the SharePoint list [Products] above, there is a Vendor column with various choices, like APPLE, DELL, HP, LENOVO, etc.

• The data table will filter and show the specific vendor details when a user chooses a name [suppose DELL] from the dropdown menu, as shown below.

• To achieve this, insert a Power Apps Dropdown control and set its Items property as:

Items = Choices(Products.Vendor)

Where,

Vendor = SharePoint Choice field

• Next, add a Data table and apply the code below on its Items property as:

Items = Filter(
    Products,
    Dropdown1.SelectedText.Value in Vendor.Value
)

Where,

Dropdown1 = Dropdown control name

• Save, publish, and preview the app. The vendor information will be filtered and displayed in the data table once you select a name from the dropdown menu.

Example-3: (Filter Power Apps Data table based on SharePoint Multiple Columns)

We can filter the Power Apps Data table control using multiple SharePoint columns [with different data types]. To do so, check this scenario below:

• There is a SharePoint list named TSInfo Attachments. This list has these many columns below with various data types:

Column	Data type
Title	Single line of text
Attachment Types	Choice [PPT, PDF, Excel, Microsoft Document]
Attachment Costs	Currency
Attachment Created Date	Date and time

• In Power Apps, there is a Text input and Data table control. A user will search for any text/item (from the Sharepoint list) in the search box; then the data table will filter accordingly.

• To achieve this, add a Data table control and set its Items property as:

Items = Search(
    AddColumns(
        'TSInfo Attachments',
        "AttachmentCreated",
        Text(
            'Attachment Created Date',
            DateTimeFormat.ShortDate
        ),
        "AttachmentType",
        'Attachment Types'.Value,
        "Attachment Cost",
        Text('Attachment Costs')
    ),
    txtSearchBox.Text,
    "Title",
    "AttachmentType",
    "Attachment Cost",
    "AttachmentCreated"
)

Where,

1. “AttachmentCreated”, “AttachmentType”, “Attachment Cost” = New Column Names
2. ‘Attachment Created Date’ = SharePoint Date column
3. ‘Attachment Types’ = SharePoint Choice column
4. ‘Attachment Costs’ = SharePoint Currency column

This way, we can filter the Power Apps Data table control with SharePoint multiple columns.

Example-4: (Filter Power Apps Data table based on ComboBox Control)

This example will discuss filtering Power Apps Data table control based on a Combo box control.

• The image below shows a Power Apps Combo box control with some items. A user can filter and view specific selected item details in the data table by selecting multiple items from the combo box.

For this example, I have taken the above SharePoint list named Products.

• On the Power Apps screen, add a Combo box control and set its Items property as: [Make sure its Multiselect option should be enabled]

Items = Products.Title

Where,

Title = Specify the SharePoint column name the user selects from the combo box.

• Next, Insert a Data table and apply the below formula on its Items property:

Items = Filter(
    Products,
    Title in ComboBox1.SelectedItems
)

Where,

ComboBox1 = Combo box control name

• Save, publish, and preview the app. Once you select one or more items in the combo box, the data table control will display the filtered result.

How to filter a Power Apps data table by dropdown (All option and multiple dropdowns)

To filter by a dropdown and still let users see every record, add an “All” item to the dropdown and wrap your Filter in an If. For several dropdowns, pass one condition per dropdown to Filter; the commas work like And.

Add an All option to the dropdown

This example uses an Employee Onboarding list with a Choice column called Employee Department. Build the dropdown values in the screen’s OnVisible property:

ClearCollect(colEmployee, {Value: "All"});
Collect(colEmployee, Distinct('Employee Onboarding', 'Employee Department'.Value))

Set the dropdown’s Items to colEmployee, then set the data table’s Items property to:

If(
    drp_Department.Selected.Value = "All",
    'Employee Onboarding',
    Filter('Employee Onboarding', 'Employee Department'.Value = drp_Department.Selected.Value)
)

Distinct returns a one-column table named Value, and it isn’t delegable, so it only reads the first batch of rows (500 by default). For a short, fixed list of departments, you can type the values instead.

Filter by multiple dropdowns

With an Expense Tracker list, add three dropdowns whose Items are Distinct('Expense Tracker', Month.Value), Distinct('Expense Tracker', 'Expense Type'.Value), and Distinct('Expense Tracker', Budget.Value). Then set the data table’s Items to:

Filter(
    'Expense Tracker',
    Month.Value = drp_Month.Selected.Value,
    'Expense Type'.Value = 'drp_Expense Type'.Selected.Value,
    Budget.Value = drp_Budget.Selected.Value
)

Want any dropdown to be optional? Set its AllowEmptySelection property to true and write that condition as IsBlank(drp_Month.Selected) || Month.Value = drp_Month.Selected.Value.

Combine a dropdown with a search box

Using the Products list from earlier, add one more condition for the text input. Only rows that match the vendor and start with the typed title stay in the table:

Filter(
    Products,
    Vendor.Value = Dropdown1.Selected.Value,
    StartsWith(Title, txtSearchBox.Text)
)

How to filter a Power Apps data table using a checkbox

Compare a Yes/No column to the checkbox’s Value property. With a Travel Requests list and a Yes/No column named Approved, set the data table’s Items to:

Filter('Travel Requests', Approved = chk_Approved.Value)

Checked shows only approved trips, and unchecked shows only the ones that aren’t approved. If you’d rather show every trip until the box is checked, use this instead:

If(chk_Approved.Value, Filter('Travel Requests', Approved = true), 'Travel Requests')

Filter with multiple checkboxes

For a status filter, add one checkbox per status and pair each one with its value. Users can tick one, two, or all three boxes, and you don’t need any variables or Reset calls:

Filter(
    colProjects,
    (chkNotStarted.Value && Status.Value = "Not Started") ||
    (chkInProgress.Value && Status.Value = "In Progress") ||
    (chkCompleted.Value && Status.Value = "Completed")
)

Leave all three boxes unchecked and the table is empty, so set the Not Started checkbox’s Default to true if you want rows on screen when the app opens.

Conclusion

Now I hope you got some clarity on how to filter a Power Apps Data table control based on various scenarios like:

• How to filter Power Apps Data table based on SharePoint Text field
• Filtering Power Apps Data table based on SharePoint Choice field
• Filter Power Apps Data table based on SharePoint multiple columns
• Power Apps filter Data table based on ComboBox control

Also, you may like some more Power Apps tutorials:

• Power Apps Data Table URL
• Power Apps Data Table Conditional Formatting
• Add a Blank Value to a Dropdown List in Power App
• Power Apps Lookup SharePoint List