Book 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.
Table of contents
- Cover Page
- Title Page
- Copyright Page
- Contents at a Glance
- Contents
- Figure Credits
- Foreword
- About the Author
- Acknowledgments
- Introduction
- Chapter 1 Introduction to Power Query
-
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
- Code Snippets
Product information
- Title: Collect, Combine, and Transform Data Using Power Query in Excel and Power BI, First Edition
- Author(s):
- Release date: October 2018
- Publisher(s): Microsoft Press
- ISBN: 9781509307982
You might also like
book
Beginning Power BI: A Practical Guide to Self-Service Data Analytics with Excel 2016 and Power BI Desktop, Second Edition
Analyze your company's data quickly and easily using Microsoft's latest tools. You will learn to build …
book
Analyzing Data with Power BI and Power Pivot for Excel, First Edition
Renowned DAX experts Alberto Ferrari and Marco Russo teach you how to design data models for …
book
Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition
Now expanded and updated with modern best practices, this is the most complete guide to Microsoft's …
video
Business Intelligence with Microsoft Power BI - with Material
Whether you want to prepare your data for visualization or create eye-catching reports and dashboards from …