In my last post I talked about useful text functions, and how they differed between Excel and Power Query. Today we’re going to look at another compare/contrast scenario, but this time it’s going to be the IF function.
The only important thing we need to remember here is that all functions in Power Query, whether text, logic or anything else, are case sensitive. That may strike you as weird in this one, but we need to remember that “if” is not the same as “IF”, and that Power Query will gag on the latter.
The base scenario
For this example I’m going to work with a table of data that holds a customer number, a boat type and a billing code schema. While the data has been scrambled, this represents a real structure that we use in my day job.
There’s no real mystery to the first two items, but the billing code schema we designed holds a ton of info. It’s always 10 characters long, and breaks down like this:
- Char 1 – Alpha – Indicates the division (G = Golf, F = Fitness, M = Marina)
- Char 2 – Alpha – Indicates the billing type (D = Dues, P = Pass, A = Annual Moorage, P = Periodic Moorage)
- Char 3-4 – Numeric – Indicates the number of months of coverage for the product (1-12)
- Char 5-6 – Numeric – Indicates the start month (and subsequent anniversary) for the customer’s product
- Char 7-8 – Variable – Slip length (in feet) for a boat in the case of marina customers, or SG, CP or CS for golf (indicating single, couple primary or couple spouse)
- Char 9 – Text – A variety of single letter codes indicating specific things we want to know. (Will factor in to a future post.)
- Char 10 – Text – Indicates the payment method (F = Financed, P = Paid up front, C = Comp/Honorary)
So a table of customer data could look like this:
Turning data into more useful data
So great, we’ve got this awesome billing code schema, but it doesn’t really tell me anything when I look at it, as it’s too complicated to read. I really need to break this into separate pieces, and make useful and readable columns out of it. So that’s what I’m going to start doing now.
The first step is, of course, to click in the table and go to Power Query –> From Table.
My goal here is to make a column that says “Annual” if the second character is an “A”, or “Periodic” if the second character is a “P”. To start, I’m just going to reach back to last week’s article and make sure I can identify which character I’m looking at. So first I’ll click “Add Custom Column”.
I’ll call my new column “Seasonality”, and use a formula to extract just the 2nd character:
And with that in place we can now focus in on the important data here:
Writing IF functions in Power Query
Assuming the data was in a table that started in row 2 of the worksheet, either of the following formulas would work to convert “A” to “Annual” or “P” to “Periodic”:
Easy enough, right? But look at how the signatures differ from Excel to Power Query:
|Excel||=IF(test, value_if_true, value_if_false)|
|Power Query||=if test then value_if_true else value_if_false|
Notice that there are no parenthesis or commas in the Power Query version, but you need to actually type out the “then” and “else” portions. So to create the same thing in Power Query, we’d need a new column that uses the formula:
=if [Seasonality]=”A” then “Annual” else “Periodic”
Or, as is my preference, we modify the Seasonality column we already built, wrapping the text extraction with the IF function as follows:
=if Text.Range([BillingCode],1,1)=”A” then “Annual” else “Periodic”
Once we modify the original formula, our table now correctly shows the different values all the way down:
Once again, I find this a bit of a departure from regular Excel formulas. Although it’s not hard to make the transition once you understand it, it would still be nice if the language could leverage the skill set we’ve worked so hard to master. You could argue that the verboseness of the Power Query IF function is easier to read, but it’s still inconsistent with the formulas we know and love.
I still feel it would be nice if we could have an alternate pointer into the same function so that I could type this in Power Query too:
I think that would just make it so much easier to get off the ground running for Excel pros.
I’ll also point out that the error message Power Query gives you when you create an IF function or formula is not exactly helpful:
Most Excel pros aren’t going to understand what “Token Eof expected.” means, and I really have to question how it is telling me anything that I need to do to fix the formula. Hopefully, in future versions of Power Query we get a more helpful message that says something like “It looks like you typed an upper case formula name. Can I fix that for you?” (Maybe that will come with Intellisense and auto-complete…)
Taking this further
Next blog post we’ll look at how to take this a bit further… extending our conditional logic to look up a corresponding value in a list, avoiding having to nest several IF functions within each other.