Mount George / Маунт Джордж - Modern Data Analytics in Excel / Современная аналитика данных в Excel [2024, PDF/EPUB, ENG]

Примеры страниц (скриншоты)

Оглавление

Preface ix
Part I. Data Cleaning and Transformation with Power Query
1. Tables: The Portal to Modern Excel 3
Creating and Referring to Table Headers 3
Viewing the Table Footers 6
Naming Excel Tables 8
Formatting Excel Tables 9
Updating Table Ranges 9
Organizing Data for Analytics 10
Conclusion 11
Exercises 11
2. First Steps in Excel Power Query 13
What Is Power Query? 13
Power Query as Excel Myth Buster 13
“Excel Is Not Reproducible” 13
“Excel Does Not Have a True null” 14
“Excel Can’t Process More Than 1,048,576 Rows” 15
Power Query as Excel’s ETL Tool 15
Extract 15
Transform 17
Load 18
A Tour of the Power Query Editor 18
The Ribbon Menu 19
Queries 21
The Imported Data 22
Exiting the Power Query Editor 24
Returning to the Power Query Editor 26
Data Profiling in Power Query 26
What Is Data Profiling? 27
Exploring the Data Preview Options 27
Overriding the Thousand-Row Limit 31
Closing Out of Data Profiling 31
Conclusion 32
Exercises 32
3. Transforming Rows in Power Query 33
Removing the Missing Values 34
Refreshing the Query 37
Splitting Data into Rows 39
Filling in Headers and Cell Values 42
Replacing Column Headers 42
Filling Down Blank Rows 43
Conclusion 44
Exercises 44
4. Transforming Columns in Power Query 45
Changing Column Case 45
Delimiting by Column 47
Changing Data Types 47
Deleting Columns 48
Working with Dates 48
Creating Custom Columns 49
Loading & Inspecting the Data 51
Calculated Columns Versus Measures 52
Reshaping Data 53
Conclusion 54
Exercises 55
5. Merging and Appending Data in Power Query 57
Appending Multiple Sources 57
Connecting to External Excel Workbooks 58
Appending the Queries 61
Understanding Relational Joins 62
Left Outer Join: Think VLOOKUP() 64
Inner Join: Only the Matches 68
Managing Your Queries 70
Grouping Your Queries 70
Viewing Query Dependencies 71
Conclusion 72
Exercises 73
Part II. Data Modeling and Analysis with Power Pivot
6. First Steps in Power Pivot 77
What Is Power Pivot? 77
Why Power Pivot? 77
Power Pivot and the Data Model 80
Loading the Power Pivot Add-in 81
A Brief Tour of the Power Pivot Add-In 83
Data Model 83
Calculations 83
Tables 84
Relationships 84
Settings 84
Conclusion 84
Exercises 85
7. Creating Relational Models in Power Pivot 87
Connecting Data to Power Pivot 87
Creating Relationships 88
Identifying Fact and Dimension Tables 92
Arranging the Diagram View 93
Editing the Relationships 94
Loading the Results to Excel 95
Understanding Cardinality 99
One-to-One Cardinality 100
One-to-Many Relationships 101
Many-to-Many Relationships 101
Why Does Cardinality Matter? 102
Understanding Filter Direction 103
Filtering orders with users 104
Filtering users with orders 105
Filter Direction and Cardinality 106
From Design to Practice in Power Pivot 106
Creating Columns in Power Pivot 106
Calculating in Power Query Versus Power Pivot 106
Example: Calculating Profit Margin 107
Recoding Column Values with SWITCH() 109
Creating and Managing Hierarchies 111
Creating a Hierarchy in Power Pivot 111
Using Hierarchies in the PivotTable 112
Loading the Data Model to Power BI 113
Power BI as the Third Piece of “Modern Excel” 113
Importing the Data Model to Power BI 114
Viewing the Data in Power BI 116
Conclusion 118
Exercises 118
8. Creating Measures and KPIs in Power Pivot 119
Creating DAX Measures 119
Creating Implicit Measures 119
Creating Explicit Measures 122
Creating KPIs 128
Adjusting Icon Styles 130
Adding the KPI to the PivotTable 131
Conclusion 132
Exercises 132
9. Intermediate DAX for Power Pivot 133
CALCULATE() and the Importance of Filter Context 134
CALCULATE() with One Criterion 135
CALCULATE() with Multiple Criteria 136
AND Conditions 136
OR Conditions 137
CALCULATE() with ALL() 138
Time Intelligence Functions 140
Adding a Calendar Table 140
Creating Basic Time Intelligence Measures 143
Conclusion 149
Exercises 149
Part III. The Excel Data Analytics Toolkit
10. Introducing Dynamic Array Functions 153
Dynamic Array Functions Explained 153
What Is an Array in Excel? 154
Array References 154
Array Formulas 156
An Overview of Dynamic Array Functions 158
Finding Distinct and Unique Values with UNIQUE() 158
Finding Unique Versus Distinct Values 159
Using the Spill Operator 160
Filtering Records with FILTER() 160
Adding a Header Column 162
Filtering by Multiple Criteria 162
Sorting Records with SORTBY() 163
Sorting by Multiple Criteria 164
Sorting by Another Column Without Printing It 164
Creating Modern Lookups with XLOOKUP() 165
XLOOKUP() Versus VLOOKUP() 165
A Basic XLOOKUP() 166
XLOOKUP() and Error Handling 167
XLOOKUP() and Looking Up to the Left 168
Other Dynamic Array Functions 168
Dynamic Arrays and Modern Excel 169
Conclusion 169
Exercises 170
11. Augmented Analytics and the Future of Excel 171
The Growing Complexity of Data and Analytics 171
Excel and the Legacy of Self-Service BI 172
Excel for Augmented Analytics 173
Using Analyze Data for AI Powered Insights 173
Building Statistical Models with XLMiner 179
Reading Data from an Image 181
Sentiment Analysis with Azure Machine Learning 184
Conclusion 188
Exercises 188
12. Python with Excel 189
Reader Prerequisites 190
The Role of Python in Modern Excel 190
A Growing Stack Requires Glue 190
Network Effects Mean Faster Development Time 191
Bring Modern Development to Excel 192
Using Python and Excel Together with pandas and openpyxl 193
Other Python Packages for Excel 194
Demonstration of Excel Automation with pandas and openpyxl 195
Cleaning Up the Data in pandas 196
Summarizing Findings with openpyxl 200
Adding a Styled Data Source 204
Conclusion 206
Exercises 207
13. Conclusion and Next Steps 209
Exploring Excel’s Other Features 209
LET() and LAMBDA() 210
Power Automate, Office Scripts, and Excel Online 210
Continued Exploration of Power Query and Power Pivot 211
Power Query and M 211
Power Pivot and DAX 212
Power BI for Dashboards and Reports 213
Azure and Cloud Computing 213
Python Programming 214
Large Language Models and Prompt Engineering 214
Parting Words 215
Index 217