Collect, Combine, and Transform Data Using Power Query in Excel and Power BI

Cover
Title Page
Copyright Page
Contents
Introduction
Chapter 1 Introduction to Power Query
	What Is Power Query?
		A Brief History of Power Query
		Where Can I Find Power Query?
	Main Components of Power Query
		Get Data and Connectors
		The Main Panes of the Power Query Editor
	Exercise 1-1: A First Look at Power Query
	Summary
Chapter 2 Basic Data Preparation Challenges
	Extracting Meaning from Encoded Columns
		AdventureWorks Challenge
		Exercise 2-1: The Old Way: Using Excel Formulas
		Exercise 2-2, Part 1: The New Way
		Exercise 2-2, Part 2: Merging Lookup Tables
		Exercise 2-2, Part 3: Fact and Lookup Tables
	Using Column from Examples
		Exercise 2-3, Part 1: Introducing Column from Examples
		Practical Use of Column from Examples
		Exercise 2-3, Part 2: Converting Size to Buckets/Ranges
	Extracting Information from Text Columns
		Exercise 2-4: Extracting Hyperlinks from Messages
	Handling Dates
		Exercise 2-5: Handling Multiple Date Formats
		Exercise 2-6: Handling Dates with Two Locales
		Extracting Date and Time Elements
	Preparing the Model
		Exercise 2-7: Splitting Data into Lookup Tables and Fact Tables
		Exercise 2-8: Splitting Delimiter-Separated Values into Rows
	Summary
Chapter 3 Combining Data from Multiple Sources
	Appending a Few Tables
		Appending Two Tables
		Exercise 3-1: Bikes and Accessories Example
		Exercise 3-2, Part 1: Using Append Queries as New
		Exercise 3-2, Part 2: Query Dependencies and References
		Appending Three or More Tables
		Exercise 3-2, Part 3: Bikes + Accessories + Components
		Exercise 3-2, Part 4: Bikes + Accessories + Components + Clothing
	Appending Tables on a Larger Scale
		Appending Tables from a Folder
		Exercise 3-3: Appending AdventureWorks Products from a Folder
		Thoughts on Import from Folder
		Appending Worksheets from a Workbook
		Exercise 3-4: Appending Worksheets: The Solution
	Summary
Chapter 4 Combining Mismatched Tables
	The Problem of Mismatched Tables
		What Are Mismatched Tables?
		The Symptoms and Risks of Mismatched Tables
		Exercise 4-1: Resolving Mismatched Column Names: The Reactive Approach
	Combining Mismatched Tables from a Folder
		Exercise 4-2, Part 1: Demonstrating the Missing Values Symptom
		Exercise 4-2, Part 2: The Same-Order Assumption and the Header Generalization Solution
		Exercise 4-3: Simple Normalization Using Table.TransformColumnNames
		The Conversion Table
		Exercise 4-4: The Transpose Techniques Using a Conversion Table
		Exercise 4-5: Unpivot, Merge, and Pivot Back
		Exercise 4-6: Transposing Column Names Only
		Exercise 4-7: Using M to Normalize Column Names
	Summary
Chapter 5 Preserving Context
	Preserving Context in File Names and Worksheets
		Exercise 5-1, Part 1: Custom Column Technique
		Exercise 5-1, Part 2: Handling Context from File Names and Worksheet Names
	Pre-Append Preservation of Titles
		Exercise 5-2: Preserving Titles Using Drill Down
		Exercise 5-3: Preserving Titles from a Folder
	Post-Append Context Preservation of Titles
		Exercise 5-4: Preserving Titles from Worksheets in the same Workbook
	Using Context Cues
		Exercise 5-5: Using an Index Column as a Cue
		Exercise 5-6: Identifying Context by Cell Proximity
	Summary
Chapter 6 Unpivoting Tables
	Identifying Badly Designed Tables
	Introduction to Unpivot
		Exercise 6-1: Using Unpivot Columns and Unpivot Other Columns
		Exercise 6-2: Unpivoting Only Selected Columns
	Handling Totals
		Exercise 6-3: Unpivoting Grand Totals
	Unpivoting 2×2 Levels of Hierarchy
		Exercise 6-4: Unpivoting 2×2 Levels of Hierarchy with Dates
		Exercise 6-5: Unpivoting 2×2 Levels of Hierarchy
	Handling Subtotals in Unpivoted Data
		Exercise 6-6: Handling Subtotals
	Summary
Chapter 7 Advanced Unpivoting and Pivoting of Tables
	Unpivoting Tables with Multiple Levels of Hierarchy
		The Virtual PivotTable, Row Fields, and Column Fields
		Exercise 7-1: Unpivoting the AdventureWorks N×M Levels of Hierarchy
	Generalizing the Unpivot Sequence
		Exercise 7-2: Starting at the End
		Exercise 7-3: Creating FnUnpivotSummarizedTable
	The Pivot Column Transformation
		Exercise 7-4: Reversing an Incorrectly Unpivoted Table
		Exercise 7-5: Pivoting Tables of Multiline Records
	Summary
