Chapter 18 Conditional Logic in Power Query

As you build more solutions using Power Query, you’re bound to come across a scenario where you need to perform some kind of logic in a column. And while Power Query has a method to do this, it’s not consistent with the way an Excel Pro would approach this issue.

IF Scenarios in Power Query

This example looks at the issues that appear when you import the following timesheet from a text file:

Figure 220 A text file with some challenges.

The data doesn’t seem so bad at first glance. And it seems like even less of an issue when you realize that you don’t really need the top four rows, as the dates are included on each row of the data table. But then you notice an issue: The employee name is not included in rows but rather is buried in the middle of the data. How do you get it out of there?

The challenge in this scenario is to figure out how to get the employee name associated with each row of data—and that is not going to be possible without some conditional logic.

Connecting to the Data

You need to connect to the data to see how it looks in Power Query, as this may give you some ideas about how to proceed:

And the problem is now fully exposed:

Figure 221 John Thompson, hanging out in the Out column.

At first glance, you might be tempted to transpose this data and fill John Thompson’s name down. But there are other rows as well, and you have no idea how many. Building a solution to use this approach could be very difficult to maintain, so you need to find a better way.

A more sensible approach in this case would be to add a new column and put a formula in it—a formula that checks whether the Out column is a time and pulls the data in that column if the test fails. But how do you do that?

Replicating Excel’s IFERROR() Function

Try an experiment here:

As you’d expect, the times all convert nicely, but the employee name returns an error:

Figure 222 John Thompson doesn’t have the time.

This is entirely to be expected, but can you use your knowledge of this behavior to solve the problem? What if you tested to see if converting this column to a time data type returns an error? If it did, then you could return null, and if it did not, you could return the time.

You can use Power Query’s Time.From() function to attempt to convert the data to a valid time. And based on your Excel knowledge, you’d kind of expect this to work:

=IFERROR(Time.From([Out]),null)

Unfortunately, this will get you nothing but an error, as Power Query doesn’t recognize the IFERROR function. All is not lost, however, as Power Query does have a function to do this, although the syntax is very different:

=try <operation> otherwise <alternate result>

Just like Excel’s IFERROR(), Power Query’s try statement attempts to perform the operation provided. If it succeeds, that result will be returned. If, however, the result is an error, then it will return the value (or other logic) specified in the otherwise clause.

This means that IFERROR() can be written in Power Query as follows:

=try Time.From([Out]) otherwise null

It should return null for any row that contains an employee name in the Out column and the time for any row that has a valid time in it. Give it a try:

The new column is added, and it works nicely to meet the current goal:

Figure 223 Returning null instead of an error.

Note: You may get a different time format, like 18:00:00 instead of 6:00:00 PM, depending on the regional settings in your computer.

This is fantastic, as it means you can now do some other tests to work out what you really want to know.

Replicating Excel’s IF() Function

You’ve now got something you can test using simple logic: If the Custom column contains null, then give us the value in the Out column. If it doesn’t, return null.

This should be pretty easy—just create a custom column and use the following function, right?

=IF([Custom]=null,[Out],null)

Not so fast! That isn’t going to work either.

Power Query doesn’t use the same syntax as Excel does for its IF() function, either. Instead, the Power Query syntax is as follows:

=if <logical_test> then <result> else <alternate_result>

Yes, you’re reading that correctly. You actually have to spell the whole thing out for Power Query. Try it:

The column is added to the data, and you can see some real potential here:

Figure 224 John Thompson finally has his own column.

Naturally, with this in place, you can fill the employee name into the null areas:

Even better, because Power Query processes the steps in the Applied Steps box as completely self-sufficient steps in sequential order, you don’t need to keep the precedent information around. You can remove that column and clean up the rest of the data, like this:

The query is now final and ready to be loaded:

Figure 225 The timesheet with employees filled down the last column.

Note: The key to working with Power Query’s logic functions is to remember to spell them out in full and make sure to keep the function names in all lowercase.