Ken Puls - Master Your Data with Power Query in Excel and Power BI

Inside front cover. This page intentionally left blank.

by

Ken Puls &

Miguel Escobar

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32953

Master Your Data with Excel and Power BI

2021 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 permission from the publisher. 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.

Authors: Ken Puls and Miguel Escobar

Layout: Jill Bee

Copyediting: Deanna Puls

Cover Design: Shannon Travise

Indexing: Nellie Jay

Ape Illustrations: Walter Agnew Moore

Cover Illustration: Pavel Goldaev

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

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: September, 2021

ISBN: 978-1-61547-058-7 Print, 978-1-61547-241-3 PDF, 978-1-61547-358-8 ePub, 978-1-61547-141-6 Mobi

Library of Congress Control Number: 2021942985

Table of Contents

Foreword

How Power Query Changed OUR Lives

Kens Story: Coffee & Power Query

Its the name on the meeting in my Outlook calendar from back in November 2013. It was during one of the Microsoft MVP summits, the product had recently had its name changed from Data Explorer, and I was meeting with Miguel Llopis and Faisal Mohamood from the Power Query team over coffee to talk about the good and the bad of the tool from an Excel users perspective.

In that conversation, I told them both that Power Query was great, but it was a lousy replacement for SQL Server Management Studio. I distinctly remember that part of the conversation. Id been working with SSMS and Power Query a lot at the time and was struggling with the fact that Power Query did some of the same tasks, but not all. I was frustrated, as I was struggling with the tool, trying to make it behave the same, but it just wasnt doing it.

What happened after I laid out my concerns flipped my complaints on their head. Im paraphrasing from my memory, but the response was something like this:

Ken, this tool isnt a replacement for SSMS. We built this for Excel people our intent is that they never need to use or learn SQL at all.

For anyone that knows me well, they know that Im very seldom left speechless, but that was just about enough to do it. That statement upset the balance of my world.

Understand that Im not a normal Excel pro. I know enough SQL to be dangerous, Im extremely accomplished with VBA and had also worked with VB.NET, C#, XML and a few other languages. And while I love technology and challenges, the true reason I know as many languages as I do today is that I taught myself out of necessity. Typically, my needs were complicated, and that involved a painful journey of jumping into the deep end with a sink or swim approach.

That meeting changed my view of Power Query forever. I took a step back and looked at it in a new light. And I started to use it as it was intended to be used on its own, driving everything through the user interface, avoiding writing SQL wherever possible. And you know something it started working better, it allowed me to go more places, it allowed me to solve things Id never been able to do before.

I love this tool. Not just because of what I can do with it, but because of how easy it makes it to get things done for a business pro without the need for coding. Yes, there is a coding layer within the tool that you can learn, but it is entirely optional. That is what makes this tool so special: it has one of the best user-interface designs Ive seen in a long time, which essentially writes code for you as you click buttons. I love this tool because the people we teach can pick it up rapidly and build complex solutions that add real business value in an incredibly short amount of time. This product is truly centered around the business professional.

On a personal note, Power Query has allowed me to quit my full-time corporate job and build my own business. We deliver live training (in-person or online), as well as our own commercial Excel add-in Monkey Tools which can help make your life even easier when working with Power Query and Power Pivot in Excel. Ultimately, there is nothing that gets me more excited than seeing someone find that magic moment that significantly impacts their workflow and saves them time.

Miguels Story: A new beginning

Before starting my business as a freelancer in 2013, I had a reputation in my past jobs of being the power user, so I kept that nickname even after leaving those jobs and thats how I named my YouTube channel and now my new website The Power User.

I was never in IT, but I was usually the guy trying to push things forward in terms of how technologically advanced we were and how much value we could get from the tools that we had at hand, which was usually just Excel (and not even the latest version). Pivot Tables and Excel formulas ended up becoming like second nature to me.

Fast forward to 2013 and I got introduced to Power Query. I cant really remember how I got to it, but things like simply filtering data, removing columns, promoting headers, and unpivoting columns had a huge impact on my day to day. I had no knowledge of VBA (and I still dont), so Power Query literally opened completely new data wrangling opportunities for me which were previously impossible. I no longer needed to become a VBA or SQL Expert I just needed Power Query and my data preparation issues would be gone.

The Power Query user interface was the hook for me. It felt intuitive and is the type of interface that puts you right in the driving seat in front of what matters most to you - your data. However, since it was a new tool and a new language, there was little content or information on the web about how to get the most out of Power Query, so I started my journey to be the very best, like no one ever was on this new technology and started creating content for it.

Through this new content creation (blogging, videos, emails, etc.), I ended up meeting people like Rob Collie and Bill Jelen who later introduced me to Ken who was also big on Power Query. Ken and I never met in person, but we decided to work together because we felt we complemented our views on Power Query and we both wanted to preach about how amazing Power Query is. We started a project called
PowerQuery.Training which ended up fostering the content that got published in the first edition of our book. During that period of writing the first edition and even before that, we realized the true potential of Power Query and how it could change the lives of most Excel users for the better. For us, Power Query was and still is a major breakthrough as far as self-service tools go.