WIXLIB

Exam Ref 70-779Analyzing and VisualizingData with Microsoft ExcelChris Sorensen

Exam Ref 70-779 Analyzing and Visualizing Data with Microsoft ExcelPublished with the authorization of Microsoft Corporation by:Pearson Education, Inc.Copyright 2018 by Pearson EducationAll rights reserved. This publication is protected by copyright, and permission must be obtained from the publisher prior toany prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical,photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts withinthe Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/. No patentliability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in thepreparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed fordamages resulting from the use of the information contained herein.ISBN-13: 978-1-5093-0804-0ISBN-10: 1-5093-0804-0Library of Congress Control Number: 20189439331 18TrademarksMicrosoft and the trademarks listed at https://www.microsoft.com on the “Trademarks” webpage are trademarks of theMicrosoft group of companies. All other marks are property of their respective owners.Warning and DisclaimerEvery effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied.The information provided is on an “as is” basis. The authors, the publisher, and Microsoft Corporation shall have neitherliability nor responsibility to any person or entity with respect to any loss or damages arising from the information containedin this book or programs accompanying it.Special SalesFor information about buying this title in bulk quantities, or for special sales opportunities (which may include electronicversions; custom cover designs; and content particular to your business, training goals, marketing focus, or brandinginterests), please contact our corporate sales department at corpsales@pearsoned.com or (800) 382-3419.For government sales inquiries, please contact governmentsales@pearsoned.com.For questions about sales outside the U.S., please contact intlcs@pearson.com.Editor-in-ChiefBrett BartowSenior EditorTrina MacDonaldDevelopment EditorRick KughenManaging EditorSandra SchroederSenior Project EditorTracey CroomEditorial ProductionBackstop MediaCopy EditorLiv BainbridgeIndexerJulie GradyProofreaderKatje RichstatterTechnical EditorDaniil MaslyukCover DesignerTwist Creative, Seattle

I dedicate this book to my wife, Joely, my daughter Camryn, andmy son Murphy. Their love, support and encouragement had nobounds through what was one of the most challenging projects Ihave ever worked on.—Chris Sorensen

Contents at a glanceAcknowledgementsxiIntroductionxvImportant: How to use this book to study for the examxixCHAPTER 1Consume and transform data by using Microsoft ExcelCHAPTER 2Model data87CHAPTER 3Visualize data171Index1239

on of this book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvMicrosoft certifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviMicrosoft Virtual Academy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviQuick access to online references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviErrata, updates, & book support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiStay in touch. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiImportant: How to use this book to study for the examChapter 1Consume and transform data by using Microsoft Excelxix1Skill 1.1: Import from data sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Connect to and import from databases, files, and folders2Connect to Microsoft SQL Azure and Big Data24Import from Excel workbooks27Link to data from other sources27Privacy Levels30Skill 1.2: Perform data transformations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32Design and implement basic and advanced transformations32Apply business rules71Change data format to support visualization73Filter data74Format data79Skill 1.3: Cleanse data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Manage incomplete data80Handle data received as a report81vii

Thought experiment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Thought experiment answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85Chapter summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85Chapter 2Model data87Skill 2.1: Create and optimize data models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87Understanding the Excel data model88Get & Transform91Manually enter data97Manage data relationships99Optimize models for reporting108Skill 2.2: Create calculated columns, measures, and tables . . . . . . . . . . . . . . 116Create DAX formulas116Create DAX queries141Create Excel formulas149Skill 2.3: Create Hierarchies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152Create date hierarchies153Create business hierarchies156Resolve hierarchy issues158Skill 2.4: Create Performance KPIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160Calculate the actual value160Calculate the target value161Calculate actual-to-target values162Thought experiments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163Thought experiment answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Chapter summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167Chapter 3Visualize data171Skill 3.1: Create and manage PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171viiiContentsFormat PivotTables172Format calculated measures192Filter data193Group and summarize data198

