book

Modern Data Analytics in Excel

by George Mount

April 2024

Beginner to intermediate

244 pages

5h 18m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Includes

Includes Quizzes

Learning ObjectivePrerequisitesTechnical RequirementsTechnological RequirementsHow I Got HereWhat Is “Modern Analytics”? Why Excel?Book OverviewPart I, Data Cleaning and Transformation with Power QueryPart II, Data Modeling and Analysis with Power PivotPart III, The Excel Data Analytics ToolkitEnd-of-Chapter ExercisesThis Is Not a Laundry ListConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
Creating and Referring to Table HeadersViewing the Table FootersNaming Excel TablesFormatting Excel TablesUpdating Table RangesOrganizing Data for AnalyticsConclusionExercises
What Is Power Query?Power Query as Excel Myth Buster“Excel Is Not Reproducible”“Excel Does Not Have a True null”“Excel Can’t Process More Than 1,048,576 Rows”Power Query as Excel’s ETL ToolExtractTransformLoadA Tour of the Power Query EditorThe Ribbon MenuQueriesThe Imported DataExiting the Power Query EditorReturning to the Power Query EditorData Profiling in Power QueryWhat Is Data Profiling?Exploring the Data Preview OptionsOverriding the Thousand-Row LimitClosing Out of Data ProfilingConclusionExercises
Removing the Missing ValuesRefreshing the QuerySplitting Data into RowsFilling in Headers and Cell ValuesReplacing Column HeadersFilling Down Blank RowsConclusionExercises
Changing Column CaseDelimiting by ColumnChanging Data TypesDeleting ColumnsWorking with DatesCreating Custom ColumnsLoading & Inspecting the DataCalculated Columns Versus MeasuresReshaping DataConclusionExercises
Appending Multiple SourcesConnecting to External Excel WorkbooksAppending the QueriesUnderstanding Relational JoinsLeft Outer Join: Think VLOOKUP()Inner Join: Only the MatchesManaging Your QueriesGrouping Your QueriesViewing Query DependenciesConclusionExercises
What Is Power Pivot?Why Power Pivot?Power Pivot and the Data ModelLoading the Power Pivot Add-inA Brief Tour of the Power Pivot Add-InData ModelCalculationsTablesRelationshipsSettingsConclusionExercises
Connecting Data to Power PivotCreating RelationshipsIdentifying Fact and Dimension TablesArranging the Diagram ViewEditing the RelationshipsLoading the Results to ExcelUnderstanding CardinalityOne-to-One CardinalityOne-to-Many RelationshipsMany-to-Many RelationshipsWhy Does Cardinality Matter?Understanding Filter DirectionFiltering orders with usersFiltering users with ordersFilter Direction and CardinalityFrom Design to Practice in Power PivotCreating Columns in Power PivotCalculating in Power Query Versus Power PivotExample: Calculating Profit MarginRecoding Column Values with SWITCH()Creating and Managing HierarchiesCreating a Hierarchy in Power PivotUsing Hierarchies in the PivotTableLoading the Data Model to Power BIPower BI as the Third Piece of “Modern Excel”Importing the Data Model to Power BIViewing the Data in Power BIConclusionExercises

Creating DAX MeasuresCreating Implicit MeasuresCreating Explicit MeasuresCreating KPIsAdjusting Icon StylesAdding the KPI to the PivotTableConclusionExercises
CALCULATE() and the Importance of Filter ContextCALCULATE() with One CriterionCALCULATE() with Multiple CriteriaAND ConditionsOR ConditionsCALCULATE() with ALL()Time Intelligence FunctionsAdding a Calendar TableCreating Basic Time Intelligence MeasuresConclusionExercises
Dynamic Array Functions ExplainedWhat Is an Array in Excel?Array ReferencesArray FormulasAn Overview of Dynamic Array FunctionsFinding Distinct and Unique Values with UNIQUE()Finding Unique Versus Distinct ValuesUsing the Spill OperatorFiltering Records with FILTER()Adding a Header ColumnFiltering by Multiple CriteriaSorting Records with SORTBY()Sorting by Multiple CriteriaSorting by Another Column Without Printing ItCreating Modern Lookups with XLOOKUP()XLOOKUP() Versus VLOOKUP()A Basic XLOOKUP()XLOOKUP() and Error HandlingXLOOKUP() and Looking Up to the LeftOther Dynamic Array FunctionsDynamic Arrays and Modern ExcelConclusionExercises
The Growing Complexity of Data and AnalyticsExcel and the Legacy of Self-Service BIExcel for Augmented AnalyticsUsing Analyze Data for AI Powered InsightsBuilding Statistical Models with XLMinerReading Data from an ImageSentiment Analysis with Azure Machine LearningConclusionExercises
Reader PrerequisitesThe Role of Python in Modern ExcelA Growing Stack Requires GlueNetwork Effects Mean Faster Development TimeBring Modern Development to ExcelUsing Python and Excel Together with pandas and openpyxlOther Python Packages for ExcelDemonstration of Excel Automation with pandas and openpyxlCleaning Up the Data in pandasSummarizing Findings with openpyxlAdding a Styled Data SourceConclusionExercises
Exploring Excel’s Other FeaturesLET() and LAMBDA()Power Automate, Office Scripts, and Excel OnlineContinued Exploration of Power Query and Power PivotPower Query and MPower Pivot and DAXPower BI for Dashboards and ReportsAzure and Cloud ComputingPython ProgrammingLarge Language Models and Prompt EngineeringParting Words