[Rob_Collie,_Avichal_Singh]_Power_Pivot_and_Power

Power

Pivot

需积分: 18 44 浏览量 2019-06-12 22:44:42 上传评论收藏 14.79MB PDF 举报

资源推荐

资源详情

资源评论

iii

Contents at a Glance

Dedicaons ...................................................................................................................................... xiv

Supporng Workbooks and Data Sets .............................................................................................. xiv

Errata and Book Support .................................................................................................................. xiv

A Note on Hyperlinks ....................................................................................................................... xiv

Foreword and Forward ...................................................................................................................... xv

Introducon - Our Two Goals for this Book ..........................................................................................1

1 - A Revoluon Built On YOU .............................................................................................................2

2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions .................................6

4 - Loading Data Into Power Pivot......................................................................................................17

5 - Intro to Calculated Columns .........................................................................................................25

6 - Introducon to DAX Measures .....................................................................................................30

7 - The “Golden Rules” of DAX Measures ........................................................................................... 48

8 - CALCULATE() – Your New Favorite Funcon ..................................................................................58

9 - ALL() – The “Remove a Filter” Funcon .........................................................................................65

10 - Thinking in Mulple Tables .........................................................................................................71

11 - “Intermission” – Taking Stock of Your New Powers .....................................................................82

12 - Disconnected Tables ...................................................................................................................83

13 - Introducing the FILTER() Funcon, and Disconnected Tables Connued ......................................92

14 - Introducon to Time Intelligence .............................................................................................. 102

15 - IF(), SWITCH(), BLANK(), and Other Condional Fun ..................................................................121

16 - SUMX() and Other X (“Iterator”) Funcons ...............................................................................130

17 - Mulple Data Tables .................................................................................................................139

18 - Mulple Data Tables – Diering Granularity .............................................................................152

19 - Performance: Keep Things Running Fast ...................................................................................162

20 - Power Query to the Rescue ......................................................................................................173

21 - Power BI Desktop .....................................................................................................................205

22 - “Complicated” Relaonships ....................................................................................................217

23 - Row and Filter Context Demysed .......................................................................................... 230

24 - CALCULATE and FILTER – More Nuances ....................................................................................240

25 - Time Intelligence with Custom Calendars: Greatest Formula in the World ................................245

26 - Advanced Calculated Columns ..................................................................................................262

27 - New DAX Funcons… and Variables! ........................................................................................273

28 - “YouTube for Data” – The Importance of a Server ..................................................................... 288

PS: Can We Ask You for a Special Favor? ..........................................................................................296

A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!) .....................................297

A2 - Cube Formulas – the End of GetPivotData() ..............................................................................304

A3 - Some Common Error Messages ................................................................................................307

A4 - People: The Most Powerful Feature of Power Pivot ..................................................................309

Index .......................................................................................................................................................311

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

Detailed Table of Contents

Power Pivot and Power BI ......................................................................................................................... ii

Dedicaons ...................................................................................................................................... xiv

Supporng Workbooks and Data Sets .............................................................................................. xiv

Errata and Book Support .................................................................................................................. xiv

A Note on Hyperlinks ....................................................................................................................... xiv

Foreword and Forward ...................................................................................................................... xv

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

What Has Changed at Microso? Virtually Everything. .................................................................... xv

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

Introducon - Our Two Goals for this Book ..........................................................................................1

1 - A Revoluon Built On YOU .............................................................................................................2

Does This Sound Familiar? .................................................................................................................2

Excel Pros: The World Is Changing in Your Favor .................................................................................2

Our Importance Today .......................................................................................................................3

Excel at the Core ............................................................................................................................................ 3

Three Ingredients of Revoluon ........................................................................................................4

Ingredient One: Explosion of Data .................................................................................................................. 4

Ingredient Two: Economic Pressure ................................................................................................................ 4

Ingredient Three: Dramacally Beer Tools .................................................................................................... 5

2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions .................................6

It’s a Family of Products Built on Shared Engines ...............................................................................6

Power Query is a Close Second in Importance ................................................................................................ 7

Visuals: The Crucial “Last Mile” .........................................................................................................8

Power BI Desktop: Two Tools for the (Learning) Price of One! ............................................................9

Same Engines, Just Dierent Visuals ............................................................................................................... 9

What do we mean by the “tough” or “valuable” stu? ................................................................................. 10

Power Pivot (in Excel) Versions ........................................................................................................11

Power Pivot for Excel 2010 ........................................................................................................................... 12

Power Pivot for Excel 2013 -

Only Available in “Pro Plus” Excel .................................................12

Dierences in User Interface: 2010, 2013, 2016 ...............................................................................12

When We Said “Cosmec” We Meant “Awkward” ........................................................................................................13

32-bit or 64-bit? ..............................................................................................................................13

Oce 2010 or Newer is Required ....................................................................................................14

Power Pivot is Like Geng Fieen Years of Excel Improvements All at Once ....................................14

Learn Power Pivot As You Learned Excel: Start Simple & Grow ........................................................14

When to Use Power Pivot, and How it Relates to Normal Pivot Usage .............................................16

What This Book Will Cover in Depth ............................................................................................................. 16

4 - Loading Data Into Power Pivot......................................................................................................17

No Wizards Were Harmed in the Creaon of this Chapter ................................................................ 17

Everything Must “Land” in the Power Pivot Window .......................................................................17

Launching the Power Pivot Window ............................................................................................................. 17

One Sheet Tab = One Table ........................................................................................................................... 17

You Cannot Edit Cells in the Power Pivot Window ........................................................................................ 17

Everything in the Power Pivot Window Gets Saved into the Same XLSX File ................................................. 18

Many Dierent Sources ...................................................................................................................18

Linked Tables (Data Source Type) ..................................................................................................... 18

Advantages .................................................................................................................................................. 19

Limitaons ................................................................................................................................................... 19

Tips and Other Notes ................................................................................................................................... 20

Pasng Data Into Power Pivot (Data Source Type) ...........................................................................21

Advantages .................................................................................................................................................. 21

Limitaons ................................................................................................................................................... 21

Imporng From Text Files (Data Source Type) ..................................................................................21