Webinar Recording and Online Class Aug 3-4

July 27, 2015

By Avi Singh [Twitter]

Friends, it was a joy again to host our free webinar Excel to Power BI. As usual, we spent close to two hours with a lengthy Q&A session after the presentation. You can find the the recording at the end of this post.

Remember, the Power Pivot Data Model is the engine that powers all the pretty visualizations that you see in all Power BI Demos. With a poor data model all you get is poor results; garbage in => garbage out. So join us for our next online class on Aug 3-4 and jumpstart your journey to Power Pivot/Power BI awesomeness!

Power Pivot is the center of the Power BI universe

Online Class
Aug 3-4th

PowerPivotPro Online Class

Enjoy the webinar recording using links below.

Excel to Power BI: Webinar Recording

Read the rest of this entry »

Connecting to an Oracle Database from Power Pivot

July 23, 2015

Guest Post by Andrew Todd

Intro by Avi: Andrew has been a part of PowerPivotPro family for a while now and has been doing wonders for the clients he has been working with. You have probably heard Rob talk about the “Data Gene”, well Andrew is the ideal specimen for that breed.
Recently I had worked with a client where we spent quite some time just trying to figure out how to connect Power Pivot to their Oracle database. Andrew here lays it all out in simple step by step approach. Andrew’s got a lot of wisdom to share and you should all look forward to more posts from him. Take it away Andrew…

Oracle Databases

The amount of data around us is staggering. Statistics citing the amount of data being generated daily describe it in terms of exabytes (1 exabyte is 1 BILLION gigabytes!) In fact, the amount of data generated daily now exceeds the entire storage capacity of the entire world in 1986!

In a world with that much data, we’re lucky to have tools like Power Pivot and Power Query that offer easy connection to data sources that just sort of … clicks! However, if you’ve tried connecting to an Oracle database in Power Pivot you might have had some trouble. Fear not, here’s how you can make your Oracle database connections click just like all the rest!

Why aren’t my Oracle connections ‘clicking’?


For Excel pros, Oracle databases are a bit different, because there are a few key connection enabling components that are not automatically included with most users’ setups:

1.) Connection details – for Oracle databases, the connection details are included either in the connection string itself (EZConnect) or in a flat file called TNSnames.ora. Don’t worry, it isn’t as complicated as it sounds! Smile

2.) Oracle data providers – You can think of these as the communication link between Power Pivot and the Oracle database.

Make a Call to your DBA for some details!

DBA to the Rescue

Before you attempt to connect to your Oracle database, you’ll need to contact your friendly Oracle DBA for the following information:

Read the rest of this entry »

Excel 2016: Ten Heartwarming Improvements

July 21, 2015

Post by Rob Collie

We Love Power Pivot in Excel 2016

Seriously, I Want to Hug the Computer and Every Software Engineer in Redmond

Tales from the Preview

I’m traditionally very slow to look at interim releases of software, but the Office 2016 public preview is out.  Everything listed below is now also available to you to look at as well.  Just go grab the preview and slap it on a “spare” computer.

Rundown of Improvements

The next release of Excel (2016) brings MAJOR improvements to our world.  Unlike 2013, which offered us little noticeable benefit over 2010 Power Pivot, I can’t wait for 2016 to become mainstream.  It’s a monstrous win.

Each of these improvements warrants its own in-depth blog post, but for now, let’s just run through the list of things that catch my eye…

Measure Icons and Search in the Field List!

Measure Icons and Field List Search are Back in Power Pivot / Excel 2016

We Had Both of these in 2010.  2013 Took Them Away.  2016 Puts them Back Smile

A bit cosmetic perhaps, but if you never used 2010 Power Pivot, you have NO IDEA how useful these are.  We welcome them back to our world with open arms.

Even Better:  Right Click and Edit Measures in Field List!

Read the rest of this entry »

RANKX with Ascending Order to Show Lowest Quotes by Vendors

July 16, 2015

By Avi Singh [Twitter]

This post is based on a query that I got in our monthly Q&A session held for our Online Class attendees.

