FormulaCamp
Completed
All interactive exercises
Hands-on Excel exercises

Power Query Exercises

Practice Power Query with hands-on exercises for cleaning, importing, appending, merging, grouping, unpivoting, pivoting, and handling errors.

Power Query is most useful when the same messy export arrives again and again. Instead of repeating manual cleanup, you record a sequence of transformations that can be refreshed for the next file. The live spreadsheet below focuses on TRIM, one small cleanup operation that makes the underlying problem visible. Then the linked Power Query exercises move that thinking into repeatable import and transformation steps.

You will clean an export, append files with the same structure, merge tables by a key, group records, unpivot repeated month columns, import from common sources, pivot a category, and handle errors. Before transforming anything, decide what one row should represent and which columns form a reliable key. After every major step, check row counts, data types, and unmatched records. Those controls help distinguish a successful-looking preview from a trustworthy query.

The exercises use FormulaCamp’s browser-based teaching tools to model the operations and check outcomes. The interface is not a claim to reproduce every part of desktop Excel. The transferable skill is the transformation plan: source, types, cleanup, reshape, join, validation, and refresh. Follow the data analyst or operations path afterward to place Power Query inside a broader reporting workflow.

Your turn

Your turn: in B2, remove extra spaces from the raw location in A2.

Example spreadsheet for messy location names
ABC
1Raw locationClean locationAmount
2 North Hub 120
3East Depot 80
4 West Store50
5North Hub 30
Not yet solved.

Related exercises

Open a task in its lesson.

  1. Create a query from the "Orders" table and remove the "Note" column.

    Power Query: Cleaning an Export Free

  2. Split the "Location" column by the delimiter ", " (comma and space).

    Power Query: Cleaning an Export Free

  3. Create a query from the "January" table and append the rows of the "February" query.

    Power Query: Append, Merge, and Group By

  4. Bring each rep's city into the deals from the "Reps" query (Merge on the "Rep" column).

    Power Query: Append, Merge, and Group By

  5. Group the rows by city: a new column "Revenue" holding the sum of the "Amount" column.

    Power Query: Append, Merge, and Group By

  6. Create a query from "Sales" and unpivot all the columns except "Product".

    Power Query: Unpivot and Totals

  7. Create a query from the file "march_sales.csv" and change the type of the "Amount" column to "Decimal Number".

    Power Query: Importing CSV, Folders, and the Web

  8. Create a query from "Sales" and pivot the column: months become columns, Amount provides the values (sum).

    Power Query: Pivot Column

  9. Create a query from "Deliveries" and change the type of "Qty" and "Price" to "Decimal Number".

    Power Query: Handling Errors

Worked answers

Try the spreadsheet first, then compare your formula and result.

Clean the location field in Power Query

What clean text should come from A3?

Formula: =TRIM(A3) → East Depot

  1. Select A1:C5, choose Data → From Table/Range, confirm My table has headers, and open the Power Query Editor.
  2. Select Raw location, then choose Transform → Format → Trim; rename it Clean location.
  3. Close & Load. The four cleaned rows are North Hub 120, East Depot 80, West Store 50, and North Hub 30.

Group the cleaned rows by location

What clean text should come from A4 before grouping?

Formula: =TRIM(A4) → West Store

  1. After Trim, select Home → Group By, group on Clean location, name the new column Total Amount, and choose Sum over Amount.
  2. Set Amount to Whole Number if needed, then Close & Load.
  3. The grouped result is North Hub 150, East Depot 80, and West Store 50; the check total is 280.

Frequently asked questions

What should I learn first in Power Query?

Start with importing, data types, filtering, renaming, and cleanup. Then learn append, merge, group, and unpivot.

What is the difference between append and merge?

Append stacks rows from similar tables. Merge joins columns by matching one or more keys.

Why do row counts change after a merge?

Unmatched keys can remove expected data, while duplicate keys on the joined table can multiply rows. Inspect both before expanding.

Can I repeat the cleanup next month?

That is the central workflow: point the recorded query at the next compatible source and refresh, then run the same validation checks.

Continue practicing

Function guides

Learning paths