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.
When you put your data in an Excel Data Model, you can continue enhancing it by performing analytics in Power Pivot. You can:
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.
