M Query Guide

Examples

M Data Cleaning Query

Cleaning a Dataset

M data cleaning query removes nulls and formats text.

Introduction to M Data Cleaning Queries

M is a powerful data transformation language used in Microsoft Power BI, Excel, and other applications. One of its key functionalities is data cleaning, which involves removing null values and formatting text for consistency and accuracy.

Removing Null Values

Null values in datasets can lead to incorrect analyses if not handled properly. M language provides an efficient way to filter out these nulls. The Table.SelectRows function is often used for this purpose.

In the example above, we use Table.SelectRows to filter out records where the Age field is null, resulting in a dataset without null age values.

Text Formatting

Consistent text formatting is crucial for data analysis. M provides functions like Text.Proper, Text.Upper, and Text.Lower to format text fields.

In this example, Text.Proper is used to capitalize the first letter of each word in the Name field, resulting in consistently formatted names.

Combining Data Cleaning Techniques

Data cleaning often requires a combination of techniques. Below is an example that removes null values and formats text in a single query.

This query first removes any rows with null values in the Age column and then applies proper case formatting to the Name column.

Examples

Next
Aggregation Query