The Keep Rows filter in the Power Query UI handles 80% of row filtering tasks without you ever opening the Advanced Editor. Most people skip past it and go straight to writing M. Five clicks and you're done, and the M it generates is clean enough to modify later.
How to do it
- Click the dropdown arrow on the column header you want to filter.
- Choose Text Filters (or Number Filters, or Date Filters, depending on the column type) from the menu.
- Pick the operator you need: Equals, Begins With, Contains, Greater Than, Between, Is In The Previous, and so on. The list is column-type aware, so the relevant comparisons surface and the irrelevant ones don't.
- Type the value or values in the dialog.
- Click OK.
That's the filter. Power Query writes the M for you in the formula bar:
= Table.SelectRows(#"Previous Step", each Text.Contains([Region], "North"))You can read it. You can edit it. You can copy the pattern into the next query without touching the Advanced Editor.
Why it works
Power Query's UI is a M code generator. Every click you make in the ribbon writes a step in the M language behind the scenes, and the step shows up in the formula bar above the preview. The Keep Rows filter is the same: the dropdown is a faster way to write Table.SelectRows than typing it from memory.
The generated M is also the version that folds back to the source. If your data is coming from SQL Server, the engine will translate this filter into a WHERE clause and run it at the database, not in your machine's memory. Filtering through the UI gives you query folding for free; writing the same filter by hand sometimes breaks it.
When not to use it
If your filter needs to reference another column, a parameter, or a list of values pulled from somewhere else in the query, the UI runs out of options. That's when you open the Advanced Editor and write the each expression yourself. The UI handles single-value and small-list comparisons. Anything dynamic, you write by hand.
For the M version, the one where you filter against a list, a parameter, or a calculated value, see the Apr 27 Magic Monday on Table.Group and the Apr 28 Build It. A full M custom functions series lands in June.