Collect, Combine, and Transform Data Using Power Query in Excel and Power BI
1st Edition
Published on 8. October 2018
368 pages
978-1-5093-0801-9 (ISBN)
Description
Using Power Query, you can import, reshape, and cleanse any data from a simple interface, so you can mine that data for all of its hidden insights. Power Query is embedded in Excel, Power BI, and other Microsoft products, and leading Power Query expert Gil Raviv will help you make the most of it. Discover how to eliminate time-consuming manual data preparation, solve common problems, avoid pitfalls, and more. Then, walk through several complete analytics challenges, and integrate all your skills in a realistic chapter-length final project. By the time you're finished, you'll be ready to wrangle any data-and transform it into actionable knowledge.
Prepare and analyze your data the easy way, with Power Query
? Quickly prepare data for analysis with Power Query in Excel (also known as Get & Transform) and in Power BI
? Solve common data preparation problems with a few mouse clicks and simple formula edits
? Combine data from multiple sources, multiple queries, and mismatched tables
? Master basic and advanced techniques for unpivoting tables
? Customize transformations and build flexible data mashups with the M formula language
? Address collaboration challenges with Power Query
? Gain crucial insights into text feeds
? Streamline complex social network analytics so you can do it yourself
For all information workers, analysts, and any Excel user who wants to solve their own business intelligence problems.
Prepare and analyze your data the easy way, with Power Query
? Quickly prepare data for analysis with Power Query in Excel (also known as Get & Transform) and in Power BI
? Solve common data preparation problems with a few mouse clicks and simple formula edits
? Combine data from multiple sources, multiple queries, and mismatched tables
? Master basic and advanced techniques for unpivoting tables
? Customize transformations and build flexible data mashups with the M formula language
? Address collaboration challenges with Power Query
? Gain crucial insights into text feeds
? Streamline complex social network analytics so you can do it yourself
For all information workers, analysts, and any Excel user who wants to solve their own business intelligence problems.
All prices
More details
Series
Edition
1. Auflage
Language
English
Place of publication
Boston
United States
Publishing group
Microsoft Press,U.S.
Target group
Professional and scholarly
File size
12,86 MB
ISBN-13
978-1-5093-0801-9 (9781509308019)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Other editions
Additional editions
Book
12/2018
1st Edition
Microsoft Press
€37.49
Shipment within 3-4 weeks
Person
Gil Raviv is a Microsoft MVP and a Power BI blogger at https://DataChant.com. As a Senior Program Manager on the Microsoft Excel Product team, Gil led the design and integration of Power Query as the next-generation Get Data and data-wrangling technology in Excel 2016, and he has been a devoted M practitioner ever since.
With 20 years of software development experience, and four U.S. patents in the fi elds of social networks, cyber security, and analytics, Gil has held a variety of innovative roles in cyber security and data analytics, and he has delivered a wide range of software products, from advanced threat detection enterprise systems to protection of kids on Facebook.
In his blog, DataChant.com, Gil has been chanting about Power BI and Power Query since he moved to his new home in the Chicago area in early 2016. As a Group Manager in Avanade's Analytics Practice, Gil is helping Fortune 500 clients create modern self-service analytics capability and solutions by leveraging Power BI and Azure.
You can contact Gil at gilra@datachant.com.
With 20 years of software development experience, and four U.S. patents in the fi elds of social networks, cyber security, and analytics, Gil has held a variety of innovative roles in cyber security and data analytics, and he has delivered a wide range of software products, from advanced threat detection enterprise systems to protection of kids on Facebook.
In his blog, DataChant.com, Gil has been chanting about Power BI and Power Query since he moved to his new home in the Chicago area in early 2016. As a Group Manager in Avanade's Analytics Practice, Gil is helping Fortune 500 clients create modern self-service analytics capability and solutions by leveraging Power BI and Azure.
You can contact Gil at gilra@datachant.com.
Content
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 2x2 Levels of Hierarchy
Exercise 6-4: Unpivoting 2x2 Levels of Hierarchy with Dates
Exercise 6-5: Unpivoting 2x2 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 NxM 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: Naive 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
9781509307951 TOC 9/6/2018
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 2x2 Levels of Hierarchy
Exercise 6-4: Unpivoting 2x2 Levels of Hierarchy with Dates
Exercise 6-5: Unpivoting 2x2 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 NxM 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: Naive 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
9781509307951 TOC 9/6/2018
