How to Make a Conditional If Statement in Power Query

In Excel, IF statements give you way to handle multiple scenarios. You can determine which result to return based on another value or input that a user makes. A common example is where a cell contains no value. You can create a formula to say if the value is blank, you return a result that is blank. And if it isn’t blank, you can perform a calculation. IF statements work similarly in Power Query although you can’t enter them in cells. Below, I’ll show you how you can create a conditional IF statement in Power Query and how you can use it in your data set.

In this example, I’m going to use data from the data.gov website on the Tuition Assistance Program. You can download the CSV data from here if you want to follow along.

Getting the data into Power Query

Once you have downloaded the data, the first step is to pull it into Power Query. For that step, just click anywhere on the data set and under the Data tab, click on the option to get data From Sheet:

The Get & Transform data tab in Excel.

The data is fine in the shape that it is right away so there is no need to make any changes when loading it into Power Query.

Creating a Conditional Column in Power Query

Suppose we wanted to just differentiate the data between whether the funding is related to the private sector or the public. You could do a pivot table but if you want to just have a column to pull in those amounts separately, you can create a conditional column. A conditional column works like an IF statement, only it is easier to set up.

One thing to remember with Power Query is if you want to just alter the current column, you want to stay on the Transform tab. But if you want to create a brand new column — which is what I’ll be doing in this example — you want to go onto the Add Column tab at the top:

The transform and add column sections in Power Query.

Once you are on the Add Column section, you will see an option for a Conditional Column right below it:

Add column options in Power Query.

Click on that button, and then you will see the following window:

Add conditional column window in Power Query.

For the column name at the top, I will call it Private Funding, since that is what I want to calculate. And the criteria is simple: I’m going to set it so that if the Sector Type column is equal to PRIVATE (this is case-sensitive in Power Query), then the output will be the TAP Recipient Dollars column. Otherwise, I want the value to be zero. Here is what that looks like:

Add conditional column window in Power Query with data filled in.

You’ll notice that on the output, value, and else fields, there is a down arrow. Clicking on this will allow you to switch between a column or a value. You can specify if you want to enter a value or reference a column. In this case, I want to reference an entire column if the criteria is met. And if it isn’t, I want to set it to a value — zero. For the operator, you also don’t need to look for an exact match, that too can give you various options:

Different operators in Power Query.

Once that is set up, I have a column called Private Funding in Power Query that is equal to the TAP Recipient Dollars if it is Private funding only. Otherwise, it is set to 0:

Private Funding conditional column set up in Power Query.

Now, I can repeat these steps for Public Funding and will now have a value in either private or public funding:

Additional columns created for private and public funding.

You may think this is a bit redundant but it saves having to create a pivot table if I wanted to do a summary (or a SUMIF function). One of the great things about Power Query is when I no longer need a column, I can just delete it. If I right-click on the original Sector Type column, there is an option to Remove from the shortcut menu:

Removing a column in Power Query.

This doesn’t impact my table because Power Query saves the steps I take and each time repeats the same order. This way it is safe to remove the unnecessary tab and avoid having redundant data that isn’t needed anymore.

Using the conditional column option is easy but if you want something more versatile to possibly include other Power Query functions, you can also use the Custom Column button, which I’ll cover next.

Creating an IF Statement Using a Custom Column

The option to create a Custom Column is also under the Add Column section:

Custom column option in Power Query under the Add Column section.

In this example, I will create a conditional column to look at if the TAP Level of Study column indicates at least a 4-year degree. By looking at the values there, we can see that the years are indicated in the first number:

Column in Power Query showing level of study.

If this was in a spreadsheet, I could just use the LEFT function to extract the first number. But in Power Query, I’m going to do it a little differently. Instead of the LEFT function, I am going to use the Text.Start function (these are also case-sensitive), which works the same way:

Text.Start([TAP Level of Study],1)

In this formula, I’m selecting the field, TAP Level of Study, and extracting just the first character from that. However, I still need to convert this into a number if I want to evaluate it as one. Next, I need to enclose this within the Number.FromText function. My formula looks like this:

Number.FromText(Text.Start([TAP Level of Study],1))

The next step is to evaluate it to see if the value returned is greater than or equal to 4:

Number.FromText(Text.Start([TAP Level of Study],1)) >= 4

If I am content with just getting back a series of TRUE or FALSE values, then I can stop here. But if I want to customize the values to say ‘YES’ or ‘NO’ then I will need to add to this formula by adding an ‘if’ statement at the beginning. I will also need to use the ‘then’ and ‘else’ keywords to tell Power Query what I want the results to be:

if Number.FromText(Text.Start([TAP Level of Study],1)) >= 4 then “Yes” else “No”

This is how it looks in the Power Query Custom Column window:

Creating a custom column formula in Power Query.

As you can see, going through the Custom Column approach will give you more flexibility as to what you can do with your conditional statements. While the Conditional Column is easy to use, it isn’t as flexible as you might need it to be. Now, when I click OK to create this column, I know have values that show either ‘Yes’ or ‘No’:

