Power Pivot与Power BI实战指南

需积分: 18 12 浏览量更新于2024-07-17 收藏 14.79MB PDF 举报

"《Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016》是由Rob Collie和Avichal Singh共同编著的一本关于数据建模和分析的专业书籍，主要面向Excel用户，旨在教授读者如何使用Power Pivot和Power BI进行数据分析和业务洞察。书中融入了作者多年教学和实践经验，涵盖了DAX（Data Analysis Expressions）语言、Power Query以及在Excel 2010至2016版本中的Power Pivot应用。" 本书的核心知识点包括： 1. **Power Pivot**: Power Pivot是Excel的一个扩展功能，用于构建大规模的数据模型。它允许用户导入大量数据，创建复杂的计算和关系，从而实现高级的数据分析。在书中，作者详细介绍了如何使用Power Pivot管理数据源、建立数据关系、创建度量值和计算列。 2. **DAX**: DAX是一种专门用于Power Pivot和Power BI的数据表达式语言，用于定义计算字段和度量值。DAX提供了一系列函数和运算符，使用户可以创建自定义的计算，如总和、平均值、计数等，以及更复杂的数据过滤和聚合。 3. **Power Query**: 又称为Get & Transform，是Excel中用于数据清洗和转换的工具。它使得用户可以从各种数据源（如Excel表格、数据库、Web等）提取数据，并进行格式化、合并和转换操作，为数据建模做好准备。书中会介绍如何使用Power Query进行数据预处理和数据流管理。 4. **Power BI**: Microsoft的商业分析服务，它将数据可视化、报告和仪表板功能与云服务相结合。作者将指导读者如何使用Power BI Desktop创建交互式报表，连接到Power Pivot数据模型，并分享这些报告到Power BI服务，以实现跨组织的数据共享和协作。 5. **实践应用**: 书中不仅包含理论知识，还有大量实例和案例研究，帮助读者理解如何在实际工作场景中应用这些工具和技术。通过这些实例，读者能够学习到如何将Power Pivot、DAX和Power BI结合使用，解决复杂的业务问题。 6. **教学方法**: 作者Rob Collie和Avichal Singh在书中分享了他们多年的教学经验和实际工作中的最佳实践，使读者能够快速掌握Power Pivot和Power BI的精髓。这本书适合已经熟悉Excel基础，但希望通过Power Pivot和Power BI提升数据分析能力的用户。无论是数据分析师、业务智能专业人员还是寻求提高数据处理技能的Excel爱好者，都能从中受益。通过阅读此书，读者将能够熟练运用这些工具，提高数据分析效率，为决策提供更有力的数据支持。

Foreword and Forward

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

As we wrapped up nal edits on this book, Avi and Bill said, “OK Rob, you know those rst two chapters? The ones that

set the stage and give perspecve to this whole thing? It’s me 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 Microso’s vigorous seismic acvity.

But when I re-read those two chapters, I found very lile 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 wring 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 im-

portance of Excel, the massive opportunies aorded to “data people,” and Microso’s connued investment in all of

the above.

I suspect that ten years from now, if we’re revising this book for the Nth edion, those chapters will again largely remain

unchanged – except that we will be talking about a data revoluon that’s already run its course, rather than one that’s

in progress 

Instead I’m going to use this Foreword to reect a bit on some things that truly have changed. Let’s start with the 800

pound gorilla, my former employer (and Avi’s)…

The Microso Corporaon.

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 Microso is not to be underesmated. Satya brings a very dierent

and more open perspecve to the game, and that absolutely makes a dierence to us. For example, today’s Microso

does not stubbornly ignore iOS and Android, whereas the old regime acted like “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 plaorms, I chortled for two months consecuve be-

fore eventually having to see a doctor to make it stop.) It’s worth reecng how far we have come since then. Microso

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 rst edion of this book went to press, I don’t think we’d even heard of Power Query. Mic-

roso 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 revoluon in how the world operates. So to have them surprise us, out of the blue, with

a relavely 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 rering that

name, the “M Engine” is here to stay, and our professional lives are forever altered. Dramacally for the beer.

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 Microso. 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 unied Microso seems to be in the BI space these days. Not perfectly unied, but dramacally

more so than I have ever seen before. Everyone seems to be pulling in the same direcon – both within the SQL team’s

many facons (who, in my me there, were in open war with one another), and across the SQL/Oce boundary. The

laer is parcularly 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 prey 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. Seriously, I’ve gone from

feeling ambushed to feeling like we’ve been given a tremendous gi. And oh yeah – a free cloud version for publishing

xvi

that anyone can use, and that’s easy to sign up for? With an open source visuals plaorm? This is Microso 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 wring an en-

re chapter on how you deal with a parcular problem, and then three weeks later, them adding a feature that makes

that problem go away, rendering the enre 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 lile downside.

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

I always tell rst-me 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 small-

