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 in Power Query.

## Critical background

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”:

=IF([@Seasonality]="A","Annual","Periodic") |

=IF(D2="A","Annual","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:

## Observations

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:

=IF([Seasonality]="A","Annual","Periodic")

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.

Hi Ken,

Thanks for the article! Once you know it, it seems really simple. But getting there/finding this out is the hard part...

Your article was perfectly timed: Just four hours ago I tried to figure it out (googling for powerquery and if isn't too much of help) - and now it pops up in my twitter stream! 🙂

Thanks!

Fantastic, glad it helped!

Ken, this helped me out. All I needed to know was "IF" is not the same as "if" (Gosh, darn!) but had been struggling with that for quite a while till I luckily stumbled on your post!

Hi,

Can any share me, How to write Nested If-Else-if-else condition in the AddColumn Tab in the Power Query

Need it Urgent...Please help me to resolve my Issue.

Thanks in Advanced

Ram

if test1 then if test2 then value-if-test2-is-true else value-if-test2-is-false else value-if-test1-is-false

Hi Ken,

is there something equivalent to dax switch function in power query? thanks

I haven't stumbled upon one yet. Not to say that it doesn't exist though.

Hi

Excellent blog

Thought I had learned from the blog and used this code to remove last 2 0s from column of numbers that result from say 115 - 15:

Text.Start(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]),Text.Length(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]))-2)

This worked except for first three columns which are say 45-45 giving a 0 so I tried this with great expectations:

if

Text.Length(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]) >=1

then

Text.Start(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]),Text.Length(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]))-2)

else

