Power Pivot and Power BI The Excel Users Guide

Power Pivot and Power BI The Excel User’s Guide to DAX Power Query, Power BI, and Power Pivot in Excel 2010-2016 by Rob Collie and Avi Singh | PDF Free Download. 

Power Pivot and Power BI The Excel User Guide 

Power Pivot and Power BI Contents

• Contents at a Glance
• A Revolution Built On YOU 
• Power Pivot and the Power BI Family: Making Sense of the Various Versions 
• Loading Data Into Power Pivot
• Intro to Calculated Columns 
• Introduction to DAX Measures 
• The “Golden Rules” of DAX Measures
• CALCULATE() – Your New Favorite Function 
• ALL() – The “Remove a Filter” Function
• Thinking in Multiple Tables
• “Intermission” – Taking Stock of Your New Powers
• Disconnected Tables
• Introducing the FILTER() Function, and Disconnected Tables Continued 
• Introduction to Time Intelligence
• IF(), SWITCH(), BLANK(), and Other Conditional Fun
• SUMX() and Other X (“Iterator”) Functions
• Multiple Data Tables
• Multiple Data Tables – Differing Granularity 
• Performance: Keep Things Running Fast 
• Power Query to the Rescue 
• Power BI Desktop 
• “Complicated” Relationships
• Row and Filter Context Demystified
• CALCULATE and FILTER – More Nuances
• Time Intelligence with Custom Calendars: Greatest Formula in the World 
• Advanced Calculated Columns
• New DAX Functions… and Variables!
• “YouTube for Data” – The Importance of a Server

Foreword to Power Pivot and Power BI The Excel User’s Guide

“State of the Union” November 2015 – What’s Changed?

As we finalized this book’s final edits, Avi and Bill said, “OK Rob, you know those first two chapters? The ones that set the stage and give perspective to this whole thing? It’s time for you to revise those and bring them up to date.”

They had a point – it had been more than three years since I wrote those chapters. And a lot has changed since then in our landscape, reshaped as it is by Microsoft’s vigorous seismic activity.

But when I re-read those two chapters, I found very little that I wanted to alter. I’m leaving those largely untouched, which is a rare move for me.

Why did I choose to forgo such a writing opportunity, since I enjoy it so much? Here’s why: those chapters talk about things that fundamentally do not change the importance of people, the importance of Excel, the massive opportunities afforded to “data people,” and Microsoft’s continued investment in all of the above.

I suspect that ten years from now if we’re revising this book for the Nth edition, those chapters will again largely remain unchanged – except that we will be talking about a data revolution that’s already run its course, rather than one that’s in progress

Instead, I’m going to use this Foreword to reflect a bit on some things that truly have changed. Let’s start with the 800-pound gorilla, my former employer (and Avi’s)… The Microsoft Corporation

What Has Changed at Microsoft? Virtually Everything.

Let’s see here, just a few things:

Ballmer out, Nadella in – the change at the top of Microsoft is not to be underestimated. Satya brings a very different and more open perspective to the game, and that absolutely makes a difference to us.

For example, today’s Microsoft does not stubbornly ignore iOS and Android, whereas the old regime said, “if we ignore them long enough, they will go away.”

(A few years back when MS announced their earliest plans for mobile-friendly BI, and it revolved solely around the soon-to-be-released Windows 8 while ignoring other platforms, I chortled for two months consecutively before eventually having to see a doctor make it stop.)

It’s worth reflecting on how far we have come since then. Microsoft Power BI is available in the Apple App Store, for crying out loud, and it’s not at all weird to see it there? Times have changed.

Power Query – when the first edition of this book went to press, I don’t think we’d even heard of Power Query.

Microsoft already had a world-changing data engine – the DAX/Power Pivot engine – and that was more than enough, in my eyes, to kickstart a total revolution in how the world operates.

So to have them surprise us, out of the blue, with a relatively user-friendly desktop engine for shaping and cleaning data… a “sibling” that does virtually everything that Power Pivot could not do on its own… um, yeah. Power Query is a big deal, folks, and even though they are retiring that name, the “M Engine” is here to stay, and our professional lives are forever altered. Dramatically for the better.

Unity – CEO isn’t the only place where MS has changed people. There have also been several changes in leadership on the relevant engineering teams at Microsoft.

Some new arrivals on the SQL side of the house and some old friends “coming home” on the Excel side of the house have already made a monster impact over the past two years. Above all, I’m struck by how unified Microsoft seems to be in the BI space these days.

Not perfectly unified, but dramatically more so than I have ever seen before. Everyone seems to be pulling in the same direction – both within the SQL team’s many factions (who, in my time there, were in open war with one another) and across the SQL/Office boundary.

The latter is particularly important because the Excel team is now 100% “in” on Power Pivot. They understand its value and strategic importance to their own product, whereas before, the Excel team regarded Power Pivot with suspicion – as something that had been done to their product.

PowerBI.com and Power BI Desktop – consider this: at the beginning of 2015, neither of these things actually existed. In eleven short months, I’ve been through the full cycle of opinions: the “this is vaporware” phase, the “oh no they’re de-emphasizing Excel” phase, the “what the heck is this Desktop thing, they really don’t get it do they” phase….

And now, the “wow this is all pretty cool, Excel Services is in PowerBI.com, Desktop opens Power Pivot models, I guess they actually DO have a good roadmap that includes Excel, and it’s all actually working” phase.

I’ve gone from feeling ambushed to feeling like we’ve been given a tremendous gift. And oh yeah – a free cloud version for publishing that anyone can use, and that’s easy to sign up for? With an open-source visuals platform? This is Microsoft doing all this? Are you KIDDING ME?

