Power Query

Construct data transformations through a visual, step-by-step interface without writing code. Users click through operations like filtering, sorting, and reshaping data, with each step automatically generating M language code in the background.

Automatically detect and assign appropriate data types (text, number, date, boolean) to columns based on content analysis. Reduces manual type-setting and catches data quality issues early.

Stack multiple datasets vertically to combine rows from different sources. Automatically aligns columns by name and handles mismatched schemas.

Split a single column into multiple columns based on delimiters, fixed widths, or patterns. Extracts structured data from unstructured text fields.

Convert data between wide and long formats. Pivot transforms rows into columns (aggregating values), while unpivot transforms columns into rows.

Identify and remove duplicate rows based on all columns or specific key columns. Keeps first or last occurrence based on user preference.

Detect, replace, and manage null or missing values in datasets. Options include removing rows, filling with defaults, or using formulas to impute values.

Apply text operations like case conversion (upper, lower, proper), trimming whitespace, and text replacement. Standardizes text data for consistent analysis.

Parse and convert text into proper date/time formats, handle different date formats, and extract date components (year, month, day, quarter). Ensures temporal data is correctly typed for analysis.

Create new columns using formulas and M language expressions that combine, calculate, or transform existing columns. Enables derived metrics and business logic implementation.

Connect to and import data from 100+ sources including databases, cloud services, web APIs, and file formats. Provides native connectors with authentication handling and query pushdown capabilities.

Load only new or changed data from a source since the last refresh, rather than re-importing entire datasets. Reduces processing time and bandwidth for large, frequently-updated data sources.

Write custom transformations using the M formula language for complex data operations not available in the visual interface. Enables advanced users to build sophisticated ETL logic with full programmatic control.

Embed Power Query transformations directly into Excel workbooks with automatic refresh capabilities. Queries can be loaded into tables or used as data sources for pivot tables and charts.

Create and manage data transformation queries within Power BI that prepare data for visualization and analysis. Queries are stored in the Power BI model and refreshed on schedule.

Remove unwanted columns, select specific columns, and reorder columns in a dataset. Simplifies datasets by keeping only relevant fields for analysis.

Filter rows based on conditions (equals, contains, greater than, date ranges, etc.) to keep only relevant records. Supports complex multi-condition filters with AND/OR logic.

Combine two or more datasets using join operations (inner, outer, left, right) based on matching columns. Enables creation of comprehensive datasets from multiple sources.