Locked lesson.

Upgrade

About this lesson

Let's be honest, a huge amount of the world's data lives in Excel. We need to be able to pull these data sources into our Power Pivot models as well. In this lesson we will show how to collect data from Excel's Table object.

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

The general process is:

  • If not already done, format your data as a proper Excel table (CTRL + T)
  • Give your table a logical name (in the box on the very left side of the Table Tools --> Design tab)
  • Create a new query --> From Table/Range
  • Perform any transformations required to the table
  • Click the Close & Load To... button
  • When the Import Data dialog opens, select Only Create Connect --> Load This Data to the Data Model --> OK

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
  • When you build relationships between two tables, the data type(s) of the related column(s) must be the same in both tables (text, whole number, date, etc.)
Login to download
  • 00:04 In this video, I would like grab a little bit more data to work with.
  • 00:08 Currently, we have a transactions table,
  • 00:10 but I'd like to also be able to slice this in some different ways.
  • 00:13 And one of those ways is by going to the Departments tab.
  • 00:17 I'd like to actually be able to slice by my department name.
  • 00:20 As it stands right now, if I were to go up and show the queries pane at the moment
  • 00:25 and mouse over the transactions table, you can see that we have department numbers,
  • 00:30 but we don't have department names listed here at all.
  • 00:33 So having a department name would be a really nice thing to do.
  • 00:37 Now the first step before you do this is, you want to take a look at your table,.
  • 00:42 And if it looks like a table, and it's not an official Excel table,
  • 00:46 which as we can see right now, it's not, there's no Table Tools Design tab,
  • 00:50 we want to turn it into an official Excel table.
  • 00:53 Now, interestingly enough, we can actually create a Power Query against a range.
  • 00:59 And if it doesn't have a table on it,
  • 01:02 it will automatically apply a table style, I never ever use that feature.
  • 01:07 And the reason that I don't is because it won't let you name your table.
  • 01:11 So it creates a table called table 1 and pulls it into the model.
  • 01:15 But that means, that's the name of the table inside Power Pivot and then fixing
  • 01:19 the naming is actually yeah, a little bit dicey, so I don't wanna do that.
  • 01:24 What we're gonna do is we're gonna set up our table first,
  • 01:27 we'll say home, we go to Format as table.
  • 01:30 I'm gonna make this one blue again, And we'll say it as headers.
  • 01:36 And now again, the most important thing you can do after creating your table
  • 01:40 is name the table with a descriptive name.
  • 01:43 So I'm gonna go with TEPTS for departments.
  • 01:47 I'm hoping one day that the Power Query team adds the ability to name a table
  • 01:51 right in that original table creation dialogue.
  • 01:55 Because at that point,
  • 01:55 then we could just use Power Query to say get data from table and it will allow us
  • 01:58 to define the table right away, the name, but unfortunately, it doesn't.
  • 02:02 So for right now, we create our table name first, we give it a name, and
  • 02:06 now we can go and we can create a new Power Query.
  • 02:09 So again, in Excel 2013, you're gonna go to the Power Query tab.
  • 02:12 In Excel 2016, you're gonna come to the Data tab, and
  • 02:16 you're gonna kick off a new query, or you're gonna go to get data to create
  • 02:20 a table, From other sources, from TableArrange.
  • 02:29 This will pull the data into Power Query, and
  • 02:32 you can see the steps that are happening on the right-hand side.
  • 02:35 It says source, which connects to the original Excel table,
  • 02:39 notice the ABC123 data types, this isn't any, it's undefined.
  • 02:44 So then power query says, well, I'm gonna change the data types, and
  • 02:48 they look like whole numbers, and they also look like text.
  • 02:52 Now at this point, it becomes kind of important to check something,
  • 02:55 because when we make relationships between two tables, much like when you VVLOOKUP
  • 03:00 between two things, they have to be the same data type.
  • 03:03 So, we're gonna go and just expand the queries navigator pane on the left,
  • 03:07 and we'll take a quick look at our transactions table.
  • 03:10 And in our transactions table, we'll notice that the department name is text,
  • 03:15 but in our department's table, it's been set to whole number.
  • 03:19 We need those to be consistent.
  • 03:20 So I'm going to change this.
  • 03:22 I'm gonna go and say, all right that's cool,
  • 03:25 we're gonna force this one to also be text.
  • 03:27 Because I happen to be on the step where the data type is being changed,
  • 03:31 the changed type is up here, it ask me, where I like to replaced current or
  • 03:36 add a new step?
  • 03:37 And in this case, I would like to replace the current step, I don't wanna set to
  • 03:41 a number, I'd rather have it set to text and it will change that for me.
  • 03:47 I've now got to make my determination as to where I want to store the table,
  • 03:50 which naturally is gonna be Power Pivot.
  • 03:53 So to keep things organized, I'm going to left click and drag my departments
  • 03:58 table into the data model folder, there we go, that looks good.
  • 04:01 Although to be fair, this does not actually decide where it gets loaded.
  • 04:05 This is just an organization step inside the query editor to keep everything
  • 04:08 grouped nicely.
  • 04:10 To set where I want this loaded, I need to go to the bottom half of Close & Load and
  • 04:15 choose Close & Load 2.
  • 04:17 And thenm I'm gonna choose Only Create Connection, and
  • 04:21 add this to the data model, we'll say OK.
  • 04:25 You'll see that it loads, it creates things, and there we go,
  • 04:28 we've got eight rows loaded, I can get a nice little preview of it.
  • 04:32 And if I want to check things out, I can go to Manage Data Model on the Data tab or
  • 04:36 I can go to Power Pivot, and go to Manage, pop open the Power Pivot window.
  • 04:41 And down in the bottom, you'll see that not only do I have a Transactions table,
  • 04:45 but I also have my Departments table, that has this information in it ready to go.
  • 04:50 So this is pretty cool.
  • 04:51 I've now got a Departments table and I've got a Transactions table.
  • 04:55 There's only one more table that I need for my solution and that's my budgets, so
  • 04:58 we'll go take a look at getting that in the next video.

Lesson notes are only available for subscribers.

Gift this course