Thanks for attending the *beeping* webinar: Q&A From Webinar

March 18, 2015

By Avi Singh

Folks, thanks for attending our webinar. We did have a full house with 200 attendees. Here is how things started…

“Welcome to our webinar: Excel to power pivot: *BEEP* the gap. A lot of users *BEEP* Excel but don’t *BEEP* Power Pivot. We’re here to *BEEP* you up about the possibilities that Power *BEEP* offers…”

Okay, that wasn’t much fun. For some truly funny unnecessary censorship watch “This Week in Unnecessary Censorship” (Caution:  If you’re at workplace, you might want to use headphones).

I did sort out the matter eventually with the help of Citrix Support. Apparently there were issues with the last automatic update of GoToMeeting software. Uninstall/Reinstall restored my ability to turn off those annoying beeps.

Q&A from the Webinar

We published the Q&A from the last webinar and I’ll try to keep that up. So here’s the Q&A from the session on Mar 17th.

TIP: If you have other questions, use the search box. We have 750+ blog posts and most likely you will find something helpful

List of Questions (click to jump to answer):

Q: If the tables are not related, can you create a Pivot Table for each unrelated table separately?
Q: Why wouldn’t you use Microsoft Access instead?
Q: How can we automate the refresh of a Power Pivot report without using server components?
Q: How many rows/columns can Power Pivot handle? My data set is wide and flat
Q: How easy is it to share Power Pivot data with non-Power Pivot users?
Q: What are good practices for preparing the tables? The tables can have blanks, right?
Q: In my calculations, Can I exclude some of the rows where the information is not applicable and calculate for the remaining rows?
Q: Can we combine/leverage VBA with Power Pivot?
Q: How do you filter out certain fields to not bring in to PP?
Q: Why is PP not available on Excel 2013 Professional?
Q: Many questions around Date table and creating Date tables
Q: Other questions discussed
Q: Will webinar be recorded?   

Q: If the tables are not related, can you crate a Pivot Table for each unrelated table separately?
D. van Eyl

It is very common to have multiple data tables distinct from each other in the same model. Example Sales data, Budget data, Inventory data, Website traffic data etc. It is valuable to keep these data sets in one model so you can analyze them together.

Your data model should not look like mushrooms where each data set has its own lookup tables

Multiple Data Tables should NOT look like mushrooms

It should look more like a web with a set of data tables and a set of lookup tables. With the data tables connected to the relevant lookup tables.

Read the rest of this entry »

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

March 17, 2015

Post by Rob Collie

Schrodinger's Cat Has Relevance to Power Pivot and Power BI

In the Classic Physics Thought Experiment, Schrodinger Hypothesized a Cat That Was Simultaneously Dead AND Alive.
(But here we will use the more humane “Simultaneously Green AND Grey.”

Back to Basics! celebrated its 5th birthday back in November.  Over 5+ years, we’ve progressively covered techniques with an increasing level of sophistication.  That’s pretty natural – we ourselves have become more skilled over time, AND there’s a tendency to not want to write the same post twice.

But today I want to drive home a basic point, one that will help “recruit” Excel pros into the Power BI camp, AND that will help “crystallize” a few things even for the longtime DAX practitioners.

Schrodinger’s Cat – A Classic Battle of the Nerds

imageIn 1935, physicist Erwin Schrodinger wanted to show Albert Einstein how wrong he was.  Einstein had recently published a paper that made an astounding claim about the nature of subatomic particles.  If those claims were true, said Schrodinger, even “big” everyday stuff, like cats, could also behave in that same outlandish way.  Which made Einstein look kinda silly, in Erwin’s mind.

He proposed the idea of a cat that was both simultaneously alive AND dead, and basically said “See, Albert?  Alive AND dead is clearly impossible, so your theory is junk.”  See here for details.

But modern quantum physicists actually think the cat experiment does NOT disprove Einstein’s claim.  In fact, they think Schrodinger’s Cat demonstrates that the universe is fundamentally a MUCH stranger place than we typically think.

In short, the concept of “impossible” is subject to re-evaluation.

This PivotTable is Simultaneously Filtered AND Unfiltered

Read the rest of this entry »

Excel Users Everywhere: Free PowerPivot Webinar Tomorrow Tue Mar 17th

March 16, 2015

By Avi Singh

Excel users are everywhere! Even at Toastmasters – a nonprofit educational organization focused on communication and public speaking. The other day, I had dropped in at our local Toastmasters as a guest, after a long break. It felt good to be back. The meeting had the familiar structure – speakers, evaluators, table topics. When it was over, as we were heading out, I struck up a conversation with another attendee. As members come from varied backgrounds, I didn’t know how technical I could get when describing my vocation. Thus, I spoke in general terms about my work at Microsoft, then leaving Microsoft to pursue a path of my own. And then I asked…

Me: So what do you do?
FTM (Fellow Toastmaster): Well, my job title says Quality Assurance, but that doesn’t quite describe what I do.

Me: Happens all the time, so what do you really do?
FTM: Well, I do a lot of data analysis.

Me (excited now): What tools do you use?
FTM: My company has a lot of data stored in these large databases, but they didn’t want to make a big investment in BI. So I just pull the data and analyze it in Excel.

Me: Did you know that Microsoft has released a new set of tools, called “Power BI”, which supercharges Excel?
FTM: Can it handle multiple data sources?
Me (trying to keep off the smug smile): Yes, indeed.

FTM: Can it handle large data sets?
Me (giving up now, a wide grin plastered on my face): It can do that and a lot more. You should attend one of our upcoming Webinars…

Free Webinar: Excel to Power Pivot – Cross the Gap

I have seen how transformative this path can be. From my own journey (read or watch) and from many others I have witnessed since joining PowerPivotPro. I believe the three steps below are needed to really set a person or organization on this path:-

  1. Awareness of Power Pivot and it’s superpowers
  2. Learning basic Power Pivot
  3. Using Power Pivot with your own data set

Typically after the third step, you have experienced the power and are motivated to pursue this path! But there are many who never make it to that step. My mission is to change that for as many people and organizations that I can.

Join me for our upcoming webinar tomorrow, where I lead you to the promised land :-)

