Existing challenge | How does Power Query help? |
---|---|
Finding and connecting to data is too difficult | Power Query enables connectivity to a wide range of data sources, including data of all sizes and shapes. |
Experiences for data connectivity are too fragmented | Consistency of experience, and parity of query capabilities over all data sources. |
Data often needs to be reshaped before consumption | Highly interactive and intuitive experience for rapidly and iteratively building queries over any data source, of any size. |
Any shaping is one-off and not repeatable | When using Power Query to access and transform data, you define a repeatable process (query) that can be easily refreshed in the future to get up-to-date data. In the event that you need to modify the process or query to account for underlying data or schema changes, you can use the same interactive and intuitive experience you used when you initially defined the query. |
Volume (data sizes), velocity (rate of change), and variety (breadth of data sources and data shapes) | Power Query offers the ability to work against a subset of the entire dataset to define the required data transformations, allowing you to easily filter down and transform your data to a manageable size. Power Query queries can be refreshed manually or by taking advantage of scheduled refresh capabilities in specific products (such as Power BI) or even programmatically (by using the Excel object model). Because Power Query provides connectivity to hundreds of data sources and over 350 different types of data transformations for each of these sources, you can work with data from any source and in any shape. |
Product | M engine1 | Power Query Desktop2 | Power Query Online3 | Dataflows4 |
---|---|---|---|---|
Excel for Windows | Yes | Yes | No | No |
Excel for Mac | Yes | No | No | No |
Power BI | Yes | Yes | Yes | Yes |
Power Apps | Yes | No | Yes | Yes |
Power Automate | Yes | No | Yes | No |
Azure Data Factory | Yes | No | Yes | Yes |
SQL Server Integration Services | Yes | No | No | No |
SQL Server Analysis Services | Yes | Yes | No | No |
Dynamics 365 Customer Insights | Yes | No | Yes | Yes |
1M engine | The underlying query execution engine that runs queries expressed in the Power Query formula language ('M'). |
2Power Query Desktop | The Power Query experience found in desktop applications. |
3Power Query Online | The Power Query experience found in web browser applications. |
4Dataflows | Power Query as a service that runs in the cloud and is product-agnostic. The stored result can be used in other applications as services. |