Power Query and Power Pivot complement each other. Power Query is the recommended experience for discovering, connecting to, and importing data. Power Pivot is great for modeling the data you’ve imported. Use both to mold your data in Excel so you can explore and visualize it with Power Map Preview, Power View, PivotTables, and PivotCharts, and then interact with the resulting workbook in SharePoint, on Power BI sites in Office 365, and in the Power BI Windows Store app.
Power Query is a data discovery and query tool, good for shaping and mashing up data even before you’ve added the first row of data to the workbook. Thus you can
- Import data from external data sources, including big data sources like Hadoop and Facebook, shaping the data before you bring it into Excel and bringing in only the data you need.
- Merge data from a variety of data sources, or append tables together, such as data from several sharded tables in SQL Azure.
- Bring the result into Excel as a single table for:
- Visualizing in Power Map and Power View.
- Further analysis and modeling in Power Pivot.
- Share queries to the Power BI data catalogue so others can use it as a starting point for their data exploration.
- “Unpivot” source data from a PivotTable or matrix format to a flat table.
You can decide to put the data in a worksheet in Excel, or you can add it to the Excel Data Model.
When you put your data in the Excel Data Model, you can continue enhancing it for analytics in Power Pivot. You can:
Note In Power Query, some functionality isn’t available yet:
- SharePoint based refresh in SharePoint Online and SharePoint On-Prem are not yet supported.
- You can’t import an Excel Data Model you created with Power Query into a tabular model (in SS Data Tools)
In short, with Power Query you get your data into Excel (either sheets or the Excel Data Model). With Power Pivot, you add richness to that model.
Microsoft Power Query for Excel Help
Power Pivot: Powerful data analysis and data modeling in Excel