Best way to learn Power Pivot: get over analysis paralysis

March 30, 2015

I have loved reading ever since I was a young kid. In my adult life though, reading fell on the wayside, as life got busy. Now, I have worked as a Business Analyst for a long time, and the role suited me naturally. However at times I fall victim to analysis paralysis. My inability to get my reading fix, was one casualty. Let me explain.

I felt that since I was soooo busy, I had to make the best use of my time. I wanted to pick the BEST business books, the BEST self-help books, the BEST fiction books that would enrich me. Unfortunately that meant, I would occasionally go on the hunt for the “best”, give up after a while, and repeat the cycle; without ever actually reading many books.

It was years before I realized my folly. The way out was the “Librarian’s Picks” section at our local library. I would trust their judgment, and started picking several books from that section. I did more reading in that one year than I had done in the previous ten years.

If you are anything like me, you have thought about attending Power Pivot training, but have vacillated for this long while. Let me say, a Live Instructor-Led class is the most intensive way to jumpstart your Power Pivot journey. There is still time to enroll in our class on Apr 1-2 in Seattle.

Live In-Person Class
Apr 1-2
(Seattle)

PowerPivot for Excel Live Class (Seattle)

 

Power On!
Avi Singh


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

March 27, 2015

By Avi Singh [Twitter]

Friends at a company play pick-up basketball during their lunch hour. Since there are no established teams, players can be randomly matched up. But these folks happen to be engineers/data-nerds, so they keep detailed track of games, teams, players and win/loss. The diagram view of the data is shown further below.

Question: How can we determine which player pairing is the most successful?
Since players are randomly teamed up, are there combinations which when teamed up have an unusually high winning percentage?


Word Cloud of Player Nicknames: Size of text indicates number of games played

Application: This would naturally extend to other sports, but I believe may also apply in many non-sports scenarios, where items are paired up somewhat randomly (or by design) and we want to know how effective those pairings are.

Thanks to Kirill Perian (basketball nickname K-Real), an attendee of one of our past webinars, who sent us the dataset and posed this question. Dataset has been simplified to showcase this scenario and anonymized to protect the identity of the losers :-)

File can be downloaded here.


Model Diagram: Showing Game, Team and Team Players

First we will address the easier scenario of creating metrics for individual players, using the many-to-many pattern. Next we will take on writing measures to compare performances of pairs of players.

Read the rest of this entry »


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

March 23, 2015

Post by Rob Collie

Power Update Now Emails PDFs or Full Power Pivot / Power BI Workbooks to Email Recipients

Autorefreshes Your Power Pivot Workbook as Frequently as Desired, Auto-Publishes to Any Location, and Now Sends Email Notifications of Success/Failures – With Attachments!

Our Gift to the Community:  New, Improved, and Free

The team has added several new features in the latest version of Power Update.  One of them (email notifications that optionally attach the updated workbook or PDF-ified version of the workook), is pictured above.

But the biggest new “feature” is that there’s now a 100% free version.  Go ahead and download it from the link below, and start using it today. 

It will work forever – no trial expiration – and will never require payment.

GET THE FREE VERSION HERE
 
(Alternate Location Here in case DropBox is blocked)

It will be installed and working in less than five minutes.  Have fun, and if you have any troubles, report those on the Power Update Forum.

“Why Free?  What’s the Catch?”

Simply put, we want everyone to have it.  Everyone.  It’s a game changer.  It will lead to more Power Pivot / Power BI adoption and overall goodness, which is very much something we want.

The only limitation in the free version is that it will only schedule one workbook. Every last feature is available – email, PDF attachments, publish to SharePoint and even SSAS Tabular.

So if you’ve only got one important workbook, you can use the free version forever.  A lot of people will run that way, and we’re ok with that.  If you someday end up with more than one workbook that needs refresh, you can opt to purchase the full version, which can schedule as many workbooks as you want.

“Wait, Can’t I Cheat That With Multiple Computers?”

Read the rest of this entry »


Course Prep: Excel 2010 and Excel 2013 PowerPivot Differences

March 20, 2015

By Avi Singh

Matt Allington, our friend from down under, had suggested the idea that we make some basic information available as course prep to students who enroll in our Live Instructor-Led classes (Upcoming: Seattle Apr 1-2, Online Apr 6-7, see Choosing your training option). Offering a course prep would enable us to get to the real fun stuff even quicker. I have taken that to heart and publishing some information in this post below and a Course Prep playlist on our YouTube channel. This is meant as a prep for students enrolled in our class, but may be helpful to others as well.

Course Prep Video Playlist (more videos to be added soon)

Key Differences between Excel 2013 and Excel 2010 User Interface

Launch Power Pivot
Create New Measure (Calculated Field)
Edit Measure (Calculated Field)
Slicers
Insert a Pivot Table connected to Power Pivot Model
Add Excel Table to Power Pivot
Power Pivot Home Tab
Power Pivot Existing Connections

Launch Power Pivot

image
PowerPivot > Manage
 
image
PowerPivot > PowerPivot Window

Read the rest of this entry »


Create a 445 Calender using Power Query

March 19, 2015

by Matt Allington

In my last blog on PowerPivotPro.com I showed how easy it is to create a standard calendar for Power Pivot using Power Query.  Most of my customers however don’t use a standard calendar but instead use a 445 calendar (which is very common in the Retail Industry). A lot of people shared with me their 445 calendars after my last post, and that reminded me that although the concept of a 445 calendar is very common, everyone seems to have different rules on how the calendar works.

Differences in 445 Calendars include

  1. What month you start the financial year
  2. What day of the week is the start of the week.
  3. 365 divided by 7 = 52 + 1 day remainder. Different companies handle the extra day in different ways.

So there are almost as many permutations as companies and I don’t want to go down the path of trying to write 1 calendar that will work for everyone.  Instead this post covers the techniques I used to solve the 445 calendar problem for one of my customers.  If you are so inclined, you can copy these techniques plus some of your own to meet your own 445 needs.  To give you an idea, it took me about an hour to think through the problems, research the functions and build a working calendar.  If you copy my techniques, you should get a head start on that for your own 445.

The rules of this particular calendar are

  1. The first day of the financial year is the Monday on or before 1 May.
  2. The number of weeks each month are in the pattern 454 (4 for May, 5 for June, 4 for July) and then repeats.
  3. The calendar weeks, months and years mirror the financial data.  So Mon 31 Dec 2013 is the first week of financial week 36.  Even though the date is actually in the year 2013, it is treated as part of the first calendar week of 2014.

Here is how I did it.  But first a word of warning.  The steps are easiest to follow if you first download the sample workbook (link at the bottom) and step through the Applied Steps as you read my explanations in this post.  If you are not a Power Query expert and you just read the post, it will quite hard to understand.  So do yourself a favour and download the workbook, and step through it as you read the post

Read the rest of this entry »


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
image

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!

PowerPivotPro.com 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 :-)

Webinar
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.
image

Take it away Dany…

Introduction

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…)

Solution

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

image

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:-

Webinar
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.

clip_image001

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:

=IF(

‘Fuel'[Plate]=LOOKUPVALUE(‘Fuel'[Plate],’Fuel'[Index],’Fuel'[Index]-1),

‘Fuel'[Odometer]-LOOKUPVALUE(‘Fuel'[Odometer],’Fuel'[Index],’Fuel'[Index]-1)

,0

)

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

Summary

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