MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cleaning Excel Data With Power Query Straight To The Point


March 2019

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

Bill Jelen, MrExcel

category: Power Query
covers: Office 365

Product Details
  • 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.

  • 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

About The 'Straight to the Point' Series

Books in this series are designed to thoroughly cover one targeted aspect of Excel.


Where to Buy

MrExcel Bookstore

eBook from MrExcel.

Add to Cart »

Hear the Figures

If you are using assistive reading technology (for low-vision, dyslexia, mobility impairments, attention issues, or if you need to read hands free), this e-book is "born accessible", with a complete description attached to each screenshot so you can follow along with the text.

Related Products


M is for (DATA) MONKEY

December 2015

Despite the moniker "data monkey", we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.


Guerrilla Data Analysis 2nd Edition

May 2015

This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These tips have been honed by Oz du Soleil, a former financial analyst charged with taking mainframe data and turning it into useful information quickly, and "MrExcel" himself, Bill Jelen.


Guerilla Data Analysis

September 2002

Bill Jelen uses his combined experience and analytical ingenuity to de-mystify the arduous task of dealing with downloaded data.