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