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.
Multiple versions of this lesson are available, choose the appropriate version for you:
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
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.
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.)
Lesson notes are only available for subscribers.