Input = Multiple quotes for different Products from different Vendors
Desired Output = For each Product show the top three quotes, both price and the Vendor name

Go from a list of Price Quotes to showing the lowest Vendor Quotes for each Product

A bit more on the Q&A session before we dive in. All our Online Class attendees are invited to a monthly Q&A session, in order to support them in their Power BI journey. Often what you learn in class, you would only apply sometime later. With the Q&A session, if you run into issues or have any questions, you have the opportunity to bring it up and discuss with your instructor. You can sign up for our upcoming Online Live Class on August 3-4.

Step 1: Structuring the Tables

We would clean things up and import the data into separate data and lookup tables. This may seem superfluous for the sample data set, but a real data set could have a lot more rows in the data table and a lot more columns (attributes) for the lookup table. Hence separating the data table and lookup tables is always a good approach.

Our Vendor Quote data loaded as separate Data and Lookup Tables

Step 2: Brainstorm Approach to Writing Measure

Read the rest of this entry »

Summer Fun at PowerPivotPro Free Webinar: Jul 22

July 13, 2015

By Avi Singh [Twitter]

It has been a busy summer. My first summer working from home. And with my kids summer vacation, it has been fun and challenging. Fun because I can join an impromptu water gun fight during midday (we have been under a heat wave in Northwest Seattle). Challenging because I am still travelling, training, proselytizing :-) and most of all trying to write a book. Getting to focus is hard and getting some quiet time impossible.

But I would put all that aside to make time for something that I have truly enjoyed – connecting with all of you via our webinars. The Q&A and discussion with you is my favorite part; which stretched the last webinar to 2 hours!

So folks, the next one is less than 10 days away on Jul 22nd. Click buttons below to find out more or to register. This one focuses on going from Excel to PowerBI.com.

Free Webinar
Jul 22

PowerPivotPro Free Webinar Series

If you would like to stay notified of our future events, please subscribe to us via email.

Consolidated Worksheets with Power Query

July 9, 2015

by Matt Allington

The more I use Power Query, the more I want to use Power Query – and of course the more I learn.  I have seen quite a few references on the Web to “consolidating multiple Excel Workbooks into a single table”, but I came across a problem working with a client recently where I had to undo someone else’s ‘helpful’ partitioning of data into separate worksheets in the one workbook.  You may have come across the problem yourself.  Someone has been collecting data over time, and they start a new sheet every month.


Well intentioned but actually quite unhelpful.

The procedure I use here to bring this all back together in a single table is the same basic concept as consolidating multiple workbooks, however I have learnt a thing or two since the first time I copied someone else’s pattern on how to do this.  Specifically I have learnt the correct and easiest way to create and work with Power Query functions.

The basic process/pattern is:

  1. Create a query to import a single sheet of your data.  Do all the transformations you need here.
  2. Convert the basic query from step 1 above into a function (the easy way) while keeping some backwards capability.
  3. Create a second query that uses the function from step 2 and consolidates all the objects (Sheets in this example), but the same principles apply for any other object.

There are other approaches you can use to do this (as is often the case with Excel) but this procedure demonstrates how easy it is to write Functions if you understand the basic concepts.

First Create a new blank workbook edit a single sheet

Go to Power Query Menu\From File\From Excel.  You will see in the navigator the list of sheets.  Just select any one of these sheets in the list.   For this process to work, the column structure of the sheets needs to be the same.   Read the rest of this entry »

Power BI Designer vs. Excel: What’s Microsoft Up To?

July 7, 2015

Post by Rob Collie

Power BI Designer:  A Good Thing for the Power Pivot Revolutionary in Your Life

This is Power BI Designer, a New Product from Microsoft, and It’s Relevant to ALL of Us.

Major Seismic Activity out of “Mount Redmond”

Some of you may have seen it already, but just in case you haven’t:  in recent months, Microsoft has been touting a preview of what it calls Power BI Designer.  You can download it here for free, and install it right next to all of your other tools, TODAY.

