Creating Custom Columns

Creating custom columns is a common data transformation activity when working with Power Query and the M language. In this section, we will explore several ways to create custom columns using both 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 that numbers the rows in sequential order. 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 dropdown arrow to the right of the Index Column option, and finally choose as shown in Figure 2.15:

Figure 2.15: Adding an index column starting at 1

Performing this action adds an Added Index step in the Applied Steps area of the Power Query Settings pane. The Formula bar displays the following M code with the Added Index step selected:= Table.AddIndexColumn(#"Changed Type", "Index"...

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, Power Query Editor provides such an interface, the Advanced Editor. 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.18:

Figure 2.18: Accessing the Advanced Editor

This launches the Advanced Editor dialog as shown in Figure 2.19:

Figure 2.19: The Advanced Editor

In Figure 2.19 the entire M code that comprises the Chapter 2, Product Inventory query is displayed. All the steps of the query are contained within the let expression introduced in Chapter 1, M Language Basics. In addition, the Display Options dropdown has been used to Display line numbers and Enable word wrap. Clicking...

Summary

The Power Query Desktop and Online experiences provide the primary interface for writing M code. These experiences largely shield the user 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 the M code directly. In this chapter, we toured the Power Query experience 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...

Retrieving web content

In the last section, we covered accessing data within files stored on a local computer or network file system. However, another common source of data is the internet. The standard M library includes a number of functions for retrieving data from the internet, including:

• Web.BrowserContents
• Html.Table
• Web.Page
• Web.Contents
• Web.Headers
• WebAction.Request

To see these functions in action, first perform these steps:

1. Select Get Data from the Home tab of the Power Query editor and then choose Web.
2. In the From Web dialog, use the following for the URL, https://subscription.packtpub.com/search.
3. Press the OK button.
4. Select Anonymous authentication and press the Connect button.
5. In the Navigator dialog, choose HTML Code under the Text folder.

Following these steps generates the following M code as viewed in the Advanced Editor:

let
    #"HTML Code" = Web.BrowserContents...

Investigating binary functions

As we have discussed, File.Contents and Web.Contents serve as core data-accessing functions. One accepts a file path while the other accepts a URI but both return the accessed file or web page as binary data. It stands to reason then that there is an entire group of functions dedicated to processing and handling binary data built into the core M library. These are the functions that start with the word Binary.

In fact, there are over 40 functions in the Binary family. Many of these functions are somewhat esoteric. However, a common use for these functions can be observed in Enter Data queries.

To observe these binary functions in action, create an Enter Data query in the Power Query editor with the following data:

Column1	Column2
One	1
...

Accessing databases and cubes

Just as the standard M function library supports many different standard file formats, there are also core functions for accessing a variety of different industry-standard database/cube formats. Here we use the term cube to refer to database systems classified as Online Analytics Processing (OLAP) systems. The term database refers to relational databases classified as Online Transactional Processing (OLTP) systems. OLAP systems are suited for the multi-dimensional analysis of data while OLTP systems are suited for transactional operations.

OLTP systems tend to use highly normalized data structures that value the efficiency of data storage and speed of write operations over other concerns. Think high-transaction scenarios such as inventory management within a warehouse or retail sales. Conversely, OLAP systems aggregate (de-normalize) historical information and value the speed of read operations and the efficiency of analysis and reporting.

The...

Working with standard data protocols

Over the years, a number of standards have been developed to facilitate efficient and seamless communication and data exchange between various platforms and systems. The standard M library provides functions for many of these data-accessing standards including:

Standard	Description	M Functions
ADO.NET	ADO.NET (ActiveX Data Objects .NET) is a data access technology in the Microsoft .NET framework that provides a set of libraries and classes for accessing and manipulating data from different data sources, such as databases, XML files, and more. ADO.NET is specifically designed for building data-driven applications and is a fundamental component in the .NET framework for working with relational databases.	AdoDotNet.DataSource...

Addressing additional connectors

Thus far we have covered most, but not all, of the data-accessing functions available within the standard M library. Note that many more data-accessing functions are available within the global M environment in products such as Power BI Desktop. These additional data-accessing functions provide the ability to connect to a wide variety of specialized business systems such as customer relationship management (CRM) systems, enterprise resource management (ERP) systems, and others. These additional data-accessing functions come from external connectors included with Power BI Desktop.

External connectors are covered in Chapter 16, Enabling Extensions. A full list of data-accessing functions available in the global environment for Power BI Desktop can be found here: https://powerquery.how/accessing-data-functions/

Let’s briefly address the remaining data access functions available in the standard M library. These generally fall into two categories...

Combining and joining data

Thus far, we have been focused on accessing and retrieving data from individual data sources with minimal data transformation. However, in addition to accessing data, one of the strengths of the M language is the ability to transform and combine that data. This provides the ability to combine data from multiple sources together such as merging inventory data with sales data or combining customer data from multiple CRM systems. Therefore, we will briefly cover five basic functions that allow us to combine and merge our data together. These five functions are:

• Table.Combine
• Table.NestedJoin
• Table.Join
• Table.FuzzyNestedJoin
• Table.FuzzyJoin

As you read this section, it will be helpful to reference Rick de Groot’s Join Types Cheat Sheet, which can be found here: https://gorilla.bi/power-query/join-types/. For ease of reference, the cheat sheet is included here:

Figure 3.13: Output of Table.NestedJoin...

Summary

The standard M function library contains a plethora of functions designed to facilitate data access and retrieval from a wide array of different systems and data storage formats. In addition, the M language also provides functions for combining and joining data together. Practical examples were included throughout this chapter to allow the reader to experiment with and visualize the output from these functions.

In this chapter, we explored data-accessing functions for a variety of file formats, folders, web content, databases, cubes, and standard data protocols such as OData and ODBC. We also covered a variety of binary functions, identity functions, and specialized functions for accessing popular software systems such as Microsoft Exchange and Microsoft SharePoint. Finally, we explored five functions for combining and joining data, including the ability to perform fuzzy matching when joining tables.

In the next chapter, we continue to explore the M language by seeking...