The IF Function in Power Query

2014-08-27
Ken Puls
Categories: Excel, Power BI, Power Query

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.

58 thoughts on “The IF Function in Power Query”

Peter on August 27, 2014 at 10:08 am said:

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!
Ken Puls on August 27, 2014 at 10:12 am said:

Fantastic, glad it helped!
Avichal Singh on October 18, 2014 at 11:48 am said:

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!
Ram on April 16, 2015 at 5:58 am said:

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
Ken Puls on April 16, 2015 at 7:57 am said:

if test1 then if test2 then value-if-test2-is-true else value-if-test2-is-false else value-if-test1-is-false
Harry Cuntapay on April 28, 2015 at 9:27 pm said:

Hi Ken,

is there something equivalent to dax switch function in power query? thanks
Ken Puls on May 5, 2015 at 2:59 pm said:

I haven't stumbled upon one yet. Not to say that it doesn't exist though.
conor on August 7, 2015 at 11:19 pm said:

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
Ken Puls on August 8, 2015 at 7:33 am said:

I believe you're just missing a ) on your second line, right before the > character.
conor on August 8, 2015 at 8:08 am said:

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
Mark on December 14, 2015 at 3:49 am said:

Very helpful post - Thank you
Dinu on February 16, 2016 at 9:34 pm said:

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.
Ken Puls on February 17, 2016 at 8:45 am said:

You should probably review this article on replicating the SWITCH function in Power Query: https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/
Kelly on February 18, 2016 at 9:59 am said:

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???
Kevin on February 22, 2016 at 11:07 pm said:

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?
Kevin on February 23, 2016 at 12:24 am said:

if Value.NullableEquals([ATP Date],1) then "Missed" else "Achieved"
returns Achieved but an error for the dates which are blank
Ken Puls on February 23, 2016 at 9:13 am said:

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.
Ken Puls on February 23, 2016 at 9:21 am said:

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.
Kevin on February 25, 2016 at 5:07 am said:

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!
Roberta on February 29, 2016 at 12:03 pm said:

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
Ken Puls on February 29, 2016 at 5:55 pm said:

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!
Marcus on March 9, 2016 at 11:03 pm said:

Hi All

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

Thanks.
Ken Puls on March 10, 2016 at 2:17 pm said:

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.
Matt on March 23, 2016 at 12:13 pm said:

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
Ken Puls on March 23, 2016 at 12:28 pm said:

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.
Rosalyn Arntzen on May 9, 2016 at 3:09 pm said:

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).
Ken Puls on May 10, 2016 at 7:11 am said:

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).
Tony on June 23, 2016 at 7:40 am said:

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.
Ken Puls on June 23, 2016 at 7:44 am said:

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.
Tony on June 23, 2016 at 9:18 am said:

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

Thanks very much - great turnaround.

Tony
Chris Botha on February 16, 2017 at 4:23 am said:

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?
Ken Puls on February 16, 2017 at 9:15 am said:

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
Ovette Abejuela on April 1, 2017 at 5:40 pm said:

does these have to come all together if-then-else? can I only use if-then? without an else?
John on November 10, 2017 at 2:49 am said:

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.
John on November 10, 2017 at 3:16 am said:

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
Ken Puls on November 10, 2017 at 6:45 pm said:

Hi John, yes, that's exactly it. Power Query is case sensitive and requires lower case.
Shahzad Hanif on November 21, 2017 at 7:14 pm said:

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.
Ken Puls on November 27, 2017 at 12:41 pm said:

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.
Chris Dickson on October 11, 2018 at 5:33 am said:

Hi Ken The below doesn't work but it also doesn't seem the best way of solving this problem? Can you help? should I be using iif? or case or iI've also read about scope? I can do this no problem with a standard excel formula but struggling with power query any help appreciated

= Table.AddColumn(#"Added Custom", "Custom",
each if [Adjust]="monkey"
then if[Pear]>0
then if[Banana]>0
then if [Apple]>0
then ([Banana]+[Pear]+[Apple])/3
else if [Apple Juice]0
then ([Pear]+[Apple])/2
else if [Banana Smoothie]>0
then [Pear]
else 0
else if [Banana]>0
Then if [Apple]>0
then if [pear juice]<0
then ([Banana]+[apple])/3
else [Banana]+[apple])/2
else if [pear juice] 0
then if [Banana Smoothie]<0
then 0
else if [pear juice]<0
then [Apple]/2
else [Apple]
else 0
else 0)
Ken Puls on October 11, 2018 at 8:11 am said:

Hey Chris, that's a pretty big if statement for sure. If it's just a copy/paste, I'd check your first "else if" clause, as this doesn't look like you included an operator: "else if [Apple Juice]0" (needs a >, <, = or something) Honestly, I'd have to dig into to this to try and figure out what's up with it and if there is a better way. If you could create a sample file and post it in our forum at https://excelguru.ca/forums, that would allow us to see if there is a better way.
Dan Brooks on November 13, 2018 at 8:02 am said:

Hi Chris, Hopefully you found your issue by now,
If not I did a copy paste of your code and first thought is that you have a capital T in the "Then if" just after: else if [Banana]>0
which I think will throw the code into an error state.

It might also be worth breaking the code down into smaller chunks, after all you can delete the columns after the fact using the query editor to tidy it back up. I'm a huge fan of having massive formula's to do several tasks into a single column, but I have to admit trouble shooting after the fact is a lot more difficult.
Koen on November 15, 2018 at 6:37 am said:

Hi,

I'm struggling with zipcodes. If i'd like to get zipcodes that starts with "01" different from zipcodes that start differently, how do I get that fixed? I've tried the following, but I don't know if 'starts with' works.

if [Pickup ZIPCode] starts with "01" then "Region1" else "Region2"

I also need something like this:

if [Pickup ZIPCode] starts with "50" till "99" then "Region1" else "Region2"

But I don't think this is possible. Suggestions? You would really help me with this issue.
Ken Puls on November 16, 2018 at 9:47 am said:

Hi Koen,

Do you have the Conditional Column dialog on the Add Column tab? If so, you should be able to pick the column and then choose the "Begins with" option.

With regards to the 50-99 bit, I would duplicate the column, then go to Transform --> extract --> First characters --> 2 (on the duplicated column). Then you can write an if statement that is pretty easy (if [Column]>49 then "Region 1" else "Region 2"). You can even delete the duplicated column after you're finished and it will keep the results.
Leah on December 11, 2018 at 9:57 pm said:

Hi Ken,

Great article. Thank you.

Do you know how to use M language in Power BI to change the name of a custom column based on the value of another field? I need to check a user's language setting value and if it's Spanish I need a column to be named, for example, "Nombre", but if the language setting value is equal to English, then the column should be named "Name". Do you know how to do this?

Kind regards,
Leah
Ken Puls on December 12, 2018 at 8:01 am said:

Hi Leah,

Sure. Create a new (empty) table in Excel that has just the headers in both English and Spanish. (So a two column table with a blank row for data).

Import this table, remove the top 1 row so that it is just the headers. Go to Close & Load -> Close & Load To.. and set it to Connection Only
Right click this table in the Queries view and Append your original table.

At this point you'll have all your original data, as well as an extra column for the other language. (Whichever is in the data set will be full, the other will be full of null values.) Merge the English and Spanish versions of the columns and you're done.
Steve on January 28, 2019 at 10:16 pm said:

Hi Miguel Ji
Thank you for your wonderful post.
Please help me to restructure the names in list such that Initial which come in the beginning to the end.
eg: N P John -> John N P
Mr J K Smith -> Smith J K
D Jason C Morris -> Jason C Morris D
Thank you very much in Advance
Ken Puls on January 29, 2019 at 9:01 am said:

Hi Steve,

I think I'd post this one in the forum at http://www.excelguru.ca/forums as it's a bit complicated.
Pingback: Multi Condition Logic In Power QueryThe Excelguru Blog
Karthik on September 9, 2019 at 7:58 am said:

Hi Ken,
I am trying to populate Assembly Number against each part using If statement in power query. I am able to achieve this using xls function but not with power query. Can you please help.

Given Details
Col A Col B
Part # Attribute
0123 Assembly
4526 Part
9876 Part
9807 Part
5471 Assembly
3542 Part
2453 Part

Desired Output

Col A Col B Col C
Part# Attribute Assembly#
0123 Assembly 0123
4526 Part 0123
9876 Part 0123
9807 Part 0123
5471 Assembly 5471
3542 Part 5471
2453 Part 5471

This is what I used in excel in Assembly# column if(B2="Assembly", A2, C1)
Ken Puls on September 9, 2019 at 8:03 am said:

Hi Karthik,

I think you want to approach this a little differently in Power Query. Providing your data is in order:
-Create a new Conditional Column called "Assembly#" using this formula =if [Col B] = "Attribute" then [Col A] else null
-Right click on the new Assembly# column -> Fill -> Down

That should get you what you need, I believe.
Karthik on September 9, 2019 at 8:49 am said:

Thank you Ken. It worked. Why the below statement does not work

=if [Col B] = "Assembly" then [Col A] else {[Index]-1} [Assembly#]

I am trying to use the row index and trying to get previous cell value from the same Assembly# column.
Zoe on October 5, 2019 at 6:51 am said:

Hi Ken,
I tried to add a conditional or custom column with an IF formula, it works fine at first but after I refresh the query I encountered an error. How to fix this? When I try to change the column name it worked again, so I'm doing refresh>change column name>refresh>change column name>refresh to run this. If I didn't change the name, this error pop up.

[Expression.Error] The field "x" already exists in the record.
Ken Puls on October 7, 2019 at 10:03 am said:

Hi Zoe,

Are you creating a new conditional column with the same name as an existing column? That's kind of what it sounds like here...
Zoe on October 14, 2019 at 5:02 pm said:

Hi Ken,

I just followed this step by step tutorial and it seems working fine except for the New Column part?
Zoe on October 14, 2019 at 5:03 pm said:

https://www.youtube.com/watch?v=duNYHfvP_8U&feature=youtu.be this is the link for my above comment.
CHANDRA SEKHAR SURISETTY on November 1, 2019 at 11:54 am said:

I have a scenario to use the Fiscal month column and select the columns from 2 tables.
Fiscal month slicer:
if Full Fiscal month =06, then use Posting_Date from other table. can we do it
Ken Puls on November 2, 2019 at 11:23 am said:

Hi Chandra,

What I would do is merge those tables together, expand the columns you want to compare, run your IF test, and then delete the columns from the merge after you don't need them any more.
Pingback: Multi Condition Logic In Power Query