What it is: Microsoft's ETL tool built into Excel and Power BI. Connect, transform, and clean data with no-code interface.

What It Does Best

Data transformation. Clean, reshape, merge, append data. GUI records steps automatically. Reproducible workflows.

Connect to anything. Files (Excel, CSV, JSON), databases (SQL, Oracle), web APIs, cloud (Azure, SharePoint). 100+ connectors.

Refresh automation. Set up once, refresh with one click. Data updates automatically. Perfect for recurring reports.

Key Features

GUI interface: Click to transform, no code required

M language: Advanced users can edit underlying code

Applied steps: All transformations recorded and repeatable

Merge queries: JOIN data like SQL, but visual

Unpivot/pivot: Reshape data between wide and long formats

Pricing

Excel/Power BI Desktop: Included free

Excel requires: Excel 2016+ or Microsoft 365

Power BI Desktop: Free download from Microsoft

When to Use It

✅ Already using Excel or Power BI

✅ Need to clean/transform data repeatedly

✅ Connecting multiple data sources

✅ Non-coders need ETL capabilities

✅ Building automated reports

When NOT to Use It

❌ Very large datasets (>1M rows get slow)

❌ Need Python/R data science libraries

❌ Real-time data processing required

❌ Not using Microsoft ecosystem

❌ Need version control for data pipelines

Common Operations

Remove duplicates: One-click deduplication

Change data types: Text to numbers, dates, etc.

Split columns: By delimiter or character positions

Merge queries: LEFT JOIN, INNER JOIN, etc.

Group by: Aggregate data like SQL GROUP BY

Custom columns: Add calculated fields

Power Query vs Alternatives

vs Excel formulas: Power Query more scalable and maintainable

vs Python/Pandas: Python more powerful, Power Query easier

vs Alteryx: Alteryx more features, Power Query free

vs SQL: SQL more powerful, Power Query more visual

Learning Path

Start here: Get & Transform Data tab in Excel

Basic: Import CSV, remove columns, filter rows

Intermediate: Merge tables, unpivot data, group by

Advanced: M language, custom functions, parameters

Best Practices

Don't modify source data: All changes in Power Query

Name your queries: Makes them easier to reference

Document steps: Add descriptions to complex transformations

Use parameters: Make queries flexible and reusable

Optimize performance: Filter early, avoid unnecessary steps

Killer Features

Folder data source: Combine all files from a folder automatically

Refresh all: Update all queries with one button

Reference vs duplicate: Reuse transformations across queries

Conditional columns: IF-THEN logic without formulas

Visit Power Query →

← Back to Data Cleaning Tools