Excel 2019 Bible

Chapter 39: Transforming Data with Power Query

39

Table 39.2 lists some other Power Query functions useful in extending the capabilities of

custom columns. Take a moment to review the list of functions and note how they differ

from their Excel equivalents. Remember that Power Query functions are case sensitive.

TABLE 39.2 Useful Transformation Functions

Excel Function Power Query Function

LEFT([Text],[Number]) Text.Start([Text],[Number])

RIGHT([Text],[Number]) Text.End([Text],[Number])

MID([Text],[StartPosition],

[Number])

Text.Range([Text],[StartPosition],[Number])

FIND([Find],[Within]) Text.PositionOf([Within],[Find])+1

IF([Expression],[Result1],

[Result2])

if [Expression] then [Result1] else

[Result2]

IFERROR([Procedure],[FailRes

ult])

try [Procedure] otherwise [FailResult]

Adding conditional logic to custom columns

As you may have noticed from Table 39.2, Power Query has a built-in if function. The if

function is designed to test for conditions and provide different outcomes based on the

results of those tests. In this section, you’ll see how you can control the output of your

custom columns by utilizing Power Query’s if function.

Just as in Excel, Power Query’s if function evaluates a specific condition and returns a

result based on a true or false determination:

if [Expression] then [Result1] else [Result2]

In Excel, you think of the commas in an IF function as THEN and ELSE statements. In Power Query, you don’t use
commas.

The Excel formula IF(Babies=2,"Twins","Not Twins") would translate to the following: If Babies
equals 2 then "Twins" else "Not Twins".

Imagine that you need to tag customers as either large customers or small customers based

on their dollar potential. You decide that you’ll add a custom column that contains either

“LARGE” or “SMALL” based on the revenue potential of the customer.

With the help of the if function, you can tag all customers with one custom column that

uses the following formula:

if [2016 Potential Revenue]>=10000 then "LARGE" else "SMALL"