Adding columns

You can add a new column in Power Query by using one of the following options on the Add column ribbon:

  • Column From Examples This option allows you to type some examples in a new column, and Power Query will try its best to write a transformation formula to accommodate the examples.
  • Custom Column You can type your own M formula for the new column.
  • Invoke Custom Function This option invokes a custom function for every row of a table.
  • Conditional Column This option provides an interface where you can specify the if-then-else logic for your new column.
  • Index Column This option creates a sequential column that starts and increments with the values you specify. By default, it will start with 0 and increment by 1 for each row.
  • Duplicate Column This option creates a copy of a column you select.

In addition to these options, you can use data type–specific transformations to add columns, most of which are also available on the Transform ribbon. The difference between the Add Column and Transform ribbons is that the former will add a new column whereas the latter will transform a column in place.

Targets For 2022

In the Targets for 2022 query, you must transform month names into dates, and you’ll use the Custom Column option for this as follows:

Select Add Column > Custom Column.

Enter Start of Month in the New column name box.

Enter Date.From([Attribute] & “2022”) in the Custom column formula box. The Custom Column window should look like Figure 1-27.

FIGURE 1-27 Custom Column window

Select OK.

Right-click the Attribute column and select Remove.

When entering a custom column formula, you can use the columns available in the table by double-clicking them in the Available columns list on the right; this will insert a column reference into your formula.

Once you complete these steps, the result should look like Figure 1-28.

FIGURE 1-28 Targets for 2022 with Start of Month column

Important Custom Column Types

The Custom column feature creates a column without a data type by default; you must set the data type manually before loading to ensure that the data is loaded correctly.

As you can see, the Start of Month column you’ve just created has no column type, and you may want to make it the first column instead of the last. Additionally, you have to rename the Value column by completing the following steps:

Select the ABC123 icon on the Start of Month column header and select Date.

Right-click the Start of Month column and select Move > To Beginning.

Double-click the Value column header.

Enter Target Excluding Tax and press the Enter key.

The Targets for 2022 query is now finished, and we need to transform the Targets query next.