Author Oz Du Soleil's area of frustration with Excel is cleaning data. From his earliest days with Excel, he has found himself constantly needing to fix names that are ALLCAPS, peel addresses away from phone numbers, and fixing all the many other things that prevent data from being useful. In this intro to Power Query, Oz shows you five core techniques in Excel Power Query: Splitting data, Filling blanks, Joining data sets, grouping and summarizing, and adding columns using Column From Examples.
Author(s): Du Soleil, Oz;
Publisher: Independent Publishers Group
Year: 2019
Language: English
Commentary: Cleaning Excel Data With Power Query : Splitting data, Filling blanks, Joining data sets, grouping and summarizing, and adding columns using Column From Examples.
Pages: 96
Tags: Cleaning Excel Data With Power Query Straight to the Point
About the Author1
Acknowledgments1
INTRODUCTION1
So, What Is Power Query?2
When I Became a Believer…2
FIRST, SOME BACKGROUND FOR POWER QUERY3
Excel Versions3
Everything Happens via Queries3
Data Types5
Power Query Warnings5
GETTING DATA INTO POWER QUERY7
Importing Data from a Table or Range7
Importing Data from a Workbook8
FILL UP AND FILL DOWN12
PARSING TEXT16
Splitting Names by Delimiter17
Splitting into Rows20
SORTING IN POWER QUERY22
MERGING DATA: JOINING AND APPENDING25
Joins and Merging25
Full Outer Join: Creating a Master List33
Inner Join: Determining Who Registered and Attended38
Right and Left Anti Joins: Determining What’s over Here That’s Not over There43
RIGHT ANTI JOIN43
LEFT ANTI JOIN43
Left and Right Outer Joins: Power Query’s VLOOKUP44
LEFT OUTER JOIN44
RIGHT OUTER JOIN44
Appending Datasets45
GROUPING DATA47
REFRESHING AND UPDATING DATA49
ADDING COLUMNS50
Adding Conditional Columns50
Adding Multiple Conditions52
Adding Custom Columns55
Adding Custom Columns with Power Query Functions and Adding Whole Numbers to Dates58
Adding a Column Using Column from Examples59
CONCLUSION63
