Power Query: Alternative Way to Get Data Into Excel

Quick reference

• 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.