scale observaons from one person and put them next to the changes happening at a goliath like Microso, but I do

have what they call a ringside seat for this parcular show. There’s relevance here, especially when it comes to hard

numbers and economics.

Let’s sck 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 mes the size as it was when the rst edion 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 connue

to hold true for quite some me. Welcome everyone 

Team Growth – at me of wring there are 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 are in some ways

even more telling: we now have an organizaon which is large enough to require auxiliary roles. I nd that incredibly

sasfying, and not just on the personal front – our organizaon wouldn’t be growing unless the demand for our ser-

vices was growing. We’re not tradional BI consultants, and we’re not spreadsheet consultants. We’re a new breed

and the market is saying “yes, this is a good mutaon, your virus may connue to grow.” In fact I’m aware of several

brand-new rms that have joined us in this “new style,” and the world of data is so large that there’s zero sense of

compeon, only a shared sense of joy in changing the rules in a posive way.

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

this 2nd Edion. This is great news, because there was zero chance I’d have been able to do this alone. (I’ve never been

busier, as a professional, than I am today). So First Edion would probably have remained Only Edion without Avi on

board. If anything, Avi believes in this stu 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 

Microso Relaonship – our relaonship with the “mother ship” is in a much beer place today than three years ago.

It’s not like there was fricon before, and I do sll have a lot of friends there, but there was also a longstanding mutual

sense that there wasn’t much ROI in cooperang. For the most part, I ignored Microso 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 aribute that to “everyone geng smarter” rather than one of us adopng the other’s longstanding stance. To-

day, our messaging helps Microso reach customers, and Microso likewise connects us with people who need help.

This may sound like a subtle point, but it could not feel any more dierent. Surprising as it sounds, this ex-Microso

employee (and High Priest of their data plaorm) feels like he’s back in the family for the rst me in six years. And

again, this reects on Microso’s posive direcon as well as the market.

Condence – 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 quesoned 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 quesoning whether this stu was catching on or not. Three

years ago I would have waded into the fray, guns blazing. But this me 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 ght anyway (thanks Greg). The point

here is that before, my certainty was predicve 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 the way things are unfolding. I hope you share

that same condence 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 eciency, or me. 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 “so” stu is what really energizes us. This stu 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.

-Rob Collie, November 2015

Introduction - Our Two Goals for this Book

Fundamentally of course, this book is intended to train you on Power Pivot and Power BI. It captures the techniques

we’ve learned from many years of teaching Power Pivot and its “cousin technologies” (in person and on PowerPivotPro.

com), as well as applying it extensively in our everyday work.

Unsurprisingly, then, the contents herein are very much instruconal – a “how to” book if ever there was one.

But we also want you to understand how to maximize the impact on your career. This isn’t just a beer way to do

PivotTables. It isn’t just a way to reduce manual eort. It’s not just a beer formula engine.

Even though Rob worked on the rst version of Power Pivot while at Microso, he had no idea how impacul it would

be unl about two years aer he le to form his own company. He had to experience it in the real world to see its full

potenal, and even then it took some me to overwhelm his skepcal nature (his Twier prole now describes him as

“skepc turned High Priest.”)

This is the rare technology that can (and will) fundamentally change the lives of millions of people – it has more in

common with the invenon of the PC than with the invenon of, say, the VCR.

The PC might be a parcularly relevant example actually. At a presgious Seale high school in the early 1970’s, Bill

Gates and Paul Allen discovered a mutual love for programming, but there was no widespread demand for program-

mers at that point. Only when the rst PC (the Altair) was introduced was there an opportunity to properly moneze

their skills. Short version: they founded Microso and became billionaires.

But zoom out and you’ll see much more. Thousands of people became millionaires at Microso alone (sadly, we both

missed that boat by a few years). Further, without the Altair, there would have been no IBM PC, no Apple, no Mac, no

Steve Jobs. No iPod, no iPhone, no Appstore. No Electronic Arts, no Myst. No World of Warcra. The number of people

who became wealthy as a result of the PC absolutely dwarfs the number of people who had anything to do with

invenng the PC itself!

We think Power Pivot and Power BI oer the same potenal wealth-generaon eect to Excel users as the PC oered

budding programmers like Gates and Allen: your innate skills remain the same but their value becomes many mes

greater. Before diving into the instruconal stu in Chapters 2 and beyond, Chapter 1 will summarize your excing new

role in the changing world.

And like many things in when you hang around with Rob, the story starts with a movie reference 

Power Pivot and Power BI: The Excel User's Guide to the Data Revoluon

1 - A Revolution Built On YOU

Does This Sound Familiar?

