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?