Contiguous Data

Quick reference

• 00:04 Let's look at another pretty nasty data sample that we have here.
• 00:07 This is an extract from a general ledger program.
• 00:10 And it's obviously not in good shape to be able to go and use it for
• 00:13 a pivot table because there's blank rows in here, there's blank columns,
• 00:17 there's all kinds of a mess.
• 00:18 So what we're going to do is we're going to use Power Query to turn this into
• 00:22 a contiguous block of cells.
• 00:24 Basically, what we want is we want a nice set of columns with no blank columns.
• 00:29 We want a nice set of rows with no blank rows,
• 00:32 and we don't want any subtotals in there, so it's ready to be unpivoted.
• 00:36 The easiest way to do this is to grab this particular output that we have here, and
• 00:40 what we're going to do is we're going to format it as a table to start with.
• 00:43 So we're going to go format as table, we'll pick our color, there we go.
• 00:46 We'll say that it does have headers because we do have some header rows
• 00:50 going across the top, and we'll say, okay.
• 00:52 Now, because it's converting it into a table, it looks pretty ugly.
• 00:55 Every column has to have a name, so this is where we see a column1, column2, and
• 01:00 whatnot that are being added to this.
• 01:01 But that's okay, we're not really worried about that at this point.
• 01:05 What we're going to do at this point,
• 01:07 we're going to just rename our table to data just to be a good practice here.
• 01:10 And then we're going to go and pull this data into Power Query.
• 01:13 So I'm going to go to the Data tab this time, and choose From Table/Range.
• 01:17 If you don't see that button on here,
• 01:19 you can also find it under Get Data > From Other Sources > From Table/Range, or
• 01:24 of course, just right click and choose to get data from table.
• 01:28 So here we go, From Table/Range, and we'll pull this into Power Query.
• 01:33 Now, things look pretty nasty here.
• 01:35 But what I want to do is I want to go through and
• 01:37 show you how we can use Power Query to start tearing away the data here.
• 01:41 What you'll notice is that we have a bunch of columns that we don't
• 01:44 appear to need, okay?
• 01:45 So we've got this column1, I'm going to hold down my Ctrl key here and
• 01:50 select the Type column.
• 01:51 And I keep holding down the Ctrl key and grab the Date column.
• 01:55 And I'm going to scroll across a little bit more here.
• 01:58 And I'll hold down my Ctrl key again and click on the Num column,
• 02:02 the Account column, all the columns that appear to have values in them.
• 02:06 Once I've got those, I'm going to choose right click,
• 02:09 and I'm going to choose Remove Other Columns.
• 02:11 And what you'll see is that it gets rid of all of those garbage columns that
• 02:15 were now down to just the ones that have data in them.
• 02:17 Now, one of the big things you'll notice is that when we're in Power Query,
• 02:21 we don't see blank cells, we see the keyword null in italics, okay?
• 02:25 And this is actually kind of useful for
• 02:27 us because it tells us that these cells are empty and we can do things with them.
• 02:32 In particular, one of the things we can do here is I want to take a look at
• 02:37 the first column that we see here, where we've got Brakes by Hickey,
• 02:42 and Burc AC, and Howell Drivelines.
• 02:45 The reality is that this is the company name or
• 02:47 a vendor name that should be associated with the next two lines of data.
• 02:51 Well, here's the cool thing.
• 02:52 When you have a null here,
• 02:54 we have the ability to fill Brakes by Hickey into these null cells.
• 02:58 Anywhere that we see a null, we can actually fill into it,
• 03:00 where we've got text, it won't overwrite it.
• 03:02 We do that by right clicking on Column1, and choosing to fill down.
• 03:08 And what you can see now is Brakes by Hickey fills in all the null cells.
• 03:11 Then it hits data, so it doesn't continue.
• 03:13 Burc AC had a null cell below it, it fills in, Howell Drivelines, there we go.
• 03:17 So that looks pretty cool.
• 03:19 The next thing that we going to do is we going to say, well,
• 03:22 let's rename this because Column1, that's kind of a gross name.
• 03:25 So we'll double click, call it Vendor.
• 03:29 I then want to get rid of all of these rows that have nulls in them because
• 03:32 they're all just looking like garbage here.
• 03:35 So what we'll do is we'll go and filter and say, remove all the nulls.
• 03:40 And we've now got a much shorter data set.
• 03:43 The last thing to do with this before we load it out, and
• 03:46 there's two things I want to do here.
• 03:47 The first is set the data types.
• 03:50 So I'm going to go and set this one to Text.
• 03:52 The Type can be Text.
• 03:54 The Date can be just a Date without any times on it.
• 03:57 Our number, this is actually an invoice number, so
• 03:59 I'm going to set this one actually as Text, Account will be Text.
• 04:03 And I always think it's a good idea to go back and
• 04:05 reset all of your data types at the end.
• 04:07 This one, I'll set as a Currency, and Balance,
• 04:10 I'll set up as a Currency as well.
• 04:12 The final thing I'm going to do is I'm going to rename my query here, and
• 04:16 I'm going to call this one Output.
• 04:19 And with that in place, I'm now going to say, Close & Load To.
• 04:23 And I'm going to load this to a table right over here beside the data set,
• 04:27 and we'll say, OK.
• 04:29 And you can see, once it loads, just how nice and clean that is.
• 04:35 And if I ever need to make any changes to it,
• 04:37 I can just come back to the Queries and Connections pane.
• 04:40 Remember that can hide here under Data > Queries and Connections,
• 04:44 double click on it to edit it and walk through the steps.
• 04:47 And if the data ever gets updated, I can just hit the Refresh button to get a nice
• 04:51 new table here that I can use to feed my charts and pivot tables and other things.

Lesson notes are only available for subscribers.