| Introduction |
|
1 | (1) |
| About This Book |
|
2 | (1) |
| Foolish Assumptions |
|
3 | (1) |
| Icons Used in This Book |
|
3 | (1) |
| Beyond the Book |
|
4 | (1) |
| Where to Go from Here |
|
4 | (1) |
|
PART 1 SUPERCHARGED REPORTING WITH POWER PIVOT |
|
|
5 | (136) |
|
Chapter 1 Thinking Like a Database |
|
|
1 | (16) |
|
Exploring the Limits of Excel and How Databases Help |
|
|
7 | (4) |
|
|
|
8 | (1) |
|
Transparency of analytical processes |
|
|
9 | (1) |
|
Separation of data and presentation |
|
|
10 | (1) |
|
Getting to Know Database Terminology |
|
|
11 | (2) |
|
|
|
11 | (1) |
|
|
|
11 | (1) |
|
Records, fields, and values |
|
|
12 | (1) |
|
|
|
13 | (1) |
|
Understanding Relationships |
|
|
13 | (4) |
|
Chapter 2 Introducing Power Pivot |
|
|
17 | (12) |
|
Understanding the Power Pivot Internal Data Model |
|
|
18 | (2) |
|
Linking Excel Tables to Power Pivot |
|
|
20 | (9) |
|
|
|
21 | (1) |
|
Adding Excel Tables to the data model |
|
|
22 | (2) |
|
Creating relationships between Power Pivot tables |
|
|
24 | (2) |
|
Managing existing relationships |
|
|
26 | (1) |
|
Using the Power Pivot data model in reporting |
|
|
27 | (2) |
|
Chapter 3 The Pivotal Pivot Table |
|
|
29 | (34) |
|
Introducing the Pivot Table |
|
|
30 | (1) |
|
Defining the Four Areas of a Pivot Table |
|
|
30 | (3) |
|
|
|
30 | (1) |
|
|
|
31 | (1) |
|
|
|
31 | (1) |
|
|
|
32 | (1) |
|
Creating Your First Pivot Table |
|
|
33 | (7) |
|
Changing and rearranging a pivot table |
|
|
36 | (1) |
|
|
|
37 | (1) |
|
Keeping the pivot table fresh |
|
|
38 | (2) |
|
Customizing Pivot Table Reports |
|
|
40 | (12) |
|
Changing the pivot table layout |
|
|
40 | (1) |
|
|
|
41 | (1) |
|
Applying numeric formats to data fields |
|
|
42 | (1) |
|
Changing summary calculations |
|
|
43 | (1) |
|
|
|
44 | (3) |
|
Showing and hiding data items |
|
|
47 | (2) |
|
Hiding or showing items without data |
|
|
49 | (2) |
|
|
|
51 | (1) |
|
|
|
52 | (2) |
|
Creating a Standard Slicer |
|
|
54 | (2) |
|
Getting Fancy with Slicer Customizations |
|
|
56 | (2) |
|
|
|
56 | (1) |
|
|
|
57 | (1) |
|
Miscellaneous slicer settings |
|
|
58 | (1) |
|
Controlling Multiple Pivot Tables with One Slicer |
|
|
58 | (1) |
|
Creating a Timeline Slicer |
|
|
59 | (4) |
|
Chapter 4 Using External Data with Power Pivot |
|
|
63 | (26) |
|
Loading Data from Relational Databases |
|
|
64 | (11) |
|
Loading data from SQL Server |
|
|
64 | (6) |
|
Loading data from Microsoft Access databases |
|
|
70 | (2) |
|
Loading data from other relational database systems |
|
|
72 | (3) |
|
Loading Data from Flat Files |
|
|
75 | (7) |
|
Loading data from external Excel files |
|
|
76 | (2) |
|
Loading data from text files |
|
|
78 | (3) |
|
Loading data from the Clipboard |
|
|
81 | (1) |
|
Loading Data from Other Data Sources |
|
|
82 | (1) |
|
Refreshing and Managing External Data Connections |
|
|
83 | (6) |
|
Manually refreshing Power Pivot data |
|
|
83 | (1) |
|
Setting up automatic refreshing |
|
|
84 | (1) |
|
|
|
85 | (1) |
|
Editing the data connection |
|
|
86 | (3) |
|
Chapter 5 Working Directly with the Internal Data Model |
|
|
89 | (14) |
|
Directly Feeding the Internal Data Model |
|
|
89 | (6) |
|
Managing Relationships in the Internal Data Model |
|
|
95 | (1) |
|
Managing Queries and Connections |
|
|
96 | (1) |
|
Creating a New Pivot Table Using the Internal Data Model |
|
|
97 | (1) |
|
Filling the Internal Data Model with Multiple External Data Tables |
|
|
98 | (5) |
|
Chapter 6 Adding Formulas to Power Pivot |
|
|
103 | (18) |
|
Enhancing Power Pivot Data with Calculated Columns |
|
|
103 | (5) |
|
Creating your first calculated column |
|
|
104 | (1) |
|
Formatting calculated columns |
|
|
105 | (1) |
|
Referencing calculated columns in other calculations |
|
|
106 | (1) |
|
Hiding calculated columns from end users |
|
|
107 | (1) |
|
Utilizing DAX to Create Calculated Columns |
|
|
108 | (8) |
|
Identifying DAX functions that are safe for calculated columns |
|
|
108 | (2) |
|
Building DAX-driven calculated columns |
|
|
110 | (2) |
|
Month sorting in Power Pivot-driven pivot tables |
|
|
112 | (1) |
|
Referencing fields from other tables |
|
|
113 | (2) |
|
|
|
115 | (1) |
|
Understanding Calculated Measures |
|
|
116 | (3) |
|
Creating a calculated measure |
|
|
116 | (2) |
|
Editing and deleting calculated measures |
|
|
118 | (1) |
|
Free Your Data with Cube Functions |
|
|
119 | (2) |
|
Chapter 7 Diving into DAX |
|
|
121 | (20) |
|
DAX Language Fundamentals |
|
|
121 | (12) |
|
|
|
125 | (1) |
|
Applying conditional logic in DAX |
|
|
126 | (2) |
|
Working with DAX aggregate functions |
|
|
128 | (1) |
|
Exploring iterator functions and row context |
|
|
129 | (4) |
|
Understanding Filter Context |
|
|
133 | (8) |
|
Getting context transitions with the CALCULATE function |
|
|
135 | (2) |
|
Adding flexibility with the FILTER function |
|
|
137 | (4) |
|
PART 2 WRANGLING DATA WITH POWER QUERY |
|
|
141 | (102) |
|
Chapter 8 Introducing Power Query |
|
|
143 | (16) |
|
|
|
144 | (11) |
|
|
|
144 | (6) |
|
Understanding query steps |
|
|
150 | (2) |
|
Refreshing Power Query data |
|
|
152 | (1) |
|
Managing existing queries |
|
|
153 | (2) |
|
Understanding Column-Level Actions |
|
|
155 | (2) |
|
Understanding Table Actions |
|
|
157 | (2) |
|
Chapter 9 Power Query Connection Types |
|
|
159 | (16) |
|
Importing Data from Files |
|
|
160 | (5) |
|
Getting data from Excel workbooks |
|
|
160 | (1) |
|
Getting data from CSV and text files |
|
|
161 | (2) |
|
Getting data from PDF files |
|
|
163 | (1) |
|
Getting data from folders |
|
|
164 | (1) |
|
Importing Data from Database Systems |
|
|
165 | (1) |
|
A connection for every database type |
|
|
165 | (5) |
|
Getting data from other data systems |
|
|
167 | (1) |
|
Walk-through: Getting data from a database |
|
|
168 | (2) |
|
Managing Data Source Settings |
|
|
170 | (1) |
|
Data Profiling with Power Query |
|
|
171 | (4) |
|
|
|
172 | (1) |
|
Data Profiling quick actions |
|
|
173 | (2) |
|
Chapter 10 Transforming Your Way to Better Data |
|
|
175 | (32) |
|
Completing Common Transformation Tasks |
|
|
176 | (17) |
|
Removing duplicate records |
|
|
176 | (2) |
|
|
|
178 | (1) |
|
|
|
179 | (2) |
|
|
|
181 | (1) |
|
Finding and replacing specific text |
|
|
181 | (2) |
|
Trimming and cleaning text |
|
|
183 | (1) |
|
Extracting the left, right, and middle values |
|
|
184 | (3) |
|
Splitting columns using character markers |
|
|
187 | (2) |
|
Pivoting and unpivoting fields |
|
|
189 | (4) |
|
|
|
193 | (8) |
|
Concatenating with a custom column |
|
|
195 | (1) |
|
Understanding data type conversions |
|
|
196 | (1) |
|
Spicing up custom columns with functions |
|
|
197 | (2) |
|
Adding conditional logic to custom columns |
|
|
199 | (2) |
|
Grouping and Aggregating Data |
|
|
201 | (2) |
|
Working with Custom Data Types |
|
|
203 | (4) |
|
Chapter 11 Making Queries Work Together |
|
|
207 | (18) |
|
|
|
208 | (3) |
|
Understanding the Append Feature |
|
|
211 | (5) |
|
Creating the needed base queries |
|
|
212 | (1) |
|
|
|
213 | (3) |
|
Understanding the Merge Feature |
|
|
216 | (5) |
|
Understanding Power Query joins |
|
|
216 | (1) |
|
|
|
217 | (4) |
|
Understanding Fuzzy Match |
|
|
221 | (4) |
|
Chapter 12 Extending Power Query with Custom Functions |
|
|
225 | (18) |
|
Creating and Using a Basic Custom Function |
|
|
225 | (4) |
|
Creating a Function to Merge Data from Multiple Excel Files |
|
|
229 | (7) |
|
Creating Parameter Queries |
|
|
236 | (7) |
|
Preparing for a parameter query |
|
|
236 | (2) |
|
|
|
238 | (1) |
|
Creating the parameter query |
|
|
239 | (4) |
|
|
|
243 | (20) |
|
Chapter 13 Ten Ways to Improve Power Pivot Performance |
|
|
245 | (8) |
|
Limit the Number of Rows and Columns in Your Data Model Tables |
|
|
246 | (1) |
|
Use Views Instead of Tables |
|
|
246 | (1) |
|
Avoid Multi-Level Relationships |
|
|
246 | (1) |
|
Let the Back-End Database Servers Do the Crunching |
|
|
247 | (1) |
|
Beware of Columns with Many Unique Values |
|
|
248 | (1) |
|
Limit the Number of Slicers in a Report |
|
|
248 | (1) |
|
Create Slicers Only on Dimension Fields |
|
|
249 | (1) |
|
Disable the Cross-Filter Behavior for Certain Slicers |
|
|
250 | (1) |
|
Use Calculated Measures Instead of Calculated Columns |
|
|
250 | (1) |
|
|
|
251 | (2) |
|
Chapter 14 Ten Tips for Working with Power Query |
|
|
253 | (10) |
|
Getting Quick Information from the Queries & Connections Pane |
|
|
253 | (1) |
|
Organizing Queries in Groups |
|
|
254 | (1) |
|
Selecting Columns in Queries Faster |
|
|
255 | (1) |
|
|
|
256 | (1) |
|
Quickly Creating Reference Tables |
|
|
257 | (1) |
|
Viewing Query Dependencies |
|
|
258 | (1) |
|
Setting a Default Load Behavior |
|
|
259 | (1) |
|
Preventing Automatic Data Type Changes |
|
|
259 | (2) |
|
Disabling Privacy Settings to Improve Performance |
|
|
261 | (1) |
|
Disabling Relationship Detection |
|
|
261 | (2) |
| Index |
|
263 | |
Lisainfo e-raamatute kohta