Filtering and searching data

1. Filtering and searching data

Lumen's full task list has 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 by Status dot-Value equals In progress narrows the list to just the active tasks. That little dot-Value after the column name is a quirk of choice columns, and we'll cover it in the exercise.

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 F5. That reactive recalculation is what makes Power Fx feel like Excel.

4. Why StartsWith beats Search

Power Fx has two search functions. Search matches substrings anywhere in a column, case-insensitive, 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, then filters locally. Fine on twenty rows, 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

You'll see a blue squiggly underline in the formula bar sometimes. That's the delegation warning. Power Apps is telling you the formula can't be pushed down to the data source, so only the first 500 rows (the default page size) come back to your device for filtering. For Lumen's task list of maybe a hundred rows, fine. For a real production list of fifty thousand, very much not fine, you'll silently miss rows. When you see a squiggle, the fix is usually to swap a non-delegable function for a delegable one. There's more depth to delegation than we'll cover here.

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.