Mar 17

We’re planning the next event in April covering a new topic. Subscribe to our blog to receive updates on future events.

Creating a Histogram with a User Defined Number of Buckets

March 12, 2015

By Dany Hoter

Intro by Avi: I have often been asked, about ways to provide an “input” to the Power Pivot model from Excel. Disconnected Slicers are a popular way to do this. But with Excel and Power Pivot, there is always more than one way to accomplish a task. Dany shows us how, while making histograms easier to use. He uses a filter dropdown, which even works with Excel Online – inside a browser! Here is the end result, read on to learn how and download file.

Take it away Dany…


Creating a histogram in Excel based on Power Pivot is not as easy as it should be.

The method I use is no different from what others have already blogged and wrote about. There is even a solution that calculates the number of bins in a histogram with a formula that is based on the total number of cases.

My take on the problem was to let the user choose in run time what is the interval between each bin as a percentage and to show the number of bins accordingly.

Business Case

The model contains data about a service that is in its infancy and so the users experience a relatively high number of errors.

The managers responsible for the service posed the following request:

“We want to see a histogram of the sessions showing how many users have experienced no errors in all the sessions they initiated, how many experienced errors in 10% of the sessions, 20% of the sessions … all the way to these poor users who saw nothing but errors in 100% of the sessions (Told you it is in early stage…)


Read the rest of this entry »

Listen to Me Interview MrExcel (Bill Jelen), and See Us Both at the PASS Biz Analytics Conference

March 10, 2015

Post by Rob Collie


I Interviewed Bill Via Phone – Me in the Arctic of Ohio,
Bill in his Winter HQ Down in Florida

It’s not often that you’re asked to interview one of your friends, but that happened to me last week.  The folks at PASS thought it would be interesting if I interviewed Bill Jelen, and then we published that interview on the PASS Business Analytics Conference site.

An excuse for me to call up Bill and jaw for awhile?  “Sign me up!”  And off we went.  I was personally “hooked” by the conversation for a full 40 minutes.  Most of Bill’s answers, I had never heard before, and they stimulated some serious “wow” thinking for me.

We found out, afterwards, that the PASS folks were expecting a 10-minute interview.  Har har.  Bill and I later chuckled, in email, at the futility of such expectations.  “Let’s get two of the biggest chatterboxes we can find, put them on the phone together, and keep them to 10 minutes.”  But the results were good, and PASS decided to publish the whole thing.

They were also gracious enough to share the audio file with us, so I’m posting it here today:

(Fancy Inline Audio Player Should Work on Most Browsers, Or Let You Download the MP3)

Highlight:  Bill STARTED in Analytics!

I can’t believe I had never heard this before, but Bill first started using spreadsheets as a reporting tool.  (Not as an accounting or financial tool).

Even more delicious:  he was using spreadsheets as a replacement for an extremely expensive reporting solution that didn’t work!

