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.xlsxSkills Covered in This ProjectGet 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. 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.Get Excel data in Power Query. Click the Get Data button [Data tab, Get & Transform Data group] and select Launch Power Query Editor. Expand the Navigator pane to the left of the window if it is collapsed. Click the New Source button [Home tab, New Query group]. Select File and then choose Excel. Navigate to the folder with the CourtyardMedical-10 workbook (Downloaded from the Resources link). Select the file name and click Import. The Navigator window displays sheet and range names in the workbook (Figure 10-75).Figure 10-75 Navigator window for an Excel file Select Data2019 to preview the data and click OK. Click the New Source button [Home tab, New Query group] again and select File and Excel. Navigate to the folder with the CourtyardMedical-10 workbook, select Data2020, and click OK. Both queries display in the Navigator pane.Append queries. Select Data2019 in the Navigator pane. Click the arrow for the Append Queries button [Home tab, Combine group] and select Append Queries as New. Confirm that the Two tables button is selected and that Data2019 displays as the Primary table. Click the drop-down list for Table to append to the primary table and choose Data2020 (Figure 10-76).Figure 10-76 Append dialog box Click OK. The new query is named Append1.Load data to Excel. Click the Close & Load button [Home tab, Close group]. The two source queries and the appended query are loaded to separate worksheets. Name the appended queries tab Append1. This sheet displays 60 rows of data. Select cell A1.Create a PivotTable for the appended queries. Select the Summarize with PivotTable button [Table Design tab, Tools group]. Select the Summarize with PivotTable button [Table Tools Design tab, Tools group]. Select New Worksheet and click OK. Close the Queries & Connections pane if it displays. Name the worksheet tab PivotTable 1.Place fields in the PivotTable. Select the Service Code field name in the PivotTable Fields pane. Label fields display in the Rows area. Select the Billed field name. Numeric fields display in the Values area. 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.Edit value field settings. 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]. Type Total Billed as the Custom Name. Click Number Format, choose Currency, set 0 (zero) decimal places, and click OK two times to close the dialog boxes. Right-click Sum of Billed2 in cell C3 and select Value Field Settings. Select the Summarize Values By tab and choose Average as the function. Type Average Billed as the Custom Name. Click Number Format, choose Currency, set 0 (zero) decimal places, and close the dialog boxes.Refresh data and format the PivotTable report. Select cell A7 in the PivotTable. This code is an outlier. Click the Append1 worksheet tab and select cell C61. Type D0120 and press Enter. Return to the PivotTable 1 sheet. The data is not automatically updated. Click the Refresh button [PivotTable Analyze tab, Data group]. Click the Refresh button [PivotTable Tools Analyze tab, Data group]. Click any cell in the PivotTable and apply the Light Blue, Pivot Style Medium 6 style. 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]. 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. Select cell A1 and type Courtyard Medical Dental Services. Type Billings by Service Code in cell A2. Format both labels as 14 pt (Figure 10-77).Figure 10-77 Completed PivotTableCreate a PivotChart. 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]. Choose Combo and Clustered Column - Line as the subtype and click OK (Figure 10-78).Figure 10-78 Create a combo PivotChart Drag the chart object so that its top-left corner is in cell E3. Size the chart object to reach cell N25.Insert a slicer. 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]. Select the Insurance box and click OK. Position the slicer so that the top-left corner is in cell B10. Format the slicer with Light Blue, Slicer Style Dark 5. Click CompDent in the slicer to filter the PivotTable and PivotChart (Figure 10-79).Figure 10-79 Completed PivotTable, PivotChart, and slicer Select cell A1. Save and close the workbook. Upload and save your project file. Submit project for grading.

Part One: Pick a topic Your topic should examine some way in which technology has changed or been changed by society. You will do some research on your topic throughout the term and give a presentation in the last week of class.Suggestions for possible topics for this assignment include:Digital divide - implications for the widening gap in technology accessGenetically modified organisms/engineered foodImpact of biotoxins on health and lifestyleChanging conceptions of romance due to technological advancementChanging family dynamics due to technological advancementOnline social networks for older adultsIdentity theftMedical advancements and how they have changed societyVaccinations and societyAntibiotics and societyChanges in the nature of workChanges in manufacturingTransportation and its impact on societyAdaptive technology for the aging and why it mattersAdaptive technology for the disabled and why it mattersArchitectural advances and the impact on societyTechnology and politicsChanging nature of disease since 1900Lifespan changes since 1900Longevity and life extensionTechnology and beautyDigital natives vs. digital immigrantsCumulative advantage and cumulative disadvantageNomophobiaImpact of gaming addiction in children or adultsRestricting technology use in young childrenTracking technologyCyberbullyingTechnology and forensic investigationTechnology and criminologyTechnology and education in the classroomTechnology and higher educationInfluence of technology on learning abilityInfluence of technology on intelligenceInfluence of technology on obesityChanges in communication patterns due to technologyTechnology and gender rolesImpact of reproductive technologies on societyTechnology and consumer behaviorTechnology and the concept of intellectual property You may pick another topic if you like. All topics require approval from the instructor.Notice that some of the categories are very broad, so narrowing your focus will help you to complete each component. For example, “Technology and Crime” could refer to the ways in which technology has been used to fight criminal activity, or it could be used to describe how criminals have used technology to carry out crimes and how this has forced society to change its behavior. Part 2 is attachedPart 3: Presentation Progress you are to submit a progress report that demonstrates that you have been working towards your final presentation. Your progress report should be 1-3 pages in length and include the following: A well-written paragraph that states in detail what your topic is. Be sure to clearly link your topic to both technology and society/societal trends.A well-written paragraph that identifies the type of presentation you plan to give (e.g. slideshow, written materials, podcast, YouTube, etc.). Your presentation should be creative, engaging, and informative. Remember that in the last week of class, YOU become the teachers. Think about your audience and how to present material in an interesting and clear manner.A preliminary outline that shows what will you address in your presentation. You may provide an actual outline, or describe in words the approach you will take. Use the rubric for the final presentation to help you structure your outline. Your outline and presentation should include the following components:Define your topic, linking it to technology and societyTrace the history of your topicDescribe how your topic compares to at least one other cultureIdentify relevant policies related to your topicDiscuss future trends related to your topicPart 4 The presentation Your presentation can take any form you choose that is conducive to an online format. This includes narrated slide shows (e.g. Powerpoint, Prezi, Knovio), podcasts or audio recordings, written materials (e.g. brochures, pamphlets, research posters, etc.), YouTube videos, etc. Your presentation should contain about 10-15 minutes of content. Use the rubric presented below to structure your work. Be sure to touch upon each required component to earn as many points as you can. Discussion questions In addition to your presentation, you will post a discussion question about your topic for your classmates. Your question should be open-ended and thought-provoking to ensure a rich discussion. Throughout the week, each student should monitor the comments generated by his/her discussion question and respond to the comments accordingly.

You are a data mining consultant hired by your organization to implement a data mining process. What challenges does your organization face in ensuring that the data mining models are receiving clean data?