Power Query basics: clean and reshape data in Excel, step by step

Connecting to data

Get & Transform connects to many kinds of source: From Table/Range for data already in the workbook (a plain range is turned into an Excel Table first), From File for CSV and text files, other workbooks, JSON, XML and whole folders, and database and online sources. Each connection is a live link that can be refreshed, not a one-time copy.

After you choose a source, the Navigator lists the tables or sheets it contains. Choose Load to bring the data in as it is, or Transform Data to clean it in the Power Query Editor first.

The editor and Applied Steps

Every change you make in the Power Query Editor, such as removing a column, filtering rows or changing a data type, becomes a named step in the Applied Steps list, run in order each time the query refreshes. Click any step to see the data exactly as it was after that step, which is the fastest way to find where something went wrong.

Rename steps to say what they do ("Removed cancelled orders" rather than "Filtered Rows1"), and be careful deleting or reordering early steps: later steps may depend on a column an earlier one created. Behind the scenes each step is written in Power Query's M language, visible in the Advanced Editor.

Everyday transformations

• Remove Columns, or Remove Other Columns to keep only the ones you selected.
• Split Column by a delimiter, such as a comma, or by a number of characters.
• Change Type: values that do not fit the new type become errors in that column rather than failing the whole step, so check the preview after each change.
• Fill Down: copies a label into the blank rows beneath it, the fix for exports where a category appears only on the first row of each group.
• Filter rows from a column's dropdown, each filter recorded as its own step.
• Remove Duplicates: compares every visible column, not just the one you right-clicked.

Refreshing automatically

Each loaded query's Properties dialog (Data, Queries & Connections, right-click the query) controls refresh: Refresh data when opening the file, Refresh every N minutes, and Enable background refresh, which keeps the workbook usable while a slow query runs.