Cleaning Excel Data With Power Query Straight to the Point
Published on 1. March 2019
60 pages
978-1-61547-149-2 (ISBN)
Description
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.
More details
Content
- Intro
- About the Author
- Acknowledgments
- INTRODUCTION
- So, What Is Power Query?
- When I Became a Believer.
- FIRST, SOME BACKGROUND FOR POWER QUERY
- Excel Versions
- Everything Happens via Queries
- Data Types
- Power Query Warnings
- GETTING DATA INTO POWER QUERY
- Importing Data from a Table or Range
- Importing Data from a Workbook
- FILL UP AND FILL DOWN
- PARSING TEXT
- Splitting Names by Delimiter
- Splitting into Rows
- SORTING IN POWER QUERY
- MERGING DATA: JOINING AND APPENDING
- Joins and Merging
- Full Outer Join: Creating a Master List
- Inner Join: Determining Who Registered and Attended
- Right and Left Anti Joins: Determining What's over Here That's Not over There
- RIGHT ANTI JOIN
- LEFT ANTI JOIN
- Left and Right Outer Joins: Power Query's VLOOKUP
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- Appending Datasets
- GROUPING DATA
- REFRESHING AND UPDATING DATA
- ADDING COLUMNS
- Adding Conditional Columns
- Adding Multiple Conditions
- Adding Custom Columns
- Adding Custom Columns with Power Query Functions and Adding Whole Numbers to Dates
- Adding a Column Using Column from Examples
- UNPIVOTING DATA
- CONCLUSION
