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!

2

Datazen = Beautiful Mobile Dashboards to Make Power Pivot Shine

2

Power BI, YouTube for Workbooks: More Affordable!

3

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

3

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

3

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

7

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

7

The Only “Requirements Doc” You Will Ever Need

8

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

10

VLOOKUP better than Relationships? Hell must have frozen over

10

Our Road Ahead

10

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

11

The Diabolical Genius of “SWITCH TRUE”

16

Create a Custom Calendar in Power Query

22

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

22

Power Pivot to SSAS Tabular in less than 30 minutes

25

Some Good Power Pivot / Power BI Computers

28

Is Your DAX/PowerPivot/Power BI Computer FAST?

35

“Beta Test” the New Reference Card?

35

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

38

Power Query Super Charges the Internet

41

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

44

Dynamic Age Calculation Using Measures

57

Power Pivot to SSAS Tabular: Automatically Publish Excel Workbooks

90

Power BI as Google Docs Antidote

126


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

image

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:

thumbdrive

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 »


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

June 16, 2015

Post by Rob Collie

clip_image002

NASA JPL is Using Modern Excel to Perform Financial Analysis On Their
Space Projects, Because Modern Excel is the Best.
(That’s me with Opportunity’s “sister” during a training engagement this Spring)

Too Hot for the Official Excel Blog!

image

This Post Was Originally Written for the Official MS Excel Blog But Never Went Live
(Their PR Censors Deleted 70% of it and wanted big changes to the rest, so I’m posting here instead)

A couple months ago I was asked if I’d like to do a post for the official Excel.  Heck YES, I said.  (Duh).

And then I realized, I couldn’t let such an opportunity go to waste.  I had to Go Big with this one.  Roll the dice.  Use it as a chance to change the entire conversation around Excel – in ways that Microsoft itself SHOULD be doing, but hasn’t.

So many products now use “we’re better than Excel” as their entire marketing campaign.  I’m swarmed by these ads on Facebook.  And none of these other products are better.  In fact, none of them are even close to being AS good as Excel, much less better.

Read the rest of this entry »


Time to revisit the Power Pivot Import Wizard

June 11, 2015

by Matt Allington

I have learnt a lot over the last year working full time as a Power Pivot professional.  And some of the things that I dismissed as “not useful” very early on have turned out to be very useful indeed.  The second option in the Table Import Wizard is a case in point.

option 2

Very early on I dismissed this as being “not useful” for Excel users of Power Pivot (although I could clearly see the benefit for SQL professionals that could already write SQL code).  But I have to say I was wrong about this and I think there is a lot of value in this second option for Excel users.

I was re-reading the excellent book by The Italians over the weekend.  I find that reading quality books for a second and third time is a worthwhile and rewarding pursuit.  Often things that didn’t mean a lot the first time you read will have a new meaning and relevance the second or third time around.  Anyway when I got to the part about importing data from a database, there in front of me was reference to the “Design Button” which appears on the “less preferred option 2″ import screen.

designbutton

I have been using this less preferred option 2 screen for some time now (read about other tricks I use here) but I have never noticed this button before.

So how do I use this button?

This button is very useful when you want to import data from a database but you don’t have access to an IT buddy to help you out by writing the SQL code you need. Now it is not the greatest user interface tool that you will ever encounter, but it is very do-able for the average Excel user. 

Let’s consider the following scenario using Adventure Works reporting DB for SQL Server.  In this example, I don’t want to bring in the full low level transactional data from the source database but instead I want to bring in a summary table for a defined period of time.

eg  Give me a table that summarises sales by day and by territory, and also how many invoices there were for the year (say 2003).  So I really want a table something like this to put into Power Pivot.

summary

Here are the wizard driven steps to complete this task.

Go through the normal “Import from Database” steps as you normally would for Power Pivot.  Select your server name and database name in the wizard.

image

Select the previously unloved option 2

option 2

Give your query a name (this will be your table name inside Power Pivot) and then click the Design button.

image

Expand the table navigation pane on the left and select the columns of data needed.  In this case we want order date, Territory Key, Order Number and Extended Amount.

image

In the selected fields section (top right), click the “Group and Aggregate” button and then select the “group by” actions needed for each field.  We want to group by order date and territory key, then do a distinct count on order number and sum up the extended amount.  These are not difficult concepts for Excel users to grasp even if you don’t know database design or the SQL language.  “Group By” simply means “give me a sub total at this level”.

image

Finally you apply a couple of filters to limit the results to the calendar year 2003.

image

After you click OK, the Design Wizard returns the appropriately written SQL code that will be used to fetch the data you need from the database.

image

And this SQL code is not hard to read or understand.  Once again, well within reach for the average Excel user that is learning Power Pivot.  In fact you can even change the SQL code here (to change the returned column names as an example).

Change this part

SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Count Distinct_SalesOrderNumber]
,SUM(fctSales.ExtendedAmount) AS Sum_ExtendedAmount

to this

SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Total Orders]
,SUM(fctSales.ExtendedAmount) AS [Total Sales]

 

Then click Finish and finalise the import of your summary table.

From here you can go ahead and bring in more tables of data, or start working on your data model as required.

Personally I think any Excel user that is regularly importing data from a database should invest a small amount of time learning how to read and write the SQL language.  12 months ago I classed my SQL skill as “knows what it looks like, can read it, but not good at writing it”. 12 months later having used SQL quite a bit for Power Pivot data imports, I class myself as “competent”.  So if I can do it, you can do it too. And UI tools like the one above can help you get started, and you will be writing your own SQL code without help before you know it.  Then there will be no stopping you.

