Exam Ref DA-100 Analyzing Data with Microsoft Power BI

320 Pages·2021·22.13 MB·English

by  Daniil Maslyuk

Exam Ref DA-100
Analyzing Data with
Microsoft Power BI
Daniil Maslyuk
M00_Maslyuk_FM_pi-pxx.indd 1 30/03/21 8:16 pm
Exam Ref DA-100 Analyzing Data with Microsoft
Power BI
CREDITS
Published with the authorization of Microsoft Corporation by:
Pearson Education, Inc. EDITOR-IN-CHIEF
Hoboken, New Jersey Brett Bartow
EXECUTIVE EDITOR
Copyright © 2021 by Pearson Education, Inc.
Loretta Yates
All rights reserved. This publication is protected by copyright, and permission DEVELOPMENT EDITOR
must be obtained from the publisher prior to any prohibited reproduction, Songlin Qiu
storage in a retrieval system, or transmission in any form or by any means,
SPONSORING EDITOR
electronic, mechanical, photocopying, recording, or likewise. For information
Charvi Arora
regarding permissions, request forms, and the appropriate contacts within the
Pearson Education Global Rights & Permissions Department, please visit MANAGING EDITORS
www.pearson.com/permissions. Sandra Schroeder
No patent liability is assumed with respect to the use of the information SENIOR PROJECT EDITOR
contained herein. Although every precaution has been taken in the preparation Tracey Croom
of this book, the publisher and author assume no responsibility for errors or
COPY EDITOR
omissions. Nor is any liability assumed for damages resulting from the use of
Liz Welch
the information contained herein.
INDEXER
ISBN-13: 978-0-13-681968-4 Timothy Wright
ISBN-10: 0-13-681968-0
PROOFREADER
Betty Pessagno
Library of Congress Control Number: 2021935778
TECHNICAL EDITOR
ScoutAutomatedPrintCode Claire Mitchell, Owen Auger
TRADEMARKS EDITORIAL ASSISTANT
Cindy Teeters
Microsoft and the trademarks listed at http://www.microsoft.com on the
“Trademarks” webpage are trademarks of the Microsoft group of companies. COVER DESIGNER
All other marks are property of their respective owners. Twist Creative, Seattle
WARNING AND DISCLAIMER COMPOSITOR
codeMantra
Every 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 author, the publisher, and Microsoft Corporation shall have
neither liability nor responsibility to any person or entity with respect to any
loss or damages arising from the information contained in this book or from
the use of the programs accompanying it.
SPECIAL SALES
For information about buying this title in bulk quantities, or for special sales
opportunities (which may include electronic versions; custom cover designs;
and content particular to your business, training goals, marketing focus, or
branding interests), please contact our corporate sales department at
[email protected] or [phone].
For government sales inquiries, please contact [email protected].
For questions about sales outside the U.S., please contact [email protected].
M00_Maslyuk_FM_pi-pxx.indd 2 30/03/21 8:16 pm
To Dasha, Leonard, and William, who served as a great source of
motivation and support.
—Daniil Maslyuk
M00_Maslyuk_FM_pi-pxx.indd 3 30/03/21 8:16 pm
This page intentionally left blank
Contents at a glance
Introduction xiv
CHAPTER 1 Prepare the data 1
CHAPTER 2 Model the data 67
CHAPTER 3 Visualize the data 141
CHAPTER 4 Analyze the data 201
CHAPTER 5 Deploy and maintain deliverables 229
Index 263
M00_Maslyuk_FM_pi-pxx.indd 5 30/03/21 8:16 pm
This page intentionally left blank
Contents
Introduction xiv
Organization of this book xiv
Preparing for the exam xiv
Microsoft certifications xv
Companion files xv
Quick access to online references xvi
Errata, updates, & book support xvi
Stay in touch xvi
Chapter 1 Prepare the data 1
Skill 1.1: Get data from different data sources .............................1
Identify and connect to a data source 2
Change data source settings 6
Select a shared dataset or create a local dataset 7
Select a storage mode 9
Choose an appropriate query type 12
Identify query performance issues 15
Use Microsoft Dataverse 18
Use parameters 19
Use or create a PBIDS file 24
Use or create a dataflow 25
Connect to a dataset by using the XMLA endpoint 26
Skill 1.2: Profile the data ...............................................27
Identify data anomalies 27
Examine data structures and interrogate column properties 28
Interrogate data statistics 30
Skill 1.3: Clean, transform, and load the data. . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Resolve inconsistencies, unexpected or null values, and
data quality issues and apply user-friendly value replacements 32
Evaluate and transform column data types 35
Identify and create appropriate keys for joins 38
vii
M00_Maslyuk_FM_pi-pxx.indd 7 30/03/21 8:16 pm
Apply data shape transformations to table structures 40
Combine queries 50
Apply user-friendly naming conventions to
columns and queries 55
Leverage the Advanced Editor to modify Power Query
M code 55
Configure data loading 58
Resolve data import errors 59
Chapter summary .................................................... 61
Thought experiment ..................................................62
Thought experiment answers .........................................64
Chapter 2 Model the data 67
Skill 2.1: Design a data model ..........................................67
Define the tables 68
Configure table and column properties 71
Define quick measures 73
Flatten out a parent-child hierarchy 76
Define role-playing dimensions 79
Define a relationship’s cardinality and cross-filter direction 82
Design the data model to meet performance requirements 86
Resolve many-to-many relationships 87
Create a common date table 91
Define the appropriate level of data granularity 94
Skill 2.2: Develop a data model ........................................96
Apply cross-filter direction and security filtering 97
Create calculated tables 97
Create hierarchies 99
Create calculated columns 100
Implement row-level security roles 102
Set up the Q&A feature 108
Skill 2.3: Create measures by using DAX ................................113
Use DAX to build complex measures 113
Use CALCULATE to manipulate filters 116
Implement Time Intelligence using DAX 122
viii Contents
M00_Maslyuk_FM_pi-pxx.indd 8 30/03/21 8:16 pm
Replace numeric columns with measures 124
Use basic statistical functions to enhance data 125
Create semi-additive measures 125
Skill 2.4: Optimize model performance ...............................128
Remove unnecessary rows and columns 128
Identify poorly performing measures, relationships,
and visuals 129
Improve cardinality levels by changing data types 130
Improve cardinality levels through summarization 131
Create and manage aggregations 131
Chapter summary ...................................................133
Thought experiment ................................................. 135
Thought experiment answers ........................................138
Chapter 3 Visualize the data 141
Skill 3.1: Create reports ................................................141
Add visualization items to reports 142
Choose an appropriate visualization type 143
Format and configure visualizations 154
Import a custom visual 155
Configure conditional formatting 156
Apply slicing and filtering 158
Add an R or Python visual 161
Configure the report page 164
Design and configure for accessibility 165
Configure automatic page refresh 168
Create a paginated report 170
Skill 3.2: Create dashboards .......................................... 172
Manage tiles on a dashboard 172
Set mobile view 174
Configure data alerts 176
Use the Q&A feature 177
Add a dashboard theme 178
Pin a live report page to a dashboard 179
Contents ix
M00_Maslyuk_FM_pi-pxx.indd 9 30/03/21 8:16 pm