Excel Power Query

Power Query is a data transformation and connection tool built into Excel. It allows importing data from a wide variety of sources, cleaning and reshaping it, and loading it into Excel — all without writing formulas or code. The steps used to transform data are recorded automatically and can be replayed with a single click whenever the data is refreshed.

What is Power Query?

Power Query acts as a data pipeline between a raw data source and the Excel spreadsheet. Think of it as a series of instructions that tell Excel how to take messy or raw data, clean it up, reshape it, and deliver it in a usable format.

Key Capabilities

• Import data from files (Excel, CSV, JSON, XML, PDF), databases, web pages, and more.
• Remove unwanted rows and columns.
• Clean messy data (trim spaces, fix data types, replace errors).
• Split or merge columns.
• Filter and sort data.
• Merge and append multiple tables.
• Group and aggregate data (like a Pivot Table for data preparation).
• All steps are saved and can be re-run automatically when data updates.

Accessing Power Query

Power Query is found under the Data tab in Excel:

• Data → Get Data — to import from a source.
• Data → Get & Transform Data — the full Power Query section.
• Data → Queries & Connections — to manage existing queries.

Importing Data with Power Query

Step-by-Step: Import from a CSV File

1. Go to Data → Get Data → From File → From Text/CSV.
2. Browse and select the CSV file.
3. A preview of the data appears. Click Transform Data to open the Power Query Editor.
4. Apply transformations as needed.
5. Click Close & Load to load the cleaned data into Excel.

Other Common Data Sources

• From Excel Workbook: Import a table or range from another Excel file.
• From Web: Import a table from a webpage (e.g., a Wikipedia table or stock prices).
• From Folder: Combine multiple CSV or Excel files from a folder into one table automatically.
• From Database: Connect to SQL Server, Access, or other databases.

The Power Query Editor

The Power Query Editor is a separate window where data is previewed and transformations are applied. It has its own Ribbon with transformation tools.

Key Areas of the Editor

• Query Pane (left): Lists all queries in the workbook.
• Data Preview (center): Shows a preview of the current state of the data after all applied steps.
• Applied Steps (right): Lists every transformation applied so far, in order. Each step can be edited, deleted, or reordered.
• Formula Bar: Shows the Power Query M language formula for the current step.

Common Transformations in Power Query

1. Removing Columns

Right-click a column header → Remove or Remove Other Columns. Only the selected columns are kept.

2. Renaming Columns

Double-click the column header to rename it directly.

3. Changing Data Types

Click the icon to the left of the column header (which shows the data type: ABC for text, 123 for number, calendar for date). Choose the correct data type from the dropdown.

Important

If a number column is stored as text (a common import issue), changing its type to "Whole Number" or "Decimal" fixes it so calculations can be performed.

4. Filtering Rows

Click the dropdown arrow on any column header to filter rows — just like AutoFilter in Excel, but within Power Query. The filter becomes a permanent step in the Applied Steps list.

5. Removing Blank Rows

Go to Home → Remove Rows → Remove Blank Rows.

6. Removing Duplicates

Select the column(s) → Right-click → Remove Duplicates.

7. Trimming and Cleaning Text

Select a text column → go to Transform → Format → Trim. This removes leading and trailing spaces. Clean removes non-printable characters.

8. Splitting Columns

Right-click a column header → Split Column → choose By Delimiter or By Number of Characters.

Example

  Column contains: "John Smith"
  Split by space delimiter → creates two columns: "John" and "Smith"

9. Merging Columns

Select two or more columns → Right-click → Merge Columns → choose a separator (space, comma, dash, etc.).

10. Replacing Values

Right-click a column → Replace Values. Enter the value to find and what to replace it with.

Example

  Replace "N/A" with blank, or replace "United States" with "US"

11. Adding a Custom Column

Go to Add Column → Custom Column. Write a formula using Power Query's M language or use column names.

Example

  New column formula: [Units] * [Price]
  → Creates a "Total" column by multiplying the Units and Price columns

12. Grouping and Aggregating

Go to Home → Group By. Choose the column to group by and define aggregate calculations (Sum, Count, Average, Min, Max).

Example

  Source data: Sales transactions with Region and Amount columns.

  Group By: Region
  Aggregate: Sum of Amount

  Result: One row per region showing the total sales amount.

Appending Queries (Combining Tables Vertically)

Append stacks two or more tables on top of each other (like adding rows). Useful for combining monthly files into one annual table.

1. Go to Home → Append Queries.
2. Choose two or three tables (or more) to append.
3. Click OK. The rows from all tables are combined into one.

Merging Queries (Combining Tables Horizontally)

Merge is the Power Query equivalent of a VLOOKUP — it joins two tables based on a common column (like an ID or key column).

1. Go to Home → Merge Queries.
2. Select the second table and the matching columns from each table.
3. Choose the join type (Left Outer, Inner, Full Outer, etc.).
4. Click OK. A new column appears containing the merged table. Expand it to select which columns to add.

Refreshing a Power Query

When source data changes, the query can be refreshed to pull in the latest data with all transformations automatically re-applied.

• Right-click the query in the Queries & Connections pane → Refresh.
• Or go to Data → Refresh All to refresh every query in the workbook.

Summary

• Power Query is a data import and transformation tool that cleans and reshapes data before loading it into Excel.
• Data can be imported from files (CSV, Excel, JSON), databases, the web, or entire folders.
• All transformation steps are recorded in the Applied Steps pane and can be re-run instantly on refresh.
• Common transformations: removing columns/rows, changing data types, filtering, splitting, merging, and grouping.
• Append combines tables vertically (stacking rows); Merge combines tables horizontally (like VLOOKUP).
• Refreshing a query reapplies all steps to the latest version of the source data automatically.