Where is M Used?

M is a versatile language that finds application in various tools and platforms where data transformation and manipulation are essential. Its integration within the Power Query ecosystem enables users to leverage M's capabilities in different environments. In this section, we explore some key areas where M is widely used.

Experiences

Before discussing specific products where M is used, it is important to understand the different experiences available for authoring M. There are two experiences available for authoring M. These experiences are the following:

1. Power Query Desktop – Power Query Desktop is the experience for Power Query found in desktop applications such as Power BI Desktop and Microsoft Excel. While the experiences are similar, there are differences. For example, the artificial intelligence (AI) and machine learning (ML) integrations as well as the integrations with R and Python present in Power BI Desktop are not present in Microsoft Excel. Conversely...

M Language Basics

M is a powerful language designed for data transformation and manipulation within the Power Query ecosystem. Understanding the basics of the M language is essential for effectively leveraging its capabilities. In this section, we delve into the fundamental elements and concepts of the M language.Here are some important fundamentals regarding the M language:

1. Expressions and Functions – In M, expressions form the building blocks of data transformations. An expression represents a computation or operation that evaluates to a value. M provides a wide range of built-in functions that can be used to perform operations on data. Functions in M are called using a syntax where the function name is followed by arguments within parentheses. For example, the function Text.Start("Hello, World!", 5) returns the substring "Hello" from the input text.
2. Data Types – M supports various data types, including text, numbers, dates, times, lists, tables, and...

Summary

The M language forms the backbone of Microsoft Power Query's data transformation capabilities. M is widely used within the larger Microsoft ecosystem including a variety of software applications and web-based services. Many different types of data professionals can benefit from learning M to efficiently and effectively ingest and transform data across a wide range of scenarios. By mastering M, you gain the ability to efficiently handle complex data transformations, customize your data workflows, and optimize performance, thereby unlocking the full potential of Power Query for your data manipulation needs.In this chapter, we have explored the fundamentals of the Power Query Formula Language (M) including what M is, its history, who should learn M, and why individuals would want to learn M. In addition, we have explored how and where M is used. Finally, we have introduced the reader to some of the fundamental components and language constructs of M. These fundamental building...

Editing experience-generated code

As pointed out in this chapter’s Your first query section, using the Power Query Desktop experience’s graphical user interface (GUI) to connect to and transform data generates M language code. This code generated can be tweaked or edited using the formula bar.

For example, in the Applied Steps area of the Query Settings pane, by clicking on the Source step for the query and then clicking the down arrow to the far right in the formula bar, you can see the full M language code for the Source step, such as the following:

= Csv.Document(File.Contents("C:\Users\gdeck\OneDrive\Books\The Definitive Guide to Power Query\Chapter 2\Chapter 2 - Product Inventory.csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None])

As you can see in this code, there are two nested functions being used to connect to the CSV file, Csv.Document and File.Contents.

• The File.Contents function has a single...

Creating custom columns

Creating custom columns is a common data transformation activity when working with Power Query and the M language. While the number of scenarios for adding custom columns is effectively endless, a common example might be combining a unit price column and a quantity column into a single total sales column. In this section, we will explore several ways to create custom columns both using the GUI of the Power Query Editor as well as writing custom M code.

Adding an index column

A common column added to M queries is an index column, which numbers the rows in sequential order. Index columns are extremely beneficial in certain scenarios, such as Mean Time Between Failure (MTBF), where it is necessary to compare the differences between two rows of data.

To add an index column to the existing query created in the Your First Query section of this chapter, do the following:

1. Click on the Add Column tab of the ribbon, then click the drop-down arrow...

Using the Advanced Editor

While most novices will rely heavily on the GUI and Power Query Editor to write all or the majority of their M code, advanced users will eventually migrate to wanting direct access to the M code, similar to how the source code is created and edited in most other programming languages. Luckily, the Power Query Editor provides such an interface, the Advanced Editor. By utilizing the Advanced Editor, the full power of the M language can be brought to bear during data transformation versus only a small fraction of the language that can be accessed using the GUI.

To access the Advanced Editor, click on the Home tab of the ribbon and then select Advanced Editor from the Query section, as shown in Figure 2.19:

Figure 2.19: Accessing the Advanced Editor

This launches the Advanced Editor dialog, as shown in Figure 2.20:

Figure 2.20: The Advanced Editor

In Figure 2.20, the entire M code that comprises the Chapter 2, Working with Power...

Summary

The Power Query Desktop and Online experiences provide the primary interface for writing M code. These experiences largely shield you from having to directly write all or most of the M code that comprises queries. However, as your expertise with the M language grows, you will find yourself more frequently writing M code directly.

In this chapter, we toured Power Query using the Power Query Desktop experience in Power BI Desktop as our guide. This included an overview of the major components of the Power Query Editor interface as well as an exploration of options that control the look and behavior of this interface and how to view and modify data source settings. We also covered several ways of tweaking or writing M code, including using the formula bar as well as when adding custom columns. Finally, we explored the Advanced Editor for making mass edits and writing more advanced M code.

In the next and subsequent chapters, we delve into a more in-depth treatment of...