How Power Query and Power Pivot work together

Important   This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Desktop, which is a stand-alone report authoring and data transformation tool for Power BI.

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

Applies To: Excel 2013

Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Change language