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

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

by Gil Raviv
Released October 2018
Publisher(s): Microsoft Press
ISBN: 9781509307982

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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

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

Product information

  • Title: Collect, Combine, and Transform Data Using Power Query in Excel and Power BI, First Edition
  • Author(s): Gil Raviv
  • Release date: October 2018
  • Publisher(s): Microsoft Press
  • ISBN: 9781509307982