Identify Insights Faster
With Power Query (known as Get & Transform in Excel) and Power Pivot in Excel, you'll spend less time manipulating data, and more time driving impact. Watch this 2-minute video to learn how.
See it for Yourself
Whether it's small data sets or large ones with up to millions of rows, you can connect, clean, analyze, and share your data faster using Power Query (known as Get & Transform in Excel) and Power Pivot.
FAQ
With Microsoft 365 Apps for enterprise, the Excel app on Windows for PCs offers the full Power Query and Power Pivot features that further enhance your analytics and modeling capabilities, take advantage of advanced data connectivity options, and effectively share your data across the organization. See the details below for more information on Power Query and Power Pivot availability and ask your IT team if your current Office plan supports these features.
For more information, see Microsoft 365 Apps for enterprise.
Basic business analytics features
Basic analytics features are available with any Microsoft 365 subscription or any Office 2016 one-time purchase. Such features include PivotTables, slicers, charts and data model capabilities. With Power Query, you can import, shape, and merge data from files, databases and websites. Basic data model support allows you to load data to the data model, detect relationships automatically, add time grouping, and create DAX measures.
For more information about obtaining the best performance, see Choose between the 64-bit or 32-bit version of Office.
Advanced business analytics features
Advanced analytics features are available with Microsoft 365 Apps for enterprise, Office 2019 Professional, Office 2019 Professional Plus, and Excel 2019 standalone one-time purchases. In addition to the basic analytics features, you can use Power Query to import, shape, and merge data from corporate, big data, and cloud data sources, and share queries. Advanced data model support with Power Pivot includes a dedicated data modeling editor, a data view, DAX calculated columns, KPIs, and hierarchies.
For more information, see Power Query data sources in Excel versions and Where is Power Pivot?
Excel 2016 for Mac and Excel 2019 for Mac have many of the same basic analysis features that are listed above: PivotTables, slicers, charts, and basic import capabilities. They do not yet have Power Query or Power Pivot, but we are continually improving Excel on the Mac. Excel for Microsoft 365 for Mac offers some support for Power Query.
For more information, see Use Power Query in Excel for Mac.
Power Query provides fast, easy data gathering and shaping capabilities. Power Query enables you to connect, combine, and refine data sources to meet your analysis needs. There are four phases to using Power Query:
-
Connect Import data 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.
For more information, see About Power Query in Excel and Power Query for Excel Help.
Power Pivot allows you to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. You can use the Data Model and a collection of tables to create relationships stored as part of the workbook. The Data Model integrates deeply with other features in Excel, such as tables and Pivot Tables, to provide a seamless analytics experience.
For more information, see Get started with Power Pivot in Microsoft Excel.
Power BI is a suite of business analytics tools that deliver insights throughout your organization. With Power BI Desktop, you can connect to hundreds of data sources, simplify data prep, and drive ad hoc analysis. You can also produce reports and then publish them for your organization to consume on the web and across mobile devices. Everyone can create personalized dashboards with a 360-degree view of their business. Governance and security tools are built-in.
For more information, see Power BI.
Excel 2016 and Power BI Desktop deliver a portfolio of tools that is unique in the industry. Together, they enable business analysts to more easily gather, shape, analyze, and visually explore their data. Power BI is a suite of business analytics tools that deliver insights throughout your organization. It helps anyone in your organization bring together data from a wide range of sources—Excel workbooks, on-premises corporate data, Azure services, and other popular cloud-based offerings like Salesforce.com or Marketo—to view and share using live dashboards and interactive reports.
For more information, see Analyze in Excel for Power BI.
For Microsoft 365 subscribers and Excel 2019 have added enhancements to the functionality and features you're already used to.
For more information, see;