Skill 3.2: Create and manage PivotCharts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198Select a chart type199Format PivotCharts217Filter data220Skill 3.3: Interact with Power BI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221Power BI overview222Import Excel data from Power BI224Manipulate Excel data in Power BI232Thought experiment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232Thought experiment answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234Chapter summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234Index239Contentsix

AcknowledgementsIwould like to thank the first two members of the Iteration Insights team, Jane Wood and EmilyGu, for their enthusiasm and contributions as we worked on this project as a newly formedcompany. They helped make this book possible as we worked days, nights, and weekends formonths to bring it all together. I would also like to thank Trina MacDonald for her encouragement, support, and belief in our team through the process of writing this book. Writing abook of this magnitude would not be possible without the contribution of the editing staff ofDaniil Maslyuk, Troy Mott, Rick Kughen, and Christina Rudloff, who sifted through the many versions of our materials to ensure technical and grammatical accuracy. Their insights and contributions were appreciated.Introduction xi

About the authorC HR I S S O R E N S E N , MCSE (Data Management and Analytics) and MCT, is the Founder andPresident of Iteration Insights Ltd. He is a consultant, architect, educator, and coach who hasbeen working in the Analytics space for nearly 20 years. Over his career, he has providedstrategic and architectural advisory services to many clients and most recently he has beeninvolved with leading numerous Power BI and Excel PowerPivot projects. He has evangelizedboth Excel and Power BI with Microsoft since July 2015. Follow him on both Linkedin andTwitter as @wjdataguy.

IntroductionThe 70-779 exam is designed for both Business Intelligence developers and Business powerusers that have used Excel for many years to help support Analytics in an organization. Thebook is an even split between the skills needed to Consume and Transform Data, Model Data,and then Visualize Data.In the Consume and Transform Data chapter the focus is on the sources that Excel can connect with for data, and then how to transform data using the Power Query Editor. It is important to understand the M language that underlies the Power Query Editor and is generatedwhen performing transform tasks using the GUI.The chapter on Modeling Data turns the focus to the Excel Data Model to build the necessary relationships that glue the data model together, and the how to optimize it for reporting.Next, we look at how to extend the model and make it easy to consume for users by utilizingDAX, KPIs, and Hierarchies.When Visualizing data, we spend time considering PivotTables and PivotCharts as the twoprimary methods in Excel for presenting data. Lastly, you will spend time investigating how tointeract with Power BI as an additional means for distributing content to users.This book covers every major topic area found on the exam, but it does not cover everyexam question. Only the Microsoft exam team has access to the exam questions, and Microsoftregularly adds new questions to the exam, making it impossible to cover specific questions.You should consider this book a supplement to your relevant real-world experience and otherstudy materials. If you encounter a topic in this book that you do not feel completely comfortable with, use the “Need more review?” links you’ll find in the text to find more informationand take the time to research and study the topic. Great information is available on MSDN,TechNet, and in blogs and forums.Organization of this bookThis book is organized by the “Skills measured” list published for the exam. The “Skills measured” list is available for each exam on the Microsoft Learning website: http://aka.ms/examlist.Each chapter in this book corresponds to a major topic area in the list, and the technical tasksin each topic area determine a chapter’s organization. If an exam covers six major topic areas,for example, the book will contain six chapters.Introduction xv

Microsoft certificationsMicrosoft certifications distinguish you by proving your command of a broad set of skills andexperience with current Microsoft products and technologies. The exams and correspondingcertifications are developed to validate your mastery of critical competencies as you designand develop, or implement and support, solutions with Microsoft products and technologiesboth on-premises and in the cloud. Certification brings a variety of benefits to the individualand to employers and organizations.MORE INFOALL MICROSOFT CERTIFICATIONSFor information about Microsoft certifications, including a full list of available certifications,go to http://www.microsoft.com/learning.Check back often to see what is new!Microsoft Virtual AcademyBuild your knowledge of Microsoft technologies with free expert-led online training fromMicrosoft Virtual Academy (MVA). MVA offers a comprehensive library of videos, live events,and more to help you learn the latest technologies and prepare for certification exams. You’llfind what you need here:http://www.microsoftvirtualacademy.comxvi Introduction