Text.Start(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]),Text.Length(Text.From([#"Time - Copy"]-[#"Minutes - Copy"])))

But syntax error keeps looking for a comma at "then"!!!!!!!

Can you help?

thanks

Conor

I believe you're just missing a ) on your second line, right before the > character.

Hi Ken

Oops. Silly me.

I hacked this fix hours after I started:

try Text.Start(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]),Text.Length(Text.From([#"Time - Copy"]-[#"Minutes - Copy"]))-2) otherwise 0

Should've talked to you first.

Thanks for that

Conor

Very helpful post - Thank you

Hi Ken, Please tell me how to get this thing in Power Query.

=IF(E2=1,"January",(IF(E2=2,"February",(IF(E2=3,"March",(IF(E2=4,"April",(IF(E2=5,"May",(IF(E2=6,"June",(IF(E2=7,"July",(IF(E2=8,"August",(IF(E2=9,"September",(IF(E2=10,"October",(IF(E2=11,"November","December")))))))))))))))))))))

Urgent.

You should probably review this article on replicating the SWITCH function in Power Query: https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/

Hi Ken,

I stumbled across your post and hoping you may be able to help me... I have the following date scheme in my data - 1130915. This is the century (don't ask), 2 digit year, two digit month, and two digit date. I need to reference the year and month to identify the fiscal year and then want that fiscal year to populate in a new column. Our fiscal year runs from June to May, so I need dates with 6-12 of 2013 and 1-5 of 2014 to say fiscal 14. Looking at this post, I get the jist of referencing the year or the year, but how do I reference them both together with a range???

Thanks Ken!

I'm stuck on this though:

if Date.From([ATP Date]) = "null" then "Missed" else "Achieved"

I cannot seem to get it to work with blank date values.

Please assist?

if Value.NullableEquals([ATP Date],1) then "Missed" else "Achieved"

returns Achieved but an error for the dates which are blank

Kevin, is the column showing as blank, or does it actually show a value of null? If it shows as blank, then try selecting the column, do a replace - selecting to replace with null, but leave the first parameter blank. That should convert your blanks to null, which should allow the formula to work.

Kelly, let's start by getting your date into a real date format. From there you should be able to spin off the logic you need. To do that, assuming your first column is called "myDates", you could use the following formula:

=Date.From("20"&Text.Range(Text.From([myDates]),1,2)&"-"&

Text.Range(Text.From([myDates]),3,2)&"-"&

Text.Range(Text.From([myDates]),5,2))

A bit of a pain, but then you've got a valid date that you can use for subsequent operations.

Hi Ken,

The date values were already converted; if there are no date values in a cell, it is represented as null. Apologies for the semantics.

What worked in the end was to replace all Errors with the value Missed. Not sure if this is the way to do it but it sees to work.

Thanks for your help!

Ken - Thanks very much for this info - exactly what I needed to convert from excel to Power BI - I have a question about the nested syntax you added in the comments - if test1 then if test2 then value-if-test2-is-true else value-if-test2-is-false else value-if-test1-is-false - I think that there needs to be VALUE-IF-TEST1-IS-TRUE ELSE after the first then. Correct? otherwise there is no option for test1 to be chosen.

I am trying to use this to group numerical data into 4 categories

? 300 days old

I can get it into 3 - if [#"Pty Age (workdays)"]300 then "biggest" else "medium"

but for 4 I need to use an AND and I can't figure out how to do that in DAX. Should I be using this or is there another function that would work better.

Thanks

Hi there,

I tend to use hard returns when writing my if statements in power query to clarify that. I.e.:

=if test1

then if test2

then test2=true_result

else test2=false_result

else test2=false_result

With regards to your second question... do you mean in DAX or in Power Query?

DAX: =IF(AND(Test1,Test2),Result_if_true,Result_if_false)

PQ: =if Test1 and Test2 then Result_if_true else Result_if_false

(And indented as I mentioned above for easier reading:

=if Test1 and Test2

then Result_if_true

else Result_if_false

Also... you may have to wrap the "Test1 and Test2" in parenthesis or write it as (Test1=true and Test2=true)

Hope that helps!

Hi All

Is there a Way to look for a Source file and if it doesn't excist then choose another file?

Thanks.

You should be able to define an alternate file using steps like this:

let

Book1 = "C:\datafile1.txt",

Book2 = "C:\datafile2.txt",

testBook = try Table.FromColumns({Lines.FromBinary(File.Contents(Book1),null,null,1252)}),

altBook = Table.FromColumns({Lines.FromBinary(File.Contents(Book1),null,null,1252)}),

Source = if testBook[HasError]

then altBook

else testBook

in

Source

Having said this, I'm finding that this is returning an error, which I don't believe it should be. (I've pinged Microsoft about it.)

As far as popping up an box to dynamically collect it... I don't think so. For that, I'd probably cook up some VBA to determine if the file exists, write the appropriate value back to a worksheet cell, then pull that in dynamically (with a parameter table) to drive the file to load.

Hi Ken,

I am working on nesting a statement with multiple IF(OR( . Does OR( work in power query, and what words should be? I would imagine it is some combination of true and false.

Thanks

Hi there,

Or does work, yes. You just need to spell it as "or" (lowercase). "And" is the same in that regard.

If you use it in a statement you may also want to wrap it in parenthesis in order to ensure it executes correctly:

=if (test1 or test2) then "success" else "try again"

The parenthesis are not always necessary but they won't hurt.

Hope that helps.

Hi Ken

Your suggestion to break into logical rows (if/else if/else) and add brackets did the trick. I just couldn't work some logic out but now it is all working.

Thanks for the tip :-). Do you have any recommendations on good documentation for implementing dates/numbers into PQ (or the new Excel 2016 flavor).

Hi Rosalyn,

M is for Data Monkey has a full table of date functions and their Excel equivalents in Chapter 24. With regards to numbers, it depends on what you're looking for. If you're talking about dealing with conversions (to/from numbers/text/dates, etc...) these are in Chapter 17 (pgs 143-144).

Hi Ken,

Where report files contain:

Total lines with no leading space ["Foods"]

Subtotal lines with (say) 4 leading spaces [" Cereals"]

Detail lines with 8 leading spaces [" Maize"]

I want to create a normal Pivot, which I can do with lots of manipulation, using only the detail lines, inserting 2 columns to show Foods and (in the case above) "Cereals". Can Powerquery help in this instance?

Thanks,

PS: Just looked at M is for Data Monkey - will be buying very soon.

Hi Tony,

So if I read this correctly, you want to split the data into three columns: Category, SubCategory and Product, so that you can then use them in separate columns on the Pivot. Providing I got that right, then yes. Right click the column in Power Query --> Split Column --> By Delimiter. Enter 4 spaces, then ensure that "repeatedly" is selected. That should be it.

I love simple! It's exactly what is needed. Just need to try this at home...

Thanks very much - great turnaround.

Tony

Thanks for this. Only discovered Power Query today. Also tried normal If but luckily got your article. I have very limited knowledge of VB. It seens as if Query uses VB codes and formulas? For example, the text.range does not ring any bells from a normal Excel formula background?

Hi Chris,

No, not even close, to be honest. Power Query uses a language called M which is VERY different from standard Excel formulas, Power Pivot's DAX formulas and VBA. To be honest, one of the best things you could do to get the most of power query is pick up a copy of my book, as that covers how to use the tool in depth. http://xlguru.ca/monkey

does these have to come all together if-then-else? can I only use if-then? without an else?

Hi Ken,

Hoping for a bit of guidance please. I am trying to round Lat & Long values to 2dp and then combine them to give a sudo primary key. The end result should look something like: "48.86,-2.30". My concern is that the numbers are being rounded and where there is a zero it is being omitted (eg. "48.86,-2.3").

I created the following bit of code using an IF statement to examine the string and append the zero if necessary but it isn't working. Can you advise me please?

Lat2DP = Number.ToText([Lat]) &

IF Text.Length(Number.ToText([Lat]))-

Text.PositionOf(Number.ToText([Lat]),".") = 2 THEN "0" ELSE "")

Also if you think I am on completely the wrong path feel free to say.

Thanks for the great site and all the best, John.

I think I have figured it out. It didn't like the capital letters.

Changing "IF THEN ELSE" to "if then else" seems to have worked.

Regards John

Hi John, yes, that's exactly it. Power Query is case sensitive and requires lower case.

I would like to ask question regarding the example in chapter 18. After going through all steps and adding custom column "employee" name of employee is not showing instead the "error" is showing. I followed the step mentioned in your book. I am using excel 2016.

Hi Shahzad, maybe try posting in our forum at http://www.excelguru.ca/forum as the example does work. If we can see what you've done, I'm sure we can point out what went wrong.