Power Pivot and Power BI by Collie, Rob -- Read -- Imperial Library of Trantor

Index

Power Pivot and Power BI Dedications Supporting Workbooks and Data Sets Errata and Book Support A Note on Hyperlinks Foreword and Forward “State of the Union” November 2015 – What’s Changed? What Has Changed at Microsoft? Virtually Everything. What’s Changed in My Corner of the World? Also Everything. Introduction - Our Two Goals for this Book 1 - A Revolution Built On YOU Does This Sound Familiar? Excel Pros: The World Is Changing in Your Favor Our Importance Today

Excel at the Core

Three Ingredients of Revolution

Ingredient One: Explosion of Data Ingredient Two: Economic Pressure Ingredient Three: Dramatically Better Tools

2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions It’s a Family of Products Built on Shared Engines

Power Query is a Close Second in Importance

Visuals: The Crucial “Last Mile” Power BI Desktop: Two Tools for the (Learning) Price of One!

Same Engines, Just Different Visuals What do we mean by the “tough” or “valuable” stuff?

Power Pivot (in Excel) Versions

Power Pivot for Excel 2010 Power Pivot for Excel 2013 - Only Available in “Pro Plus” Excel

Differences in User Interface: 2010, 2013, 2016

When We Said “Cosmetic” We Meant “Awkward”

32-bit or 64-bit? Office 2010 or Newer is Required 3 - Learning Power Pivot “The Excel Way” Power Pivot is Like Getting Fifteen Years of Excel Improvements All at Once Learn Power Pivot As You Learned Excel: Start Simple & Grow When to Use Power Pivot, and How it Relates to Normal Pivot Usage

What This Book Will Cover in Depth

4 - Loading Data Into Power Pivot No Wizards Were Harmed in the Creation of this Chapter Everything Must “Land” in the Power Pivot Window

Launching the Power Pivot Window One Sheet Tab = One Table You Cannot Edit Cells in the Power Pivot Window Everything in the Power Pivot Window Gets Saved into the Same XLSX File

Many Different Sources Linked Tables (Data Source Type)

Advantages Limitations Tips and Other Notes

Pasting Data Into Power Pivot (Data Source Type)

Advantages Limitations

Importing From Text Files (Data Source Type)

Advantages Limitations

Databases (Data Source Type)

Advantages Limitations

Less Common Data Source Types

SharePoint Lists Reporting Services (SSRS) Reports Cloud Sources Like Azure DataMarket and SQL Azure “Data Feeds”

Other Important Features and Tips

Renaming up Front – VERY Important! Don’t Import More Columns than You Need Table Properties Button Existing Connections Button

5 - Intro to Calculated Columns Two Kinds of Power Pivot Formulas Adding Your First Calculated Column

Starting a Formula Referencing a Column via the Mouse Referencing a Column by Typing and Autocomplete Just like Excel Tables! Rename the New Column Reference the New Column in Another Calculation

Properties of Calculated Columns

No Exceptions! No “A1” Style Reference Stored Statically with the File

Slightly More Advanced Calculations

Function Names Also Autocomplete Aggregation Functions Implicitly Reference the Entire Column Quite a Few “Traditional” Excel Functions are Available Excel functions Are Identical in Power Pivot

Enough Calculated Columns for Now 6 - Introduction to DAX Measures “The Best Thing to Happen to Excel in 20 Years” Aside: A Tale of Two Formula Engines Adding Your First Measure

Create a Pivot Add a Measure! Name the Measure Results Works As You Would Expect

“Implicit” Versus “Explicit” Measures Referencing Measures in Other Measures

Another Simple Measure First Creating a Ratio Measure Original Measures Do NOT Have to Remain on the Pivot Changes to “Ancestor” Measures Flow Through to Dependent Measures Cases Where This Makes Real Sense Reuse Measures, Don’t “Redefine”

Other Fundamental Benefits of Measures

Use in Any Pivot Centrally-Defined Number Formatting

Whetting Your Appetite: COUNTROWS() and DISTINCTCOUNT()

