What It Does
Power Query is a tool built into Excel (and Power BI) that transforms messy raw data into clean, analysis-ready tables — without formulas, without copy-pasting, and without losing the original data. You connect to a data source (a CSV file, a database, a web page, another Excel file), use a visual editor to clean and reshape the data, and Power Query records every step you took. The next time new data arrives, you click Refresh and all those steps run again automatically.
How to Access
Already installed in Excel 2016 and later. Go to the Data tab and look for Get & Transform Data or Get Data. In older Excel versions it appears as a separate Power Query tab. In Power BI Desktop, it opens automatically when you import data. No download or installation needed.
Cost
Free. Power Query is built into Excel and Power BI Desktop at no extra charge. If you have Excel 2016 or later, you already have it.
Key Features
- Connect to hundreds of data sources — Excel, CSV, SQL databases, web pages, SharePoint, APIs
- Remove duplicates, fill blank cells, split and merge columns with clicks
- Merge tables (like SQL JOINs) using a common column
- Pivot and unpivot to reshape data between wide and tall formats
- Change data types, filter rows, group and summarize
- Every step is recorded and replayable — one Refresh updates everything
- M language for advanced custom transformations
Why Popular
Before Power Query, cleaning data in Excel meant hours of copy-paste, formulas, and manual work that had to be repeated every month when new data arrived. Power Query turned that into a one-time setup. The next time, you just click Refresh. For analysts who spend significant time preparing data before they can analyze it, Power Query is one of the highest-impact tools to learn.
Project Ideas
- Import a messy CSV from a government website and clean it into a usable table
- Combine monthly sales reports from 12 separate Excel files into one unified table automatically
- Merge a products table with a sales table using a shared product ID column
- Build a data model that updates with one click every time new files arrive