From the MVPs: End of Excel VLOOKUPS – Power Pivot Relationships


This is the 55th in our series of guest posts by Microsoft Most Valued Professionals (MVPs). You can click here to see all past MVP articles.

Since the early 1990s, Microsoft has recognized technology champions around the world with the MVP Award. MVPs freely share their knowledge, real-world experience, and impartial and objective feedback to help people enhance the way they use technology. Of the millions of individuals who participate in technology communities, around 4,000 are recognized as Microsoft MVPs. You can read more original MVP-authored content on the Microsoft MVP Award Program Blog.

This post is by Power BI MVP Rob Collie and Avi Singh. Thanks Rob and Avi!


Do you suffer from Excel VLOOKUP fatigue? The life of an Excel user inevitably involves combining data from various sources. And that, typically involves a generous use of VLOOKUPs.

We, at PowerPivotPro, argue that Excel is the best BI tool in the world; it’s our superhero (and of millions of business users who use it daily). However, the “old” Excel has some dark corners. For instance, the use of VLOOKUPs is problematic in cases such as

  • Large numbers of tables which need to be looked up: creating these formulas can be laborious and easy to break
  • Large number of rows: Million+ rows is certainly a problem for Excel. Less rows may still cause a performance issue
  • Large number of columns: Even with limited number of rows, if you have, say many columns that you need to VLOOKUP; that can slow down things significantly

VLOOKUP got you down? We got the cure

Power BI for Excel

Well, meet the “new” Excel! Our superhero has a new sidekick – Power BI. You can build Power BI solutions, using the standalone tool, Power BI Desktop. But you can also harness the strength of Power BI from within Excel, using available add-ins (varies by Office Edition, see article).

Excel and Power BI: a super powerful combination

Let’s take a simple AdventureWorks data set. We could pull this in Excel and “flatten” it, using VLOOKUPs. Even with this small data set, it’s somewhat laborious to build and degrades the performance (noticeable any time Excel formulas get recalculated).

Simple collection of tables in Excel…

Takes an army of VLOOKUPs to flatten Excel Tables

To achieve the same result, using Power BI, you would pull in the data to the Excel Power Pivot add-in. And then instead of an army of VLOOKUPs, you would create relationships between the tables by simply using your mouse to drag and drop the related columns.

Easily connect tables using the power of relationships

This simple step, allows you to then slice and dice your data using any of the fields in the connected tables. Guess what, this is faster to build, gives you lightning speed performance, a much smaller file size and can easily scale to hundreds of millions of rows!

Build pivot tables easily by selecting columns from any available table

Of course relationships is just one of many superpowers that you look to gain, when you leverage Power BI with Excel. To give you a flavor, think about calculating a distinct/unique count of customers making a purchase based on our sales data. This is not straightforward, when doing in Excel. There are a few common tricks that I hear – remove duplicates, creative pivots using customer key – along with fancier solutions (VBA macros, complicated formulas) – none of which are straightforward.

Let’s see how we would accomplish the same using Power BI with Excel. Using the Power Pivot add-in, we can simply define a new measure (think of a measure as a Power BI Formula, which by the way uses its own syntax called DAX).

Customer Count = DISTINCTCOUNT( Sales[Customer[Key] )

Now you can slice-and-dice and analyze the Customer Count using any of the fields available in our tables.

Hard calculations in Excel (like Distinct Count) become easy formulas in Power BI

Formulas, you Define Once and Use Everywhere

Notice that our formula for Customer Count, does not specify anything about showing customer count by country or product category. All of that works as if by magic, using the power of relationships we created earlier. We call this “Define Once, Use Everywhere“. You define your measures just once – but they can be used in a pivot of any shape, with any fields.

Define Once, Use Everywhere: The same Customer Count measure can be used to show monthly trends, simply be rearranging the fields in the Pivot Table field list

Contrast this with typical Excel formulas were changing the shape of a report, usually involves a fair amount of formula rework.

Furthermore, the Power BI formula language, DAX (Data Analysis eXpressions), lets you write extremely sophisticated measures easily. Check out formulas to calculate New or Returning Customers.

Download Sample Chapter

Download Sample Chapters from this book, Power Pivot and Power BI, by Rob Collie and Avi Singh

Download complete Chapter 2: Power Pivot and the Power BI Family to make sense of the various Power BI tools and versions available.

Download complete Chapter 10: Thinking in Multiple Tables for step-by-step instructions on creating and leveraging relationships using Excel and Power Pivot.

Purchase the book at Amazon.com

 

 

 

 

About Rob Collie

Rob Collie is the founder of PowerPivotPro, which offers training and consulting on Power Pivot and Power BI.

In 2010, after a 14-year career at Microsoft and helping found Power Pivot, Rob started his own consulting and training firm, PowerPivotPro. He has since seen the same theme repeated: this toolset doesn’t just increase efficiency; it doesn’t just reduce costs; it doesn’t just lead to dramatically smarter decisions and same-day agility; it also makes people (and organizations) happier. Rob wants to help you experience this.

When not training, consulting, speaking, or writing, Rob can be found in his laboratory, devising new tools and techniques for the Power BI community. He is the author of three Power Pivot and Power BI books, has penned over 600 articles and whitepapers, and also occasionally sleeps.

 

About Avi Singh

Avi Singh is a Principal Consultant at PowerPivotPro, where he has helped others transform their BI world by blogging, training, and consulting. He is co-author of the book “Power Pivot and Power BI: The Excel User’s Guide”.

Avi has personally experienced the transformation and empowerment that Power BI can bring, going from an Excel user to building large-scale Power BI platforms for 600+ users within Microsoft. His mission now is to share his knowledge and spread the word about Power BI.

Comments (0)

Skip to main content