skip to Main Content

  
Eat This not That PowerPivot EditionEat This Not That, PowerPivot Edition!

Just like normal Excel, PowerPivot requires little or no premeditation – you can just jump right in and build something quite impressive.

But if you want to be a true PowerPivot Pro, and reap the full long-term manageability and robustness benefits, applying a little bit of discipline to your workbook development can go a long way.  

The list of best practices we’ve been developing at Pivotstream is a little longer than I have time to pull together right now, but I thought I’d share a few quick tips.

#1:  In Measure Formulas, ALWAYS Include Table Names in Col Refs

You may have noticed that PowerPivot sometimes lets you omit table names in your measure formulas.  Resist this temptation.  Always include the table name.

Use THIS:

   =SUM(Table1[MyColumn])

NOT that:

   =SUM([MyColumn])

Yes, I know they both work, and both return the same result.  But some places REQUIRE the table name, like arguments to the CALCULATE function.  So you might as well get into the habit.  Plus, when combined with the next tip, you get another benefit too.

#2:  NEVER Include Table Names in Measure Refs

In another odd bit of flexibility, PowerPivot lets you include the table name in references to other measure names.  My advice here is exactly the opposite of the above.  NEVER include the table names when referencing a measure.

Use THIS:

   =[Measure1] – [Measure2]

NOT that:

   =Table1[Measure1] – Table1[Measure2]

Measure names must be unique across the entire PowerPivot workbook, so the table name is not needed.

More importantly, if you follow tips 1 and 2 religiously, you will always be able to tell the difference between columns and measures in your measure formulas, and trust me, that is a big advantage.

#3:  Don’t cut corners on prototype data sources

Here’s the quick version:

Use THIS:

  1. SQL Server
  2. Another “real” database like Oracle, DB2, etc.
  3. SharePoint Lists, Access Services, etc.
  4. Reporting Services reports
  5. Other OData Data Feeds

NOT that:

  1. Excel Linked Tables
  2. Copy/Pasted Sources

These are MAYBE’s:

  1. Access MDB’s
  2. Text Files

Long version:  Data Sources are like Marriages (High Switching Cost)

Why do I have these biases?  Primarily because changing data sources later is exceedingly painful.

You CANNOT take a PowerPivot table that is sourced from Copy/Paste, for instance, and switch it to use another data source type.  In fact, you can’t change any source type to any other.

So, if you prototype against a text file and later want to point that table to SQL Server…  you must delete the table (which deletes ALL of that table’s measures, calc columns, and relationships), and start over.

If you think that isn’t a big deal, you haven’t lived through it yet :)  Nothing is more frustrating than re-doing work you have already done.

So…  if you think there’s a decent chance that long-term, your data will come from a real DB, go ahead and take the time to start that way.  It will pay off over and over.

Note that you CAN point a PowerPivot table from one db server to another, or point it at another db table, or another db view, etc. – and this DOES NOT require you to delete your table.  There is a universe of flexibility once your data is sourced from a db.

Oh, and if you think you’ve got a table that you’ll never need to update, and that you will never want to move to a real db, well, from experience I can tell you that you might be mistaken.  I’ve made that mistake already, many times, and paid for it later.

There are some other surprising benefits of having your data come from a robust refreshable source, but I will cover that another time.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 2 Comments
  1. one not so good hacky way I found to solve around the data source change problem was to a)copy all formulas from oldTable and reserve b) import newTable c) replicate all relationships and formulas in newTable d)delete oldTable e)rename newTable to oldTable

    this broke surprisingly few workbooks, though it was painful to c/p dozens of calc columns. I know this is an old post but this page SEO’s to the top for “change powerpivot source” or something like that.

  2. I am too late to read this post. I created my data model, relationships and measures in the same workbook as my source data tables. Later I realized that my excel workbook had bloated so I deleted the source data tables from excel. Although tables in my data model shows linked icon in powerpivot window, but there is no excel table (Go to Excel Table is greyed out in Linked Table tab). Now I can’t refresh my lookup tables or recreate links 🙁

    Probably I will have to recreate my data model and paste my measures 1 by 1. Is there any way I can reduce work on any step. Thank you

Leave a Comment or Question