Origin World Labs
Analytics Tools Guide

Power Query

Excel's built-in data transformation engine — clean, reshape, and combine data sources without writing a single formula.

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

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

Learn how to use Power Query through AI

The Build Your Custom AI Data Analyst course teaches you to control tools like this using AI agents. No prior programming experience needed.