Power Query (Get & Transform) and Power Pivot complement each other. Power Query is the recommended experience for importing data. Power Pivot is great for modeling the data you’ve imported. Use both to shape your data in Excel so you can explore and visualize it in PivotTables, PivotCharts, and Power BI.

In short, with Power Query you get your data into Excel, either in worksheets or the Excel Data Model. With Power Pivot, you add richness to that Data Model.

With Power Query, you can locate data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Then, you can load your query into Excel to create charts and reports. There are four phases to using Power Query:

  • Connect    Import and make connections to data in the cloud, on a service, or locally.

  • Transform    Shape data to meet your needs, while the original source remains unchanged.

  • Combine    Further shape data by integrating it from multiple sources to get a unique view into the data.

  • Load    Complete your query and save it into a worksheet or Data Model.

Think of it this way. A vase starts as a lump of clay that one shapes into something practical and beautiful. Data is the same. It needs shaping into a table that is suitable for your needs and that enables attractive reports and dashboards.

Ways to shape data in Power Query

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

The logical hierarchy in Olympic Medals data

Notes

  • Data Model refresh is not supported in SharePoint Online or SharePoint On-Premises.

  • You can’t import an Excel Data Model you created with Power Query into a tabular model in SQL Server  Data Tools.

See Also

Power Query for Excel Help

Get started with Power Pivot in Microsoft Excel

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.