Matt Allington is full time Self Service BI professional based in Sydney Australia


RANKX-apalooza: Within and Across Groups, With Filters, Etc.

June 9, 2015

Post by Rob Collie

The Ranking of a Single Thing Can Vary Depending on What You Rank it Against.  RANKX() Can Handle All of That.

One from the Reference Stacks

I sometimes find RANKX() a bit perplexing.  So awhile back I made myself a workbook of examples, which I will share with you today:

The Ranking of a Single Thing Can Vary Depending on What You Rank it Against.  RANKX() Can Handle All of That.

List of Stores, Grouped by Region, and then Ranked by Units Sold –
Both Overall (vs. All Stores) and Within Region (Just vs Stores Within Same Region)

Background – The Tables

Read the rest of this entry »


Power BI Jun 2nd Webinar Recording Available

June 5, 2015

Inf

By Avi Singh [Twitter]

Thanks to all, who attended our Power BI Webinar on Jun 2nd. We had 400+ (well, 438 to be precise) attend, what ended up being a 2 hour webinar with all the questions being asked.

Be Notified of Future Webinars…

Click here to subscribe to our Newsletter, to be notified of future Webinars/Events.

Webinar Infographic

Here are some fun facts…

Webinar Recording: Build BI Solutions Using Power BI

Read the rest of this entry »


Data Nirvana: Power Pivot, OData, and Acumatica ERP

June 4, 2015

Guest Post By: Tim Rodman

There is finally an ERP product that gets it, that embraces Power Pivot, Power BI, and the more than 800 million users of Microsoft Excel.

“What Power Pivot did to Excel, Acumatica is doing to the world of ERP”.

Acumatica recently announced the ability to securely connect to Acumatica ERP data through OData

This is huge. It’s as if two worlds are colliding, creating a good kind of explosion.

2015-05-31_02-38-40

You: “So, wait a second, what is ERP?”

Me: “Great question, I should probably back up for a minute.”

What is ERP

ERP stands for Enterprise Resource Planning and it is the computer system that ties all of the departments in your organization together.

If you work with Power Pivot, there is a very good chance that you also work with ERP data.
SAP, Oracle, Microsoft Dynamics, Infor, Epicor, Sage, and NetSuite are all examples of ERP systems.

Many of the ERP systems in companies today are very old and very ugly. The problem is that they are expensive to replace so they continue to exist through a patchwork of duct tape fixes that have been cobbled together over the years.

However, ERP systems contain a virtual Fort Knox of data that can lead to incredible insights if analyzed correctly (with Power Pivot of course).

The ERP Problem

Read the rest of this entry »


A New Take on “Data Quality?”

June 2, 2015

Post by Rob Collie

image

Bad Data DOES Lead to Bad Results.  But Good Data Can STILL Lead to Bad Results.

Garbage in, Garbage out.  We’re all familiar with this.  If you’re being given junky source data, it’s going to be hard to perform ANY meaningful analysis or reporting on said data until the quality of the inputs is addressed.

The term “Data Quality” has come to mean precisely that – the quality of your inputs.

But at my recent PASS BA presentation on the Bottom Line, I was talking at length about how we often generate poor outputs – our reports and dashboards often leave much to be desired, because we ourselves, the producers of the work, need to be better.

It’s one of my most-emphasized themes:  we’ve been given this amazing new toolset (Power Pivot and the rest of the Power BI stack).  We shouldn’t just use it to produce the same stuff we produced for decades (even though we can do so much faster and more efficiently than before).

We should strive for more meaningful metrics for instance – metrics that remove noise and produce a clearer picture than the age-old default of “raw dollars.” 

A product may be generating more dollars than last year for instance, but that could be misleading.  Is it generating more profit (it may also be more expensive for us to acquire this year)?  Is it generating more profit per store (we may have increased the number of stores that sell it)?  Per day (maybe it was introduced in May of last year, but this year it’s been available since Jan 1)? 

Read the rest of this entry »


Last Chance to register for Power BI Webinar, Tue Jun 2nd

June 1, 2015

Are you…

  • Confused by all the terms and acronyms around Power BI?
  • Wondering how all the pieces fit together?
  • Interested in leveraging Power BI for your team/org but don’t know where to start?
  • Would like to know what to expect on this Power BI journey?
  • Find yourself or others around you asking “Do I need Power Pivot or Power Query?”
    (Hint: Both! But one more than the other)
  • Find yourself struggling to find the odd one out in this list? Power Pivot, Power View, Power Point, Power Query, Power Map

    Well, if you’re struggling with the last one, maybe you are beyond help. But for all the other questions, this is your last chance to register for our Power BI Webinar tomorrow. Our webinar’s purpose is to shine a guiding light on your Power BI path and get you started on this transformational journey. Click here to register or follow links below.

    Free Webinar
    Jun 2

    PowerPivotPro Free Webinar Series

    Power On!
    Avi Singh [Twitter]


  • Dear Accountants: PowerPivot is your friend!

    May 28, 2015

    Guest Post by: Mike Griffin

    Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…

    Accountants are NOT typically data GEEKS

    Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.

    The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.

    The Accounting Need: Remove needles from the hay stack

    Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
    Use Power Pivot and Power BI to look for the proverbial needles in your data haystack

    In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).

    Read the rest of this entry »