Part V: Understanding Power Pivot and Power Query
- Next, find the CompanyNumber column in the Available Columns list and
double-click it. You will see [CompanyNumber] pop into the formula area.
At this point, your formula area should contain this syntax:
"0000000000"&Number.ToText([CompanyNumber])
This formula will result in nothing more than a concatenation of 10 zeros and the
CompanyNumber. The goal is to go further and extract only the right 10 characters.
The RIGHT function is an Excel function that won’t work in Power Query. However,
Power Query does have an equivalent function called Text.End(). Just like the
RIGHT function, the Text.End function requires a couple of parameters: the text
expression and the number of characters to extract.
Text.End([MyText], 10)
- In this scenario, the text expression will be your formula, and the number of
characters to extract will be 10. Enter Text.End and a left parenthesis (before
your existing formula) and then follow the formula with a comma and then the
number 10 and a right parenthesis. The final syntax is shown here:
Text.End("0000000000"&Number.ToText([CompanyNumber]),10)
- In the New Column Name input, enter TenDigitCustNumber.
At this point, your dialog box should look similar to the one shown in Figure 39.28.
Again, make sure that the message as the bottom of the dialog box reads “No syn-
tax errors have been detected.”
FIGURE 39.28
A formula to create a consistent CompanyNumber padded with 10 digits
- Click OK to apply your custom column.