That was in the 1980’s.  How little has changed between now and then!  Before the advent of what I call “Modern Excel” (aka Power BI), the world was in EXACTLY the same state 20+ years later.  (And in places that have yet to grasp the potential of Power Pivot and its kin, things STILL have not changed.  But at least now we have a way out of the decades-old mess).

Anyway, definitely listen to the interview.  Lots of interesting nuggets in there, such as the observation that more than half of analytics pros are women.

See us both at PASS BA Conference Next Month!

It’s not too late to join us both, plus other Modern Excel experts, at the PASS Analytics conference next month in Santa Clara.

Click to Visit the Modern Excel Speakers Page

Next Month’s Speakers Include Quite a Few Proponents of Modern Excel / Power BI

$150 Off – Discount Code

Register Here and Use Code BASPROB

Excel to Power Pivot Webinar Countdown – in 10 days

March 6, 2015

By Avi Singh

Happy Friday! Our next webinar is coming up in about 10 days from now, on Tue Mar 17th. Make sure you save it to your calendar. See webinar page for more information. Participants would also have an opportunity to sign-up for our online class at a discounted rate.

For the webinar, we have doubled the capacity from 100, up to 200 attendees – so if you were unable to get in on a previous webinar, take heart! There would be an opportunity to ask questions as well. See Q&A from the past webinar.

Here is what we would cover:

Walkthrough key steps in using Power Pivot and explain how Power Pivot supercharges regular Excel
Do it Yourself! We would provide you dataset and instructions to try those steps yourself at your own pace and build your first data model. Best way to learn is by doing.
Q&A: There would be opportunity to ask questions and seek guidance regarding using or deploying Power Pivot or Power BI
Resources to continue your journey

See links below for more details:-

Mar 17

We’re planning the next event in April covering a new topic. Subscribe to our blog to receive updates on future events.

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

March 5, 2015

Post By Dany Hoter

Intro from Rob:  In this post, Dany demonstrates how we can use Power Query to add a numerical index column to our table, and then use that to address the previous row in a calc column.  Lots of good stuff here.

More generally, this is achievable using the misleadingly-named EARLIER function.  EARLIER does not mean “previous row,” but coincidentally, you can use it to do just that, and many other “cross-row reference” things.  Check out this post on EARLIER for the basics (or read the calc column chapter in Power Pivot Alchemy), and read some of the comments at the end of this post for further examples.

Take it away, Dany…

Business Case

I was approached with a business question from the car fleet manager in our company.

There are many different types of cars and the fleet manager is trying to optimize the fuel cost and reduce emissions.

The data about the fuel consumption comes from a smart device installed in the car.

Every time a company car enters a gas station the device reads the car’s odometer and reports the car’s ID + the odometer + gas quantity purchased to a central database.

From this data we needed to create a report that shows average fuel consumption by make and model of the cars in the fleet. We got the data as two csv files , one with the entire history of gas transactions and one with the car fleet details.

Sounds Simple

The data model and the report are really basic and straight forward as you can see in the attached workbook which is obviously fake data created for this article.

So where is the challenge? The only problem is that each row representing a fuel transaction includes the odometer for this transaction and the question is how to calculate the distance traveled since the last visit to the pump.

The Excel way

In excel this is a very simple problem.

- Make sure the rows are ordered by car plate # and date

- Subtract the odometer value for the previous row from that of the current row checking that both rows are from the same car. Ignore the first transaction for each car.


The example uses a table and notice that the expression is a mix between structured reference (circled black) and regular reference (circled red) when the cell referenced is on a different row in the table.

One problem with this solution is that it is difficult to make it refreshable without using VBA.

Next month when there will be more data the table need to be sorted again and this is possible only from the UI or from VBA.

The Power Pivot way

Actually there is no PP way without some help. In DAX you can’t reference the previous row in any way because there is no order to the rows. There are probably solutions to this specific example using purely DAX but I’m sure that they are pretty complex.

Some Help from Power Query

I used PQ to create a new column which helps PP understand what the previous row is.

If you open the query that reads the fuel csv file, you’ll see that I sort the data and immediately add an index column to the table which persists the current order of the rows which is ascending by car and date.

Back to Power Pivot

I can use this new column in PP to calculate the difference between any row and the row next to it.

I add a calculated column with this expression:






The index column allows me to reference other rows relative to the current row using the LOOKUPVALUE function.


Using Power Query I was able to sort the data and persist the order after sort using an index column.

These two operations are part of the import operation and so will be performed each time new data arrives.

