M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query

470 Pages·2015·20.83 MB·English

inside front cover - page intentionally left blank
by
Ken Puls &
Miguel Escobar
Holy Macro! Books
PO Box 541731
Merritt Island, FL 32953
M is for (Data) Monkey
© 2016 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: Kitty Wilson
Technical Editor: Roger Govier
Cover Design: Shannon Mattiza 6’4 Productions
Indexing: Nellie Jay
Ape Illustrations: Walter Agnew Moore
Cover Illustration: Irdan Teras
Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32953, USA
Distributed by: Independent Publishers Group, Chicago, IL
First Printing: October, 2015. This edition generated May 3, 2016 with new
margins.
ISBN: 978-1-61547-034-1 Print, 978-1-61547-223-9 PDF, 978-1-61547-345-8
ePub, 978-1-61547-034-1 Mobi LCCN: 2015940635
Table of Contents
Foreword How Power Query Changed Our Lives
Introduction: A New Revolution
Chapter 1 Importing Basic Data
Chapter 2 Overriding Power Query Defaults
Chapter 3 Basic Append Operations
Chapter 4 Importing All Files in a Folder
Chapter 5 Aggregating Excel Worksheets
Chapter 6 Unpivoting Data
Chapter 7 Importing Nondelimited Text Files
Chapter 8 Importing from Databases
Chapter 9 Merging Tables and Queries
Chapter 10 Query Loading Destinations
Chapter 11 Defining Data Types
Chapter 12 Importing Web Data
Chapter 13 Loading Data from Exchange
Chapter 14 Grouping and Summarizing
Chapter 15 Transposing and Unpivoting Complex Data
Chapter 16 Automating Refreshing
Chapter 17 Power Query Formulas
Chapter 18 Conditional Logic in Power Query
Chapter 19 Power Query Objects
Chapter 20 Understanding the M Language
Chapter 21 Creating Custom Functions
Chapter 22 Advanced Conditional Logic
Chapter 23 Dynamic Parameter Tables
Chapter 24 Dynamic Calendar Tables
Chapter 25 Query Organization
Index
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.