1.4 Step 1: Importing Data into Excel
When you import data into Excel, you don’t just copy the data once and lose the connection to the original file. Instead, Excel creates a data connection to the source and, if you tell it to do so, checks the file to see if its contents have changed and if your worksheet needs to be updated. You get to control how often Excel updates your worksheet data by displaying the external data range properties dialogue box.
Note: Technically, we really haven’t imported data into an Excel workbook until we select the “Load” option in the Power Query Editor. We are only viewing the data. We don’t “officially” bring the data into an Excel workbook until we select the “Load” choice from the Power Query Editor menu.
Practice Exercise 1: Import an Access Database table into Excel
|
STEP 1: Go up to the Ribbon and select the Data tab. In the “Get and transform data” section, select “Get Data” (FIGURE 1).
|
 FIGURE 1
|
|
STEP 2A: On the drop-down menu, select the choice, “From Database” (FIGURE 2).
STEP 2B: On the secondary menu, select the choice, “From Microsoft Access Database” (FIGURE 2). |
 FIGURE 2
|
|
STEP 3: From the “Import Data” dialog box, select from the Data Files folder, the file named, “XYZ COMPUTERS” (FIGURE 3).
This file is a Microsoft Access file. It has the filename extension, “accdb” |
 FIGURE 3
|
|
STEP 4: You are advised that the connection to the database file is being established (FIGURE 4).
|
 FIGURE 4
|
|
STEP 5: The Navigation screen of the Power Query Editor appears (FIGURE 5).
|
 FIGURE 5
|
|
STEP 6A: Click on the CUSTOMERS table shown on the left of the screen. We can now see the data from the CUSTOMERS table load into Microsoft Query (FIGURE 6).
|
 FIGURE 6
|
|
STEP 6B: At the bottom of the Navigator screen, select the arrowhead next to the “Load” button. On the drop-down menu, select the “Load to” choice. This choice gives you options on where to load the CUSTOMERS table data. Selecting only the “Load” choice will only load the CUSTOMERS table into your current Excel worksheet (FIGURE 7).
|
 FIGURE 7
|
|
STEP 7: When you select “Load To…” option, the “Import Data” dialog box appears. On the “Import Data” dialog box, select the following choices:
|
 FIGURE 8
|
|
STEP 9A: Now we will rename the sheet. Move mouse to the “Sheet1” tab at the bottom of the Excel worksheet screen. Right-button mouse click “Sheet1” tab. You will see a menu appear as shown in FIGURE 10. Select “Rename” from the menu.
|
 FIGURE 10
|
|
STEP 9B: Notice that “Sheet1” is now highlighted, indicating that you may enter a replacement name by typing it in at this time. Enter “CUSTOMERS” as the replacement name, then press the “Enter” key (FIGURE 11).
|
 FIGURE 11
|