Locked lesson.
About this lesson
Data often lives inside Excel tables. But even if it doesn't, Power Query will create one for you.
Exercise files
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.
Getting data from Excel tables.xlsx20.6 KB Getting data from Excel tables - Completed.xlsx
36.1 KB
Quick reference
Getting Data from Excel Tables
An overview of importing data from an Excel table.
When to use
When you have tabular data on which you would like to build a query.
Instructions
Getting started
- If not already done, format your data as a proper Excel table (CTRL + T)
- Ensure your table has a readable name (shown on the left side of the Table Tools Design tab)
- Create a new query --> From Table/Range
Transformation Tips
- Dates are stored based on the date serial number, so you never need to use Locale for these
- Dates do get a DateTime datatype applied
- Replace the DateTime datatype by clicking the calendar icon on the top left of the column --> Date
Changing the name of a table used by Power Query
- Table names are hardcoded into queries, and are not updated when you change the name of the table
- When you rename a query, it will break Power Query’s ability to refresh
- To fix a broken reference, edit the query, select the Source step and replace the name of the old table in the formula bar with the name of the new table
Hints & tips
- Because Power Query will create a table named Table1, it is advised to manually create and name your tables, rather than let Power Query apply its defaults
- There is no need to promote headers when a table in imported, because tables already have defined headers that Power Query uses
- You cannot have multiple tables in a file with the same name. If you try to use the same name multiple times, a value will be appended to the name to keep it distinct
- 00:05 Let's look at how to pull tabular data into Power Query.
- 00:09 Notice here that I have a beautiful set of tabular data.
- 00:12 It's a gorgeous header row there's no blank row between the headers and
- 00:16 the actual data itself and
- 00:18 we have consistent data types down each of the columns.
- 00:21 This data is perfect for an actual Excel table.
- 00:25 But the beautiful thing about Power Query is that I don't actually even have
- 00:29 to turn it into a table before I decide to go and create a new query against it.
- 00:33 I can just click somewhere in the center of the data and
- 00:37 then I can create a new query from table arrange.
- 00:40 And at this point, Power Query will actually go and setup the table for me so
- 00:45 I can go and say, all right that's perfect.
- 00:47 We'll say, OK.
- 00:50 It'll add the table style.
- 00:51 It'll pull into Power Query and away we're ready to go.
- 00:55 Now you'll notice that it has actually gone through and done a couple things.
- 01:00 We've got the source which already has headers in place here
- 01:04 unlike working with CSphere text files,
- 01:06 there's no need to promote header because the table style already has that defined.
- 01:12 The next thing Power Query does is it changes the types on the columns.
- 01:16 And at this point you'll see there is got most of them pretty good.
- 01:18 But I'm not really in love with the first one here.
- 01:21 And the reason being is because it's actually putting dates and times.
- 01:25 Now the dates are gonna be right.
- 01:27 There is no need to interpret these with locale because the underlying date serial
- 01:32 number already exists in Excel.
- 01:34 It's only when we're pulling in from an external source that we actually have to
- 01:37 convert it.
- 01:38 But now that it's in Excel, the date format,
- 01:40 the UC here will be the same date format that you use in your original settings
- 01:45 in your Windows Control Panel which is great.
- 01:47 But the problem is Power Query always also assigns times, and
- 01:51 the reason for that is because if you actually go and
- 01:55 put a fraction on your actual date serial numbers.
- 01:58 So let's say you put on 0.25, that would indicate 6 AM on that given day.
- 02:04 0.5, that's half way through the day so it's noon.
- 02:06 0.75, three quarters away, that's 6 PM.
- 02:11 So Power Query always puts a date time format on this.
- 02:13 I'm not in love with that.
- 02:14 I'm gonna go and change it to just date.
- 02:18 Again, don't need to use locale cuz it already knows the date serial numbers.
- 02:22 All I need to do is just replace this as current.
- 02:25 And I can now go and give this guy a nice little name here.
- 02:29 I could go and call this one something like sale summary for example.
- 02:34 And then I could go and hit Close and Load, and
- 02:37 load this out into a nice looking table.
- 02:40 Now, obviously it's very rare that I would ever just go and connect with table,
- 02:44 bring it in, rename something and land it to a table.
- 02:47 This is a demo about getting data from excel ranges not using them.
- 02:51 So we'll get into using them a little bit later, but for right now,
- 02:55 that tells me how to go and grab data from a range.
- 02:58 By contrast, if I wanna grab data from a table,
- 03:01 you'll notice we have a nice table here called Sales.
- 03:05 If I were to go and click somewhere inside this, and go and
- 03:08 create a new query from Table Arranged, the only real difference is that it
- 03:12 doesn't prompt me to create the table first, which would make sense.
- 03:16 So just select a single cell inside an Excel table, and we're good to go.
- 03:21 I'll convert this to date.
- 03:22 And I'm gonna show you now, as I Replace Current, this table has a name of Sales.
- 03:28 I brought it in, it's created a query called Sales.
- 03:32 And when I now go to hit, Close and Load,
- 03:35 you'll notice that it comes back and makes me my query called sales.
- 03:40 But it's actually named my new table Sales 2.
- 03:43 And that's because we can't have two tables with the same name.
- 03:47 Now let me talk about one of the things that I don't like.
- 03:51 When we went to the From Range and we clicked inside this area and
- 03:55 we said to get my data from this area,
- 03:57 it popped up a thing asking, would you like me to create a table?
- 04:01 One of the number one things that I believe is when you create a table
- 04:04 the first thing you should do is you should immediately go and
- 04:07 change the table's name, because right now we've got a table called Table 2.
- 04:11 This name shows up in our drop down list, so if I'm trying to find things right now,
- 04:16 if I wanna go to that Sales table, nice and easy you can pop over there.
- 04:20 But if I want to go to my other table, how do I know that this might be, say,
- 04:23 my inventory table or something else.
- 04:25 The name is terrible, so I always encourage people to change the name.
- 04:29 So let's pretend this one's called Inventory.
- 04:33 So I'm gonna change it here, and
- 04:35 remember the sales summary is coming off of that table.
- 04:39 So now you'll notice that it's all ready when I mouse over the preview,
- 04:43 telling me that it can't find Table 2.
- 04:45 And this is a challenge because the names of these tables get hard
- 04:49 coded into the actual Power Query.
- 04:51 So let me go right-click and choose Edit to open the sub and
- 04:55 show you what we have to do to fix this.
- 04:58 The first thing is we need the Formula Bar.
- 05:01 So on your View tab, choose Formula Bar.
- 05:04 As a matter of fact if there is a check box here, check it by default.
- 05:08 When I go over here back to my source step,
- 05:10 you can see that here we are, here is the name of the original table.
- 05:15 It's been hard coded into the code, it doesn't update when I rename things.
- 05:19 So I need to now go and change it manually in order to actually get it to update.
- 05:25 This my big bugbear with working with the way Power Query works.
- 05:29 If I had the ability when I was creating my new table, to name it right away,
- 05:33 that would be great.
- 05:34 But I don't.
- 05:35 So personally, I never use that feature.
- 05:38 I will always go into my raw unformatted data,
- 05:41 I'll go to the Home tab and I'll choose format as table first.
- 05:45 And then rename the table right away to make sure that it's got the correct name
- 05:50 before I pull it into Power Query and potentially get myself into an issue where
- 05:54 it breaks in future when I update the name.
Lesson notes are only available for subscribers.