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

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

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