COUNTROWS(Sales) DISTINCTCOUNT(Sales[OrderDate]) Deriving More Useful Measures From These Two Rearrange Pivot, Measures Automatically Adjust!

Slicers in Different Versions of Excel Measures Are “Portable Formulas” 7 - The “Golden Rules” of DAX Measures How Does the DAX Engine Arrive at Those Numbers?

Stepping Through That Example

Translating the Examples Into Three Golden Rules

Rule A: DAX Measures Are Evaluated Against the Source Data, NOT the Pivot Rule B: Each Measure Cell is Calculated Independently Rule C: DAX Measures are Evaluated in 6 Logical Steps

Step 1: Detect Pivot Coordinates Step 2: CALCULATE Alters Filter Context Step 3: Apply Those Filter Coordinates to the Underlying Table(s) Step 4: Filters Follow the Relationship(s) Step 5: Evaluate the Arithmetic Step 6: Return Result

How the DAX Engine Calculates Measures

No “Naked Columns” in Measure Formulas Best Practice: Reference Columns and Measures Differently Best Practice: Assign Measures to the Right Tables

8 - CALCULATE() – Your New Favorite Function A Supercharged SUMIF()

CALCULATE() Syntax CALCULATE() in Action – a Few Quick Examples

How CALCULATE() Works Two Useful Examples of CALCULATE()

Example 1: Transactions of a Certain Type Example 2: Growth Since Inception

Alternatives to the “=” Operator in <Filters> Evaluation of Multiple <filters> in a Single CALCULATE() The “ALL” (aka “Unfiltered”) Filter Context

Not all Totals Are Completely (or Even Partially) Grand

9 - ALL() – The “Remove a Filter” Function The Crisp Basics The Practical Basics – Two Examples

Example 1 – Percentage of Parent Example 2 – Negating a Slicer

Variations ALLEXCEPT() ALLSELECTED() 10 - Thinking in Multiple Tables A Simple and Welcome Change Unlearning the “Thou Shalt Flatten” Commandment Relationships Are Your Friends “Lookup” Tables

The Diagram View Using Related Tables in a Pivot Why That Works: Filter Context “Travels” Across Relationships Visualizing Filters Flowing “Downhill” – One of Our Mental Tricks

Filters from All Related Lookup Tables Are Applied CALCULATE() <Filters> Also Flow Across Relationships 11 - “Intermission” – Taking Stock of Your New Powers 12 - Disconnected Tables

A Parameterized Report Adding the Parameter Table Adding a “Parameter Harvesting” Measure The Field List is Grumpy About This Using the Parameter Measure for Something…Useful Parameter Table Can Be Used on Rows and Columns Too! Why is it Important That They Be Disconnected? A Very Powerful Concept

Disconnected Table Variation: Thresholds

Create a Disconnected Table to Populate the Slicer: Write a Measure to “Harvest” the User’s -Selection: Diverging From the Prior Example: We Need to Filter, Not Perform Math CALCULATE() Has a Limitation? Not really.

13 - Introducing the FILTER() Function, and Disconnected Tables Continued When to Use FILTER() FILTER() Syntax Why is FILTER() Necessary?

It’s All About Performance (Speed of Formula Evaluation) How to Use FILTER() Carefully

Applying FILTER() in the “Thresholds” Example

Revisiting the Successful Formula Verifying That the Measures Work This Could Not Be Done with Relationships Tip: Measures Based on a Shared Pattern – Create via Copy/Paste

More Variations on Disconnected Tables

Upper and Lower Bound Thresholds Fixing the Sort Order on the Slicer: The “Sort By Column” Feature Completing the Min/Max Threshold A Way to Visualize Disconnected Tables

Putting This Chapter in Perspective 14 - Introduction to Time Intelligence At Last, It is Time! “Standard Calendar” versus “Custom Calendar”

Standard Calendars: The Focus of This Chapter Custom Calendars: Perhaps Even More Important than Standard (Covered Later)

Calendar: A Very Special Lookup Table

Where to Get a Calendar Table Properties of a Calendar Table Our Calendar table: Imported and Related Operates like a Normal Lookup Table

