Locked lesson.
About this lesson
What are the basics you have to check when you use Power Query? We learn how to get data into Excel in a much more effective manner than copy and paste.
Exercise files
Download this lesson’s exercise file.
Bella's Garden Design Debtors ledger.xlsx33.2 KB
Quick reference
Power Query
Learn to clean data and import into Excel using Power Query.
When to use
Use Power Query if you often need to get the same date into Excel and adjust it along the way before the data is useful.
Instructions
- Where to find Power Query in Excel
- Power query can be found on the Data tab, Get & Transform data, Get data button
- You can import data from various different places and formats
- Inside Power Query
- Elements of the Power Query screen
- Ribbon menu
- Query Settings and recorded steps
- Data table preview
- Remember, the Power Query does not change the original data, it just adjusts the data before the Excel upload
- Elements of the Power Query screen
- Working in Power Query:
- Get rid of top rows
- Make the next row headers
- Right-click and delete to get rid of columns
- Double click the name of the column to change
- To fill the range with a name that should fill the column, right-click and fill down
- Check and adjust the data types by clicking on the buttons next to the headers
- Steps are recorded as you go, if you make a mistake, just delete the step, there is no other undo alternative
- If you filter an item out in Power Query, the item will not appear in the Excel data, filter out all blank rows (<none>)
- Close and load
- 00:04 In this example we have a debtor's ledger as it was exported from an accounting
- 00:09 system.
- 00:10 And I want to use the transactions from this debtor's ledger in
- 00:12 a different report.
- 00:14 You can pretend that everything above this dark gray line is the information
- 00:19 that we're going to work on.
- 00:21 And the rest we're going to add later for illustration purposes.
- 00:26 So we can do all of this with normal formulas in Excel, that’s not a problem.
- 00:30 But if we're going to do this often, and
- 00:32 if we want to be consistent in our application of formulas, and
- 00:36 we don't want to forget any steps, it's best to use Power Query.
- 00:41 Power Query sits on the Data tab under the Get & Transform Data grouping.
- 00:46 You can get data from lots of different sources and
- 00:49 this example has been imported to Excel.
- 00:52 So I'm going to choose this From Table/Range option.
- 00:58 So where's the data for my table?
- 01:00 Well, I'm going to include the headings of this report just to show you what
- 01:04 Power Query can do.
- 01:05 And I'm not going to check off My table has headers either.
- 01:09 So we hit Ok.
- 01:11 And Excel opens up Power Query on top of the normal Excel and
- 01:15 it converts your original range into a table.
- 01:20 So there's my Power Query.
- 01:22 In Power Query you'll see a ribbon across the top and
- 01:25 you have Query Settings on the side.
- 01:28 Let's change the name of our query,
- 01:31 we'll call it debtors and transactions or Debtors_TX.
- 01:37 Now, Power Query records every step that we do.
- 01:41 If we ever want to undo something about our query you just go to
- 01:45 the steps on the right and delete the one that shows up in the list.
- 01:50 So we have a data table preview and we have a list of all possible queries
- 01:56 in the document, there are no others so we can ignore that right now.
- 02:01 Our data table preview shows the data as it would be if we exported
- 02:05 it back to Excel right now.
- 02:07 All these null values are the blank cells in Excel so let's get this fixed up.
- 02:13 You'll see here at the top we have our report name.
- 02:17 The first three rows in this dataset are basically useless then right now so
- 02:21 let's get rid of those.
- 02:22 In the Home tab, under Reduce Rows, we click on Remove Rows, and
- 02:27 then Remove Top Rows.
- 02:29 And I want to get rid of the top three rows.
- 02:32 All right, perfect, those are gone.
- 02:34 Now, you'll see that the top rows we're left with are the actual headings that we
- 02:38 want to use.
- 02:38 So on the Home tab, under the Transform grouping,
- 02:42 click on Use First Row as Headers.
- 02:45 And this promotes that first row as headers.
- 02:49 Now you'll see we have a bunch of null columns that's just null all the way down.
- 02:55 Let's select those and delete them.
- 02:58 And we can do multiple ones here we select and then hold down CTRL and click.
- 03:06 It's just left click to select each one, but
- 03:09 keep holding the CTRL button as you do this.
- 03:12 Select all the columns, column 7, 9, 11,
- 03:16 it’s pretty much every other column was added in to make things look pretty.
- 03:22 And so then we right click and select Remove Columns.
- 03:27 Now all those extra columns are deleted.
- 03:30 Now in this first column we have the debtor name.
- 03:32 Let's change the name of the column to Debtor.
- 03:37 That's much better.
- 03:39 And we need the debtor name next to every transaction.
- 03:42 So right click while the column is selected, Fill, and then fill Down.
- 03:49 This fills the debtor name in all the blank cells below and
- 03:52 it will stop as soon as it hits a cell that has a value in it.
- 03:56 If I had a longer list of debtors it would do that for the whole entire list.
- 04:01 Now I have two useless rows, one at the top and one at the bottom.
- 04:06 To get rid of those, click on this button that looks like the auto filter button and
- 04:13 you deselect the null values, and then hit OK.
- 04:18 And now we're left only with valid transactions.
- 04:22 So let's review what we have in our columns.
- 04:25 This column here is called Type and it is text so that looks good.
- 04:30 The Date column has date and time in it, but we don't need both, we actually
- 04:36 just want date so click on that column and change it to date only, much better.
- 04:41 The next column is the Document Number, but
- 04:44 that should be text, it doesn't have to be a number.
- 04:48 Then we have Accounts Receivable, that's the contra account,
- 04:51 we can actually delete that column, we don't need that.
- 04:54 And then I have Amount and Balance.
- 04:57 So select Amount, then hold down SHIFT and
- 05:00 click to also select Balance, we want to change both columns to Currency.
- 05:06 There we go, that's much better.
- 05:09 So you see the steps on the right have been recorded,
- 05:12 everything we've done is listed as a step.
- 05:15 If you decide you don't want those last two columns as currency and
- 05:18 you want it back to being just a normal decimal,
- 05:21 you can undo that last step by clicking on the red x.
- 05:23 And then it changes back to the decimals.
- 05:27 Okay, great, so we're ready to close and load this now.
- 05:31 That's how we finish up, we click on Close and Load and it loads the query
- 05:36 into a new sheet automatically, that’s what this Sheet6 is here.
- 05:42 That is my query name on the right and
- 05:46 my random data is now in this table format.
- 05:50 Now, later on if I want to add all the rest of that data to this,
- 05:54 let's get the new data in.
- 05:57 I'm going to select all the columns and the rows that I want to add and
- 06:02 then I'm going to drag and drop it right under my table.
- 06:06 Now, the error message that we are going to get with Excel here is warning me
- 06:09 that we’re about to paste the new data over the headings that I don't need.
- 06:13 So that's fine, we hit OK.
- 06:16 And if I scroll to the bottom I see the sizing handle is now extended to the rows
- 06:20 that I just added so that's been included.
- 06:22 But if we go back to Sheet6 we see the Power Query results have not been updated.
- 06:27 So we just go up to the Data tab and hit Refresh All.
- 06:30 And now the Power Query has refreshed.
- 06:33 And all my new data has been added without any issues, or problems, or
- 06:38 any other formulas, or any extra work done on my side.
- 06:42 So that is how Power Query works.
Lesson notes are only available for subscribers.