The index column enables creating a calculated column that compares data in the current row with data in the “previous” row, when previous is defined by the index column.

Download Files

PowerPivotPro Expands to Europe/Africa/Asia, Welcomes Dany Hoter!

March 4, 2015

Post by Rob Collie

My Business Intelligence “Yoda” Figure Joins the Team!


Master and Student, on a Fact Finding Mission in Denmark, 12 Years Ago!

In late 2002 back at Microsoft, I suddenly found myself in charge of most of the new BI-focused features of Excel (in the product wave that ultimately became Excel 20007).

The interesting thing is, I had ZERO idea what BI was back then.  I mean, I wasn’t even sure how to spell BI, as the old joke goes, heh heh.

I wouldn’t have survived that era without the patient tutelage of Dany Hoter.  Dany was our resident guru on the team back then, and still plays that role for Microsoft engineering teams to this day (he moved back home and now works for Microsoft Israel, but he and I met when we both worked in Redmond WA).

If He’s Still at Microsoft, How is He Joining PowerPivotPro?

imageBecause Microsoft is an amazing place, and Dany is an amazing person.  Dany is so valuable as a mentor – both for new hires and for veterans joining the Excel and BI “game” for the first time – that there’s a LOT of incentive to keep him on the engineering teams.  He makes everyone better.

So when he asked about doing something different – mentoring/teaching people outside of Microsoft, his management team was very supportive.  He keeps his job at Microsoft and can utilize his veteran-level “time off” to share his talents with organizations who are starting to adopt the Power BI suite of tools.

Win win for Microsoft, when you think about it.  Kudos to them for supporting it.

Extends Our Geographical and Timezone Reach


With Matt in Australia and Dany in Israel, the Sun Never Sets on PowerPivotPro!
(Click Here for More on Matt)

It’s been VERY hard for us to get over to Europe (for instance) to consult or teach classes – the travel time and expenses tend to be too high.  And even our remote (screenshare-based) consulting and assistance is often a bit awkward to schedule with the Europe/Africa/Mideast timezones.

Dany fills that need in a big way – I mean, he taught me, and continues to teach Microsoft employees to this day.  How could we possibly get any luckier?

You can contact Dany directly at his new email address:

More to Come Once I (Rob) Get Caught Up

I’m WAY overdue on sharing this announcement, and this is intended to be short (so that I actually get the post written and published rather than delaying further).

So there’s more to be shared here for sure:

  1. I interviewed Dany recently on Skype, and need to transcribe that for posting here.
  2. I’ve got more to say about why Dany is PERFECT for this role, how he fits in with our personality and philosophy, etc.
  3. A rundown of Dany’s many skills.  He’s fluent in basically everything, from SQL to DAX to M, and that’s just the tech stuff.  His wisdom, experience, and people skills are just as important.
  4. Dany has guest-blogged for PowerPivotPro before – you can find some of his articles here.

In the meantime, here’s his email address again:

Contact Dany Via Email

Interview with Chris Finlan of Microsoft

March 3, 2015

Post by Rob Collie

A Kindred Spirit Revealed!

Rob Collie of PowerPivotPro and Chris Finlan of Microsoft

Me and Chris Last Week at the Microsoft Offices in D.C.
(Their Electronic Signs Are Awkwardly Truthful.)

For about a year I have been working closely with a Microsoft employee named Chris Finlan, the BI TSP for Microsoft’s Mid Atlantic Sales District.  Loosely translated, that means that when it comes to Business Intelligence, he’s the “go to” resource for all of the Enterprise sales teams in Pennsylvania, Maryland, and Virginia.

On the face of it, that may sound like “well of COURSE you two work closely together – he sells MS BI, and you teach/help people to use it.”  But there are a LOT of technologies in the MS BI stack, and we (at PowerPivotPro) are specialists in the New Wave – not just the newer technologies like Power Pivot and Power BI, but also in the way that the tools are positioned, evangelized, and sold.

Even though we’re 100% aligned with Microsoft’s direction, it takes time for habits to change – both for large companies AND the software sales teams who work with them.  Neither is particularly incented to take risks – the consequences of a failed experiment are high.  So, it’s natural that not everyone has rushed to embrace the New Wave as the total paradigm shift that it is.

The traditional Microsoft BI sales strategy can be loosely characterized as “top down” (pitch/sell the software to the people who write big checks) whereas I think Power Pivot is often better pitched bottom-up (prove its value to a single department or group of users, and the checks come later).  Neither is an “incorrect” approach of course, and they are not mutually exclusive.  In particular, I’ve long believed that “bottom-up” messaging can be an effective part of a “top-down” engagement.

