Librería Portfolio

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:
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