The Work Gym

Courses / Data and analytics tools

Power Query

Clean, reshape and combine data in the Power Query Editor, step by step.

Start lesson 1 Practice interview Take the assessmentPractice the first unit of every course. No account needed.

60lessons

339checked exercises

12projects, cases and exams

Prepares you for Reporting roles, PL-300

Projects, case studies and mock exams

Project · IntermediateA reusable monthly sales query

Turn two months of raw exports and a manager list into revenue per manager, with steps that rerun every month.

6 steps · 20 min Project · AdvancedTurn a printed report into data

Rebuild a regional sales report made for printing into a clean table, then total it by region.

8 steps · 22 min Project · AdvancedService backlog by team

Prepare an open-service queue with clean team names and a priority-hours total for each team.

6 steps · 28 min Project · AdvancedInventory count audit

Build a reconciled count summary from a warehouse sheet that includes a duplicated scan and inconsistent location text.

6 steps · 28 min Case study · AdvancedSupplier exception exposure

Quantify open supplier exceptions while keeping unknown amounts separate from zero exposure.

6 steps · 28 min Case study · AdvancedCapacity shortfall

Identify the team with the largest positive shortage of scheduled hours compared with available hours.

6 steps · 28 min Project · ExpertMonthly shipment refresh

Build a repeatable shipment report from two monthly files, keeping unresolved quantities out of usable-unit totals.

8 steps · 45 min Mock exam · ExpertPower Query preparation A

A timed, original Power Query preparation section with checked answers and explanations.

15 questions · 25 min Mock exam · ExpertPower Query preparation B

A timed, original Power Query preparation section with checked answers and explanations.

15 questions · 25 min

Build repeatable data preparation queries from supplied CSV and workbook files. Start with headers, types, filters, and text cleanup, then work through joins, grouped summaries, reshaping, errors, and reusable M functions in the Advanced Editor. Projects carry your query from one stage to the next, and hidden replacement files check that your transformations work when the data changes.

Practice sets revisit skills on fresh records. Interview questions test your reasoning, while two timed preparation sections cover Power Query topics relevant to PL-300. These sections focus on data preparation; they do not reproduce the full certification exam.

Beginner

The editor and its steps

Open files, inspect steps and keep a clean record population.

3 lessons

1The Power Query Editor: open a file and shape itOpen a CSV file in Power Query, read its applied steps, and remove, rename and choose columns.4 exercises · 10 minFree

2Remove rows: titles, blanks and totalsStrip report titles, blank lines and total rows from an export so only the data is left.5 exercises · 12 minFree

Practice setPrepare a theater stock countClean an original theater inventory while preserving zeros and valid repeated categories.Free

Cleaning data

Clean text, select records, sort and split columns.

4 lessons

4Clean text: trim, case and replaceFix stray spaces, inconsistent capitals and old names so the same value always looks the same.4 exercises · 12 minWith a plan

5Filter and sort rowsKeep only the rows you need with column filters, and put them in order.4 exercises · 12 minWith a plan

6Split a column by delimiterSplit "Last, First" names, "City, State" locations and email addresses into separate columns.4 exercises · 12 minWith a plan

Practice setClean an inventory countEight cleanup tasks on a messy stock count: the Beginner skills mixed together.With a plan

Types and source conventions

Preserve identifiers and interpret regional numbers and dates.

3 lessons

8Choose types without damaging identifiersSet a deliberate schema while preserving leading zeros and missing measurements.4 exercises · 14 minWith a plan

9Read numbers and dates with the right localeInterpret regional dates and decimal separators before they enter a report.4 exercises · 15 minWith a plan

Practice setSchemas and missing valuesKeep ID, Team and Hours only.With a plan

Intermediate

Adding columns

Create calculations and labels, then recover block-style layouts.

4 lessons

11Custom columns with M formulasAdd calculated columns with short M formulas: totals, month names, codes and rounding.4 exercises · 14 minWith a plan

12Conditional columnsLabel rows by rules (if Qty is 10 or more, Large) without writing M.4 exercises · 12 minWith a plan

13Fill upward from section footersRecover footer labels without borrowing labels from the wrong section.4 exercises · 14 minWith a plan

Practice setShape an event crew rosterTurn crew contact and availability files into useful operational columns.With a plan

Summarizing and reshaping

Choose output grain, aggregate records and pivot or unpivot fields.

5 lessons

15Group by: totals and counts per groupTurn order rows into one row per region or product, with counts, sums and several totals at once.4 exercises · 14 minWith a plan

16Unpivot: from wide to longTurn a budget with one column per month into one row per department and month.4 exercises · 12 minWith a plan

17Pivot row values into columnsBuild wide summaries and choose what happens when several rows meet in one cell.4 exercises · 16 minWith a plan

18Group by several keys and measuresChoose the result grain and combine counts, sums and distinct counts correctly.4 exercises · 16 minWith a plan

Practice setAdd, group, reshape and combineEight mixed tasks on the coffee orders and the budget: choose the right transformation each time.With a plan

Combining queries

Choose a join kind, preserve intended matches and align appended schemas.

8 lessons

20Merge queries and join kindsBring the regional manager into each order, and find orders with no manager, with the right join kind.4 exercises · 15 minWith a plan

21Append queries: stack months togetherStack April's orders under March's, then sort and summarize the combined list.4 exercises · 10 minWith a plan