But changes to the script require a LOT of confidence.  The “game” just isn’t set up to reward experimentation.  So ultimately, it often requires someone who’s wired a bit differently.

Rob's Face When he Got Chris's EmailIn my world at least, that person first “surfaced” in an email I received about a year ago.  Chris just dropped me a note and said “hey I’ve been adapting some of the messaging on your website for use with customers, and it’s been working.  Can we have a phone call at some point?”

And at that moment I scrambled for the phone.  The rest, as they say, is history.  Chris and I talk probably three times a week, cooperate on multiple customer engagements, ran classes in Philly (last year) and DC (last week), hatched Insight Center (more on this below), and generally just pester the hell out of each other all week long.

On to the Interview!

Read the rest of this entry »

Power Pivot Blitz: Webinar, Seattle Class, Online Class

February 27, 2015

By Avi Singh [Twitter]

My New Year’s resolution to help 1000 people start using Power Pivot, will not achieve itself. So we are taking the bull by the horns and announcing the training events below. Click on the links to enroll or obtain additional information.

Need help choosing? Webinar is a free event so all are welcome to join. Read this article to help you…Choose Between: Live In-Person, Live Online and other training options. 

Mar 17

Live In-Person Class
Apr 1-2

Live Online Class
Apr 6-7

PowerPivot for Excel Live Class (Seattle)

PowerPivot for Excel Online Class

More Info on Live In-Person Class

Live In-Person class includes: Thumb Drive, 50% Off Online University and other goodies!

All Students Receive a PowerPivotPro thumb drive containing the materials from the course.

Power Pivot Seattle Class: Free Thumb Drive

You also receive 50% off our online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we just can’t get to in two days), so we now offer it as a bundle.

More goodies! You receive a physical copy of one of Rob Collie’s Books and digital copy of both books. Wait wait, there’s more Winking smile: you also get a laminated DAX Reference Card – which many have sworn is a life saver when solving a complex CALCULATE() puzzle.

Live In-Person Seattle Class Details & Registration

Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks

February 26, 2015

By Avi Singh [Twitter]

This article is Part II of a series.
<<Read Part I: Power Pivot to SSAS Tabular in less than 30 minutes

In Part I we explored how powerful one of the Server Options can be for Excel Power Pivot. Be it SharePoint, SSAS Tabular or Power BI – moving up to the server option lets you separate the Model from the Reports and unleash the potential of Power Pivot.

For Part II, we will continue exploring the SSAS Tabular option. Specifically once you choose to host your Model on SSAS Tabular, you are faced with two options

a) Move to Visual Studio as a Development Environment (this article has some guidelines)

b) Continue using Excel as Development Environment (read on…)

Or a combination of a) and b), with some users moving to a) Visual Studio while others stick with b) Excel, to build and update Power Pivot models.

For option b) Excel, ideally you would want some way to automatically publish your Excel workbook to SSAS Tabular. As easy as it is to publish them manually (See Part I), it would still be preferable to automate this task. Automatic upload of Excel Power Pivot Workbooks to SSAS Tabular would enable:-

  • > Uploading dozens or more workbooks automatically. One or two ad-hoc upload is fine, but to upload more and do so on a schedule begs for automation.
  • > Separation of Roles between Model Authors & SSAS Tabular Administrators. You can potentially have multiple authors using Excel and saving their workbooks on a shared location. From there the SSAS Tabular Administrator is tasked to pick up the files and upload to SSAS using one of the options below

Publish Power Pivot to Tabular using Power Update

This is by far the easier option. You can try it out using the Power Update Trial. This is fairly point and shoot: you select Source, Destination Type (SSAS Tabular) and provide Destination information; besides the other typical settings for Schedule etc.

Click to watch YouTube Video showing Publish to SSAS using Power Update/XMLA

Publish Power Pivot to Tabular using XMLA Script

Read the rest of this entry »

Create a Custom Calendar in Power Query

February 19, 2015

by Matt Allington

In this post, I am going to show you how simple it is to create a custom calendar using Power Query.  If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.

Different Types of Calendars

There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on (25 prior to this one).  Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year.  There is one problem with this approach however –  the date over runs when you are looking at last years’ data in a pivot.

over run

As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet.  It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually).  You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.

Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.

First Create a New Blank Power Query Workbook.

The Blank Query option is right at the bottom of the “From Other Sources” menu.

blank query

If you haven’t done so already, turn on your Formula bar from the view menu.  You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.

turn on formula
Read the rest of this entry »