M Query Guide

Transformations

M Filtering Rows

Filtering Table Rows

M filtering rows uses Table.SelectRows with conditions.

Introduction to Table.SelectRows

In Power Query M, filtering rows is a crucial transformation task that allows you to refine your data by removing unwanted entries based on specific conditions. The function Table.SelectRows is a powerful tool that facilitates this process.

Basic Usage of Table.SelectRows

The Table.SelectRows function filters a table by applying a condition to each row. Only the rows that satisfy the condition will be retained in the resulting table. The basic syntax is as follows:

Filtering Rows with a Simple Condition

Let's look at an example where we filter a table to include only the rows where the column Age is greater than 30.

Using Multiple Conditions

To filter rows using multiple conditions, you can use logical operators like and or or. For instance, filter rows where Age is greater than 30 and Name starts with 'C'.

Filtering with Custom Functions

For more complex filtering, you can define custom functions. Suppose you want to filter rows based on a custom condition that involves multiple columns or complex logic.

Summary and Best Practices

Using Table.SelectRows efficiently can greatly enhance your data transformation workflows in Power Query M. Remember to:

  • Keep your conditions as simple as possible for better performance.
  • Use logical operators for combining conditions.
  • Consider custom functions for complex filtering logic.

With practice, filtering rows using Table.SelectRows will become an integral part of your data preparation toolkit.

Transformations

Next
Removing Columns