Writing Your Own Conditional Formulae

Quick reference

• 00:05 Sometimes in power query, we need to write our conditional columns manually.
• 00:10 Sometimes it's because we're trying to do more complex logic than is permitted by
• 00:14 the conditional column dialogue.
• 00:15 And other times, like if you're using the non subscription version of Excel 2016,
• 00:20 it's because you don't have the condition column dialogue at all,
• 00:24 since it came out much later than Excel 2016 release.
• 00:28 So for you, you'll have to write things manually, because you don't have a choice.
• 00:32 So lets go and take a look at how to do that.
• 00:35 We're gonna go and create a new query from File, and again, from Text or CSV.
• 00:42 This particular one we're gonna drill into our ghost goes data folder and
• 00:46 it's going to be located in the conditional logic.
• 00:49 It's called manual conditions in the same port.
• 00:53 This file is a different format or time sheet file.
• 00:57 You'll notice that it is tabbed delimited and this time we have
• 01:00 four different columns, that we need to deal with so we're now going to say edit.
• 01:05 As we look at the data in the preview, we can see that we have our worker names
• 01:09 associated with every line, we have our work date associated with every line.
• 01:14 So it looks like these first four rows are no longer required.
• 01:17 So we'll say Remove rows, Remove top rows.
• 01:20 And we'll remove the top four rows from the data file.
• 01:24 We'll then promote the first row to headers and we'll filter to remove
• 01:29 the number of records line that has the totals and sub totals in it.
• 01:36 At this point, we can look at things like changing dates with locale.
• 01:40 So we'll go and say using locale, change it to a date
• 01:46 and set this to English US because that is the version that we're actually gonna
• 01:51 be working with here that the data comes from.
• 01:55 We'll set the out to a time.
• 01:58 And now we're looking at the The hours.
• 02:02 The challenge we have here, though,
• 02:03 is that we want to extract the overtime portion from the hours.
• 02:07 So right here on John's line, where I've got 9.5, what I'd actually like to see is
• 02:12 1.5 because we have a maximum of 8 hours for the workday.
• 02:16 So naturally, you'd think well, this is a great place to go and
• 02:19 do a conditional column.
• 02:21 And if I were to look at this and say, all right, well, let's take a look and
• 02:24 see if hours is greater than.
• 02:28 And we'll go with eight.
• 02:30 Then in that case, what I really want to do, is I want to take, No.
• 02:35 I can only get one column and I can't modify this in any way.
• 02:40 Well, okay, so could we use a value and say instead,
• 02:44 the syntax for a column is to wrap it in square brackets and
• 02:49 we'll say equals hours minus eight or null.
• 02:54 In this case,
• 02:55 when I say okay, you'll see that I actually get that piece of text back.
• 02:59 So this plainly isn't going to work.
• 03:01 I'm gonna delete this because I need to do something different.
• 03:07 Now, what we need to do to write our own conditional logic columns is go to
• 03:12 Custom Column.
• 03:14 And what I'm gonna do is I'm gonna make a new column here called, OT Portion.
• 03:20 If you're used to working with IF formulas in Excel, this is going to feel both
• 03:24 very similar and yet horrendously different, all at the same time.
• 03:29 And the reason being is because when you are writing an IF statement for
• 03:32 power query you actually need to type in lower case I, F.
• 03:40 We can then make our check if the hours is greater than eight.
• 03:47 And what I'm gonna do is I'm gonna put a hard return in now.
• 03:49 This is completely unnecessary.
• 03:51 I just prefer to actually align things up with spaces and
• 03:54 actually set these on separate lines.
• 03:56 So see if the hours is greater than eight, than what we're going to do is we're gonna
• 03:59 take hours minus eight, we'll hit enter and
• 04:04 to close this out unlike what you saw in the conditional column dialogue.
• 04:09 We actually typed in else not otherwise.
• 04:13 So if the hours is greater than eight,
• 04:16 we'll take hours minus eight, else we'll type in, no.
• 04:20 Now at this point, you'll notice we have no syntax errors,
• 04:23 everything looks good and we can say okay.
• 04:25 And what you can see is that our OT portion shows up as 1.5 and 1.5.
• 04:31 So now I can go back and either write this manually or go through
• 04:35 the conditional column dialog here to say, let's go in with regular hours.
• 04:42 And this one can be if the OT portion equals no,
• 04:49 then at that point what we're gonna do is we're gonna take the original hours.
• 04:55 So these will come from the hours column.
• 04:58 Else, we can say we know that they must be at least eight
• 05:03 because otherwise there wouldn't be overtime.
• 05:06 There we go.
• 05:07 And now we have them both in place.
• 05:09 We can delete the regular hours column and
• 05:13 set both the OT and regular hours to decimal number and
• 05:18 finally or rename this to timesheet and go to home and
• 05:24 close and load and land it to the worksheet.
• 05:28 At that point, again, whenever the timesheet is updated,
• 05:31 all we need to do is come in here and hit refresh all.
• 05:34 And we'll now have the overtime and regular hours stripped out nicely.

Lesson notes are only available for subscribers.