San Diego State University Power Querys PivotTable Excel Practice

I’m working on a databases question and need guidance to help me study.

These instructions are only compatible with the Microsoft Windows operating system.

Courtyard Medical Plaza has two tables with dental insurance data. You get these tables in Power Query to append one to the other and to create a PivotTable with a PivotChart.

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

File Needed: EX2019-GuidedProject-10-3.xlsx (Available from the Start File link.) and CourtyardMedical-10.xlsx (Available from the Resources link.)

Completed Project File Name: [your name]-EX2019-GuidedProject-10-3.xlsx

Skills Covered in This Project

• Get data from a workbook in Power Query.
• Append queries.
• Create and customize a PivotTable.
• Insert a PivotChart.
• Insert a slicer in a PivotTable.

This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.

IMPORTANT: The projects in this chapter are for Windows (PC) computers only. The Power Query, Power Pivot, and Get & Transform features used in these projects are not currently available in Excel 365 for Mac.

1. Open the EX2019-GuidedProject-10-3 start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Get Excel data in Power Query.
   1. Click the Get Data button [Data tab, Get & Transform Data group] and select Launch Power Query Editor.
   2. Expand the Navigator pane to the left of the window if it is collapsed.
   3. Click the New Source button [Home tab, New Query group].
   4. Select File and then choose Excel.
   5. Navigate to the folder with the CourtyardMedical-10 workbook (Downloaded from the Resources link).
   6. Select the file name and click Import. The Navigator window displays sheet and range names in the workbook (Figure 10-75).
      

      

   7. Select Data2019 to preview the data and click OK.
   8. Click the New Source button [Home tab, New Query group] again and select File and Excel.
   9. Navigate to the folder with the CourtyardMedical-10 workbook, select Data2020, and click OK. Both queries display in the Navigator pane.
3. Append queries.
   1. Select Data2019 in the Navigator pane.
   2. Click the arrow for the Append Queries button [Home tab, Combine group] and select Append Queries as New.
   3. Confirm that the Two tables button is selected and that Data2019 displays as the Primary table.
   4. Click the drop-down list for Table to append to the primary table and choose Data2020 (Figure 10-76).
      

      

   5. Click OK. The new query is named Append1.
4. Load data to Excel.
   1. Click the Close & Load button [Home tab, Close group]. The two source queries and the appended query are loaded to separate worksheets.
   2. Name the appended queries tab Append1. This sheet displays 60 rows of data.
   3. Select cell A1.
5. Create a PivotTable for the appended queries.
   1. Select the Summarize with PivotTable button [Table Design tab, Tools group].
      Select the Summarize with PivotTable button [Table Tools Design tab, Tools group].
   2. Select New Worksheet and click OK.
   3. Close the Queries & Connections pane if it displays.
   4. Name the worksheet tab PivotTable 1.
6. Place fields in the PivotTable.
   1. Select the Service Code field name in the PivotTable Fields pane. Label fields display in the Rows area.
   2. Select the Billed field name. Numeric fields display in the Values area.
   3. Point to Billed in the Choose fields to add to report area and drag the field name to the Values area to show the field twice in the PivotTable.
7. Edit value field settings.
   1. Click Sum of Billed in cell B3 and click the Field Settings button [PivotTable Analyze tab, Active Field group].
      Click Sum of Billed in cell B3 and click the Field Settings button [PivotTable Tools Analyze tab, Active Field group].
   2. Type Total Billed as the Custom Name.
   3. Click Number Format, choose Currency, set 0 (zero) decimal places, and click OK two times to close the dialog boxes.
   4. Right-click Sum of Billed2 in cell C3 and select Value Field Settings.
   5. Select the Summarize Values By tab and choose Average as the function.
   6. Type Average Billed as the Custom Name.
   7. Click Number Format, choose Currency, set 0 (zero) decimal places, and close the dialog boxes.
8. Refresh data and format the PivotTable report.
   1. Select cell A7 in the PivotTable. This code is an outlier.
   2. Click the Append1 worksheet tab and select cell C61.
   3. Type D0120 and press Enter.
   4. Return to the PivotTable 1 sheet. The data is not automatically updated.
   5. Click the Refresh button [PivotTable Analyze tab, Data group].
      Click the Refresh button [PivotTable Tools Analyze tab, Data group].
   6. Click any cell in the PivotTable and apply the Light Blue, Pivot Style Medium 6 style.
   7. Select the Banded Rows and Banded Columns boxes [PivotTable Design tab, PivotTable Style Options group].
      Select the Banded Rows and Banded Columns boxes [PivotTable Tools Design tab, PivotTable Style Options group].
   8. Click the Grand Totals button [PivotTable Design tab, Layout group] and choose Off for Rows and Columns.
      Click the Grand Totals button [PivotTable Tools Design tab, Layout group] and choose Off for Rows and Columns.
9. Select cell A1 and type Courtyard Medical Dental Services.
10. Type Billings by Service Code in cell A2.
11. Format both labels as 14 pt (Figure 10-77).
    

    

12. Create a PivotChart.
    1. Select a cell in the PivotTable and click the PivotChart button [PivotTable Analyze tab, Tools group].
       Select a cell in the PivotTable and click the PivotChart button [PivotTable Tools Analyze tab, Tools group].
    2. Choose Combo and Clustered Column – Line as the subtype and click OK (Figure 10-78).
       

       

    3. Drag the chart object so that its top-left corner is in cell E3.
    4. Size the chart object to reach cell N25.
13. Insert a slicer.
    1. Click any cell in the PivotTable and click the Insert Slicer button [PivotTable Analyze tab, Filter group].
       Click any cell in the PivotTable and click the Insert Slicer button [PivotTable Tools Analyze tab, Filter group].
    2. Select the Insurance box and click OK.
    3. Position the slicer so that the top-left corner is in cell B10.
    4. Format the slicer with Light Blue, Slicer Style Dark 5.
    5. Click CompDent in the slicer to filter the PivotTable and PivotChart (Figure 10-79).
       

       

    6. Select cell A1.
14. Save and close the workbook.
15. Upload and save your project file.
16. Submit project for grading.