book

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

Name: Collect, Combine, and Transform Data Using Power Query in Excel and Power BI, First Edition
Author: Gil Raviv
ISBN: 9781509307982

by Gil Raviv

October 2018

Beginner to intermediate

432 pages

13h 20m

English

Microsoft Press

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Contents at a Glance
Contents
Figure Credits
Foreword
About the Author
Acknowledgments
Introduction
Who this book is forHow this book is organizedAbout the companion contentSystem requirementsHow to get support & provide feedback

Chapter 1 Introduction to Power Query
What Is Power Query?A Brief History of Power QueryWhere Can I Find Power Query?Main Components of Power QueryGet Data and ConnectorsThe Main Panes of the Power Query EditorExercise 1-1: A First Look at Power QuerySummary
Chapter 2 Basic Data Preparation Challenges
Extracting Meaning from Encoded ColumnsAdventureWorks ChallengeExercise 2-1: The Old Way: Using Excel FormulasExercise 2-2, Part 1: The New WayExercise 2-2, Part 2: Merging Lookup TablesExercise 2-2, Part 3: Fact and Lookup TablesUsing Column from ExamplesExercise 2-3, Part 1: Introducing Column from ExamplesPractical Use of Column from ExamplesExercise 2-3, Part 2: Converting Size to Buckets/RangesExtracting Information from Text ColumnsExercise 2-4: Extracting Hyperlinks from MessagesHandling DatesExercise 2-5: Handling Multiple Date FormatsExercise 2-6: Handling Dates with Two LocalesExtracting Date and Time ElementsPreparing the ModelExercise 2-7: Splitting Data into Lookup Tables and Fact TablesExercise 2-8: Splitting Delimiter-Separated Values into RowsSummary
Chapter 3 Combining Data from Multiple Sources
Appending a Few TablesAppending Two TablesExercise 3-1: Bikes and Accessories ExampleExercise 3-2, Part 1: Using Append Queries as NewExercise 3-2, Part 2: Query Dependencies and ReferencesAppending Three or More TablesExercise 3-2, Part 3: Bikes + Accessories + ComponentsExercise 3-2, Part 4: Bikes + Accessories + Components + ClothingAppending Tables on a Larger ScaleAppending Tables from a FolderExercise 3-3: Appending AdventureWorks Products from a FolderThoughts on Import from FolderAppending Worksheets from a WorkbookExercise 3-4: Appending Worksheets: The SolutionSummary
Chapter 4 Combining Mismatched Tables
The Problem of Mismatched TablesWhat Are Mismatched Tables?The Symptoms and Risks of Mismatched TablesExercise 4-1: Resolving Mismatched Column Names: The Reactive ApproachCombining Mismatched Tables from a FolderExercise 4-2, Part 1: Demonstrating the Missing Values SymptomExercise 4-2, Part 2: The Same-Order Assumption and the Header Generalization SolutionExercise 4-3: Simple Normalization Using Table.TransformColumnNamesThe Conversion TableExercise 4-4: The Transpose Techniques Using a Conversion TableExercise 4-5: Unpivot, Merge, and Pivot BackExercise 4-6: Transposing Column Names OnlyExercise 4-7: Using M to Normalize Column NamesSummary
Chapter 5 Preserving Context
Preserving Context in File Names and WorksheetsExercise 5-1, Part 1: Custom Column TechniqueExercise 5-1, Part 2: Handling Context from File Names and Worksheet NamesPre-Append Preservation of TitlesExercise 5-2: Preserving Titles Using Drill DownExercise 5-3: Preserving Titles from a FolderPost-Append Context Preservation of TitlesExercise 5-4: Preserving Titles from Worksheets in the same WorkbookUsing Context CuesExercise 5-5: Using an Index Column as a CueExercise 5-6: Identifying Context by Cell ProximitySummary
Chapter 6 Unpivoting Tables
Identifying Badly Designed TablesIntroduction to UnpivotExercise 6-1: Using Unpivot Columns and Unpivot Other ColumnsExercise 6-2: Unpivoting Only Selected ColumnsHandling TotalsExercise 6-3: Unpivoting Grand TotalsUnpivoting 2×2 Levels of HierarchyExercise 6-4: Unpivoting 2×2 Levels of Hierarchy with DatesExercise 6-5: Unpivoting 2×2 Levels of HierarchyHandling Subtotals in Unpivoted DataExercise 6-6: Handling SubtotalsSummary
Chapter 7 Advanced Unpivoting and Pivoting of Tables
Unpivoting Tables with Multiple Levels of HierarchyThe Virtual PivotTable, Row Fields, and Column FieldsExercise 7-1: Unpivoting the AdventureWorks N×M Levels of HierarchyGeneralizing the Unpivot SequenceExercise 7-2: Starting at the EndExercise 7-3: Creating FnUnpivotSummarizedTableThe Pivot Column TransformationExercise 7-4: Reversing an Incorrectly Unpivoted TableExercise 7-5: Pivoting Tables of Multiline RecordsSummary
Chapter 8 Addressing Collaboration Challenges
Local Files, Parameters, and TemplatesAccessing Local Files—IncorrectlyExercise 8-1: Using a Parameter for a Path NameExercise 8-2: Creating a Template in Power BIExercise 8-3: Using Parameters in ExcelWorking with Shared Files and FoldersImporting Data from Files on OneDrive for Business or SharePointExercise 8-4: Migrating Your Queries to Connect to OneDrive for Business or SharePointExercise 8-5: From Local to SharePoint FoldersSecurity ConsiderationsRemoving All Queries Using the Document Inspector in ExcelSummary
Chapter 9 Introduction to the Power Query M Formula Language
Learning MLearning Maturity StagesOnline ResourcesOffline ResourcesExercise 9-1: Using #shared to Explore Built-in FunctionsM Building BlocksExercise 9-2: Hello WorldThe let ExpressionMerging Expressions from Multiple Queries and Scope ConsiderationsTypes, Operators, and Built-in Functions in MBasic M TypesThe Number TypeThe Time TypeThe Date TypeThe Duration TypeThe Text TypeThe Null TypeThe Logical TypeComplex TypesThe List TypeThe Record TypeThe Table TypeConditions and If Expressionsif-then-elseAn if Expression Inside a let ExpressionCustom FunctionsInvoking FunctionsThe each ExpressionAdvanced TopicsError HandlingLazy and Eager EvaluationsLoopsRecursionList.GenerateList.AccumulateSummary
Chapter 10 From Pitfalls to Robust Queries
The Causes and Effects of the PitfallsAwarenessBest PracticesM ModificationsPitfall 1: Ignoring the Formula BarExercise 10-1: Using the Formula Bar to Detect Static References to Column NamesPitfall 2: Changed TypesPitfall 3: Dangerous FilteringExercise 10-2, Part 1: Filtering Out Black ProductsThe Logic Behind the Filtering ConditionExercise 10-2, Part 2: Searching Values in the Filter PanePitfall 4: Reordering ColumnsExercise 10-3, Part 1: Reordering a Subset of ColumnsExercise 10-3, Part 2: The Custom Function FnReorderSubsetOfColumnsPitfall 5: Removing and Selecting ColumnsExercise 10-4: Handling the Random Columns in the Wide World Importers TablePitfall 6: Renaming ColumnsExercise 10-5: Renaming the Random Columns in the Wide World Importers TablePitfall 7: Splitting a Column into ColumnsExercise 10-6: Making an Incorrect SplitPitfall 8: Merging ColumnsMore Pitfalls and Techniques for Robust QueriesSummary
Chapter 11 Basic Text Analytics
Searching for Keywords in Textual ColumnsExercise 11-1: Basic Detection of KeywordsUsing a Cartesian Product to Detect KeywordsExercise 11-2: Implementing a Cartesian ProductExercise 11-3: Detecting Keywords by Using a Custom FunctionWhich Method to Use: Static Search, Cartesian Product, or Custom Function?Word SplitsExercise 11-4: Naïve Splitting of WordsExercise 11-5: Filtering Out Stop WordsExercise 11-6: Searching for Keywords by Using Split WordsExercise 11-7: Creating Word Clouds in Power BISummary
Chapter 12 Advanced Text Analytics: Extracting Meaning
Microsoft Azure Cognitive ServicesAPI Keys and Resources Deployment on AzurePros and Cons of Cognitive Services via Power QueryText TranslationThe Translator Text API ReferenceExercise 12-1: Simple TranslationExercise 12-2: Translating Multiple MessagesSentiment AnalysisWhat Is the Sentiment Analysis API Call?Exercise 12-3: Implementing the FnGetSentiment Sentiment Analysis Custom FunctionExercise 12-4: Running Sentiment Analysis on Large DatasetsExtracting Key PhrasesExercise 12-5: Converting Sentiment Logic to Key PhrasesMulti-Language SupportReplacing the Language CodeDynamic Detection of LanguagesExercise 12-6: Converting Sentiment Logic to Language DetectionSummary
Chapter 13 Social Network Analytics
Getting Started with the Facebook ConnectorExercise 13-1: Finding the Pages You LikedAnalyzing Your FriendsExercise 13-2: Finding Your Power BI Friends and Their FriendsExercise 13-3: Find the Pages Your Friends LikedAnalyzing Facebook PagesExercise 13-4: Extracting Posts and Comments from Facebook Pages—The Basic WayShort Detour: Filtering Results by TimeExercise 13-5: Analyzing User Engagement by Counting Comments and SharesExercise 13-6: Comparing Multiple PagesSummary
Chapter 14 Final Project: Combining It All Together
Exercise 14-1: Saving the Day at Wide World ImportersCluesPart 1: Starting the SolutionPart 2: Invoking the Unpivot FunctionPart 3: The Pivot Sequence on 2018 RevenuesPart 4: Combining the 2018 and 2015–2017 RevenuesExercise 14-2: Comparing Tables and Tracking the HackerCluesExercise 14-2: The SolutionDetecting the Hacker’s Footprints in the Compromised TableSummary
Index
Code Snippets

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

CHAPTER 10From Pitfalls to Robust Queries

Data is a precious thing and will last longer than the systems themselves.

—Tim Berners-Lee

IN THIS CHAPTER, YOU WILL

Learn the main factors in creating weak queries and common pitfalls that lead to refresh failures and incorrect data
Learn how awareness, best practices, and M modifications can help you prevent the pitfalls
Learn how to avoid refresh failures due to the automatic detection and change of column types
Learn how to avoid dangerous filtering that can lead to partial data in your reports
Learn when reordering the position of columns is effective and how to reorder a subset of columns
Learn how to remove and select columns and avoid future refresh errors
Rename columns by their location in ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

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

Daniil Maslyuk, Gil Raviv

Data Analysts' Toolbox - Excel, Python, Power BI, PivotTables, Alteryx, Qlik Sense, R, Tableau

Simon Sez IT

Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition

Alberto Ferrari, Marco Russo

Data Modeling with Microsoft Power BI

Markus Ehrenmueller-Jensen

Publisher Resources

ISBN: 9781509307982

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Gil Raviv

CHAPTER 10From Pitfalls to Robust Queries

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.