The main pointemphasis should be that Power Query is for discovery/connectivity/importwhereas Power Pivot is for modeling data that has already been imported. Theare both experiences that are built on top of the Data Model in Excel i.e.xVelocity/Vertipaq.
Power Query iswhere you will find the support for the data sources and the ability to connectto them. In the fullness of time, Power Pivot will not have it's own connectexperience.
Power Query isabout reshaping and mashing up data even before the first row of data has everlanded into the workbook (again, the emphasis being query). You can jointables together, append tables together etc. and then land the resultinto the data model for further analysis/modeling in Power Pivot. Thisalso allows you to work with large data where you don't have the luxury to landdata into the model before you start shaping it.
I use thefollowing questions to help folks understand the differences:
- How do youquery data from big data sources like Hadoop and land that data into Excel?
- How do youmerge two tables from different sources into a single table and land that intothe model?
- How do youappend data from 10 sharded tables in SQL Azure into Power Pivot and land thatas a single table in PP?
To make thelong story short: PQ lets you get data into the model. PP let's you addrichness to that model. There is some overlap in terms of operations - and itreally doesn't matter at the end of the day. Sure you can create custom columnsin PQ as well as PP - but don't forget that you can also docustom columns when you do CREATE VIEW in SQL. Power Query justhappens to be the way of doing CREATE VIEW over the entire universe ofdisparate and heterogenous data.