Cleaning Excel Data With Power Query Straight to the Point E-Book

Oz du Soleil

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 36

Veröffentlichungsjahr: 2024

Beschreibung

This book provides a step-by-step guide to using Power Query in Excel for efficient data cleaning and transformation. Starting with an introduction to its capabilities, it explains how to import data, handle missing values, and parse text fields with ease.
Advanced techniques such as merging datasets, appending data, and performing joins are explored in detail. The book also covers grouping data, creating conditional and custom columns, and reshaping data through unpivoting for analysis. Each concept is illustrated with practical examples for clarity.
By the end of the book, readers will be equipped with the skills to automate repetitive tasks and streamline workflows. Whether dealing with messy data or preparing datasets for analysis, this guide ensures you can confidently tackle any Excel data transformation challenge.

Leseprobe

Cleaning Excel Data WithPower QueryStraight 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 2019Author: 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

Oz du Soleil is an Excel MVP who’s been working with Excel since 2001. He’s co-author, along with Bill Jelen, of Guerrilla Data Analysis, 2nd edition. Oz has several Excel courses on the LinkedIn Learning platform. He’s possibly best known for the dramatic and colorful Excel tutorials he posts on his YouTube channel Excel on Fire.

Data cleansing is Oz’s area of specialization in Excel. From his earliest days with Excel, he has found himself constantly needing to fix names that are ALLCAPS, peel addresses away from phone numbers, clean up the messes that result from extracting data from PDF files,…and fixing all the many other things that prevent data from being useful.

Most recently, Oz has presented Excel topics and master classes at conferences in Amsterdam; Sofia, Bulgaria; São Paulo, Brazil; Toronto, Canada; and cities around the United States.

When Oz isn’t elbows-deep in the guts of a spreadsheet, he does storytelling around Portland, Oregon. He has told stories onstage for Risk!, Pants on Fire, Seven Deadly Sins, Pickathon, The Moth, and other storytelling shows.

Acknowledgments

Thanks to Bill Jelen for giving me the opportunity to share my favorite part of Excel with you. Thanks to the teams at Microsoft who built Power Query for Excel. Special thanks to Guy Hunkin, who has taken special interest in how we use Power Query and how it can be made even better.

Ken Puls and Miguel Escobar have been especially valuable in sharing knowledge about Power Query through blog posts and open challenges that help the community make better use of this tool.