Power BI Desktop (Designer) vs. Excel: Rematch

July 30, 2015

By Avi Singh [Twitter]

In our last webinar the #1 question and confusion seemed to be around Excel versus Power BI Desktop (formerly called Power BI Designer). The thing is, both tools are really part of an ecosystem. And the biggest confusion was around understanding how these tools fit into the overall landscape.

Here is the picture we would build to, but do read through so you understand how we arrived here and also find out how you control the future direction of Power BI (seriously, you do!).


Excel versus Power BI Desktop
Note: Things change fast in the Power BI world, so this picture may change as well

Standalone Tool for End-to-End BI

Rob did an excellent head to head comparison of Power BI Designer versus Excel. Just read that, but I’ll add some commentary.

If you were to use a single tool for BI – getting data, building a data model and building charts/reports – we have firmly established that Excel is the world’s best data tool, period Smileand that is primarily due to it’s internal network effect and that it is easy to adopt.

Excel is the World’s Best Data Tool, Period

Power BI Desktop is a very slick tool, with all different facets meshed together beautifully without any seams showing. Unlike Excel, where due to the “Add-In” nature Power* tools always feel a little clunky, not to mention cases where they go totally awry. But in spite of all that, it is hard, no impossible, to build a tool that can supersede Excel, or replace Excel.

However that is not quite a fair comparison. Because Power BI Desktop is primarily built for PowerBI.com.

Power BI Desktop for PowerBI.com

Power BI Desktop is primarily an accompaniment to PowerBI.com. It is essentially an “authoring tool” for PowerBI.com. Its true purpose is to build something that gets published to PowerBI.com.


The last step in Power BI Desktop is to publish it to PowerBI.com

You would almost never use Power BI Desktop standalone. Well you may, but understand that it is not really built for that purpose.

With these two combined – Power BI Desktop and PowerBI.com – they outshine Excel in the BI department. With the fluid development environment of Power BI desktop for authors…and the rich, easy to share, mobile ready visualization platform of PowerBI.com.

However that is not a fair comparison either, since Excel models can be published as well. Read the rest of this entry »


Are Your Official Data Tools an “Arranged Marriage?”

July 28, 2015

Post by Rob Collie

image

A Recent Trip to the Hospital Highlighted the Folly of The Way Things Work

That Oh So Sneaky “Adoption Problem”

I spend a lot of time talking to software vendors. One vendor specifically of course, whose name rhymes with “Bike Row Loft.”  But all software vendors share one basic habit, which is that they’re constantly asking, “How do we get more customers to BUY our tools?”

But I also spend a lot of time talking to their customers.  And while the vendors expect their customers’ most pressing question to be “which software tools do I buy,” the reality I see is FAR different.

Buying software is just the FIRST step down a very difficult road.  You choose the software from Vendor X, announce the decision to your organization, send Vendor X a check…  and that’s the starting gun.

image

Yeah, Vendor X sees that as the END of the story.  And so do their competitors, Vendor Y and Vendor Z, who slink away defeated.  But you, the purchaser of the tools, well, your story is just beginning isn’t it?

Now you have to get your PEOPLE to start USING the new tools.  And to KEEP using them.  I’ve discovered that this is by FAR a much bigger problem than choosing the tool. 

“The New Operating Room Sucks, Who Designs These Things?”

Read the rest of this entry »


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’?

oracle

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.

image

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!

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