Guided Project 10-2

Sierra Pacific Community College District (SPCCD) analyzes departmental course fees. The source data is stored in separate XML files, and you plan to merge the queries.

[Student Learning Outcomes 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7]

Files Needed: XML_SPCCDept-10.xml and XML_SPCCFees-10.xml; companion files XML_SPCCDept-10.xsd and XML_SPCCFees-10.xsd must be accessible. (GP-E10-2.zip) (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 10-2.xlsx

Skills Covered in This Project

• Get XML data in Power Query.
• Merge queries.
• Manipulate data in Power Query.
• Create a custom column in Power Query.
• Create a PivotTable from the data model.
• Use PivotTable tools.
• Create a PivotChart from the data model.
• Build a dashboard.

1. Open a new workbook and save it as [your initials] Excel 10-2.
2. Launch Power Query.
   1. Click the Get Data button [Data tab, Get & Transform Data group].
   2. Choose Launch Power Query Editor.
   3. Expand the Navigator Pane if it is collapsed.
3. Get XML data in Power Query.
   1. Click the New Source button [Home tab, New Query group] to displays its options.
   2. Select File and then choose XML.
   3. Navigate to the folder with XML_SPCCDept-10, select the file name, and click Import. The Navigator window does not preview data for XML files.
   4. Click OK to import the data. Fields are not automatically expanded in an XML file. (Refresh data if prompted.)
   5. Click the New Source button [Home tab, New Query group] again and import XML_SPCCFees-10. Both queries are listed in the Navigator Pane (Figure 10-78).
4. Transform data in Power Query.
   1. Select the XML_SPCCDept-10 query name in the Navigator Pane.
   2. Click the http://…Schema… column header (second column), press Ctrl, and click the Attribute:generated header (third column).
   3. Click the Remove Columns button [Home tab, Manage Columns group].
   4. Click the Expand/Aggregate button next to the SPCCDept-10 column header.
   5. Verify that the Expand button is selected, that all fields are selected and click OK.
   6. Click the SPCCDept-10.ID column label, click the Data Type button [Home tab, Transform group], and choose Whole Number.
   7. Click the SPCCDept-10.Course… label in the third column. Note that the Data Type [Home tab, Transform group] is Text. Leave this field as text for use in a custom column in a later step.
   8. Select the …Hours and …Sections columns and set the data type to Whole Number (Figure 10-79).
   9. Display the XML_SPCCFees-10 query.
   10. Select and remove the http://…Schema… and the Attribute:generated fields.
   11. Click the Expand/Aggregate button and display the data.
   12. Change the ID and Fee columns to Whole Number data type.
5. Merge queries.
   1. Display the XML_SPCCDept-10 query.
   2. Click the Merge Queries drop-down arrow [Home tab, Combine group] and select Merge Queries as New.
   3. Select the ID field as the common field in XML_SPCCDept-10.
   4. Click the drop-down list for the related query and choose XML_SPCCFees-10.
   5. Select the ID field as the common field.
   6. Verify that the Join Kind is Left Outer and click OK (Figure 10-80). The new query name is Merge1.
   7. Expand and display the data from XML_SPCCFees-10 in the merged query.
6. Create a custom column with a multiplication formula.
   1. Select and remove the SPCCDept-10…Hours field. The credit hours field is not necessary in your analysis.
   2. Remove the XML_SPCCFees-10…ID field.
   3. Click the Custom Column button [Add Column tab, General group].
   4. Type Fees Collected as the New column name.
   5. Click after the = symbol in the Custom column formula box.
   6. Double-click the field name for Sections in the Available columns list.
   7. Type * to multiply.
   8. Double-click the field name for Fee in the Available columns list (Figure 10-81).
   9. Click OK. The new column displays at the right.
7. Create a custom column with a concatenated text expression.
   1. Click the Custom Column button [Add Column tab, General group].
   2. Type Code as the New column name.
   3. Click after the = symbol in the Custom column formula box.
   4. Double-click the field name for Dept in the Available columns list.
   5. Type & (an ampersand) to concatenate fields.
   6. Type “ ” (quotation mark, space, quotation mark) to place a space in the custom field.
   7. Type & to join the next field.
   8. Double-click the name for the Course Number field in the Available columns list.
   9. Compare your formula to the following:
   10. Click OK. The custom column joins the department name and course number in a single field.
8. Remove and reposition fields in a query.
   1. Select the …Dept field, press Ctrl and select …Course Number field (second and third columns).
   2. Remove the selected columns.
   3. Point to the Code column header and drag the column to be the second field in the query.
   4. Reposition the Fees Collected column as the fourth column (Figure 10-82).
9. Load query data and format the Excel table.
   1. Click the Close & Load button [Home tab, Close group]. Each query is loaded to the workbook on its own sheet.
   2. Confirm that the Merge1 sheet is active. The table includes the Code column.
   3. Rename the sheet as Merge1 if it was not automatically named.
   4. Format columns D and F as Currency with two decimals.
   5. Edit the column header for column C to Course Name.
   6. Edit the column header for column E to # of Sections.
   7. Edit the column header for column F to Fee.
   8. Hide column A.
   9. AutoFit all columns.
10. Create a PivotTable from the data model.
    1. Select the XML_SPCCDept-10 worksheet tab; its table displays six columns. Rename the sheet as XML_SPCCDept-10 if necessary.
    2. Click a cell in the table and click the Summarize with PivotTable button [Table Design tab, Tools group].
    3. Confirm the table range and that the New Worksheet button is selected.
    4. Select the Add this data to the Data Model box and click OK.
    5. Close the Queries & Connections pane.
    6. Expand the field list in the PivotTable Fields pane if it is not expanded.
    7. Select the Dept, Course Name, and Sections boxes to place the fields in the PivotTable layout. (Point to a field name in the pane to see the complete name.)
    8. Click All at the top of the PivotTable Fields pane and expand the XML_SPCCFees-10 list.
    9. Select the Fee box to add it to the PivotTable layout.
    10. Click Auto-Detect. No relationship is found.
    11. Click Manage Relationships and click New.
    12. Click the Table drop-down list and choose Data Model Table: XML_SPCCFees_10.
    13. Click the Column (Foreign) drop-down list and choose SPCCFees-10.ID.
    14. Select the …Dept_10 table as the Related Table with the ID column (Figure 10-83).
    15. Click OK and then click Close.
    16. Name the sheet tab Dashboard.
11. Format and move a PivotTable.
    1. Click the Report Layout button [Design tab, Layout group] and choose Show in Tabular Form.
    2. Select cell A3, type Dept, and press Tab.
    3. Select cell B3, type Course Name, and press Tab.
    4. Select cell C3, type # of Sections, and press Tab.
    5. Edit the label in cell D3 to Course Fee.
    6. Click the More button [Design tab, PivotTable Styles group] and choose White, Pivot Style Light 22.
    7. Select the Banded Rows box [Design tab, PivotTable Style Options group].
    8. Click the PivotTable Name box [PivotTable Analyze tab, PivotTable group] and name the table pvtSections.
    9. Click the Select button [PivotTable Analyze tab, Actions group] and select Entire PivotTable.
    10. Click the Move PivotTable button [PivotTable Analyze tab, Actions group], select cell C3, and click OK (Figure 10-84).
    11. AutoFit the columns in the PivotTable.
    12. Select a cell in column F and format the field settings as Currency with two decimal places.
    13. Click the Grand Totals button [Design tab, Layout group] and turn off all totals.
12. Copy and build a PivotTable.
    1. Click the Select button [PivotTable Analyze tab, Actions group] and select the PivotTable.
    2. Copy and paste the PivotTable to cell H3. Press Esc to remove the border.
    3. Click the PivotTable Name box [PivotTable Analyze tab, PivotTable group] and name the copied table pvtFees.
    4. Expand the Merge1 field list in the PivotTable Fields pane.
    5. Select the Fees Collected box to place the field in the PivotTable layout.
    6. Click Auto-Detect. The relationship is found.
    7. Close the dialog box.
    8. Remove the # of Sections and the Course Fee fields from PivotTable Field List pane.
13. Format a PivotTable.
    1. Select cell J3 and change the label to Total Fees.
    2. Format the values in column J as Currency with two decimal places.
    3. Click the Subtotals button [Design tab, Layout group] and show subtotals at the bottom of each group.
14. Insert and connect a slicer on the dashboard.
    1. Set the width of column A to 35.
    2. Click cell C3 and click the Insert Slicer button [PivotTable Analyze tab, Filter group].
    3. Select the Dept box in the XML_SPCCDept_10 list and click OK.
    4. Position the slicer window to start at cell A3.
    5. Size the slicer window to display all items.
    6. Apply White, Slicer Style Dark 3.
    7. Click the Report Connections button [Slicer tab, Slicer group].
    8. Select the pvtFees box and click OK.
15. Insert a PivotChart on the dashboard.
    1. Select cell A23.
    2. Click the PivotChart button [Insert tab, Charts group].
    3. Confirm or select the Use this workbook’s Data Model radio button.
    4. Confirm or select the Existing Worksheet radio button and click OK.
    5. Expand the Merge1 list in the PivotChart Fields pane.
    6. Select the Course Name and Fees Collected boxes.
16. Format a PivotChart on the dashboard.
    1. Click the Change Chart Type button [Design tab, Type group].
    2. Select the Pie category and the Doughnut subtype.
    3. Turn off display of the Field Buttons [PivotChart Analyze tab, Show/Hide group].
    4. Triple-click the Total text box and change the label to Fees by Course.
    5. Click the Filter Connections button [PivotChart Analyze tab, Filter group].
    6. Select the Dept box to connect the chart to the slicer and click OK.
    7. Position and size the chart object to span cells A23:B40.
    8. Apply a ½ pt. Black, Text 1 outline to the chart object.
17. Complete labels for the dashboard.
    1. Select rows 1:2 and set the height to 35.
    2. Select cell A1 and type Sierra Pacific Community College District.
    3. Select cell A2 and type Department Course Fees.
    4. Format both labels with Calibri 20 pt. italic.
    5. Insert a blank row at row 1 and a blank column at column A.
    6. Hide Gridlines, the Formula Bar, and the Headings [View tab].
    7. Select the BUS and CGI items in the slicer.
18. Save and close the workbook (Figure 10-85).