Learning goals

Explain why the Clockwork matrix is unreadable to formulas and queries
Unpivot the six week columns into one Week Start column and one Hours column
Rebuild the tidy table's 42 rows and the Month column that follows from them
Rebuild the In Report Month check formula and confirm the July weeks drop out

Concepts

Wide for humans, narrow for machines

Clockwork exports timesheets the way people read them: one row per consultant, one column per week -- Week of Jul 20, Week of Jul 27, Week of Aug 3, and so on. That crosstab is fine for eyeballing and useless for computing. There is no single Hours column to sum, no week to filter on, and every new week adds a column, which breaks any formula that referenced the old shape.

The tidy alternative is one row per consultant per week: Consultant, Role, Week Start, Hours. Seven consultants times six weeks is 42 narrow rows that group, filter, and pivot without complaint. This single reshape is the highest-value transform in most reporting workbooks -- budgets by month, headcount by quarter, and sales by region all arrive as crosstabs and all want the same treatment.

💡 Tips:

A quick smell test: if you are about to write a formula that names individual columns (Jul + Aug + Sep), you are about to unpivot-by-hand. Let the query do it once instead.

Unpivot Other Columns

The click: select the columns that identify a row (Consultant and Role), right-click, and choose Unpivot Other Columns. Power Query turns every remaining column into two: Attribute (the old column header, 'Week of Jul 20') and Value (the cell's number). Rename them Week Start and Hours, then parse the date out of the header text -- the Hours Tidy script uses Text.AfterDelimiter to keep what follows 'of ', appends the year, and converts with the en-US locale.

One more cleanup hides in this query: the staged grid carries 'Sam Lindgren ' with a trailing space, so the unpivot step begins with Text.Trim on Consultant. Without it, 'Sam Lindgren' and 'Sam Lindgren ' would group as two different people in lesson 8.

=IF(MONTH($D5)=MONTH(Settings!$C$7),"Yes","No")

The In Report Month check on Hours Tidy: if the row's Week Start month equals the close date's month (Settings!$C$7 is Aug 31, 2026), the row belongs to the report. MONTH of a real date returns 8 for every August week and 7 for the two July weeks -- impossible to compute on the original crosstab, where the week lived inside a column header.

Derive the Month column

Once Week Start is a real date, the month follows: Date.ToText with the format 'MMM yyyy' produces 'Jul 2026' and 'Aug 2026'. That derived column is what the utilization query in lesson 8 filters and groups on -- never a hand-typed month label, which is where month-of-year errors are born.

The finished Hours Tidy table is deliberately boring: 42 identical-shaped rows, sorted by consultant then week. Boring is the goal. Every interesting number in the rest of the course -- 892 August hours, 84.95 percent utilization -- is now one Group By away.

💡 Tips:

Weeks that straddle a month boundary belong to the month they start in. Northgate's weeks start Monday, so the Jul 27 week counts as July even though it ends in August -- state the rule once in the Query Log and it stops being an argument.

Practice

The exercise opens on the Hours Tidy output (AFTER); the BEFORE state is the Hours Grid tab. Cell G5 is blank -- you retype the In Report Month formula. Nothing is saved in the browser.

Count the reshape — Rows 5 through 46 hold the 42 tidy rows: seven consultants times six weeks. Dana Whitfield's six rows read 34, 36, 38, 37, 36, and 35 hours -- the same numbers that sat across one row of the grid, now stacked.
Retype the report-month check — Cell G5 is empty. Type =IF(MONTH($D5)=MONTH(Settings!$C$7),"Yes","No") and press Enter: Yes, because Dana's Week of Aug 3, 2026 falls inside the report month. The same formula returns No on the Jul 20 and Jul 27 rows.
Check the Month column — Column F reads Jul 2026 for fourteen rows (two weeks times seven consultants) and Aug 2026 for the other twenty-eight. It was derived from Week Start, not typed -- which is why nothing can drift.
Compare with the BEFORE sheet — Flip to Hours Grid: seven wide rows, six week columns, and the trailing-space typo in Sam Lindgren's name. One Unpivot Other Columns plus a trim turned it into something a query can group.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

☐ Retyped =IF(MONTH($D5)=MONTH(Settings!$C$7),"Yes","No") into G5 and got Yes
☐ Confirmed the tidy table holds 42 rows (7 consultants x 6 weeks) with the two July weeks flagged No
☐ Verified the baseline: 892 August hours sit in the 28 August rows of this table (1,050 is the capacity they are measured against)

⚡ Lesson 05 · Unpivot the weekly grid