Filtering and searching data

1. Filtering and searching data

Lists and other datasets can have hundreds of rows. A gallery that shows all of them is unusable. This video is about cutting through that with a dropdown filter, a search box, and two Power Fx functions.

2. Filter()

The function for narrowing a list is Filter. It takes two arguments: the source you're filtering and a condition each row must match. Put it in a gallery's Items property and the gallery shows only the rows that match. In our example, filtering the Tasks collection where Status equals In progress narrows the list to just the active tasks. One thing to know for later: on a SharePoint choice column you'd write Status dot Value instead, because the column stores a record rather than plain text.

3. Wire it to a dropdown

Hard-coding the status defeats the point. Wire it to a Dropdown so the user picks instead. You give the Dropdown its list of status options, then point the gallery's filter at whatever the user has selected, the way the slide shows. Now picking a value from the Dropdown changes which rows the gallery shows, live, with no save and no preview. That reactive recalculation is what makes Power Fx feel like Excel.

4. StartsWith versus Search

Power Fx has two search functions. Search matches substrings anywhere in a column, case-insensitively and with no anchoring. It feels obvious for a search box. The catch is that Search is non-delegable on most connectors, including SharePoint. That means Power Apps can't push the filter down to the data source, so it pulls every row to your device first and filters locally. That's fine on twenty rows and broken on twenty thousand. StartsWith delegates cleanly. It only matches from the beginning of a string, but for almost every search box in a real app, that's what users want anyway. Prefer StartsWith whenever delegation matters.

5. The delegation warning

A blue squiggly underline in the formula bar is the delegation warning, and it means only the first 500 rows, the default page size, come back to your device for filtering. For a list of maybe a hundred rows, that's fine. For a production list of fifty thousand, it isn't, because you'll silently miss rows. You won't see the squiggle in this course's exercises, because a collection is already in memory and there's nothing to delegate to. It appears the moment you point the same formula at a connected list, which is why the habit is worth building now. When you see a squiggle, the fix is usually to swap a non-delegable function for a delegable one. Delegation goes deeper than this course covers, so treat that squiggle as your prompt to read up on it before you ship against a large list.

6. Combining Filter and StartsWith

Real apps usually want both filters working at once. A status dropdown narrows the categories, and a search box narrows by title. You don't need two galleries. A single Filter call can take more than one condition, separated by commas: the status condition and the title condition side by side. Both conditions have to match for a row to appear. Pick a status, type a few letters, and the gallery narrows on both at once.

7. What you'll build

In the exercises you'll add a dropdown filter, a search box, and combine them so both controls drive the same gallery.

8. Let's practice!

Time to make Lumen's gallery filterable and searchable.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.