First Special Feature: Enable Date Filtering via Mark as Date Table Second Special Feature: Time Intelligence Functions!

Diving in with DATESYTD() Anatomy of DATESYTD()

Function Definition How Does it Work? Changing the Year-End Date

DATESMTD() and DATESQTD() – “Cousins” of DATESYTD() TOTALYTD() – Another Cousin of DATESYTD()

The Remaining (Many) Time Intelligence Functions – Grouped Into “Families” FIRSTDATE() and LASTDATE() ENDOFMONTH(), STARTOFYEAR(), etc. DATEADD()

Growth Versus Last Year (Year-Over-Year, YOY, etc.) Quirks and Caveats

You Must Have Contiguous Date Ranges on Your Pivot DATEADD() Has Special Handling for “Complete” Months/Quarters/Years DATEADD() Lacks Intelligence for Weeks

SAMEPERIODLASTYEAR() PARALLELPERIOD(), NEXTMONTH(), PREVIOUSYEAR(), etc.

PARALLELPERIOD() NEXTMONTH(), PREVIOUSYEAR(), etc.

DATESBETWEEN()

“Life to Date” Calculations Removing That Hardwired 1/1/1900 DATESBETWEEN() is Fantastic with Disconnected Tables Too!

15 - IF(), SWITCH(), BLANK(), and Other Conditional Fun Using IF() in Measures The BLANK() Function DIVIDE() Function The ISBLANK() Function HASONEVALUE() IF() Based on Row/Column/Filter/Slicer Fields

The VALUES() Function Using VALUES() for Columns That Are Not on the Pivot VALUES() Only Returns Unique Values

SWITCH()

SWITCH TRUE()

16 - SUMX() and Other X (“Iterator”) Functions Need to Force Totals to Add Up “Correctly?” Anatomy of SUMX() SUMX() in Action

Detailed Stepthrough

MINX(), MAXX(), AVERAGEX() FILTER() COUNTX() and COUNTAX()

Why is This Different From COUNTROWS(), Then? COUNTAX() versus COUNTX()

Using the X Functions on Fields That Aren’t Displayed

But Which Country?

RANKX()

The Use of ALL() Ties The Optional Parameters Duplicate FullNames?

TOPN() Non-Measure Second Arguments to the X Functions

The COUNTAX() Mystery Solved!

17 - Multiple Data Tables Service Calls Service Calls and Sales Mashup

In Traditional Excel Do Not “Flatten” Measures from Different Data Tables in the Same Pivot! Hybrid Measures

Multiple Data Tables Gotchas

Using Fields from Lookup Table vs. the Data Table Data Table Connected to Some but Not All Lookup Tables

Staying Out of Trouble

18 - Multiple Data Tables – Differing Granularity Example1: Budget versus Actuals

Difficult in Normal Excel Much Faster and More Flexible in Power Pivot Creating Relationships – We Need Some New Lookup Tables Where Do We Get This New Lookup Table? Consider a Database or Power Query SalesTerritory is at Same Granularity Already Repeating the “New Table” Process for Calendar Integrated Pivot Hybrid Measures with Data at Different Grain

Example 2: Using that Mysterious RANKX() Third Argument

The Problem: Ranking MY Products Against Theirs! Year Granularity Mismatch Means a New Lookup Table Simple Measure Now the Absolutely Amazing “Cross-Rank” Measure And Since Both Are Filtered by the Years Table…

19 - Performance: Keep Things Running Fast How Important is Speed?

"Now" Is Three Seconds in Length What Happens When Something Takes Longer Than Three Seconds?

Slicers: The Biggest Culprit

“Cross-Filtering” Behavior Cross-Filtering is Expensive in Terms of Performance Mitigating the Effects of Cross-Filtering

How to Turn off Cross-Filtering Turning off Cross-Filtering Only Impacts that Slicer Slicers For Which You Should Turn Cross-Filtering Off

The Shape of Your Source Tables Is Also Important

Narrower Tables are Better Imported Columns Are Generally Better than Calculated Columns “Star Schema” is Generally Better than “Snowflake Schema”

Measure Performance

