Foreword How Power Query Changed Our Lives

Ken’s Story: “Coffee & Power Query”

It’s 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 Services Management Studio. I distinctly remember that part of the conversation. I’d 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 as SSMS, but it just wasn’t doing it.

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

“Ken, this tool isn’t 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 I’m very seldom left speechless, but that was just about enough to do it. That statement upset the balance of my world.

Understand that I’m not a normal Excel pro. I know enough SQL to be dangerous, I’m extremely accomplished with VBA, and have working knowledge of 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.

No Excel pro should need to work with SSMS in order to get the data they need. But years of working with data and fighting issue after issue left me assuming that Power Query was an Excel focused replacement for that tool. It never occurred to me, somehow, that it was being designed to actually make the life of the Excel pro so much easier that they wouldn’t even need to reach to SQL at all.

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 I’d never been able to do before.

I love this tool. Not 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. I love it because the people we teach pick it up rapidly and can add real value in an incredibly short amount of time. This product is truly centered around the Excel pro, allowing us to build complex solutions via one of the best user interface designs I’ve seen in a long time. And while we do teach the coding techniques in this book, the best part is that it’s optional, just extending the capabilities further.

Miguel’s Story: The Underdog

I truly don’t remember the exact moment when I got introduced to Power Query. I believe it was early 2013 when I downloaded the tool and started playing with it. At first it just seemed like a neat tool that could help me transform data, but I still felt that Power Pivot was going to be the center of attention in the data story. Back then Power Query was called ‘Data Explorer’ and – like today – it focused on delivering a rich user interface, allowing you to do amazing things without ever needing to look at the code. Little did I know that this ‘neat’ tool would solve almost all of my data problems.

At first, Power Query might seem to you like what it seemed to me – just a guided end-user tool that can do some really cool data transformations. But then you’ll realize the true power and flexibility that the Power Query engine brings to the table. Today, I feel that Power Query is as important as Power Pivot, as how you shape your data will ultimately determine how well your Power Pivot Data Model works.

Power Query is the new underdog of Business Intelligence tools. It’s a major breakthrough in self-service Business Intelligence and data manipulation for the end-user or data steward. Our goal with this book is to change everyone’s perspective of the tool forever.

This is the time for the underdog to rise and claim a victory – a victory for us all that work with data on a daily basis. A victory for those who want to make the data work for us and not the other way around.

Author Acknowledgements

As with any book, there are a bunch of people who are quite influential with making things happen. Without the influence of the people below, this book would never have come to fruition:

Bill Jelen – A lot of publishers would have laughed us out of the room with the title of this book, but Bill loved it. He’s been incredibly enthusiastic, accommodating with the timeline, and we really appreciate his support.

Rob Collie – Rob is synonymous with Power Pivot and Power BI. He’s also a friend, and the guy that suggested we (Ken & Miguel) work together and write a Power Query book. It’s very likely that without Rob’s prodding, this never would have happened.

Miguel Llopis – From the very first meeting over coffee, Miguel has been our go-to guy at Microsoft, even joking that his full time job is answering Ken’s emails. He’s been super supportive since day one, has responded to feature design requests, bugs and so much more. Viva el Atleti!

Roger Govier – A tech book isn’t worth anything unless it’s gone through a proper tech review. Every page of this book was reviewed by our friend Roger, and the book is so much better for it. From wording suggestions, to exposing issues, Roger’s input was invaluable, even inspiring a whole new chapter in the process.

Matt Masson, Curt Hagenlocher, Gil Raviv, Faisal Mohamood, Miguel Martinez, Samuel Zhang and all the others on the Power Query/Power BI team that have answered our many questions and responded to our many emails. Your help and clarifications have been incredibly helpful in turning out the finished product.

Chris Webb and Bill Szysz for pushing the Power Query boundaries, as well as countless others that have commented on our blogs and videos in an effort to show different and better ways to accomplish solutions. Your creativity and alternate approaches have helped us explore new methods, develop techniques, and have a lot of fun with this program.

Ken would like to thank:

Over the past few months I’ve started my own consulting practice in addition to writing this book. None of that would have been possible without the support of my wife Deanna and my daughter Annika. Even more than being a rock in my corner, though, Deanna did the initial proof read of every page of this book, clearing up the odd wording that I sometimes write down when my brain is a paragraph further than my typing.

I don’t think I could ever write a book without thanking my good friend and mentor Jim Olsen. I worked with Jim for 16 years, and the support and freedom he gave me to explore technology is the fundamental reason I am where I am today. He let me experiment with new technology, develop cutting edge solutions and test them in our work environment. Without that experience there is no way that I would have been able to develop the knowledge in order to complete this project.

I also want to thank my co-author, Miguel. It was Miguel’s idea and energy that led to the creation of http://powerquery.training and the online workshop that we teach there. Without his devotion to the project, it would not have come to fruition. And without the workshop, we probably wouldn’t have a book that is this well organized, this fluid, as the layout and material is based on the things we teach and have learned from that experience.

Miguel would like to thank:

I’d like to thank YOU for reading this. Yes…YOU! You’re a crucial part of our main objective and our intention with this book is to provide you with the resources so you can become a Data Hero. I’d like to thank you in advance for making this world a better place – at least in the context of business decision making and the world of data. :-)

I’d also like to thank all of the Excel and BI practitioners worldwide that have shown their support towards our book and our Power Query related endeavors. It is truly an honor to be part of this worldwide community and I invite you to join us by simply using this tool.

Let’s not forget about a crucial part of my life: Friends and Family. I’m not putting names in here as I’m afraid I might leave someone out of it – so I’m playing it safe here! :)

Special thanks to Ken for being extremely supportive and being able to overcome the language barrier at times with me! “Spanglish” gets me sometimes, yet Ken distinguishes what I’m trying to say and makes a better version of it.

Let’s hope that Canada and Panama can go to the FIFA World Cup in 2018!

Members of the Power Query Revolution

We’ve received some incredible support from the Power Query community as well, including those who pre-ordered the book or attended our online workshop at http://powerquery.training/course (or both). Your support means so much to us! The following is a list of those people who jumped at the chance to be listed as part of the M is for Data Monkey supporters club:

And finally…

We’d like to thank YOU. For both buying the book, putting your trust in our teaching methods, and for becoming part of the Power Query movement.

This book was written for you, in an effort to help you master your data. We truly hope it does, and that you’ll find it to be the most impactful Excel book you’ve ever purchased.