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:
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.
Hope you enjoy it!
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.
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?
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?
When would you prefer to build a datawarehouse VS using PowerQuery.
The context is the ERP world : finance, sales, purchases, inventory, manufacturing, retail data ?
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.