WIXLIB

Exam Ref 70-778Analyzing and VisualizingData with MicrosoftPower BI Daniil Maslyuk

Exam Ref 70-778 Analyzing and Visualizing Data with Microsoft Power BIPublished 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-0702-9ISBN-10: 1-5093-0702-8Library of Congress Control Number: 20189384871 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 EditorChris SorensenCover DesignerTwist Creative, Seattle

To my wife, Dasha, who was very patient and supported me duringthe writing process in every way she could.—Daniil Maslyuk

Contents at a glanceCHAPTER 1IntroductionxviiImportant: How to use this book to study for the examxxiConsuming and transforming data by usingPower BI DesktopCHAPTER 2Modeling and visualizing dataCHAPTER 3Configure dashboards, reports, and apps in thePower BI ServiceIndex183271333

zation of this book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiMicrosoft certifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiiMicrosoft Virtual Academy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiiQuick access to online references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiiErrata, updates, & book support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xixStay in touch. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xixImportant: How to use this book to study for the examChapter 1Consuming and transforming data by usingPower BI Desktopxxi1Skill 1.1: Connect to data sources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Connect to databases, files, and folders2Data connectivity modes4Importing data5DirectQuery5Implications of using DirectQuery6When to use DirectQuery8Live Connection9Connecting to Microsoft SQL Server10Connecting to Access database12Connecting to an Oracle database13Connecting to a MySQL database15Connecting to PostgreSQL database15Connecting to data using generic interfaces17Connecting to Text/CSV files17Connecting to JSON files18vii

Connecting to XML files19Connecting to a Folder20Connecting to a SharePoint folder22Connecting to web pages and files22Connecting to Azure Data Lake Store and Azure Blob Storage24Import from Excel. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Import data from Excel25Import Excel workbook contents26Connect to SQL Azure, Big Data, SQL Server Analysis Services (SSAS). . . . . 27Connecting to Azure SQL Database and Azure SQLData Warehouse27Connecting to Azure HDInsight Spark28Connecting to SQL Server Analysis Services (SSAS)28Connecting to Power BI service29Skill 1.2: Perform transformations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Design and implement basic and advanced transformations32Power Query overview32Using the Power Query Editor interface35Basic transformations44Advanced transformations52Appending queries55Merging queries56Creating new columns in tables60Apply business rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63Change data format to support visualization64Skill 1.3: Cleanse data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Manage incomplete data74Meet data quality requirements75Thought experiment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77Thought experiment answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Chapter summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79viiiContents

Chapter 2Modeling and visualizing data83Skill 2.1: Create and optimize data models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Manage relationships84Optimize models for reporting95Manually type in data102Use Power Query104Skill 2.2: Create calculated columns, calculated tables, and measures. . . . . 107Create DAX formulas for calculated columns107Calculated tables134Measures173Use What-if parameters205Skill 2.3: Measure performance by using KPIs, gauges, and cards. . . . . . . 206Calculate the actual207Calculate the target208Calculate actual to target213Configure values for gauges214Use the format settings to manually set values216Skill 2.4: Create hierarchies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217Create date hierarchies217Create hierarchies based on business needs219Add columns to tables to support desired hierarchy221Skill 2.5: Create and format interactive visualizations . . . . . . . . . . . . . . . . . . 225Select a visualization type225Configure page layout and formatting238Configure interactions between visuals239Configure duplicate pages242Handle categories that have no data242Configure default summarization and datacategory of columns242Position, align, and sort visuals245Enable and integrate R visuals247Format measures249Use bookmarks and themes for reports250Contentsix

Skill 2.6: Manage custom reporting solutions. . . . . . . . . . . . . . . . . . . . . . . . . . 255Configure and access Microsoft Power BI Embedded256Enable developers to create and edit reports throughcustom applications257Enable developers to embed reports in applications257Use the Power BI API to push data into a Power BI dataset259Enable developers to create custom visuals261Thought experiment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262Thought experiment answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266Chapter summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267Chapter 3Configure dashboards, reports, and apps in thePower BI Service271Skill 3.1: Access on-premises data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271Connect to a data source by using a data gateway272Publish reports to the Power BI service from Power BI Desktop277Edit Power BI service reports by using Power BI Desktop277Skill 3.2: Configure a dashboard. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279Add text and images279Filter dashboards282Dashboard settings283Customize the URL and title283Enable natural language queries284Skill 3.3: Publish and embed reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291Publish to web291Publish to Microsoft SharePoint294Publish reports to a Power BI Report Server296Skill 3.4: Configure security for dashboards, reports, and apps . . . . . . . . . 302xContentsCreate a security group by using the Admin Portal302Configure access to dashboards and app workspaces305Configure the export and sharing setting of the tenant309Configure row-level security312

Skill 3.5: Configure apps and apps workspaces . . . . . . . . . . . . . . . . . . . . . . . . 320Create and configure an app workspace321Publish an app322Thought experiment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328Thought experiment answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329Chapter summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330Index333Contentsxi

AcknowledgementsIwould like to thank Trina MacDonald for handling the project and giving me the opportunityto write my first book, which turned out to be a very rewarding experience. Also, I would liketo thank all the people who helped making the book more readable and contain fewer errors:Chris Sorensen, Rick Kughen, Liv Bainbridge, Troy Mott, and everyone else at Pearson whoworked on this book but I haven’t worked directly with.A few people have contributed to my becoming a fan of Power BI. Gabriel Polo Reyes wasinstrumental in my being introduced to the world of Microsoft BI. Thomas van Vliet, my firstclient, hired me despite my having no prior commercial experience with Power BI and fed memany problems that led to my mastering Power BI.Introduction xiii

About the authorDANIIL M A S LY U K (MCSA: BI Reporting; MCSE: Data Management andAnalytics) is a Microsoft business intelligence consultant who specializes inPower BI, Power Query, and Power Pivot; the DAX and M languages; and SQLServer and Azure Analysis Services tabular models. Daniil blogs at xxlbi.comand tweets as @DMaslyuk.