Excel 2019 Bible

Chapter 39: Transforming Data with Power Query

39

FIGURE 39.27

Use the Data Type drop-down to discover and select the data type for a given field.

Spicing up custom columns with functions

With a few basic fundamentals and a little knowledge of Power Query functions, you can

create transformations that go beyond what you can do through the Query Editor. In this

example, we’ll use a custom column to pad numbers with zeros.

You may encounter a situation where key fields are required to be a certain number of char-

acters for your data to be able to interface with peripheral platforms such as ADP or SAP.

For example, imagine that the CompanyNumber field must be 10 characters long. Those that

are not 10 characters long must be padded with enough leading zeros to create a 10-charac-

ter string.

The secret to this trick is to add 10 zeros to every company number, regardless of the cur-

rent length, and then pass them through a function similar to the RIGHT function that

will extract only the right 10 characters.

For example, company number 29875764 would first be converted to 000000000029875764;

then it would go into a RIGHT function that extracts only the right 10 characters. This

would leave you with 0029875764.

Although this is essentially two steps, you can accomplish the same thing with just one

custom column. Here’s how to do it:

1. While in the Query Editor, click Add Column ➪ Custom Column.


2. Place your cursor in the formula area after the equal sign.


3. Enter 10 zeros in quotes (as in "0000000000") followed by an ampersand (&).


4. Next, enter Number.ToText(). (Do not type the period.)


5. Place your cursor between the parentheses for the Number.ToText function.