Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016
271
Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016
271eBookSecond edition (Second edition)
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473496 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 12/01/2015 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 271 |
| File size: | 41 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Power Pivot and Power BI: the Excel User's Guide to the Data Revolution
By Rob Collie, Avi Singh
Holy Macro! Books
Copyright © 2016 Robert Collie and Tickling Keys, Inc.All rights reserved.
ISBN: 978-1-61547-349-6
CHAPTER 1
A Revolution Built On YOU
Does This Sound Familiar?
(Updated Fall 2015, but we decided to leave this part in Rob's first-person "voice" – because the authenticity is better-preserved).
In the movie Fight Club, Edward Norton's character refers to the people he meets on airplanes as "single serving friends" – people he befriends for three hours and never sees again. I have a unique perspective on this phenomenon, thanks to a real-world example that is relevant to this book.
A woman takes her seat for a cross-country business flight and is pleased to see that her seatmate appears to be a reasonably normal fellow. They strike up a friendly conversation, and when he asks her what she does for a living, she gives the usual reply: "I'm a marketing analyst."
That answer satisfies 99% of her single-serving friends, at which the conversation typically turns to something else. However, this guy is the exception, and asks the dreaded follow-up question: "Oh, neat! What does that mean?"
She sighs, ever so slightly, because the honest answer to that question always bores people to death. Worse than that actually: it often makes the single-serving friend recoil a bit, and express a sentiment bordering on pity.
But she's a factual sort of person, so she gives a factual answer: "well, basically I work with Excel all day, making PivotTables." She fully expects this to be a setback in the conversation, a point on which we share no common ground.
Does this woman's story sound familiar? Do you occasionally find yourself in the same position?
Well imagine her surprise when this particular single-serving friend actually becomes excited after hearing her answer! He lights up – it's the highlight of his day to meet her.
Because, you see, on this flight, she sat down next to me. And I have some exciting news for people like her, which probably includes you
Excel Pros: The World Is Changing in Your Favor
If you are reading this, I can say confidently that the world is in the early stages of an incredible discovery: it is about to realize how immensely valuable YOU are. In large part, this book is aimed at helping you reap the full rewards available to you during this revolution.
That probably sounds pretty appealing, but why am I so comfortable making bold pronouncements about someone I have never met? Well, this is where the single-serving friend thing comes in: I have met many people like you over the years, and to me, you are very much 'my people.'
In fact, for many years while I worked at Microsoft, it was my job to meet people like you. I was an engineer on the Excel team, and I led a lot of the efforts to design new functionality for relatively advanced users.
Meeting those people, and watching them work, was crucial, so I traveled to find them. When I was looking for people to meet, the only criteria I applied was this: you had to use Excel for ten or more hours per week.
I found people like that (like you!) all over the world, in places ranging from massive banks in Europe to the back rooms of automobile dealerships in Portland, Oregon. There are also many of you working at Microsoft itself, working in various finance, accounting, and marketing roles, and I spent a lot of time with them as well (more on this later).
Over those years, I formed a 'profile' of these 'ten hour' spreadsheet people I met. Again, see if this sounds familiar.
Attributes of an Excel Pro:
• They grab data from one or more sources.
• They prep the data, often using VLOOKUP.
• They then create pivots over the prepared data.
• Sometimes they subsequently index into the resulting pivots, using formulas, to produce polished reports. Other times, the pivots themselves serve as the reports.
• They then share the reports with their colleagues, typically via email or by saving to a network drive.
• They spend at least half of their time re-creating the same reports, updated with the latest data, on a recurring basis.
At first, it seemed to be a coincidence that there was so much similarity in the people I was meeting. But over time it became clear that this was no accident. It started to seem more like a law of physics – an inevitable state of affairs. Much like the heat and pressure in the earth's crust seize the occasional pocket of carbon and transform it into a diamond, the demands of the modern world 'recruit' a certain kind of person and forge them into an Excel Pro.
I can even put an estimate on how many of you are out there. At Microsoft we used to estimate that there were 300 million users of Excel worldwide. This number was disputed, and might be too low, especially today. It's a good baseline, nothing more. But that was all users of Excel – from the most casual to the most expert. Our instrumentation data further showed us that only 5-10% of all Excel users created PivotTables.
'Create' is an important word here – many more than consume pivots made by others, but only 5-10% are able to create them from scratch. Creating pivots, then, turns out to be an overwhelmingly accurate indicator of whether someone is an Excel Pro. We might as well call them Pivot Pros.
You may feel quite alone at your particular workplace, because statistically speaking you are quite rare – less than 0.5% of the world's population has your skillset! But in absolute numbers you are far from alone in the world – in fact, you are one of approximately thirty million people. If Excel Pros had conferences or conventions, it would be quite a sight.
As I said up front, I am convinced that our importance is about to explode into the general consciousness. After all, we are already crucial.
Our Importance Today
As proof of how vital we are, here's another story from Microsoft, one that borders on legend. The actual event transpired more than ten years ago and the details are hazy, but ultimately it's about you; about us.
Someone from the SQL Server database team was meeting with Microsoft CEO Steve Ballmer. They were trying to get his support for a 'business intelligence' (BI) initiative within Microsoft – to make the company itself a testbed for some new BI products in development at that time. If Steve supported the project, the BI team would have a much easier time gaining traction within the accounting and finance divisions at Microsoft.
In those days, Microsoft had a bit of a 'prove it to me' culture. It was a common approach to 'play dumb' and say something like, "okay, tell me why this is valuable." Which is precisely the sort of thing Steve said to the BI folks that day.
To which they gave an example, by asking a question like this: "If we asked you how much sales of Microsoft Office grew in South America last year versus how much they grew the year before, but only during the holiday season, you probably wouldn't know."
Steve wasn't impressed. He said, "sure I would," triggering an uncomfortable silence. The BI team knew he lacked the tools to answer that question – they'd done their homework. Yet here was one of the richest and most powerful men in the world telling them they were wrong.
One of the senior BI folks eventually just asked straight out, "Okay, show us how you'd do that."
Steve snapped to his feet in the center of his office and started shouting. Three people hurried in, and he started waving his arms frantically and bellowing orders, conveying the challenge at hand and the information he needed. This all happened with an aura of familiarity – this was a common occurrence, a typical workflow for Steve and his team.
Those three people then vanished to produce the requested results. In Excel, of course.
Excel at the Core
Let that sink in: the CEO of the richest company in the world (and one of the most technologically advanced!) relied heavily on Excel Pros to be his eyes and ears for all things financial. Yes, I am sure that now, many years later, Satya Nadella has a broad array of sophisticated BI tools at his disposal. However, I am equally sure that his reliance on Excel Pros has not diminished by any significant amount.
Is there anything special about Microsoft in this regard? Absolutely not! This is true everywhere. No exceptions. Even at companies where they claimed to have 'moved beyond spreadsheets,' I was always told, off the record, that Excel still powered more than 90% of decisions. (Indeed, an executive at a large Microsoft competitor told me recently that his division, which produces a BI product marketed as a 'better' way to report numbers than Excel, uses Excel for all internal reporting!)
Today, if a decision – no matter how critical it is, or how large the organization is – is informed by data, it is overwhelmingly likely that the data is coming out of Excel. The data may be communicated in printed form, or PDF, or even via slide deck. But it was produced in Excel, and therefore by an Excel Pro.
The message is clear: today we are an indispensable component of the information age, and if we disappeared, the modern world would grind to a halt overnight. Yet our role in the world's development is just getting started.
Three Ingredients of Revolution
There are three distinct reasons why Excel Pros are poised to have a very good decade.
Ingredient One: Explosion of Data
The ever-expanding capacity of hardware, combined with the ever-expanding importance of the internet, has led to a truly astounding explosion in the amount of data collected, stored, and transmitted.
Estimates vary widely, but in a single day, the internet may transmit more than a thousand exabytes of data. That's 180 CD-ROMs' worth of data for each person on the planet, in just 24 hours!
However, it's not just the volume of data that is expanding; the number of sources is also expanding. Nearly every click you make on the internet is recorded (scary but true). Social media is now 'mined' for how frequently a certain product is mentioned, and whether it was mentioned positively or negatively. The thermostat in your home may be 'calling home' to the power company once a minute. GPS units in delivery vehicles are similarly checking in with 'home base.'
This explosion of volume and variety is often lumped together under the term 'Big Data.' A few savvy folks are frontrunning this wave of hype by labeling themselves as 'Big Data Professionals'. By the time you are done with this book, you might rightfully be tempted to do the same.
There's a very simple reason why 'Big Data' equals 'Big Opportunity' for Excel Pros: human beings can only understand a single page (at most) of information at a time. Think about it: even a few hundred rows of data is too big for a human being to look at and make a decision. We need to summarize that data – to 'crunch' it into a smaller number of rows (i.e. a report) – before we can digest it.
So 'big' just means 'too big for me to see all at once.' The world is producing Big Data, but humans still need Small Data. Whether it's a few hundred rows or a few billion, people need an Excel Pro to shrink it for human consumption. The need for you is only growing.
Ingredient Two: Economic Pressure
Much of the world has been in an economic downturn since 2008, and in general this is a bad thing. If played properly, however, it can be a benefit to the Excel Pro.
Consider, for a moment, the BI industry. BI essentially plays the same role as Excel: it delivers digestible information to decision makers. It's more formal, more centralized, and more expensive – an IT function rather than an Excel Pro function – but fills the same core need for actionable information.
A surprising fact: paradoxically, BI spending increases during recessions, when spending on virtually everything else is falling. This was true during the dot-com bust of 2000 and is true again today.
Why does this happen? Simply put: when the pressure is on, the value of smart decisions is increased, as is the cost of bad ones. I like to explain it this way: when money is falling from the sky, being 'smart' isn't all that valuable. At those times, the most valuable person is the one who can put the biggest bucket out the window. However when the easy money stops flowing, and everyone's margins get pressured, 'smart' becomes valuable once again.
Unlike BI spending, spending on spreadsheets is not measured – people buy Microsoft Office every few years no matter what, so we wouldn't notice a change in 'Excel spending' during recessions. I suspect, however, that if we could somehow monitor the number of hours spent in Excel worldwide, we would see a spike during recessions, for the same reason we see spikes in BI spending.
So the amount and variety of data that needs to be 'crunched' is exploding, and at the same time, the business value of insight is increasing. This is a potent mixture.
All it needs is a spark to ignite it. And boy, do we have a bright spark.
Ingredient Three: Dramatically Better Tools
The world's need for insights is reaching a peak. Simultaneously, the amount of data is exploding, providing massive new insight opportunities (raw material for producing insights). Where is the world going to turn?
It is going to take an army of highly skilled data professionals to navigate these waters. Not everyone is cut out for this job either – only people who like data are going to be good at it. They must also be trained already – there's no time to learn, because the insights are needed now!
I think you see where I am going. That army exists today, and it is all of YOU. You already enjoy data, you are well-versed in the nuances of your particular business, and you are already trained on the most flexible data analysis tool in the world.
However, until now there have been a few things holding you back:
1. You are very busy. Many of you are swamped today, and for good reason. Even a modestly complex Excel report can require hundreds of individual actions on the part of the author, and most of those actions need to be repeated when you receive new data or a slightly different request from your consumers. Our labor in Excel is truly "1% inspiration and 99% perspiration," to use Edison's famous words.
2. Integrating data from multiple sources is tedious. Excel may be quite flexible, but that does not mean it makes every task effortless. Making multiple sources 'play nicely' together in Excel can absorb huge chunks of your time.
3. Truly 'Big' Data does not fit in Excel. Even the expansion of sheet capacity to one million rows (in Excel 2007 and newer) does not address all of today's needs. In my work at Pivotstream I sometimes need to crunch data sets exceeding 100 million rows, and even data sets of 100,000 rows can become prohibitively slow in Excel, particularly when you are integrating them with other data sets.
4. Excel has an image problem. It simply does not receive an appropriate amount of respect. To the uninitiated, it looks a lot like Word and PowerPoint – an Office application that produces documents. Even though those same people could not begin to produce an effective report in Excel, and they rely critically on the insights it provides, they still only assign Excel Pros the same respect as someone who can write a nice letter in Word. That may be depressing, but it is sadly true.
The answer is here
The Power BI family of tools addresses all of those problems. I actually think it's fair to say that it completely wipes them away.
You are the army that the world needs. You just needed an upgrade to your toolset. Power Pivot and its close cousin Power BI provide that upgrade and then some. I would say that we probably needed a 50% upgrade to Excel, but what we got is more like a 500% upgrade; and that is not a number I throw around lightly.
I bet you are eager to see that new jet airplane. Let's take a tour.
(Continues...)
Excerpted from Power Pivot and Power BI: the Excel User's Guide to the Data Revolution by Rob Collie, Avi Singh. Copyright © 2016 Robert Collie and Tickling Keys, Inc.. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Contents
Dedications,Supporting Workbooks and Data Sets,
Errata and Book Support,
A Note on Hyperlinks,
Foreword and Forward,
Introduction - Our Two Goals for this Book,
1 - A Revolution Built On YOU,
2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions,
3 - Learning Power Pivot "The Excel Way",
4 - Loading Data Into Power Pivot,
5 - Intro to Calculated Columns,
6 - Introduction to DAX Measures,
7 - The "Golden Rules" of DAX Measures,
8 - CALCULATE() – Your New Favorite Function,
9 - ALL() – The "Remove a Filter" Function,
10 - Thinking in Multiple Tables,
11 - "Intermission" – Taking Stock of Your New Powers,
12 - Disconnected Tables,
13 - Introducing the FILTER() Function, and Disconnected Tables Continued,
14 - Introduction to Time Intelligence,
15 - IF(), SWITCH(), BLANK(), and Other Conditional Fun,
16 - SUMX() and Other X ("Iterator") Functions,
17 - Multiple Data Tables,
18 - Multiple Data Tables – Differing Granularity,
19 - Performance: Keep Things Running Fast,
20 - Power Query to the Rescue,
21 - Power BI Desktop,
22 - "Complicated" Relationships,
23 - Row and Filter Context Demystified,
24 - CALCULATE and FILTER – More Nuances,
25 - Time Intelligence with Custom Calendars: Greatest Formula in the World,
26 - Advanced Calculated Columns,
27 - New DAX Functions ... and Variables!,
28 - "YouTube for Data" – The Importance of a Server,
PS: Can We Ask You for a Special Favor?,
A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!),
A2 - Cube Formulas – the End of GetPivotData(),
A3 - Some Common Error Messages,
A4 - People: The Most Powerful Feature of Power Pivot,
Index,