Column in Power Query that was created with Custom Column.

If you are looking for other Power Query functions, you can check out this page.

If you liked this post on How to Make a Conditional If Statement in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.


How to Remove Time From Excel Date

If your data contains date and time, and you only need the former, there are ways for you to remove time from the excel date. The first step, however, is in determining whether your data is in date format or whether the information is stored as text. Depending on which one it is, it will change how you will need to manipulate the data.

You can use the TYPE function to determine whether your data is in text or numeric format. The function evaluates a value and if it is numeric it will return 1 and if it is text the result will be 2. That will determine which path you need to focus on: converting a text date or just pulling the date values that you need. The latter is the easier of the two approaches.

Removing time from a date value

If the data is in date format, then it’s as easy as using the DATE function to pull out the fields you need. Let’s start with a date that shows the following:

2020-02-29 12:00:00 PM

It has more detail than we need with the time in there but it also has everything that’s needed—year, month, and day. The easiest way to pull out the date is using a formula as follows (where A1 is where the original data is):


The DATE function takes three arguments: year, month, and day. By pulling these values out from the cell that has the time, we’re effectively creating a new value that has everything except the time. Now, if you don’t want this to remain a formula what you can do is copy the cell with the date and not the time, and paste it as values. Now, you’re left with hard-coded date values that do not contain the time.

As mentioned, this is the easy part of the process. The more difficult one is if your date is stored as text and where the DATE function results in an error if you try the above calculation. Let’s take a look at how to remove time from an Excel date when it’s in text format.

How to extract the date from a text field

If the same value above was stored as text, the formula involving the DATE function would result in an error. To pull the values that are needed to arrive at a proper date value, we’ll need to parse the data. Parsing can be a bit complicated but when you’re dealing with text, it’s the only way around getting the data you need.

In the above example, the date fields were separated by hyphens but it could be that slashes are used as well. Ultimately, it doesn’t matter, so long as there is some pattern that separates the month, day, and year fields. We will still use the DATE function. But in order to put the correct values in, pulling out the key information is going to be the challenging part.

Let’s start with pulling out the month, since in a month-day-year format, it’ll be the first value and thus, the easiest to extract. Here’s how the formula to pull the month would look, again, assuming A1 is where the data is:


Since the month is the first value, we use the LEFT function to pull the characters at the beginning of the cell. A1 is the cell we’re looking at, and the second argument is the length of the string to pull. Here, we’re looking for the dash(-) within the cell and subtracting one character so that the dash itself isn’t included in the extraction. This formula would produce a value of ’02’ and correctly return the month value.

To get the day is a bit trickier since it’s between dashes. It’s still possible to extract it but the formula is a bit more complex and requires using the MID function. Here’s the function with just the first two arguments filled in:

=MID(A1,FIND(“-“, A1 )+1,

The first part of the formula specifies the starting point. For here, we’re again using cell A1 but this time we’re looking for the dash using the FIND function to indicate where the second value begins. We add one to this value to ensure that we aren’t starting at the dash. Here’s what the next argument looks like, for the length of the value:

=FIND(“-“,A1,FIND(“-“, A1,1)+1)-FIND(“-“, A1,1)-1)

Here what we’re doing is using the FIND function to search for the dash but this time we aren’t starting from the first position but are starting from where the first dash was found, and adding a one to that. Then we subtract where the first dash was found, and the difference is the length of the string. It’s a complicated, nested function but it does what we need it to do. The completed formula for the day looks as follows:


The last part is to extract the year. And because this comes after the second dash, we’re going to need to nest two FIND functions, not just one. You could try and always start from a certain number, for example, the seventh character if your date format will always by mm-dd-yyyy. However, using the FIND function ensures you aren’t taking any assumptions (e.g. they may be leading spaces). I also avoid hardcoding numbers in formulas whenever possible. Here is the formula that remains for the year function:


The nested FIND functions are needed to ensure that I’m starting to search for the dash after the second instance was found. I use the number four for the last argument because rather than making this formula even more complicated, I figure the year will either be two characters or four, and it won’t deviate. If your data contains two characters for the year, then you can just change the final argument accordingly.

That leaves us with this long formula to extract the date for the mm-dd-yyyy:


It’s a complicated one so it may be easier to just copy and paste it rather than trying to reconstruct it yourself. If your original date is in dd-mm-yyyy format, here is a formula for that:


This just involves flpping around the formulas to grab the month and day. If your dates use “/” instead of “-“, then you can just to a find and replace in the formulas above to replace all the “-” with “/” or whatever else your system may use. Regardless how the data is separated, you can adapt the formula to how your data looks.

As you can see, having your data in the right format can make this process a whole lot easier. It’s once you get into text that it becomes much more challenging in pulling the date out. And again, once you’ve got the data you want, copying and pasting as values will ensure you don’t have to keep both the old and new data together.

If you liked this post on how to remove time from Excel date, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.