Locked lesson.

Upgrade

About this lesson

Power Query is the best tool for consolidating your data so it's vertically contiguous (without blank rows or other garbage) – for Tables, PivotTables and Charts.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Contiguous Data.xlsx
64.7 KB Contiguous Data - Completed.xlsx
89.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.)
Login to download
  • 00:04 Here's a data set that has some issues, the biggest one, it's not contiguous.
  • 00:09 Look at all those blank columns that are actually showing up in between here.
  • 00:13 Not only that but things like Brakes by Hickey, the vendor name,
  • 00:16 really needs to be showing on the individual rows with the data.
  • 00:20 This data is really hard to work with, and the worst part?
  • 00:23 This was dumped directly from a major North American accounting system
  • 00:27 directly into my worksheet.
  • 00:29 So, if I want to build a pivot table against this,
  • 00:31 I have to do a bunch of clean up first, build my pivot table, and then next month.
  • 00:35 I'm gonna have to do the same manual cleanup, that's not cool.
  • 00:38 We need to find a better way and, we have in Power Query.
  • 00:42 What I'd like to do,
  • 00:42 is I'd like to turn this entire range of data here into a table.
  • 00:47 So next month I can just copy and
  • 00:49 paste this right on top of the table and refresh it.
  • 00:52 So let's do that first, format as table.
  • 00:55 We'll set it up as a table here and I don't have headers in place.
  • 00:59 So what's gonna happen when I don't check this box?
  • 01:01 Is it will put headers across the top called Column 1, Column 2,
  • 01:05 Column 3 and so on.
  • 01:07 Now it looks really ugly, but you know what, it look ugly before, so whatever.
  • 01:11 The big key, next month I can just paste my new data right here and
  • 01:14 it'll overwrite everything down below.
  • 01:16 Now that we have this,
  • 01:18 we should probably rename our table to something like SourceData.
  • 01:22 And go and pull this into Power Query from table arrange.
  • 01:27 Now, this is gonna pull our data in and the first thing that we're gonna look at
  • 01:32 is we can get rid of some of these columns.
  • 01:35 I don't need certain columns along the way here Column 1, for example,
  • 01:40 doesn't look it adds a lot of value, so I'm going to press the delete key.
  • 01:44 Columns 3 and 4, going to hold down control to select them.
  • 01:47 Column 6, 8, these guys all look like they're filled with null values,
  • 01:52 that's essentially equivalent to a blank.
  • 01:54 So, I'm gonna go scroll across here with my control key held and
  • 01:57 I'm gonna grab the individual columns I wanna get rid of.
  • 02:00 Plus A, delete.
  • 02:02 And just like that they'll all disappear,
  • 02:05 the first row here looks like it's actually the headers.
  • 02:09 So I'm gonna go to use first row as headers and
  • 02:12 pop those up to the top of my table.
  • 02:16 Column one doesn't have a header but I think this is my Vendor so,
  • 02:19 I'm gonna go and rename this by double clicking on it to make it by Vendor.
  • 02:24 Now, the next piece that I wanna do is I actually want to take Brakes by Hickey and
  • 02:28 Burc AC and Howell Drivelines and I want them to be associated with the data rows.
  • 02:32 And there's this really cool command, notice the thing that says, null here?
  • 02:35 Well, you can right click here.
  • 02:38 Fill and you can fill down.
  • 02:40 And what that does, it takes the data and it fills it anywhere that it's null
  • 02:44 until it runs into a piece of data so that's pretty cool.
  • 02:47 I've actually now put the correct vendor on each line.
  • 02:50 And at this point really all I need to do is get rid of all of the total and
  • 02:53 subtotal rows.
  • 02:54 You can see we have some here, every row that has that, has a null in here.
  • 03:00 So I can go and say, let's get rid of this, uncheck the null and
  • 03:05 it filters it out.
  • 03:07 So that's pretty cool, I have actually got a pretty good looking piece of data here.
  • 03:10 This doesn't look like it's really, well maybe it is a type, a bill,
  • 03:13 I guess an invoice.
  • 03:15 We've got a date, it has times on it, I don't need that.
  • 03:18 So we're gonna change this to just work as a date
  • 03:22 this looks like this is probably the invoice number so
  • 03:24 it shouldn't really be a number, it should probably be text so we'll change that.
  • 03:29 The account for accounts payable is fine, my amount and my balance, they're
  • 03:33 showing both as decimal numbers, hold down my shift key to select them both.
  • 03:37 And I can now right click and change type and say, let's change this to a currency.
  • 03:43 The only different really is that currency can hold four decimal places where
  • 03:47 a big list of decimals there can hold as many as we need.
  • 03:51 Finally, we'll give this a nice name like accounts payable and at that point,
  • 03:57 we got a nice looking list of transactions, I can say Close & Load To.
  • 04:03 And I can now go and say, let's put this on an existing worksheet, and
  • 04:06 we'll just go put it on the right side over here.
  • 04:10 And boom, just like that, Power Query is gonna go and
  • 04:14 load nine rows of data that's cleaned up into a nice simple table or
  • 04:19 I can then go and build a pivot table off of it.
  • 04:21 Next month when I get new data, all I need to do
  • 04:25 is delete the rows in this original table, paste the new data rows in there, and
  • 04:29 then do a nice data refresh all, and if I have a pivot table,
  • 04:33 refresh on top of it again, and I can reuse my existing business intelligence.
  • 04:37 So power query is useful for cleaning up nasty data sets and
  • 04:41 getting them into a perfect format.
  • 04:43 That's refreshable, so you can drive other things.

Lesson notes are only available for subscribers.

Gift this course