Chapter 8 Addressing Collaboration Challenges
	Local Files, Parameters, and Templates
		Accessing Local Files—Incorrectly
		Exercise 8-1: Using a Parameter for a Path Name
		Exercise 8-2: Creating a Template in Power BI
		Exercise 8-3: Using Parameters in Excel
	Working with Shared Files and Folders
		Importing Data from Files on OneDrive for Business or SharePoint
		Exercise 8-4: Migrating Your Queries to Connect to OneDrive for Business or SharePoint
		Exercise 8-5: From Local to SharePoint Folders
	Security Considerations
		Removing All Queries Using the Document Inspector in Excel
	Summary
Chapter 9 Introduction to the Power Query M Formula Language
	Learning M
		Learning Maturity Stages
		Online Resources
		Offline Resources
		Exercise 9-1: Using #shared to Explore Built-in Functions
	M Building Blocks
		Exercise 9-2: Hello World
		The let Expression
		Merging Expressions from Multiple Queries and Scope Considerations
		Types, Operators, and Built-in Functions in M
	Basic M Types
		The Number Type
		The Time Type
		The Date Type
		The Duration Type
		The Text Type
		The Null Type
		The Logical Type
	Complex Types
		The List Type
		The Record Type
		The Table Type
	Conditions and If Expressions
		if-then-else
		An if Expression Inside a let Expression
	Custom Functions
		Invoking Functions
		The each Expression
	Advanced Topics
		Error Handling
		Lazy and Eager Evaluations
		Loops
		Recursion
		List.Generate
		List.Accumulate
	Summary
Chapter 10 From Pitfalls to Robust Queries
	The Causes and Effects of the Pitfalls
		Awareness
		Best Practices
		M Modifications
	Pitfall 1: Ignoring the Formula Bar
		Exercise 10-1: Using the Formula Bar to Detect Static References to Column Names
	Pitfall 2: Changed Types
	Pitfall 3: Dangerous Filtering
		Exercise 10-2, Part 1: Filtering Out Black Products
		The Logic Behind the Filtering Condition
		Exercise 10-2, Part 2: Searching Values in the Filter Pane
	Pitfall 4: Reordering Columns
		Exercise 10-3, Part 1: Reordering a Subset of Columns
		Exercise 10-3, Part 2: The Custom Function FnReorderSubsetOfColumns
	Pitfall 5: Removing and Selecting Columns
		Exercise 10-4: Handling the Random Columns in the Wide World Importers Table
	Pitfall 6: Renaming Columns
		Exercise 10-5: Renaming the Random Columns in the Wide World Importers Table
	Pitfall 7: Splitting a Column into Columns
		Exercise 10-6: Making an Incorrect Split
	Pitfall 8: Merging Columns
		More Pitfalls and Techniques for Robust Queries
	Summary
Chapter 11 Basic Text Analytics
	Searching for Keywords in Textual Columns
		Exercise 11-1: Basic Detection of Keywords
		Using a Cartesian Product to Detect Keywords
		Exercise 11-2: Implementing a Cartesian Product
		Exercise 11-3: Detecting Keywords by Using a Custom Function
		Which Method to Use: Static Search, Cartesian Product, or Custom Function?
	Word Splits
		Exercise 11-4: Naïve Splitting of Words
		Exercise 11-5: Filtering Out Stop Words
		Exercise 11-6: Searching for Keywords by Using Split Words
		Exercise 11-7: Creating Word Clouds in Power BI
	Summary
Chapter 12 Advanced Text Analytics: Extracting Meaning
	Microsoft Azure Cognitive Services
		API Keys and Resources Deployment on Azure
		Pros and Cons of Cognitive Services via Power Query
	Text Translation
		The Translator Text API Reference
		Exercise 12-1: Simple Translation
		Exercise 12-2: Translating Multiple Messages
	Sentiment Analysis
		What Is the Sentiment Analysis API Call?
		Exercise 12-3: Implementing the FnGetSentiment Sentiment Analysis Custom Function
		Exercise 12-4: Running Sentiment Analysis on Large Datasets
	Extracting Key Phrases
		Exercise 12-5: Converting Sentiment Logic to Key Phrases
	Multi-Language Support
		Replacing the Language Code
		Dynamic Detection of Languages
		Exercise 12-6: Converting Sentiment Logic to Language Detection
	Summary
Chapter 13 Social Network Analytics
	Getting Started with the Facebook Connector
		Exercise 13-1: Finding the Pages You Liked
	Analyzing Your Friends
		Exercise 13-2: Finding Your Power BI Friends and Their Friends
		Exercise 13-3: Find the Pages Your Friends Liked
	Analyzing Facebook Pages
		Exercise 13-4: Extracting Posts and Comments from Facebook Pages—The Basic Way
		Short Detour: Filtering Results by Time
		Exercise 13-5: Analyzing User Engagement by Counting Comments and Shares
		Exercise 13-6: Comparing Multiple Pages
	Summary
Chapter 14 Final Project: Combining It All Together
	Exercise 14-1: Saving the Day at Wide World Importers
		Clues
		Part 1: Starting the Solution
		Part 2: Invoking the Unpivot Function
		Part 3: The Pivot Sequence on 2018 Revenues
		Part 4: Combining the 2018 and 2015–2017 Revenues
	Exercise 14-2: Comparing Tables and Tracking the Hacker
		Clues
		Exercise 14-2: The Solution
		Detecting the Hacker’s Footprints in the Compromised Table
	Summary
Index
	A
	B
	C
	D
	E
	F
	G
	H
	I
	J
	K
	L
	M
	N
	O
	P
	Q
	R
	S
	T
	U
	V
	W
	X-Y-Z