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 Windows 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:

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

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.