Chapter 39: Transforming Data with Power Query
39
In these instances, you can create your own custom column. Follow these steps to create a
new column that merges the Type and Code columns:- While in the Query Editor, click Add Column ➪ Custom Column.
- Place your cursor in the formula area after the equal sign.
- Find the Type column in the Available Columns list and double-click it. You will
see [Type] pop into the formula area. - After [Type], enter the following text: &"-"&. This text will ensure that the val-
ues in the two columns are separated by a hyphen. - Next, enter Number.ToText(). This Power Query function converts a number to
text format on the fly so that it can be used with other text. In this case, since the
Code field is formatted as a number, we’ll need to convert it on the fly to join it to
the Type field. You’ll learn more about data type conversions later. - Place your cursor between the parentheses for the Number.ToText function.
- Next, find the Code column in the Available Columns list and double-click it.
You will see [Code] pop into the formula area. - In the New Column Name input, enter MyFirstColumn.
At this point, your dialog box should look similar to the one shown in Figure 39.26. Note
the message at the bottom of the dialog box stating that “No syntax errors have been
detected.” Each time you create or adjust a formula, you’ll want to ensure that this message
states that no errors have been detected.- Click OK to apply your custom column.
If all went well, you have a new custom column that concatenates two fields. With this
example, you get the basic foundation for how Power Query formulas work.Understanding data type conversions
When working with formulas in Power Query, you’ll inevitably need to perform some action
on fields that have differing data types. Take, for example, the previous exercise where
we merged the Type column (a text field) with the Code column (a numeric field). In that
example, we used a conversion function to change the data type of the Code field so that it
can be temporarily treated as a text field.Conversion functions are just what they sound like. They convert data from one data type
to another.Table 39.1 lists common conversion functions. As demonstrated in the previous section, you
simply wrap these functions around the columns that need converting.Number.ToText([ColumnName])