Getting Data from Excel Tables

Quick reference

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