Blog Post

Power BI Desktop: A Guide for Excel Users

,

Today Microsoft officially released the new development tool for Power BI called Power BI Desktop.  First of all let me share my excitement in what this release means for Power BI.  The team at Microsoft has done an incredible job and the results are something they can truly be proud of.

This new release presents a lot of questions for those who have built Power BI solutions in the past using Excel though.  So I thought it would be appropriate in this post to answer many of those questions as you begin exploring Power BI Desktop and compare it to Excel capabilities.

**Disclaimer: Information current as of 7/24/2015**

Overview

The good news is fundamentally what you’re doing with Power BI isn’t changing. You’re first going to find the data you want to import.  Then you may apply some business rules or transformations to the data.  Next, you’ll organize or model the data, which is an especially important step if you’re pulling from multiple data sources so they all relate properly to each other.  Last, you take what you’ve modeled and visualize it in reports that are presented to your users.

So if these steps haven’t changed then what has?  Well, the main changes have come with which tool you may choose to do your development in but there is also significant changes from the visualization side that are important to highlight. Let’s look at each of these steps: Importing, Transforms, Modeling and Visualizing your data and discuss how things have changed as you begin to develop in the Power BI Desktop application.

Importing Data

Excel

When working in Excel if you wanted to import data you had 3 possible ways to do this. This could be done with the Data, Power Pivot, or Power Query tabs in Excel.  You would choose each of these for different reasons.  The Data tab would be used to connect often to more enterprise data sources like Analysis Services cubes or Tabular models that have been prepared by IT.  The Power Pivot tab would be used to import data and begin modeling it with calculations, hierarchies, KPIs, etc..  Then the Power Query tab would not only import data but also allow you to apply business rules to the data through a built-in transformation language.  Many people have questioned whether or not to start with Power Query or Power Pivot to import data in Excel.

My guidance to you now would be to get in the habit of starting with Power Query if you’re not already because it presents you with many advantages from an overall Power BI perspective.  Consider things like additional data source options, the ability to share queries, and the continuing upgrades coming with Power Query as just a few reasons.

I’ll also mention that in Excel 2016 you will find Power Query under the Data tab going forward.

Power BI Desktop

In the Power BI Desktop application this story becomes much more simple. The one and only way to import data starts with Power Query.  By selecting Get Data in Power BI Desktop you’re presented with a very large (yet continuing to grow) list of data sources that you can use for importing data.

image

This is using Power Query as initial tool to connect to the data you need.  After you select the data you want you can decide if you want to bring it in as-is by selecting Load or if you need to apply business rules to the data first before importing you can select Edit.

image

Behind the scenes what this means is when you select Load the data immediately gets placed into an in-memory data model (aka Power Pivot).  If you select Edit then it will launch the Query Editor (aka Power Query).  Here you can continue to modify the data using the user interface or by writing your own M Query.

The one thing I’ll mention that’s currently missing from the Power BI Desktop for importing data is it’s currently missing the Data Catalog Search that Excel Power Query has.  This allows you to search public and organization queries.

Transform Data

Excel

In Excel if you want to apply business rules or transform data you will likely be using Power Query.  It’s the only tool that really allows you to modify the results of data as you import it in.  Tools like Power Pivot are all about importing first, completely read only, and then building on top of what you’ve imported.  While Power Query has an in-depth query language for modifying data during the import process.  This also can use features like Query Folding to run the final query against the data source and not on the client side.

Power BI Desktop

Really no big changes here in regards to the actual transformation process.  The Query Editor for Power Query in Excel looks essentially the same as it does in the Power BI Desktop.

image

The one thing I’ll note here is that it will be difficult to accomplish things like macro integration and creating user interactive workbooks like I’ve show here.

Modeling Data

Excel

Data modeling in Excel is done with the in-memory technology called Power Pivot.  Power Pivot has been available since Excel 2010 as a great way to do things like create relationships between currently unrelated items, create hierarchies as a way to navigate deeper into your data, create KPIs to analyze metrics in more in-depth, and many other features.

Power Pivot is still the core of what makes visualization work and work fast regardless of whether it’s Excel or Power BI Desktop.

Power BI Desktop

In the Power BI Desktop there’s still a lot of growing to do as far as creating data models.  I’m not too concerned about this though because as I’ve seen Power BI Desktop grow most recent investment has been placed on the data modeling side.  For example, up until the most recent release there was no data or diagram view like you have in Excel but with the public release of Power BI Desktop today you will now find these two features added.

image

You’re also seeing some things released to the desktop application before Excel.  For example, there are new DAX capabilities in the Power BI Desktop that aren’t available until Excel 2016.  In Power BI Desktop you now have the ability to do things like variables in DAX and there are new functions also available.  Here’s an example of the new DATEDIFF function:

image

There are even some new more advanced relationship properties to help deal with many-to-many relationships and bidirectional relationships that are currently only available in the desktop application. Chris Webb has a great post detailing this update here.

There’s still a lot of work to do here still to have full feature parity between Excel and the Power BI Desktop application.  Again this is as of 7/24/2015 and I anticipate many of these will be added as time goes on but here a quick list of some features that are currently not available to Power BI Desktop that are in Excel:

  • Perspectives
  • Hierarchies
  • Web URL Image support only (No database images yet)
  • Default Field Set (assigned default fields to a table)
  • Table Behavior
  • Summarize By (for changing the default aggregation behavior)

Some of these are more critical than others but for the most part you should be able to do what you need in the Power BI Desktop application when it comes to modeling now.

Now even though many of these properties don’t exist in Power BI Desktop that doesn’t mean you can’t import an Excel workbook to PowerBI.com that’s already turned on these features and use them with visualizations.

Visualizing Data

Excel

In Excel you have multiple options for how to visualize data once it’s imported.  You can do standard Excel PivotTables or PivotCharts, Apps for Office, Power View, or Power Map. Depending on what type of data you’re working with or what the desired final report needs to look like would determine which route you chose.  Geographical data would fit nicely into Power Map, Power View or even some Apps for Office visualizations.  Power View is great for exploring data and drilling deeper into your data.  They all have a purpose depending on what your needs are

Power BI Desktop

The Power BI Desktop uses exclusively Power View to visualize your data, but it’s a significantly overhauled version of Power View.  You’re now given new visualizations like:

  • Combo chart
  • Tree Map
  • Area Chart
  • Filled Map
  • Funnel Chart
  • Gauges
  • Donut Chart
  • Matrix

Here’s a few of the new visualizations

image

The interactive nature of Power View that you are used to in Excel still exists here and it is still dependent on a well defined model. So if you click on a bar in a bar chart the rest of the report will filter on that selection.

Not only are you seeing new amazing visualizations but it’s also using a framework that’s very extendable.  Meaning you can use custom visualizations that you create on your own or that someone else created.  Check out this Power BI Visual Playground on GitHub to see what’s possible.

You also have a quick and easy way to publish the results to your Power BI site with a simple click of the Publish button in the ribbon the results will be shared with others.

image

Now this has simply been just a comparison between what’s possible in the Power BI Desktop application versus Excel.  If you’re curious about the other Power BI features that were released as well you should read this blog from the Power BI team.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating