PAUSE & PRACTICE: EXCEL 10-1

For this project, you get data from an XML file and from an Access database. You transform each dataset before loading the data into an Excel workbook.

Files Needed: XML_PerfectRentals-10.xml, XML_PerfectRentals-10.xsd (XMLPerfectRentals-10.zip), and Database-10.accdb (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] PP E10-1.xlsx

1. Open a new workbook and save it as [your initials] PP E10-1.
2. Get data from an XML file.
   1. Click the Get Data button [Data tab, Get & Transform Data group], choose From File, and From XML.
   2. Navigate to the folder with the XML_PerfectRentals-10.xml file from your student data files. You may not see the file name extension in the Import Data dialog box. The XML_PerfectRentals-10.xsd file is a support file in the same folder, but you will not see its name.
   3. Select the file name XML_PerfectRentals-10.xml, click Import, and then click Transform Data. (Click Refresh in the message bar if it displays.)
   4. Click the Expand/Aggregate button next to the tblRentalActivity label at the top of the first column in the Power Query Editor.
   5. Verify that the Expand button is selected, that all field boxes are selected, and click OK.
3. Remove columns in Power Query. (If you make an error, delete the step in the Query Settings pane and try again.)
   1. Scroll the window to the right to see the http://www.w3.org… column, the fifth column. Each row displays “Table.”
   2. Click the column heading to select the column.
   3. Press Ctrl and click the Attribute:generated column, the sixth column. These two columns are identifiers from the companion XSD file; they are not necessary for your work.
   4. Click the Remove Columns button [Home tab, Manage Columns group]. Both columns are removed.
4. Rename columns in Power Query.
   1. Click the tblRentalActivity.ID column header, the first column.
   2. Click the Transform tab and click the Rename button [Any Column group].
   3. Type ID as the heading and press Enter.
   4. Point to the tblRentalActivity_…Rental… column header, the second column, and double-click to select the label.
   5. Type Rental Name as the heading and press Enter.
   6. Edit the remaining column headers to display the labels shown here.Start Date# of Days
5. Change the data type in Power Query.
   1. Click the ID column header.
   2. Press Ctrl and click the # of Days column header.
   3. Click the Data Type button [Home tab, Transform group] and choose Whole Number.
   4. Click the Start Date header and change the data type to Date/Time.
   5. Select the first cell in the ID column. The calculations area may open above the Status bar. (Figure 10-12).
6. Load data to a workbook.
   1. Click the Close & Load button [Home tab, Close group].
   2. Rename the sheet as XML Query if necessary. (Enterprise Excel versions may display a default sheet name.)
7. Get data from an Access database file.
   1. Click the Get Data button [Data tab, Get & Transform Data group] and choose From Database.
   2. Choose From Microsoft Access Database.
   3. Navigate to the folder with the Database-10 file from your student data files.
   4. Select the Database-10 name and click Import.
   5. Select tblProperties in the Navigator window and click Transform Data. (Click Refresh in the Power Query window if a message bar opens about the age of the file.)
   6. Click the ID column.
   7. Change the data type to Whole Number. This field will establish a relationship between the tables in the next project.
8. Load data from an Access database file.
   1. Click the Close & Load button [Home tab, Close group]. The data loads to a new worksheet.
   2. Rename the sheet as Database Query.
9. Save and close the workbook (Figure 10-13).