22Inner joins and repeated matchesKeep matching dispatch records and explain why duplicate lookup keys multiply rows.4 exercises · 17 minWith a plan

23Right and full outer joins for reconciliationPreserve the required side and expose records missing from either operational list.4 exercises · 16 minWith a plan

24Anti joins find what is missingBuild exception lists without confusing unmatched rows with matching rows.4 exercises · 15 minWith a plan

25Append files when the schema changesCombine reordered, missing and renamed fields without shifting values into the wrong columns.4 exercises · 15 minWith a plan

ProjectA reusable monthly sales queryTurn two months of raw exports and a manager list into revenue per manager, with steps that rerun every month.With a plan

Practice setReconcile equipment movement listsChoose joins from business questions and preserve each source population correctly.With a plan

Advanced

Reliable queries

Handle conversion errors, repair steps and retain reusable staging queries.

4 lessons

28Errors: find, replace and removeTurn text columns into numbers, then deal with the values that won't convert.4 exercises · 12 minWith a plan

29Edit, delete and fix stepsChange a query after the fact: remove a wrong step, change a filter, rename the query and fix a step that broke.4 exercises · 12 minWith a plan

30Reference queries and stagingBuild several outputs from one cleaned query, and keep the staging query out of the workbook.4 exercises · 12 minWith a plan

Practice setAudit a parcel quality fileRepair a refresh without deleting valid records or converting missing measurements to zero.With a plan

Report layouts and expressions

Fill, index and transpose layouts, then calculate with M expressions.

6 lessons

32Fill down, index columns and top rowsRepair report layouts that show a label once per block, number rows, and keep the top N.4 exercises · 12 minWith a plan

33M functions in custom columnsWrite the M that buttons cannot: tests on text and parts of dates inside a custom column.4 exercises · 12 minWith a plan

34Transpose a horizontal exportTurn record columns into rows while keeping field labels and missing cells aligned.4 exercises · 15 minWith a plan

Practice setErrors, steps, layouts and MEight tasks mixing errors, step repairs, fill down, top rows and M formulas.With a plan

ProjectTurn a printed report into dataRebuild a regional sales report made for printing into a clean table, then total it by region.With a plan

Practice setReshape planning exportsChoose a layout transformation from the reporting grain and preserve unknown and zero values.With a plan

Operational reporting

Build service and inventory reports and return checked exposure and capacity findings.

5 lessons

ProjectService backlog by teamPrepare an open-service queue with clean team names and a priority-hours total for each team.With a plan

ProjectInventory count auditBuild a reconciled count summary from a warehouse sheet that includes a duplicated scan and inconsistent location text.With a plan

Case studySupplier exception exposureQuantify open supplier exceptions while keeping unknown amounts separate from zero exposure.With a plan

Case studyCapacity shortfallIdentify the team with the largest positive shortage of scheduled hours compared with available hours.With a plan

Practice setOperational reportingTrim Team and group by Team, summing Hours into Total hours.With a plan

Expert

M language

Edit complete M programs with functions, lists, records and table results.

6 lessons

43M: let bindings and the return valueReturn only rows whose Status is Open, using a named let binding.4 exercises · 14 minWith a plan

44M: each and function parametersAdd Extended equal to Amount multiplied by Units.4 exercises · 14 minWith a plan

45M: lists and list transformationsReturn one column Total and one row containing the sum of Amount.4 exercises · 14 minWith a plan

46M: records and optional fieldsReturn Name and Value rows for the record Team = Cedar and Limit = 90.4 exercises · 14 minWith a plan

47M: table functions and output grainReturn a distinct one-column table of Status values.4 exercises · 14 minWith a plan

Practice setM values and table resultsReturn one value Batches equal to the count of Raw rows.With a plan

Reusable refresh

Parameterize rules, handle errors, combine files and explain folding limits.

6 lessons

49M: parameters and reusable functionsSet Minimum to 100 and return batches with Amount at least Minimum.4 exercises · 14 minWith a plan

50M: try and otherwiseAdd Parsed as Number.FromText(Reading), using null when conversion fails.4 exercises · 14 minWith a plan

51Combine files from a folderCombine selected files with per-file headers, provenance and a deliberate schema.4 exercises · 18 minWith a plan

52Query folding and connector limitsDistinguish file processing from database pushdown and identify the evidence needed to confirm folding.4 exercises · 12 minWith a plan

ProjectMonthly shipment refreshBuild a repeatable shipment report from two monthly files, keeping unresolved quantities out of usable-unit totals.With a plan

Practice setRepeatable refresh decisionsCombine the two final monthly files with promoted headers and Source.Name.With a plan

Interviews and timed preparation

Explain workplace choices and complete original Power Query preparation sections.

6 lessons

Interview setEasy Power Query decisionsExplain the data decisions behind a practical Power Query workflow.With a plan

Interview setMedium Power Query decisionsExplain the data decisions behind a practical Power Query workflow.With a plan

Interview setHard Power Query decisionsExplain the data decisions behind a practical Power Query workflow.With a plan

Mock examPower Query preparation AA timed, original Power Query preparation section with checked answers and explanations.With a plan

Mock examPower Query preparation BA timed, original Power Query preparation section with checked answers and explanations.With a plan

Practice setFinal Power Query reviewReturn only ID, Team and Units for Open rows.With a plan