Use Power Query to do the Heavy Lifting

Quick reference

• 00:05 Up until now, you've worked on a sample dashboard in Excel.
• 00:09 Before you start writing your own,
• 00:11 I'd like you to consider how you're going to get your data into the dashboard.
• 00:17 In the examples that we've used,
• 00:19 we've effectively downloaded the data from Google Analytics.
• 00:22 I've added a Month column or a Date column.
• 00:26 And then we've copied the data into Excel and we've dragged it to add to the table.
• 00:31 But there is an easier way to do this entire process, and
• 00:35 that's by using Power Query.
• 00:37 Power Query automates the entire process, saves you time, and
• 00:41 reduces the chance of human error.
• 00:44 In this way,
• 00:45 you can spend more time turning your newfound insights into actionable steps.
• 00:50 So to set this up, you need to create a dedicated folder that you're going to
• 00:55 point Excel to to find your data.
• 00:57 In this folder, I've downloaded all my data.
• 01:01 I'm using consistent file names for each month.
• 01:05 I have a few files on standby to drag and drop into my folder once we've set it up.
• 01:11 So to set it up, let's go up to the Data tab,
• 01:17 > Get & Transform Data, and then Get Data > From File > From Folder.
• 01:25 And I'm going to browse to find the folder, or
• 01:28 to point Excel to the folder on my hard drive.
• 01:31 It can be on a Google Drive also if you want.
• 01:34 There it is.
• 01:36 And I'm going to hit OK.
• 01:38 So now it brings up this screen that shows the files in that folder,
• 01:43 there are four files.
• 01:45 So if everything looks good to me, I can go down and
• 01:48 click on Combine > Combine & Transform the Data.
• 01:54 Now, after I do this setup once, Power Query will do the work for me.
• 01:59 So the data should be on sheet one in every file.
• 02:04 Now, this screen pops up, and this is after Power Query editor,
• 02:08 it sits on top of Excel.
• 02:10 Excel's more or less incapacitated while this is open.
• 02:13 We can ignore this part here, we can just hit OK.
• 02:17 The important parts are coming up, it's the menu, the previous screen, and
• 02:22 the query settings screen that we'll see here.
• 02:25 Now, this is the query settings screen.
• 02:28 This is the area where you can change the name.
• 02:30 The default is PQ Data, Power Query data, and I'm fine with that.
• 02:35 What I want you to keep an eye on are the applied steps below.
• 02:38 What this does is it records the steps that we're doing.
• 02:42 This is what happened in the background so far to get up to this point.
• 02:46 As we work on it, you'll see that steps are recorded.
• 02:50 So why does it do that?
• 02:51 It's because this is going to repeat everything for you.
• 02:55 If you add new data, Power Query will repeat this so you will never have to.
• 03:02 So let's see what I've done here so far.
• 03:04 I have a column with my Source Name, that's the file name, and
• 03:09 then I have my normal columns as it is in the data.
• 03:14 And you'll see a button on each of the headers that indicates the data type.
• 03:21 And remember to check all that in the headers before you Close & Load.
• 03:26 First, I'd like to get a date on this sheet.
• 03:30 I'm going to use my Source Name column.
• 03:33 There's no other indication of date in the file,
• 03:36 so that's why I added the name like this.
• 03:39 I'm going to select that, left-click on the header, and
• 03:43 I'm going to click on Add Column.
• 03:47 From the From Text grouping, I'm going to click on Extract.
• 03:52 And I want the first characters, I want the first seven characters.
• 03:58 So look what happens.
• 04:01 2020_01, still not exactly what I want.
• 04:05 So while it's selected, I click on Extract again.
• 04:09 This time, I want the first four characters.
• 04:11 Now I have 2020.
• 04:12 All right, select First Characters again, Extract, select Last Characters and
• 04:18 two, and then we'll get the numeric value of the month.
• 04:23 So if we want to real quick, we just go up to the header, double-click,
• 04:27 we can rename this and just call it month right now.
• 04:31 And this column with first characters, 2020, should be the year.
• 04:34 And the next thing that we can do, we can see that month and year are both numbers,
• 04:38 so let's change the data type to reflect that.
• 04:41 Click on the little icon up there, change it to whole number.
• 04:45 And you'll see now it auto aligns to the right because Excel recognizes that as
• 04:49 a number.
• 04:50 Do the same thing for the Year column.
• 04:53 Select that, whole number, and now those are recognized as numbers.
• 04:58 And now we can get rid of this extra column, we don't need that anymore,
• 05:02 it won't affect the other two columns.
• 05:05 We can go back and we can also get rid of the Source Name column.
• 05:08 We just needed that to get the Month and Year columns.
• 05:11 So now it just changes the way that things are going to be shown in Excel.
• 05:17 Now, if you go over to the right, you can see that we have some extra steps.
• 05:23 If I click on one of those and hit Remove, you'll see the column is back.
• 05:30 Right-click > Remove, it goes away again, and that step has returned.
• 05:36 Now, if we go up to the File menu > Close & Load To, this will export as a table.
• 05:42 I want it on our existing worksheet, I'll put it at A3.
• 05:47 And there you go, this is your table.
• 05:49 Maybe if I change the format to something else,
• 05:53 maybe that looks a little more familiar.
• 05:57 Click inside the table and you can see the Table Design contextual tab up top.
• 06:04 Now, if I move to the right-hand side, I have my year and month.
• 06:07 I can look at the filter, I can see I have four months of data, that's 2,714 rows.
• 06:13 If I see I missed anything, I can double-click on the query, and
• 06:18 it opens up the editor again and I can make my changes, and it will record.
• 06:25 Now, the beauty of this is if I open up Explorer and
• 06:29 I add an extra month of data, I drag and drop.
• 06:32 Remember, the only thing I did with this is I downloaded it from Google Analytics,
• 06:37 I changed the name of the folder.
• 06:38 Drag and drop it into my dedicated file that Excel knows to look for.
• 06:43 It doesn't update anything in Excel yet, still 2,700 rows.
• 06:48 But right-click > Refresh, And now it has 6,200 rows.
• 06:56 If I click on the month filter, I see I have all my months, and
• 07:00 my table's updated.
• 07:02 This is the easiest way to update your table.
• 07:05 If you have pivot tables that links to this, remember,
• 07:09 a pivot table has to update separately from the table.
• 07:13 But you only have to update one pivot table in your charts and
• 07:16 your dashboard will be updated automatically.
• 07:19 And that's the easiest way to update data in Excel through Power Query.
• 07:24 So I hope you go and create some wonderful dashboards.
• 07:26 Thank you so much for watching.

Lesson notes are only available for subscribers.