DISTINCTCOUNT() is Much Faster than COUNTROWS(DISTINCT()) FILTER() Should Only Be Used Against Lookup Tables and Other “Small” Columns Remember That the “X” Functions Are Loops

20 - Power Query to the Rescue Power Query: Bring Order to Messy Data #1 - Appending Files to Create a Single Power Pivot Table

Scenario

Connecting to One of the CSV Files Adding a Custom Column to “Tag” This File Loading the Data into Power Pivot Connecting to the Second CSV File Connecting to the Third CSV File Time for the Append! “Keeping” Only the Appended Query Testing Refresh

Why This Is a Major Benefit

#2 - Combine Multiple Files from a Folder into a Single Table

Scenario

From Folder Combine CSV Files First Row As Headers Change Data Type and Remove Errors Testing Refresh

Why This Is a Major Benefit

#3 – Adding Custom Columns to Your Lookup Tables

Scenario

Get Data Add Custom Column Define Custom Formula

Why This Is so Amazing

#4 - Using Power Query to “Unpivot” a Table

Scenario

Get Data from Excel Header Row Handling and Remove Column Unpivot! Rename and Change Type

Why This Is a Major Benefit

#5 - Using Power Query to Create a Lookup Table from a Table

Scenario

Create Lookup Table Create Data Table Relating the Two Tables

Why This Is so Amazing

#6 - Creating a Calendar Table: Advanced Power Query

“Wait, I Don’t See a ‘Make Calendar’ Button!” Steps Why This Is a Major Benefit

How NOT to Use Power Query

Don’t Use Power Query Without Power Pivot

Don’t Use Power Query Calculations as a Substitute for DAX Measures Don’t Use Power Query to Flatten Tables Don’t Use Power Query to Mash Two Data Tables Together

21 - Power BI Desktop Meet the New Kid On the Block

Tons of Visualizations

Creating Reports is Easy as 1-2-3 Fully-Interactive Reports Make Your Data Come to Life Power Pivot, Power Query and Power View++ All in One Package

Download Today!

Remember: Same Engines, Different Visuals A Few Words of Perspective

You’re Probably Going to Use Both The “Sales Pitch” – Show Excel-Based to the Analysts, Desktop to Execs

The “Tour”

Missing Terminology The Different Modes Get Data (a.k.a. Power Query) Data Model (a.k.a. Power Pivot) Reports (a.k.a. Power View, but Much Better!) Import Existing Excel Power Pivot Models! Sharing Power BI Desktop Files

22 - “Complicated” Relationships Multiple Relationships Between the Same Two Tables

USERELATIONSHIP()

Many to Many Relationships

First, a Bad Example Another Bad Example Real-World Overlap: The Source of “Legit” Many-to-Many “Bridge” Table Apply M2M as a Pattern

Power BI Desktop 23 - Row and Filter Context Demystified The Basics: Gateway to Doubling Your Superpowers

The Simple Definitions Row Context: The Key Ingredient of Calc Columns There’s No Row Context in Measures! Filter Context: The Key Ingredient of Measures There’s No Filter Context in Calc Columns! Recap So Far

Interaction with Relationships

Relationships and Filter Context Relationships and Row context

Exceptions and Overrides!

Iterator Functions Create Row Context During Measure Calculation CALCULATE Creates Filter Context in Calc Columns We can use CALCULATE to “follow” relationships in calc columns Using Measures Within a Row Context: a Genuine Curveball

Putting It All Together: Review Example

Why Did Our Original Measure Work to Begin With? Recap Within the Context of FILTER() In Case of Emergency…

Key Points from This Chapter 24 - CALCULATE and FILTER – More Nuances CALCULATE Filter Arguments Override Pivot Filters The “Secret” Second Purpose of ALL(), FILTER(), Etc.

CALCULATE’s Definition Gives Us a Hint! ALL() Is the “Remove Filters” Function, but it’s Also a Table! There Are Dozens of These Dual-Purpose Functions! Could Have Been Separate Functions?

Nesting Table Functions Inside One Another

FILTER Can Unfilter?

