Power Query

Overview

Power Query is a powerful data connection and data preparation technology provided by Microsoft. It is embedded in several Microsoft products, including Excel, Power BI Desktop, Power BI Service (Dataflows), Power Apps (Dataflows), Power Automate (Dataflows), Azure Data Factory, and SQL Server Analysis Services. Power Query enables users to discover, connect, combine, and refine data from a wide variety of sources, such as databases, files, web services, and big data platforms.

The tool offers a user-friendly graphical interface (Query Editor) that simplifies complex data manipulation tasks without requiring extensive coding knowledge. Users can perform common data transformations like filtering, sorting, merging, appending, pivoting, unpivoting, and aggregating data through a visual workflow. It also includes advanced features like Data Profiling and the 'Add Column From Examples' feature, which leverages machine learning to automatically suggest transformations based on user input examples, demonstrating an intelligent approach to data preparation. Power Query records every transformation step, making the process repeatable and easy to update when the source data changes.

By providing a robust and accessible way to prepare data, Power Query significantly enhances productivity for data analysts, business intelligence professionals, and everyday Excel users. It ensures data is in the correct format and structure for analysis, reporting, or building applications, saving considerable time and effort compared to manual data cleaning methods. Its integration across the Microsoft ecosystem makes it a cornerstone for data preparation within these platforms.

Key Features

• Connect to hundreds of diverse data sources (databases, files, cloud, web, etc.)
• Intuitive graphical interface (Query Editor) for data transformations
• Wide range of built-in transformation functions (filter, sort, merge, split, pivot, etc.)
• M formula language for creating custom and advanced transformations
• Automatic recording of transformation steps for easy refresh and automation
• Data Profiling features (column quality, distribution, value counts)
• Automatic data type detection
• "Add Column From Examples" uses machine learning to suggest transformations
• Ability to create reusable functions and parameters
• Integrated deeply within the Microsoft ecosystem (Excel, Power BI, Power Platform, Azure)

Supported Platforms

• Web Browser (Power Query Online/Dataflows)
• Windows App (Excel, Power BI Desktop)
• macOS App (Excel)
• API Access (via Dataflows in Power Platform/Azure)

Integrations

• Connects to hundreds of data sources (Databases like SQL Server, Oracle, MySQL; Files like Excel, CSV, JSON, XML, Parquet; Cloud Services like Azure Blob Storage, SharePoint, Dynamics 365, Salesforce; Web Services, OData feeds, etc.)
• Integrated into Microsoft Excel
• Integrated into Microsoft Power BI Desktop
• Integrated into Microsoft Power BI Service (Dataflows)
• Integrated into Microsoft Power Apps (Dataflows)
• Integrated into Microsoft Power Automate (Dataflows)
• Integrated into Azure Data Factory
• Integrated into SQL Server Analysis Services

Pricing Tiers