Locked lesson.
About this lesson
Power Query automates the process of getting data into a table and performing routine edits.
Exercise files
Download this lesson’s related exercise files.
Bonus Power Query.xlsx9.9 KB 2020_01 GA.xlsx
51.4 KB 2020_02 GA.xlsx
51.6 KB 2020_03 GA.xlsx
51.8 KB 2020_04 GA.xlsx
51.6 KB 2020_05 GA.xlsx
53.9 KB 2020_06 GA.xlsx
54 KB 2020_07 GA.xlsx
59.1 KB 2020_08 GA.xlsx
60.6 KB 2020_09 GA.xlsx
62.1 KB
Quick reference
Power Query
You can use a Power Query to add data to your dashboard.
There are many ways to do this. In this lesson, we pointed Excel to a created folder.
Preparations
- Prepare a dedicated folder on your drive where you are going to only add the data for the dashboard
- Download data from the source and save it using a consistent naming method, I used the date as a file name, because I needed something in the folder to give me an indication of the date
Back in Excel
- On a blank sheet, go to the data tab, get and transform data grouping, get data, from file, from folder and point Excel towards the dedicated folder
- Combine and transform the data
- The power query editor screen will pop up over the Excel screen
Power Query Editor
In general, always check your headings and the data type for each column
- Select the first column (the name of the sheet containing the date) and on the add column tab, from text grouping, extract button helps to get the first numbers to get the date
- Repeat the process until you have the columns you need
- Delete the columns you don’t need and change column headings and data types
- On the home tab, click close and load to and point Power Query towards the area in the sheet where you want the data table
To refresh data
- Download data from your source, change the file name to be consistent with your other file names
- Drag and drop the file in the dedicated folder
- Open Excel, right click on the table and refresh
Remember to refresh any pivots linked to the table separately, you’ll probably only need to update the first pivot and all pivots and charts linked to the table will update
Login to download- 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.