Power Query: practice exercises
Power Query lets you clean and combine data with repeatable steps instead of manual editing. In this set you load a table, remove columns, filter rows, change types, split, merge and append queries, unpivot and group data, and read the M code behind each step. A built-in editor simulates the real interface in your browser, so no installation is needed. A good fit if you refresh the same report every week and want to stop redoing it by hand.
21 exercises · level: Analyst
Showing 21 of 21
The Query Editor
Create a query from the "Orders" table and remove the "Note" column.
Keep only the paid orders (Status equals "Paid").
Change the type of the "Amount" column to "Decimal Number".
Split the "Location" column by the delimiter ", " (comma and space).
Click "Close & Load": the result should appear starting at cell A12.
Create a query from the "January" table and append the rows of the "February" query.
Bring each rep's city into the deals from the "Reps" query (Merge on the "Rep" column).
Group the rows by city: a new column "Revenue" holding the sum of the "Amount" column.
Close and load the result to A8.
Create a query from "Sales" and unpivot all the columns except "Product".
Remove the rows with no sales: keep those where "Value" is greater than 0.
Group by "Attribute" (the month): a column "Units Sold" holding the sum of "Value". Load the result to F1.
Sources, Errors, and M
Create a query from the file "march_sales.csv" and change the type of the "Amount" column to "Decimal Number".
Two amounts became errors ("n/a" and "—"). Replace the errors in "Amount" with 0.
Group by store with the sum of the sales (a new column named "Amount") and load the result to A1.
Create a query from "Sales" and pivot the column: months become columns, Amount provides the values (sum).
Load the result to E1.
In I2, use a formula to total Chicago across all months from the loaded table.
Create a query from "Deliveries" and change the type of "Qty" and "Price" to "Decimal Number".
Remove the rows with an error in "Qty": without a quantity you cannot cost a delivery.
Replace the errors in "Price" with 0 and load the result to F1.
No exercises match these filters.