(Updated Fall 2015, but we decided to leave this part in Rob’s rst-person “voice” – because the authencity is bet-

ter-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 perspecve 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 ight and is pleased to see that her seatmate appears to be a

reasonably normal fellow. They strike up a friendly conversaon, and when he asks her what she does for a living, she

gives the usual reply: “I’m a markeng analyst.”

That answer sases 99% of her single-serving friends, at which the conversaon typically turns to something else.

However, this guy is the excepon, and asks the dreaded follow-up queson: “Oh, neat! What does that mean?”

She sighs, ever so slightly, because the honest answer to that queson always bores people to death. Worse than that

actually: it oen makes the single-serving friend recoil a bit, and express a senment 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 Piv-

otTables.” She fully expects this to be a setback in the conversaon, a point on which we share no common ground.

Does this woman’s story sound familiar? Do you occasionally nd yourself in the same posion?

Well imagine her surprise when this parcular single-serving friend actually becomes excited aer hearing her an-

swer! He lights up – it’s the highlight of his day to meet her.

Because, you see, on this ight, she sat down next to me. And I have some excing 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 condently 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 revoluon.

That probably sounds prey 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 Microso, it was my job to meet people like you. I was an engineer on the

Excel team, and I led a lot of the eorts to design new funconality for relavely advanced users.

Meeng those people, and watching them work, was crucial, so I traveled to nd 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 Microso itself, working in var-

ious nance, accounng, and markeng roles, and I spent a lot of me with them as well (more on this later).

Over those years, I formed a ‘prole’ of these ‘ten hour’ spreadsheet people I met. Again, see if this sounds familiar.

Aributes of an Excel Pro:

• They grab data from one or more sources.

• They prep the data, oen using VLOOKUP.

• They then create pivots over the prepared data.

• Somemes they subsequently index into the resulng pivots, using formulas, to produce polished reports.

Other mes, 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 me re-creang the same reports, updated with the latest data, on a recurring

basis.

1 - A Revoluon Built On YOU

At rst, it seemed to be a coincidence that there was so much similarity in the people I was meeng. But over me

it became clear that this was no accident. It started to seem more like a law of physics – an inevitable state of aairs.

Much like the heat and pressure in the earth’s crust seize the occasional pocket of carbon and transform it into a dia-

mond, the demands of the modern world ‘recruit’ a certain kind of person and forge them into an Excel Pro.

Aside: Most Excel Pros do not think of themselves as Pros: I nd that most are quite modest about their

skills. However, take it from someone who has studied Excel usage in depth: if you t the bulleted criteria

above, you are an Excel Pro. Wear the badge proudly.

I can even put an esmate on how many of you are out there. At Microso we used to esmate that there were 300

million users of Excel worldwide. This number was disputed, and might be too low, especially today. It’s a good base-

line, nothing more. But that was all users of Excel – from the most casual to the most expert. Our instrumentaon 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. Creang 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 parcular workplace, because stascally speaking you are quite rare – less than 0.5%

of the world’s populaon 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 convenons, it would be quite a sight.

I, too, t the denion of an Excel Pro. It is no accident that I found myself drawn to the Excel team aer a

few years at Microso, and it is no accident that I ulmately le to start an Excel / Power Pivot-focused

business (and blog). While I have been using the word ‘you’ to describe Excel Pros, I am just as comfortable

with the word ‘we.’

As I said up front, I am convinced that our importance is about to explode into the general consciousness. Aer all, we

are already crucial.

Our Importance Today

As proof of how vital we are, here’s another story from Microso, one that borders on legend. The actual event trans-

pired more than ten years ago and the details are hazy, but ulmately it’s about you; about us.

Someone from the SQL Server database team was meeng with Microso CEO Steve Ballmer. They were trying to get

his support for a ‘business intelligence’ (BI) iniave within Microso – to make the company itself a testbed for some

new BI products in development at that me. If Steve supported the project, the BI team would have a much easier

me gaining tracon within the accounng and nance divisions at Microso.

In those days, Microso had a bit of a ‘prove it to me’ culture. It was a common approach to ‘play dumb’ and say some-

thing 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 queson like this: “If we asked you how much sales of Microso Oce

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 queson – 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 oce and started shoung. Three people hurried in, and he started wav-

ing his arms francally and bellowing orders, conveying the challenge at hand and the informaon he needed. This

all happened with an aura of familiarity – this was a common occurrence, a typical workow 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 nancial. Yes, I am sure that now, many years later, Satya

Nadella has a broad array of sophiscated BI tools at his disposal. However, I am equally sure that his reliance on Excel

Pros has not diminished by any signicant amount.

剩余329页未读，继续阅读

weixin_45224829

粉丝: 0
资源: 1

Power Pivot与Power BI实战指南

power pivot and power BI

MTK makefile解析

[Daniel_Liberzon]_Switching_in_Systems_and_Control(b-ok.xyz).pdf

com.yuantiku.collie_2100100016_installer.exe

Collie-crx插件

SCJP_310-065

Border Collie HD Wallpapers Dog Puppy Theme-crx插件

collie界面原型

My Collie HD Dog Wallpapers New Tab Theme-crx插件

html5动画框架collie

最新资源