Locked lesson.
About this lesson
One of the most important data sources to pull from is a database, but they have their own interesting nuances as well. In this lesson we will take a look at those.
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 Databases.xlsx19.6 KB Getting data from Databases - Completed.xlsx
18.7 MB
Quick reference
Getting Data from Databases
An overview of importing data from a database into Power Query.
When to use
When your data to be used in the query exists in a separate database system.
Instructions
Getting started
- Create a new query --> From Database --> choose the appropriate database
- Browse to or enter the location of the database
- If required, enter your database security credentials
The preview window
- Use the preview window to browse the tables and views
- Selecting the Multiple Items checkbox will allow you to import multiple tables at once
- Once done selecting tables, click Edit
Best practices for working with Database data
- Make your tables “Short and Narrow”
- Remove any columns that are not needed for your analysis
- Filter out any rows unneeded for analysis
- Rename columns to friendly names
- Ensure that the final step is to force the data type for each column
Working with green keywords
- Columns could contain the following green keywords
- Record or Value: These indicate a multi-column, single row data point based on the database’s internal relationships and can be expanded into the extra columns
- Table: These indicate a multi-column, multi row data table based on the database’s internal relationships and can be expanded into the extra columns
- Clicking
- The green text of a keyword will drill down into that record or table
- The white space next to the keyword will display the contents in the preview window at the bottom of the screen
- Clicking on the left-right arrows on the top right corner of the column header will expand the related records into new columns and/or rows
Hints & tips
- When expanding the values nested in a Value or Tables:
- You can check which columns you would like to expand
- Checking the option to Use Original Column Name As Prefix will prepend the new columns with the name of the column being expanded
- 00:05 >> In this video we're gonna look at how to get data from databases because they're
- 00:09 a little different then files.
- 00:12 We're gonna use a Microsoft access database but
- 00:15 to be completely honest with you this is very similar to any
- 00:18 other database that you might actually try and start with.
- 00:21 The workbook that I'm starting with here is just a blank workbook with a few lines
- 00:24 of text, the real key that I want is a starting point so that I can essentially
- 00:28 go and say, let's go create a new query from database, from Microsoft Access.
- 00:35 We're gonna drill into the GoSkills sample files folder and
- 00:39 we're gonna grab the data from database folder and we're gonna grab the POS
- 00:44 database which stands for point of sale and we'll choose import.
- 00:48 Now this will bring us to a preview window and you can see the tables enumerate.
- 00:53 You'll notice that we have a table for table categories, we've got one for
- 00:57 table Chits which is the food and beverage invoice vernacular and
- 01:02 we've got table items which leaves us our items.
- 01:05 If I wanted to work with multiple tables at the same time I could check
- 01:08 the box which will allow me to bring in multiple tables here.
- 01:12 And if I wanted to get a connection to the root of the database itself without
- 01:17 drilling into a table, I would grab the POS here, and just click Edit.
- 01:22 But for right now, the Chits table is the one that has the most detail,
- 01:26 that's the one that I'm most interested in.
- 01:29 So I'm gonna select that and say, Edit, this will create a connection to the Chits
- 01:34 table and it's gonna bring me in a preview of the data that's actually there.
- 01:39 So I can go and rename this guy now and call this one something like Transactions,
- 01:46 you'll notice that there's a source which was the actual Access database.
- 01:49 This was what you would get if you chose the raw folder and said edit instead of
- 01:53 the rest and then it navigated into the table that we actually were looking at.
- 01:58 Now there's a few things in here that I probably don't need, so
- 02:01 let's get rid of those first.
- 02:03 This ID column is just a row number there's no need to have that,
- 02:06 doesn't add any logic, so I'll right click and remove that.
- 02:10 In food and beverage is very rare that I would ever do a sales analysis down to
- 02:14 the minute, so I'll right click and I'll remove that as well.
- 02:18 And maybe I'm not happy with the column names, maybe I wanna call the first column
- 02:22 here something like dates, so I'll just double click it and
- 02:25 rename that one and hour, so I'll make the change there for that one as well.
- 02:31 The chit number, I could go and actually just call it chit and
- 02:36 our item code, we'll call item code and
- 02:40 our POS category we'll rename to category code.
- 02:47 So that all looks fine at this point what I'm gonna do is I'm gonna change
- 02:51 the primary column here to just a date because it's giving me this date time
- 02:55 which I don't really need.
- 02:56 And now I'm gonna scroll across and look at the rest of columns and
- 03:00 partway across I come to this weird thing with TBL categories and
- 03:03 TBL items that are showing me all of this values.
- 03:07 So what are those about?
- 03:08 Well these are actually tables that are linked together so
- 03:12 when I look at this inside the database,
- 03:14 the categories table will be linked by the category code to the chits table.
- 03:19 That's what a relational database is all about and I don't wanna
- 03:23 click onto the green word with little hand here cuz that would drill into it.
- 03:26 But if I click on the white space, you will notice that it actually brings me
- 03:30 back the records that are associated with this particular table.
- 03:34 So, it gives me a POS category code again, that's the field that is actually linked
- 03:38 to this guy here, it tells me the category description, what group it's from and
- 03:43 it also gives me a table chits table.
- 03:45 Well this happens to be the table chits table, so it could link back on itself.
- 03:50 What about table items?
- 03:52 Well, table items gives me the POS item code from here, that's what it used to
- 03:56 make the relationship, it tells me the description for the actual sales items,
- 04:01 and then again links back to this particular table.
- 04:03 Now the reason why this is so important is because of these little arrows
- 04:08 at the top here which will allow me to actually expand these values.
- 04:12 So if I go to table categories and click on the expansion arrow I can say
- 04:17 I don't want to use the original column name as prefix cuz this would expand them
- 04:21 as TBL categories dot, whatever these titles are here, so you don't want that.
- 04:26 I also don't need the POS category code because I've already got the category
- 04:31 code, I don't need table chits because I'm already in table chits but
- 04:36 the category description and group, I'll take those, why not.
- 04:39 We'll say, OK, and what you'll see is that it expands it for
- 04:43 the related records from the database for my sandwiches, for my description.
- 04:47 So I can go and call this one, category name.
- 04:54 And I can go and shortcut this guy down to, group.
- 04:59 Now what about the items, I can expand these guys as well
- 05:03 I don't really need the item code because I've all ready got it over here,
- 05:06 I don't need the chits table.
- 05:08 I'm really just gonna bring across the item description, we'll say, OK, so
- 05:13 now we've got something that I can rename to, item name.
- 05:18 And the final step with every query is to set our data types so
- 05:20 let's do those as quickly as we can.
- 05:22 We'll reset date, make it a date, power can be a whole number,
- 05:26 the chits, I'm gonna hold down my control key and grab location,
- 05:31 item code, category code, category name group and item name.
- 05:36 These guys are all text based because they're serial numbers that we're not
- 05:40 going to be adding, multiplying, subtracting, dividing, or their text.
- 05:43 So we'll just right-click and
- 05:44 change type to text by right-clicking on any of the headers that I've selected.
- 05:48 Again, holding down control allows me to grab the non-contiguous
- 05:52 blocks like that and set them all at once.
- 05:55 Units, I'm gonna change to a decimal number and my amount column,
- 05:59 I will change to currency.
- 06:01 And with all of that done, I can now hit home, close and load, and
- 06:05 this is gonna go and create me a new table from my database that will go and
- 06:09 land me a whole bunch of records.
- 06:11 Rather than wait I'm gonna speed up the video here just a little bit,
- 06:15 this takes about 30 seconds in real time to retrieve about 350,000 records.
- 06:20 And the nice thing here is, when I need to update it,
- 06:23 because the data in the database is changed, I just hit the refresh button and
- 06:26 shortly thereafter all of its there for me to use.
Lesson notes are only available for subscribers.