Basic Conditional Logic

Quick reference

• 00:05 In this video, we're gonna demonstrate how conditional logic works in PowerQuery.
• 00:10 We're gonna start by creating a new query, from File, from Text or CSV.
• 00:14 And we're gonna bring in a time sheet file that we can find underneath
• 00:19 our GoSkills data folder in the conditional logic folder, and
• 00:23 it's called basics.txt, and we'll say import.
• 00:29 This will bring us to the preview window, like normal,
• 00:32 it shows us that it's picked it up as a tab delimiter file.
• 00:35 We're gonna now go, and say Edit, and take a little closer look at this data.
• 00:41 Now the first thing you'll notice is that the dates that are showing in rows 2 and
• 00:46 3, they actually exist already on every single row for
• 00:50 the individual time sheet records.
• 00:52 So that means that the first four rows here don't really add us a lot of business
• 00:56 value, so let's go and get rid of them.
• 00:58 We'll remove top rows and we'll remove the top four rows.
• 01:04 The next row looks like it's headers, so we'll promote those,
• 01:08 we'll click the Use First row as headers button to move them up.
• 01:12 And now, we're going to filter out of the Work Day column and get rid of the stuff
• 01:16 that we can get rid of, which is really only the Number of Records line.
• 01:20 And the reason being is because when we serve this into a pivot table,
• 01:24 we don't need totals or subtotals because the pivot table can generate those for us.
• 01:29 The problem we have, though, now, that we're looking at,
• 01:33 is we have these lines for contingent workers.
• 01:36 And next to those, we can see John Thompson and Bob Johnson.
• 01:41 The problem is, we need their names associated with
• 01:44 every one of their date records, but how do we get them there?
• 01:48 So, what I'm gonna do first is I'm gonna say you know,
• 01:51 let's take a look at the structure of this.
• 01:53 We've got a couple of different ways that we can make this work.
• 01:56 One of the commonality is that every time we see the word contingent worker
• 02:00 in the work day column, we can see that we actually have the employee name.
• 02:04 Also, we can also see that if we have a null in the regular hours,
• 02:08 it would also appear that we have their name.
• 02:11 I don't really like keying off of a null inside of a numeric column,
• 02:14 it's something that I just don't trust about that.
• 02:17 But I will say, that when we've got some nice text like this, that seems to be
• 02:21 machine generating consistent that looks like a good place to start.
• 02:24 So what I'm gonna do is I'm gonna go into the preview, and
• 02:27 I'm gonna mouse over this, and I'm gonna press Ctrl + C to copy it.
• 02:31 Because now, I'm gonna go to Add Column, and I'm gonna add a conditional column.
• 02:38 In the conditional column dialog, I'm gonna give my new column a name,
• 02:43 and I'm gonna call it Worker.
• 02:45 This is an expression builder that will allow me to build up so that I can
• 02:50 say if work date equals, I can come over to the value column and press Ctrl +l V.
• 02:56 So if it equals contingent worker, the reason I copied it,
• 02:59 cuz that's a hard word to spell and
• 03:01 I wanna make sure that the colon on the end is correct, and everything is good.
• 03:05 If I don't wanna do this entire thing,
• 03:08 I could also change this to say what about if it begins with and
• 03:11 I had the word contingent or even CO should be enough for me there.
• 03:17 Unfortunately, while I can change to choose, enter a value, or
• 03:21 select a column, there's no way for
• 03:23 me to actually pick off a drop down list of the valid values that are in my column.
• 03:26 That's kind of a shame it'd be really cool if there was.
• 03:30 So now, let me check my logic, if the work day equals contingent worker,
• 03:34 then what would I like to do?
• 03:36 Well, if it equals contingent worker here, I want the value from the out column.
• 03:40 So, what we're gonna do, instead of saying ABC123, which would give us text,
• 03:44 what we're gonna do, is we're gonna choose to select column.
• 03:49 And then, we get the drop down list to say give me the out column.
• 03:56 After that we could add more rules if we need to but
• 03:59 in this case we really don't need to.
• 04:01 So I'm gonna come back over here click the ellipse and say Delete.
• 04:05 You've notice that I could build an else if statement which is kinda nice.
• 04:09 What I'm really interested in is the otherwise piece here.
• 04:12 This is the rule that's gonna trigger If my first column does not equal
• 04:16 on that row contingent worker, like in the case of all my dates.
• 04:21 What I'd like to put in instead is null, this is a keyword, it's all lower case,
• 04:26 and what that does is it essentially fills the cell with an empty style value.
• 04:31 And we can say, OK, and you'll noticed that what we get is John Thompson
• 04:36 with a bunch of nulls underneath it, and then Bob Johnston with a bunch of nulls.
• 04:40 The reason why this is important is because we have a really cool command at
• 04:44 fills into null areas called fill.
• 04:46 So if we wanna move John above, we call fill, and we say, right click,
• 04:52 Fill, down, and the way fill works, is he fills into null areas.
• 04:56 Not blanks, just nulls, until he finds a piece of data that exists and
• 05:01 then he stops.
• 05:02 So as soon as John runs into Bob, they just hang out together.
• 05:05 They don't actually, John's not going to overwrite Bob and
• 05:08 that's kind of important.
• 05:09 With that in case, now I can look at it and say all right, that's cool, now that
• 05:13 we have that we don't need the contingent worker line anymore, I'll filter him out.
• 05:18 And in this point the only thing that's left to do is maybe reorder
• 05:23 things if I want my worker over here, and set the data types.
• 05:28 So we'll set it to Text, my work date, I'll change naturally using Locale.
• 05:35 We'll flip this out to a date, pressing F to get to English US
• 05:39 because that's where the data's coming from and we'll say OK.
• 05:44 We'll set our time, And we'll set our regular
• 05:49 hours through expenses to right click, Change Type> Decimal Number.
• 05:55 And at that point we've got a beautiful list of time sheet
• 05:59 transactions that we can now say Home> Close and Load.
• 06:03 And every time that file gets updated you just have to save it over the last one and
• 06:07 hit Refresh to bring it in.

Lesson notes are only available for subscribers.