Locked lesson.

Upgrade

About this lesson

This lesson will explore how to use modern techniques (Power Query) to collect and reshape your data before loading it into the Power Pivot Data Model.

Quick reference

Getting Data from Databases

An overview of importing data from a database.

When to use

When you want to bring data from a database into Power Query and load it into Power Pivot.

Instructions

The general process is:

  • Begin a new query --> Get Data --> From Database --> choose your Database
  • Locate the database you want to use and log in (if necessary)
  • When the Navigator dialog opens, select the desired database and choose Edit to launch the Power Query Editor
  • Rename the query in the Query Settings pane on the right
  • Remove any columns that are not needed by one of the following three methods:
    • Selecting the column and clicking Remove Columns
    • Selecting the column and pressing the DELETE key
    • Right clicking the column and selecting Remove
  • 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

  • If your version of Excel contains a From Access button directly on the ribbon, DO NOT use it. This is an old legacy connector that will be hidden in Excel 2016 and higher
  • Quickly replace values in an entire column by:
    • Right clicking the column --> Replace Values -->
    • Enter current value --> Enter the value to replace it with --> OK
Login to download
  • 00:05 Lets go and get some data for our Power Pivot model.
  • 00:08 But in order to do this, it depends on which program you're in,
  • 00:11 how you're gonna get there.
  • 00:13 So if you're in Excel 2013 you'll have a big Power Query tab up here,
  • 00:16 I want you to click on that.
  • 00:18 If you're in Excel 2016, you're gonna go to the Data tab.
  • 00:23 Now depending on the version of Excel 2016 you're on, your buttons may look the same
  • 00:27 as mine on the left hand side, and here's the key thing I want you to recognize.
  • 00:31 On the very left-hand side of my tab, it says Get Data.
  • 00:35 If yours does not, if it has a button that says something different, then you need to
  • 00:38 look a little bit further across, somewhere around this sort of stage here.
  • 00:42 You'll find a button called New Query, and this is really important.
  • 00:46 Whenever I call out New Query in Excel 2013,
  • 00:49 I want you to go to the Power Query tab.
  • 00:51 In Excel 2016 you're gonna come and you're gonna click on either New Query,
  • 00:54 if you have that older button, or Get Data.
  • 00:57 And what that's gonna do is it's gonna expose the menu of different items.
  • 01:01 In Excel 2013 this will be right on the ribbon, for
  • 01:04 Excel 2016 it's buried under either that New Query or Get Data button.
  • 01:07 What I'm looking for is from Database, and I'd like from Microsoft Access,
  • 01:12 and I wanna be really clear on this.
  • 01:14 If you see a from Access button
  • 01:19 on your ribbon that is not under one of these guys here.
  • 01:22 This is the wrong one, that's the legacy connector.
  • 01:25 We want the one from the Power Query tab, or
  • 01:28 that's hidden on the Data tab under Get Data or New Query, okay?
  • 01:31 We do not want to see the button that used to live right around in this area.
  • 01:35 It's very old, and we don't want that one.
  • 01:37 This one, when we click it, should take us to browse for our file.
  • 01:42 Now, you can see I've already browsed for the file once.
  • 01:44 It's in my GoSkills data that I downloaded.
  • 01:46 I'm gonna click on Mulligans, and I'm gonna say Import.
  • 01:52 If we're using the proper Power Quesry connector,
  • 01:53 you will be taken to this navigator window.
  • 01:55 If you're the old one, you won't, so that's your first clue.
  • 01:59 What I'm gonna do is I'm gonna click on Transactions, and you can see that I get
  • 02:03 a nice little preview of the table, and I can see what's actually going on in here.
  • 02:07 This looks pretty good, but one of the things I know about every data source that
  • 02:11 I've ever dealt with is that it needed to be modified in some way.
  • 02:14 And true enough, I can see that there's this column here called ID that has values
  • 02:18 that I don't need.
  • 02:19 They're just row numbers, and that doesn't really add any business value.
  • 02:22 So I'm gonna choose Edit.
  • 02:25 And this will launch me into the Power Query editor where I have a huge amount of
  • 02:29 tools at my disposal to actually change and manipulate and clean up the data.
  • 02:34 So that it comes in in a nice tabular format that's perfectly ready for
  • 02:37 Power Pivot.
  • 02:39 On the right hand side,
  • 02:40 under Name, you'll notice that we have it called Transactions.
  • 02:43 This has inherited the table name, if I don't like it I can change it.
  • 02:47 Transactions is fine for me right now.
  • 02:49 I've got an applied steps window and you're gonna see that things are gonna
  • 02:52 start growing in this area as we do a few things here.
  • 02:55 Like, this ID column, I don't really need it,
  • 02:58 I got three different ways I can get rid of this.
  • 03:01 I can click on the Remove Columns button, I can right-click and choose Remove.
  • 03:05 Or I can even just select the column and press the Delete key, and
  • 03:08 it'll actually remove that column.
  • 03:10 It does not delete it from the underlying database,
  • 03:13 it just says, don't bring it in to Power Pivot.
  • 03:17 My date looks like it's formatted as dates and times.
  • 03:20 So I'm gonna click on this little icon here, and I'm gonna change it, and
  • 03:23 say hey, let's format it as a date.
  • 03:26 And there we go, the times get dropped off.
  • 03:28 Account and department seem to be formatted as text, I'm never gonna add,
  • 03:32 multiply, subtract, or divide account numbers though, so I'm okay with that.
  • 03:35 The amounts look good, the account name, this looks kinda gross.
  • 03:40 Notice how everything else is space dash space, but this guy's not?
  • 03:44 So check this out, right-click, we can go Replace Values, and
  • 03:49 what we can do is we can make a minor manipulation, let's replace -b with- b.
  • 03:58 And now, you'll notice that it actually cleans that up a little bit, so
  • 04:02 that's kinda nice.
  • 04:03 And at this point, everything looks pretty good, I'm pretty happy with what's there.
  • 04:08 So what I'm gonna do is I'm gonna load this to Power Pivot.
  • 04:10 Now, there's a trick to doing this though.
  • 04:13 In order to load this to Power Pivot, we need to go to close and load two.
  • 04:16 So we're gonna click the bottom half of this button here,
  • 04:18 wer'e going to choose close the load two, dot dot dot.
  • 04:23 This will close this window and bring us back to Excel, and
  • 04:27 then it's going to pop up a little window that gives us some choices of what to do.
  • 04:32 So here we go, and the choices that I need are this,
  • 04:36 only create connection and add data to the data model.
  • 04:42 This is Power Pivot, what this means is it's not gonna add a table or
  • 04:46 a worksheet or anything like that.
  • 04:47 It's just gonna create a pointer to our data that looks like this.
  • 04:53 So it's loading transactions, and it's spinning them all into Power Pivot.
  • 04:57 And if we can just wait and see until it completes here.
  • 05:00 Here we go, it's loaded 35,000 rows into Power Pivot.
  • 05:04 And I can actually tell by going into the Power Pivot tab,
  • 05:09 clicking on Manage, and when this happens, it'll open Power Pivot for me.
  • 05:15 And you can see that I've got 35,000 rows here
  • 05:19 that have been loaded into the data model.
  • 05:21 They've all come through Power Query, everything's ready to go.
  • 05:24 I'm just gonna close this guy down right now and say, hey I'm pretty happy.
  • 05:28 This is actually coming quite nicely.

Lesson notes are only available for subscribers.

Gift this course