Cleaning Excel Data With Power Query Straight To The Point
This book provides a quick Straight-to-the-Point introduction to Power Query.
Power Query saves you from the drudgery of cleaning data again and again
- 52 Pages
- Publisher: Holy Macro! Books
- PDF ISBN: 978-1-61547-149-2
This book provides a quick Straight-to-the-Point introduction to Power Query. There’s a lot here, and there’s also a lot that’s not here. Reading this book will be like sitting down at a café and striking up a conversation with the person at the next table, where you learn enough random stuff about that person to decide if you want to know more and keep in touch. In this case, of course, you’ll want to know more about Power Query. And there are lots of resources for digging deeper into it.
This book is designed to give you just what you need to experiment with Power Query because that’s a great way to learn. I encourage you to play! Make up your own datasets and see what happens.
- 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
- 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
REFRESHING AND UPDATING DATA
- 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
About The 'Straight to the Point' SeriesBooks in this series are designed to thoroughly cover one targeted aspect of Excel.