Locked lesson.
About this lesson
Learn tricks for consolidating your data so it's vertically contiguous (without blank rows) – for Tables, PivotTables, and Charts.
Exercise files
Download this lesson’s related exercise files.
Contiguous Data - Begin.xlsx63.8 KB Contiguous Data - Complete.xlsx
87.4 KB
Quick reference
Contiguous Data
Using Power Query to reshape data into a refreshable contiguous data set.
When to use
Often when you import data from another application, you’ll find that it contains blank or garbage rows. Since Excel’s most powerful tools require contiguous ranges to work with, we need a way to quickly convert our data to this format.
Instructions
Definition of a good “contiguous” data set
- A range that contains no blank rows or columns
- A range that contains a good header row
- A range that had consistent data types down each row of the column
- NOTE: Blank cells within a data set are acceptable, as long as it’s not an entire row or column
Importing data into Power Query
- Format the data as a table and give it a good table name
- Go to Data --> From Table/Range to pull the data into Power Query
Using Power Query to reshape data into a contiguous range
- Removing columns: Select the column(s) and press the DEL key
- Removing rows: Click the filter icon at the top of a column and uncheck items to remove
- Renaming columns: Double-click the column header and change the name
- Filling data down (or up): Right-click the column and choose Fill --> Down (or up)
- Replacing values: Right-click the column --> Replace Values and provide the data to search for and replace
- Define Data Types: Use the icons at the top left of the columns, or right-click and choose Change Type
Loading and Updating Power Queries
- Provide a good name before finalizing your query
- To Load your query:
- To a new table on a new worksheet: Go to Home --> Close & Load
- To a custom destination: Go to Home --> Close & Load --> Close & Load To…
- To update your query go to Data --> Refresh All
Hints & tips
- The fill command will only fill into areas that are null (not blank)
- Select contiguous columns by holding down the SHIFT key
- Select non-contiguous columns by holding down the CTRL key
- Data Types are not formats - Formatting is done in Excel, defining the type of data is done in Power Query
- There is no Undo in Power Query. To undo an action, click the x next to the step in the Applied Steps window (on the right side)
- If you build a Pivot Table off a Power Query table, you will need to hit the Refresh All button twice (the first updates the query, the second updates the Pivot Table.)
- 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.