Power BI - Why would you ever need Power Query when I already have Power Pivot?

  • Article

I've been mulling this question over since the first incarnation of the former Data Explorer technology.  At first, I wondered if this was developed in some secret back-room in Redmond - completely outside the watching eyes of any of the "mainstream" developers.

Now, I get it.

The easiest way to think about Power Query vs. Power Pivot is to look at things as ETL vs. business modeling.  In the conventional Microsoft BI stack, you have SSIS for ETL tasks and SSAS for modeling.  In no way would someone expect your cube development, for example, to simultaneously address integration/cleansing/manipulation kinds of tasks.  The same sort of principle applies to Power Query and Power Pivot.

So far, I've noticed the following real-world uses for Power Query:

  • You can pull data from sources that aren't readily available in Power Pivot - Facebook, MySQL, and Hadoop are notable data source types built right into Power Query.
  • Inline transformations are a breeze.  When your inbound data isn't exactly how you'd like it, Power Query provides some built-in transformations (splitting columns and un-pivoting data are two popular tasks) that can help square up data prior to modeling in Power Pivot.
  • If your transformations wind up being fairly involved, Power Query provides a nice "Steps" view that recaps each transformation.
  • My favorite - materialized tables!  Let's say you have a single table (with the same structure) in two different databases (perhaps one is located in Europe and one is in North America) that you want to analyze as one all-up entity.  By itself, Power Pivot cannot automatically consolidate multiple tables into one.  With Power Query's "Append" and "Merge" functions, it's pretty easy.
  • When calling Web APIs, the data is often produced in a structure unlike a flat source or relational database.  JSONs, in particular, don't lend themselves to directly feeding a Power Pivot model.  With Power Query in the middle, Power Pivot is able to ingest data from Web APIs.

So is Power Query necessary for *every* Self-Service BI use case?  Nope - if you're wiring things up to a well-designed data warehouse, and the DW has everything you need, you probably don't need to pull Power Query into the mix.  Short of that, you may find Power Query to be a really helpful addition to the arsenal.

I recorded a 30+ minute demo on Power Query and Power Map - illustrating how to grab data from Craigslist and plot some dollar figures on a map.  Hopefully, it'll show why Power Pivot alone would not have been able to solve for this particular requirement.  The demo starts with the raw data (RSS feeds, in this case) and ends with a completed (albeit thin) Power Map illustration.

[View:https://www.youtube.com/watch?v=_BnVGNlYuP0]

 

https://www.youtube.com/watch?v=_BnVGNlYuP0

Hope you enjoy it!

Comments

  • Anonymous
    August 08, 2013
    I know this is pretty much not the forum to post this. But just thought you would be able answer it having worked in these tools. How is power pivot different from a power view? I see power view doing almost the same as that of power pivot.

  • Anonymous
    August 13, 2013
    Hi Ganesh.  Think of things this way: Power Pivot is not, in any way, a visualization tool.  You build out analytic data models with Power Pivot.  A Power Pivot model exposes metrics, KPIs, dimensions, etc - but is isn't the presentation layer at all.  Power View is one option (albeit a pretty slick one) for visualizing data in a Power Pivot model.  Without Power View, you'd typically use standard things like PivotTables and PivotCharts to expose the contents of a Power Pivot model.   At the end of the day, both the Power Pivot model and the Power View reports can wind up living within the same Excel workbook (which, by proxy, will wind up living inside SharePoint as one cohesive app).  Does that make sense?

  • Anonymous
    April 20, 2014
    The same query was bothering me for few last couple of days and now I know :) a very well written article. Thanks for posting. By the way can we read from multiple text files in power query like ssis? What is the limitation that we should be Concerned of? For example can,it work with over a billion of rows?

  • Anonymous
    March 19, 2015
    When would you prefer to build a datawarehouse VS using PowerQuery. The context is the ERP world : finance, sales, purchases, inventory, manufacturing, retail data ?

  • Anonymous
    November 16, 2015
    I use Power Query as a big connections manager.  I have hundreds and hundreds of data sources in every imaginable format.  Many of the sources need to be be cleansed and then appended to the existing data model.  Trying to manage all of this within the PowerPivot application would be mind bending.  One of the amazing backroom features of PowerQuery is its ability to unpivot columns.  Some of my analysis requires that sparse data undergoes complex spreadsheet pre-processing that exceeds to scope of DAX. I could write a complex cursor operation in SQL SVR or I can simply perform the operations in an Excel sheet and then unpivot the results for standard data format importing to my data model.