How Power Query and Power Pivot work together

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

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, 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 Microsoft Store app.

Power Query

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.

Power Pivot

When you put your data in the Excel Data Model, you can continue enhancing it for analytics in Power Pivot. You can:

• Create relationships between tables.

• Add formulas with Data Analysis Expressions (DAX).

• Create key performance indicators (KPIs) in Power Pivot.

• Set table and column properties to improve the experience of designing in client tools such as Power View and Power Map.

• Organize fields in hierarchies.

• Create perspectives.

  Notes:  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.

More info

Microsoft Power Query for Excel Help

Power Pivot: Powerful data analysis and data modeling in Excel