Transforming Data with the M Language

As we have seen throughout this book so far, Power BI and Power Query are powerful tools designed to help transform, clean, and shape data for effective visualization and analysis. Central to their data transformation capabilities is a functional, case-sensitive language called M, which serves as the backbone for performing data transformations within these tools.

in this chapter, we will delve into the essence of M, exploring its significance, structure, and common use cases, and get hands-on with some examples, which will be covered in the following topics:

• Understanding the M language
• Filtering and sorting data with M
• Transforming data with M
• Working with data sources in M

By the end of this chapter, you will have built a basic foundation of knowledge and hands-on experience using M within the advanced editor – useful for creating complex data transformations that are not easily achievable through the graphical...

Technical requirements

Using the following link, you will find the uncleaned dataset to be used during this chapter: https://github.com/PacktPublishing/Data-Cleaning-with-Power-BI.

Understanding the M language

M, also known as the Power Query formula language, is an integral part of Power BI and Power Query. It facilitates the transformation of raw, unstructured, or messy data into organized, refined datasets that are ready for analysis and visualization. Unlike traditional formulas in Excel that operate on cell-level data, M operates on entire columns or tables, enabling complex data transformations across large datasets.

M and DAX are two coding languages within Power BI; however, M is also very different from DAX. It’s a functional, case-sensitive language (similar to F# for those familiar) that employs a sequence of steps to transform data. In Power Query’s M language, each step in the query editor represents a transformation or operation applied to the data. While not explicitly defined as functions, these steps function in a manner similar to functional programming. The steps operate sequentially, with the output of one step serving as...

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...

Transforming data with M

Now, there are a plethora of transformation functions that can be used within Advanced Editor to transform your data. With the issue/error we faced when trying to apply the filter, there are a certain number of functions we will need to use, such as Table.TransformColumns and Table.RemoveLastN.

As mentioned earlier, the first issue we can see in the data that might prevent us from filtering is that the values for cost and price contain a $ character. This is leading Power BI to read this as a text value. So our first port of call should be to remove this value from the column.

Now, of course, you could use the Split column function in the Power Query UI but it’s important to understand what M code is created behind the scenes from using such buttons. Using M will also help reduce the steps you need to get to the desired goal. This will particularly help when you’re looking to script more complex queries in M later in your data journey.

...

Working with data sources in M

To effectively work with different data sources, you need a good grasp of M’s capabilities for more dynamic and flexible data transformations. It’s essential to understand how M can be used to customize and parameterize data source connections. In short, this will help you to enhance reusability and facilitate easy adjustments to queries without manual code modification, thus saving you time later down the line.

In the following sections, you will learn about how to actually go about creating parameters with the help of examples for you to follow along and implement in your own environment.

Creating parameters and variables

Parameters and variables can be used for a number of different uses in M queries. One of which is to allow you to decouple your data source details from your M query, making it easy to switch between different sources. By defining parameters for key attributes such as file paths or server addresses, you can change...

Summary

In this chapter, you gained an understanding of how to work with data sources using the M language within Power Query in Power BI. You learned about topics such as M queries, M structure, variable declarations, data source connections, query steps, data type manipulation, and the importance of comments for code readability. You also discovered the versatility of M in handling different data sources, customizing connections, and using parameters, offering step-by-step instructions on creating and using parameters to enhance code maintainability and flexibility.

Additionally, the chapter presented practical examples of using parameters in M when connecting to SQL Server databases, conditionally selecting data sources using parameters, and combining multiple CSV files from a folder. These examples illustrated how M can be used to perform advanced data transformations, beyond what the UI offers.

Overall, you gained valuable insights into the power of M for working with diverse...

Questions

1. What is the purpose of M in Power BI and Power Query?
   1. Formatting cell-level data
   2. Transforming entire columns or tables
   3. Creating calculated tables
   4. Sorting individual cells
2. What keyword marks the beginning of a new M variable declaration block?
   1. Start
   2. Let
   3. let
   4. Define
3. How is a data source typically connected using M?
   1. Through the Connection tab
   2. Via the Import button
   3. Using a variable, often named Source
   4. By applying filters
4. What does the # symbol represent in M?
   1. The next step in the query
   2. A step/identifier that includes a space or special characters
   3. Previous steps in a query
   4. An intermediate step
5. Which function is used to convert extracted text into a numeric value in M?
   1. Text.Extract
   2. Number.From
   3. Transform.Text
   4. Numeric.Convert