And it all happened this year. Pace – you can put this together from the previous bullet, but MS is now moving at a frightening pace.

Frightening? Did I say frightening? Well, it’s only frightening if you write books. There’s now an ever-present danger of us writing an entire chapter on how you deal with a particular problem, and then three weeks later, they add a feature that makes that problem go away, rendering the entire chapter obsolete, and thereby making the authors look silly.

Actually, this is virtually guaranteed to happen. But outside of the authoring world, yeah, this is a very good thing. Not having to wait two years for key omissions and/or bugs to be addressed has precious little downside.

What’s Changed in My Corner of the World? Also Everything

I always tell first-time public speakers and bloggers to talk about their own personal experiences. You are, in fact, the world’s #1 expert on what has happened in your own life.

That’s what I’m going to do here, because hey, I can’t be wrong! Yes, it is a “skewed” view in some ways, to take smallscale observations from one person and put them next to the changes happening at a goliath like Microsoft, but I do have what they call a ringside seat for this particular show.

There’s relevance here, especially when it comes to hard numbers and economics. Let’s stick to that list format: 4x Community Growth – Judging by PowerPivotPro.com blog stats, our community – those who are aware of and using Power Pivot and Power BI – is now approximately four times the size as it was when the first edition went to print.

That’s right, there are a lot more “new” people at this point than grizzled veterans. As it should be! This will continue to hold true for quite some time.

Welcome everyone Team Growth at the time of writing there is now seventeen human beings with PowerPivotPro.com email addresses.

Guess how many humans had such addresses three years ago? Zero – not even I had one! Not all of the seventeen are doing Power Pivot / Power BI work, but most of them are. And the handful who play auxiliary roles is in some ways, even more, telling: we now have an organization which is large enough to require auxiliary roles.

I find that incredibly satisfying, and not just on the personal front – our organization wouldn’t be growing unless the demand for our services was growing.

We’re not traditional BI consultants, and we’re not spreadsheet consultants. We’re a new breed and the market is saying “yes, this is a good mutation, your virus may continue to grow.”

In fact, I’m aware of several brand-new firms that have joined us in this “new style,” and the world of data is so large that there’s zero sense of competition, only a shared sense of joy in changing the rules in a positive way.

Avi – among those seventeen is our esteemed co-author, Avi Singh, who has been working himself half to death on this 2nd Edition.

This is great news because there was zero chance I’d have been able to do this alone. (As a professional, I’ve never been busier than I am today).

So First Edition would probably have remained Only Edition without Avi on board. If anything, Avi believes in this stuff more than I do – anyone who says “I’m coming to work with you even if you can’t pay me” is a bit crazy, but the right kind of crazy.

We are lucky to have him, and yes, we do pay him Microsoft Relationship our relationship with the “mother ship” is in a much better place today than three years ago.

It’s not like there was friction before, and I do still have a lot of friends there, but there was also a longstanding mutual sense that there wasn’t much ROI in cooperating. For the most part, I ignored Microsoft and they returned the favor by ignoring me.

But my views and their views on the world have converged quite a bit over the past three years, and I would attribute that to “everyone getting smarter” rather than one of us adopting the other’s longstanding stance.

Today, our messaging help Microsoft reach customers, and Microsoft likewise connects us with people who need help.

This may sound like a subtle point, but it could not feel any more different. Surprising as it sounds, this ex-Microsoft employee (and High Priest of their data platform) feels like he’s back in the family for the first time in six years.

And again, this reflects on Microsoft’s positive direction as well as the market. Confidence – this one is my favorite.

Three years ago, I was “sure” that The New Way was going to replace The Old Way. So “sure,” in fact, that I’d happily argue vigorously with anyone who disagreed or questioned it. Today though I’m not just “sure” I am sure.

For example, a few weeks back I watched a debate unfold in the comments thread of a Power PivotPro.com blog post, in which one “combatant” was questioning whether this stuff was catching on or not. Three years ago I would have waded into the fray, guns blazing.

But this time I sat it out – my pulse didn’t rise, I didn’t take the bait. I just moved on to the next task. Someone else was taking up the good fight anyway (thanks Greg).

The point here is that before, my certainty was predictive in nature and that naturally carries some insecurity. Today’s certainty comes from having seen it happen we are no longer talking about what will be, we are talking about what undeniably is.

I’m a lot more at peace, a happier person, and very much at ease with how things unfold. I hope you share that same confidence already, or that you soon will.

Happy – OK, I lied. This one is my favorite. If you ask us what’s the #1 service we provide these days, the most important thing we do for people, my answer will not be related to money, or efficiency, or time. Yes, we do help people quite a bit when measured in those terms.

But the thing that strikes us all as most important, is making people happy. It’s fair to call Power Pivot a “hard skill,” and it’s one that delivers ROI on a grand scale (ex: one of our one-week projects ended up saving the client $25 Million a year). But the “soft” stuff is what really energizes us.

This stuff makes people happier, and you cannot put a price on that. We live charmed lives working in data and solving valuable problems is the sort of thing that we “expect” to be boring and dehumanizing, but when it actually works, it’s exactly the opposite.

Welcome to Happy Data Land.

Download Power Pivot and Power BI the Excel user’s guide to DAX Power Query, Power BI & Power Pivot in Excel 2010-2016 PDF 

Author(s): Collie, Rob;Singh, Avi

Publisher: Holy Macro! Books, Year: 2016

ISBN: 9781615470396

Download Power Pivot and Power BI The Excel User’s Guide in PDF Format For Free.