Acknowledgements |
|
xi | |
Introduction |
|
xv | |
Organization of this book |
|
xv | |
Microsoft certifications |
|
xvi | |
Microsoft Virtual Academy |
|
xvi | |
Quick access to online references |
|
xvi | |
Errata, updates, & book support |
|
xvii | |
Stay in touch |
|
xvii | |
Important: How to use this book to study for the exam |
|
xix | |
|
Chapter 1 Consume and transform data by using Microsoft Excel |
|
|
1 | (86) |
|
Skill 1.1 Import from data sources |
|
|
2 | (30) |
|
Connect to and import from databases, files, and folders |
|
|
2 | (22) |
|
Connect to Microsoft SQL Azure and Big Data |
|
|
24 | (3) |
|
Import from Excel workbooks |
|
|
27 | (1) |
|
Link to data from other sources |
|
|
27 | (3) |
|
|
30 | (2) |
|
Skill 1.2 Perform data transformations |
|
|
32 | (47) |
|
Design and implement basic and advanced transformations |
|
|
32 | (39) |
|
|
71 | (2) |
|
Change data format to support visualization |
|
|
73 | (1) |
|
|
74 | (5) |
|
|
79 | (1) |
|
|
79 | (8) |
|
|
80 | (1) |
|
Handle data received as a report |
|
|
81 | (2) |
|
|
83 | (2) |
|
Thought experiment answers |
|
|
85 | (1) |
|
|
85 | (2) |
|
|
87 | (84) |
|
Skill 2.1 Create and optimize data models |
|
|
87 | (29) |
|
Understanding the Excel data model |
|
|
88 | (3) |
|
|
91 | (6) |
|
|
97 | (2) |
|
Manage data relationships |
|
|
99 | (9) |
|
Optimize models for reporting |
|
|
108 | (8) |
|
Skill 2.2 Create calculated columns, measures, and tables |
|
|
116 | (36) |
|
|
116 | (25) |
|
|
141 | (8) |
|
|
149 | (3) |
|
Skill 2.3 Create Hierarchies |
|
|
152 | (8) |
|
|
153 | (3) |
|
Create business hierarchies |
|
|
156 | (2) |
|
|
158 | (2) |
|
Skill 2.4 Create Performance KPIs |
|
|
160 | (11) |
|
Calculate the actual value |
|
|
160 | (1) |
|
Calculate the target value |
|
|
161 | (1) |
|
Calculate actual-to-target values |
|
|
162 | (1) |
|
|
163 | (3) |
|
Thought experiment answers |
|
|
166 | (1) |
|
|
167 | (4) |
|
|
171 | (68) |
|
Skill 3.1 Create and manage PivotTables |
|
|
171 | (27) |
|
|
172 | (20) |
|
Format calculated measures |
|
|
192 | (1) |
|
|
193 | (5) |
|
|
198 | (1) |
|
Skill 3.2 Create and manage PivotCharts |
|
|
198 | (23) |
|
|
199 | (18) |
|
|
217 | (3) |
|
|
220 | (1) |
|
Skill 3.3 Interact with Power BI |
|
|
221 | (18) |
|
|
222 | (2) |
|
Import Excel data from Power BI |
|
|
224 | (8) |
|
Manipulate Excel data in Power BI |
|
|
232 | (1) |
|
|
232 | (2) |
|
Thought experiment answers |
|
|
234 | (1) |
|
|
234 | (5) |
Index |
|
239 | |