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