Filtering and sorting data with M

Filtering and sorting are essential data transformation tasks that help you extract relevant information from large datasets and organize it in a meaningful way.

Power Query’s M language offers a range of functions to efficiently filter rows based on conditions and sort data according to specific criteria. In this section, we’ll explore how to filter and sort data using M, accompanied by step-by-step examples and explanations of key functions.

First of all, filtering data from your analysis typically involves selecting certain rows from a dataset based on certain conditions. M has a function named Table.SelectRows for this exact purpose. As it suggests, it allows you to specify a condition within the argument that determines which rows should be retained.

Following on from the Products table we connected our M to earlier, we can add an additional step to help us filter the data for analysis.

For example, suppose for our analysis...