Explanation

Power Query is Excel’s data connection and transformation environment.

It allows you to perform repeatable data-cleaning operations without manually editing the original source.

Open Power Query through:

Data → Get Data → Transform Data

or by editing an existing query.

Power Query Editor

The Power Query Editor provides areas such as:

  • Queries pane
  • Data preview
  • Formula bar
  • Applied Steps
  • Query settings

Common Transformations

Power Query can:

  • Remove columns
  • Remove rows
  • Rename columns
  • Change data types
  • Filter data
  • Replace values
  • Split columns
  • Merge columns
  • Remove duplicates
  • Add calculated columns

Applied Steps

Power Query records transformations as Applied Steps.

Example:

Source

  ↓

Promoted Headers

  ↓

Changed Type

  ↓

Removed Columns

  ↓

Filtered Rows

This is one of the major advantages of Power Query because the same transformation process can be repeated when the source is refreshed.

Practice

Import a sales dataset into Power Query and identify:

  • Source
  • Data preview
  • Applied Steps
  • Query name
PreviousNext