Putting it All Together 25 - Time Intelligence with Custom Calendars: Greatest Formula in the World Perhaps Our Favorite Thing in DAX Custom Calendars

The Periods Table - a “4/4/5” Example How This Changes Things: We Need to “Write” Our Own Time Intelligence Functions

Connecting the Periods Table Simple “Sales in Period” Measure Another Familiar Concept: Sales per Day First New Concept: Sales per Day in Prior Period

Getting Organized First Desired Results

The Greatest Formula in the World

“Clear Filters Then Re-Filter” – Another Name for GFITW Clear Filter Re-Filter: Navigation Arithmetic

Table[Column] Uses Row Context Generated by FILTER MAX() Operates Over a Filter Context

In Your Periods Table, You Always Need a Numeric PeriodID Column or Equivalent

More GFITW measures – Year Over Year and Year To Date

Prior Period Sales Year Over Year (YOY) Custom Calendar Measure Year To Date (YTD) Measure with Custom Calendar

Fixing Measures to Work at Total Level

Suppressing Prior Period for Totals Fixing YOY to Work at Total Level

The Fix

Fixing Prior Period to Work on Totals, Too

The Usual “Percent Growth” Formulas 26 - Advanced Calculated Columns Perspective: Calculated Columns Are Not DAX’s Strength!

OK, Power Pivot Calc Columns Are a Strength in Some Ways. But More Difficult in Some Cases

Start Out With “Not so Advanced”

Grouping Columns Unique Columns for Sorting Another Sort by Column Example

Now For the Advanced Examples

Summing up in a Lookup Table Use of the EARLIER() Function

EARLIER() in Action

An Even More Advanced Example

Calculated Columns are Static Memory and CPU Consumption During Recalculation of Complex Calc Columns 27 - New DAX Functions… and Variables! Amazing Since 2010, and STILL Improving Important Note: Excel 2016+ and Power BI Desktop Only! New Functions – Some Highlights

DATEDIFF() MEDIAN() and PERCENTILE PRODUCT() GEOMEAN() and GEOMEANX() Other Corresponding X Functions CONCATENATEX: The Most Interesting Function in the World? ISEMPTY() INTERSECT(), EXCEPT() and UNION()

INTERSECT() EXCEPT() UNION()

More New Functions

DAX Variables

Variables Are like a Tape Recorder Variables Offer Three Benefits Benefit One: Cleaner Formulas The VAR Keyword The RETURN Keyword Referencing a Variable Cleaner Formulas (Benefit One) Revisited Benefit Two: Less “Mysterious” Formulas

Example 1: Alternative to EARLIER? Example 2: Measure References Inside FILTER (Within a Measure)

28 - “YouTube for Data” – The Importance of a Server Files – Great for Storage, Not Great for Sharing

Email Sucks as a Delivery Vehicle for Our Awesome Work! Network Distribution via File Shares? Not much better. Parallels to Video Files, Circa 1998

Parent, Grandparents, and Pictures of Cats YouTube Happens! Importance of Web/Mobile

So We Need “YouTube for Data”

PowerBI.com Quick Tour

Step 1: Upload XLSX/PBIX File to PowerBI.com Step 2: Sharing Your Dashboard

Cloud/Server Option Comparison

Cloud/Server Sharing Option: Even More Valuable than YouTube

PS: Can We Ask You for a Special Favor? A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!) SSAS Tabular Features Power Pivot to SSAS Tabular

Connect to SSAS Tabular from Excel

Going Further with SSAS Tabular: Visual Studio Key Takeaways A2 - Cube Formulas – the End of GetPivotData() Formulas Reaching into Pivots = The Dark Ages One Click That Will Change Your Life The Data Is Still “Live!” You Can Also Write Them “From Scratch”

For Starters, CUBEVALUE() Is All You Really Need Adding a Slicer is easy…

Perspective – When to Use, Tradeoffs, Etc. More Information A3 - Some Common Error Messages Addin is “Out of Sync” “Initialization of the Data Source Failed” Other Scary-But-Harmless Errors Perspective A4 - People: The Most Powerful Feature of Power Pivot Index

← Prev
Back
Next →

← Prev
Back
Next →