I’ve held off, a LONG time, in writing about Designer.  Because I wasn’t yet sure what to make of it.  I’ve been wary of it, critical of its existence.  It’s taken many, MANY off-the-record conversations with my former colleagues at Microsoft, and a lot of reflection, but now I am ready to talk about it, and even endorse it – with a few caveats near the very end of this post.

The Many Desktop Applications of Power BI

My Start Menu is Getting Crowded with Data Tools:
Excel 2010, Excel 2013, Power BI Designer, and Power Update

Designer is Clearly…  Familiar to Us

Once you install it, you immediately start noticing some similarities to things we know and love:

Power BI Designer Basically Contains Power Query, It's Just Not Called That

Same Basic List of Data Sources We See in Power Query

And then the ribbon has some old friends for us as well…

Read the rest of this entry »

Power Query Training: From Zero to [M]ax

July 6, 2015

Post by Rob Collie

I’ve known Ken Puls and Miguel Escobar for many years now. They’ve been “in” on this Power BI / Power Pivot revolution basically from the beginning.  In fact, Ken pressuring me was the reason I wrote DAX Formulas for Power Pivot, and Miguel subsequently translated it into Spanish!

When Power Query hit the scene, it really, really “spoke” to both of them.  At one point they were both separately emailing me about writing a book but unsure if they had the time, so I introduced them, and the two of them hit it off in a big way.

They both left their day jobs, are co-authoring the upcoming ‘M is for Data Monkey’ book, and are also now delivering online workshops for new and advanced users of Power Query.

Online Live Course, Aug 12-13

Their next live workshop is in August – eight hours of instruction (four hours each day) plus an additional 3 hour Q&A session on August 20th.

I’ve seen a video of the content – they make sure that you get to an advanced level through a really well thought learning curve that all Power Query users can benefit from.

I encourage everyone to look into this.  Power Query is here to stay and is an amazing complement to our Power Pivot skills, especially when paired with Power Update.  Whether you learn from Ken and Miguel or not, I encourage you to start adding Power Query to your repertoire.

More Info And Registration Here

The next tool that every Power Pivot user needs

Data Hero Path:  Power Query & Power Pivot are THE Engines of Power BI

We’ve published quite a few posts recently about Power Query and how it complements Power Pivot.

In case you want a teaser of their class’s content – take a look at their Patterns section here.

BTW, in tomorrow’s post I’m going to try to bring some clarity to all of Microsoft’s recent moves in the Power BI space, including the new Designer tool.  Spoiler alert:  Power Pivot and Power Query, DAX and M, are front and center in the whole strategy.

A Hello Heard Around the World

July 2, 2015

By Avi Singh [Twitter]

“Hello Everybody…”

With those words, Rob opened the very first blog post on this site back on Oct 26th, 2009. He spoke about his passion for PowerPivot and for the Excel community. He talked about bringing people together.

Today, I am happy to announce two big milestones in the PowerPivotPro journey: 800 Blog Posts and 2000+ YouTube Subscribers! Get a list of our hottest blog posts below.

PowerPivotPro has touched people across the world, through our blog, our youtube channel, our classes and free webinars. And we hope to do more. We hope to set the world of BI on fire and herald the disruptive new force that is Power BI.

We have not gotten here by ourselves though. And we wouldn’t get to our goal alone either. You have supported us, rallied around us, amplified our message and have given us strength. A BIG Thank You!

As a token of our gratitude, we will send a special offer to our subscribers on Tue July 7th. To receive this offer, all you have to do is make sure you are subscribed to our newsletter:

Subscribe to our Newsletter by July 7th to recieve a special thank you gift

What’s this Newsletter?

So far our blog has been our primary means of communication – for our blog articles, but also for our classes/free webinars/events etc.

You could subscribe to our blog via email, and many of you do. However many of you prefer to consume the blog via other methods – just the website or using an aggregator like feedly (my tool of choice). Also our cadence of two blog posts a week (Tuesday & Thursday) is more email than some folks want to receive.

Finally, we heard from many of you, that you would like to be notified via email of upcoming classes, free webinars and other events.

