Learning goals

Describe Northgate Services' reporting problem in one sentence
Say what Power Query is, what it costs, and which Excel versions run it
Find the BEFORE sheets, the AFTER sheets, and the M scripts in the practice workbook
Read the Settings sheet and state the reporting window

Concepts

Meet Northgate Services

Northgate Services LLC is a 12-person managed-IT and consulting firm in Columbus, Ohio. Seven of the twelve staff bill client time. Like most small firms its size, Northgate runs its business on three subscription systems that were never designed to talk to each other: Fieldlight CRM tracks the sales pipeline, Ledgerly raises the invoices, and Clockwork records timesheets.

Every month the same scramble: the operations lead exports three CSV files, pastes them into one workbook, fixes the same typos by hand, and rebuilds the same report. It takes about four hours, it is different every month, and nobody can prove the numbers are right. For August 2026 the questions the owners actually ask are: how much did we invoice (16,820.00), how much cash landed (10,500.00), what is still open (10,415.00), what is overdue (930.00), and did the team bill its capacity (892 of 1,050 hours).

This course rebuilds that monthly report once, properly, with Power Query. After that, next month's close is one folder drop and one refresh.

💡 Tips:

The case is fixed: August 2026, close date Aug 31, 2026. Every number in every lesson comes from that one month, so you can always check yourself.

What Power Query is, and where it runs

Power Query is the import-and-shape engine built into Excel. It lives on the Data ribbon as Get Data (in Excel 2016 and 2019 the tab itself is called Power Query in older builds; the features are the same). You point it at a source, click your way through transforms, and it records every click as a repeatable step. Next month you drop the new file in the same folder and click Refresh All.

It is free and included, but only on desktop: Excel 2016 and later, and Microsoft 365, on the Windows and Mac desktop apps. It does not run in Excel for the web -- if your only copy of the workbook lives in a browser, the queries will not refresh. Northgate keeps the reporting workbook on the operations lead's desktop and shares the finished report as values.

💡 Tips:

Check before you promise: File > Account > About Excel shows the version. No 'Get Data' on the Data ribbon means the workbook was opened in the browser, not that Excel is missing the feature.

The queries are written in a language called M. You will read a little M in this course, but you build everything by clicking; the M Code Reference sheet is there for the day you want to read or paste a whole query.

How this course practices Power Query without running it

The browser practice engine on this page is a spreadsheet, not Power Query -- it cannot execute queries. So every lesson models the same before-and-after pair: the raw staging sheet shows the data exactly as the CSV delivers it, and the query-output sheet shows the tidy table the query loads. The written steps tell you exactly what to click in your own desktop Excel to turn the BEFORE into the AFTER, and the pale-green verification formulas let you prove the AFTER is right without leaving the browser.

The workbook keeps 13 sheets in a deliberate order: Read Me and Settings first; three raw staging sheets (CRM Export, Billing Export, Hours Grid) plus the Service Lines master data; four query outputs (Clean Pipeline, Invoice Facts, Hours Tidy, Utilization); then Monthly Report, M Code Reference, and Query Log.

=COUNTA('CRM Export'!$B$5:$B$1004)

Counts the opportunity IDs staged on the raw CRM sheet: 12 rows for August. The Clean Pipeline output holds 10, because the cleaning query in lesson 4 removes one duplicate re-export and one row whose amount cannot be read.

💡 Tips:

Amounts exclude sales tax; US sales-tax rules vary by state and service type, so confirm the treatment with your state Department of Revenue before invoicing.

Practice

Fixed case (August 2026). The exercise opens on the Read Me sheet; every other sheet stays one tab click away. Nothing you type in the browser is saved -- reset with the button or by switching lessons.

Read the contract — On the Read Me sheet, read the rows 'What this file is' and 'You need desktop Excel'. Note the rule that matters most this month: staging sheets take the CSV text as-is, and fixes belong in the query, never in the staging copy.
Check the reporting window — Switch to Settings. Cell C6 is the period start (Aug 1, 2026), C7 the close date (Aug 31, 2026), C10 the monthly capacity of 150 hours per consultant, and C12 the source folder C:\Reports\Exports that every import reads. These four values drive every dependent formula in the workbook.
Tour the BEFORE sheets — Open CRM Export, Billing Export, and Hours Grid. Everything on CRM Export and Billing Export is text -- dates, amounts, all of it -- because that is exactly how a CSV arrives. Find the day-first dates on Billing Export (04/08/2026) and the weekly matrix on Hours Grid.
Tour the AFTER sheets and the M scripts — Open Clean Pipeline, Invoice Facts, Hours Tidy, and Utilization, then Monthly Report. Finish on M Code Reference: twelve queries, each with its full script. You will recognize every one of them by lesson 11.

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:

☐ Read the Read Me rows covering the desktop-Excel requirement and the before/after sheet map
☐ Verified Settings: report month August 2026, period start Aug 1, 2026, close date Aug 31, 2026, capacity 150 hours
☐ Confirmed on my own computer that Power Query is available (Excel 2016 or later, or Microsoft 365 desktop -- not Excel for the web)
☐ Verified the baseline: 12 raw CRM rows staged against 10 cleaned pipeline rows

⚡ Lesson 01 · The monthly reporting scramble