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!