So now you have a choice, click links below to…

Subscribe to our Blog & Newsletter 2 Informative Blog Posts each week + Newsletter delivered to your email
Subscribe to our Newsletter alone 2-3 Emails max each month
regarding our upcoming classes, free webinars, events AND special offers for our favorite peeps in the world

A Story of 800 Blog Posts

800 Blog posts means that you would find pretty much everything under the sun, just use the search box.

Search Box is your friend! (top right corner)

For e.g. if you search for Cycle Plots, you would not only find my post from June 2015, but also Colin Banfield’s guest post from 2012! Read how we tackled it slightly differently. Similarly you can search for disconnected slicers, cube formulas and more.

Race to 1000 Page Views

Here are the fastest burning blog posts from this year (2015) – as measured by how quickly they reached 1000 views. This analysis is modeled after the Compare Product Performance Since Launch post, check out that post to see how you can run a similar analysis. Look through the top blogs list below and see if you had missed any, or go back and revisit an old favorite. A special thanks to our guest bloggers who appear in the list below. And our partners in crime Matt Allington and Dany Hoter who are regular posters and also appear below.

2015 Blog Post

Days to Reach 1K Views

Introducing Power Update!


Datazen = Beautiful Mobile Dashboards to Make Power Pivot Shine


Power BI, YouTube for Workbooks: More Affordable!


No More Apologies: Excel is the World’s Best Data Tool, Period.


Power Update Adds Email Notifications, Macro Support, and 100% Free Version


Harness Power Query to Gain Competitive Analysis Insights from LinkedIn (Gil Raviv)


Power Query for Excel: Combine multiple files of different file types (Miguel Escobar)


The Only “Requirements Doc” You Will Ever Need


Dear Accountants: PowerPivot is your friend! (Mike Griffin)


VLOOKUP better than Relationships? Hell must have frozen over


Our Road Ahead


Secret Pot Roast Recipe: Power Query vs. VBA Macros (Willem van Dijk)


The Diabolical Genius of “SWITCH TRUE”


Create a Custom Calendar in Power Query


Schrodinger’s Pivot, or Why CALCULATE() Should Be Your Favorite Function EVER.


Power Pivot to SSAS Tabular in less than 30 minutes


Some Good Power Pivot / Power BI Computers


Is Your DAX/PowerPivot/Power BI Computer FAST?


“Beta Test” the New Reference Card?


Power Pivot and Basketball Superstars: Many-to-Many and USERELATIONSHIP


Power Query Super Charges the Internet


How to Compare the Current Row to the Previous Row in Power Pivot


Dynamic Age Calculation Using Measures


Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks


Power BI as Google Docs Antidote


2-Day Class in Indianapolis, Aug 25-26

June 30, 2015

Post by Rob Collie

Power Pivot and Power BI Training

Two Days in Indy to Make You a (Happy) Data Rockstar

Hey folks, I will be teaching our first-ever Indianapolis class in August.  (Yes, we are relocating our secret rebel base, aka PowerPivotPro HQ, from Cleveland to Indy in July).

I LOVE teaching these classes.  There’s a certain vibe to them – of optimism, improvement, and friendly unity.  It’s FUN to meet other people similar to yourself, and to see that you are FAR from alone in the world.

But make no mistake – the name of the game is professional development, and dramatic expansion of your data superpowers.  The fact that it’s fun is merely an enhancer, a multiplier.  It helps hold your attention, and inspire/encourage you to greater achievement.

***Only 10 Spots Available, So Reserve Yours Quickly!***

Since this is a new town, we decided to start kinda small, and only booked a 10-student classroom.

But in the past, Indiana students outnumbered Ohio students even when we held the class in Cleveland (!), so we might fill up quickly.  We’ll find out soon enough.

Reserve Your Spot, Enroll Today

A Previous Class – Many Of These Folks Have Gone On to Do AMAZING Things.

Optional Gathering/Dinner on “Middle” Night


Some Folks from last August’s Class Throw the “Excel Gang Sign” at Dinner
(Can You Spot MrExcel Himself, Bill Jelen?)

On the night of the first classroom day, we’ve been running an informal gathering at a local restaurant, and many people reported that it was one of their favorite social outings ever.  Just being surrounded by people with similar backgrounds, mindsets, and problems is a unique experience, especially for the Excel crowd.

Totally optional, but we usually end up with about 75% attendance, and attendees often describe it as one of their favorite all-time experiences.  Because, you know, how often do you get to go out socially with other data geeks?

Included:  Thumb Drive, Lunch Both Days, and 50% Off Online University

All Students Receive a PowerPivotPro thumbdrive containing the materials from the course:


And also a 50% off discount code to the online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we don’t have time for in two days), so we have decided to offer it as a bundle.

Click Here for More Info and Registration

Cycle Plots Show You Data Insights using Power Pivot

June 25, 2015

By Avi Singh [Twitter]

If you want to look for trends based on weekday (Sunday…Saturday) or month-of-the-year seasonality (January…December), Cycle Plots can be a potent visualization tool. Some clever folks thought of this back in 1978, but my education on cycle plots has been from Naomi Robbins’ excellent paper. This question was asked during one of our Q&A sessions for the Online Class (Next Class scheduled for Aug 3-4). In this post I’ll discuss the Cycle Plot and then we would build it step by step using Power Pivot. Here is the end result we will achieve (animated gif):

Cycle Plot showing Weekday values for each Week Number and the Average. A slicer also lets the user select the weekday chart should start at

Looking for Periodic Trends

We’ll use sample data showing eight weeks worth of web traffic and we are looking for trends based on the weekday (Sunday…Saturday).

Try 1: We might plot Visitors by Weekday. While this does show the overall pattern of visitors across weekdays, we have no information of how visitor count is changing over those eight weeks.

Try1: No information on how visitors trend changes over the eight weeks

Try 2: We can plot visitors by date. This shows a trend over time and the cyclical pattern is apparent. However, it is hard to track the performance of a single weekday, say Monday over the eight weeks.

Try 2: Hard to track a single weekday (say Monday) over the eight weeks

Try 3: We plot by weekday but add Weeknum as a series. This has a lot of information coded in the graph, but that is also the problem. Viewer is overwhelmed and it is hard to look for the patterns we want to see.

Try 3: A little too much information

Cycle Plot

A Cycle Plot would show data for each weekday broken by the Weeknum. With the same data now rendered as Cycle Plot, you can see the trend for each weekday and see them in relation with other weekdays. Insights just start hitting you on the head!

Cycle Plot: Click to enlarge, see numbered insights below

1) Friday is the peak traffic day and Mon/Tue are the low traffic days.
2) Thu shows strong growth, especially in the recent weeks
3) Fri shows steady growth
4) Sunday shows a gradual decline (except for last week) and may need closer monitoring

Building a Cycle Plot Using Power Pivot

Read the rest of this entry »

Counting Overlapping/Shared Twitter, Facebook, Instagram, etc. Followers

June 23, 2015

Post by Rob Collie

From Last Week’s Client Work

Last week a client asked us to solve a somewhat unusual problem:  given any two lists of Twitter followers, tell us how many followers “overlap” between the two lists.

Two Lists of Twitter Followers:  How Do We Find the Overlap Using Power Pivot / Power BI / DAX?

How Many of Han Solo’s Followers Also Follow Leia Organa, and Vice Versa?
(Randomly-generated Twitter handles are funny.  I particularly like “@Gommo” and “@Xxfok”)

Loading the Data:  Using Power Query

Let’s use Power Query to perform the import this time, both because we’re using PQ a lot more around here now that we have Power Update, and because we’re gonna need PQ for the more complex steps later.

Note that all of the steps below are performed using Excel 2013.  (I find Power Query to be a bit too clumsy in Excel 2010.)

Power Query, aka Power BI Data Import

Importing from a Table Using Power Query:  Step 1
(Unchecked “has headers” because of the “Han Solo’s Followers” Row)

Read the rest of this entry »