Cleaning Excel Data With Power Query Straight to the Point

By Oz du Soleil

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.

• Language: English
• Publisher: Independent Publishers Group
• Release date: Mar 1, 2019
• ISBN: 9781615471492

Book preview

Cleaning Excel Data With

Power Query

Straight to the Point

Oz du Soleil

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Cleaning Excel Data with Power Query Straight to the Point

© 2019 by Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an as is basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

First Published: March 2019

Author: Oz du Soleil

Copyeditor: Kitty Wilson

Cover Design: Suat M. Ozgur

Cover Illustration: Shannon Mattiza, 6'4 Productions

Indexer: Nellie Jay

Tech Editor: Bill Jelen

Screen Reader Captions: Deb Govern

Compositor: Jill Cabot

Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-250-5 PDF, 978-1-61547-371-7 ePub, 978-1-61547-149-2 Mobi

Table of Contents

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

About the Author