Exercise: The Bike Shop's Monthly Report

Turn Maple Street Bikes' messy October exports into the owner's monthly report, cleaning the sales and rentals CSV files, adding them up per product and per store, changing one setting in a JSON file and making a web page of the totals, while a Pester check tells you which steps are right.

Journeyman · PowerShell · Operating System · Updated

It's the first of November at Maple Street Bikes, which now has a third shop, Hillcrest. The till and the rental desk have exported October, as messy as ever, and the owner wants the month's report by lunchtime. Bookkeepers and office managers spend a morning on this every month, in Excel. You'll do it with the commands from this chapter, five steps, each saved as a file, and a check tells you which steps are right. Next month, the same commands do it again in a minute.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-powershell-the-bike-shops-monthly-report, also linked from the Exercise box beside the lesson. Click Code, then Download ZIP, and unzip it into your terminal-practice folder. Or clone it with Git.

The folder holds:

  • exports: October's sales from the till (sales-2026-10.csv) and October's rentals from the booking app (rentals-2026-10.csv), as exported. Don't change them.
  • shop-settings.json: the shop's settings, which you change in step 4.
  • README.md: the five steps.
  • Check.Tests.ps1: the check that reads the files you make. You don't need to change it.

What to do

Open PowerShell 7 in the exercise folder, the one with Check.Tests.ps1 in it, and look at the sales first:

Import-Csv exports\sales-2026-10.csv | Select-Object -First 6 | Format-Table

Date       Store     Item       Qty Price
----       -----     ----       --- -----
2026-10-01 Downtown  Helmet     2   $39.99
2026-10-02 Hillcrest Inner Tube 5   $6.99
2026-10-03 Riverside Bike Lock  1   $24.50
2026-10-04 Downtown  City Bike  1   $449.00

2026-10-06 Hillcrest Bike Light 2   $18.75

Prices with dollar signs, a blank row, and a column called Item where the rest of the shop's files say Product. The rentals export is no tidier. Run every command from the exercise folder, and save every new file there too, beside Check.Tests.ps1, not inside exports. The five steps, also in the folder's README.md:

  1. Clean the sales. clean-sales.csv, with the columns Date, Store, Product, Qty as a whole number, Price as a plain number, and a new Total, the Qty times the Price. No blank rows.
  2. Totals per product. product-totals.csv, from the clean sales: Product, Sold and Revenue, the biggest Revenue first.
  3. Rental income per store. rental-income.csv, from the rentals export: Store, Rentals and Income, the biggest Income first.
  4. Change a setting. In shop-settings.json, the report's month goes from 2026-09 to 2026-10, with everything else kept as it was. Copy the file to shop-settings.backup.json first.
  5. The report. report.html, a web page of the product totals with the heading Maple Street Bikes: October 2026 and a <style> block of your own.

Check your work

The check uses Pester, PowerShell's test tool. If you haven't installed it yet, install it once:

Install-PSResource Pester

Then, from the exercise folder:

Invoke-Pester

At first all five tests fail. That's expected: each step you finish turns its test green. A failing test says which step it is, what it expected and what it found.

Made a mistake? Run the step again: Export-Csv and Set-Content replace what a file held. If shop-settings.json went wrong, copy your backup over it. To start over, download the exercise again.

If PowerShell says the check can't be loaded because running scripts is disabled or the file is not digitally signed, Windows has marked the downloaded files as coming from the internet. From the exercise folder, run Get-ChildItem -Recurse | Unblock-File once, then Invoke-Pester again. Why a Script Won't Run: The Execution Policy, in Module 5, explains what that does.

Hints

  • Look before you save. End each pipeline with Format-Table until the screen shows the right rows, then swap it for Export-Csv, as Saving Data: Export-Csv and Cleaning a CSV does.
  • Dollar signs out, numbers in. .Replace(' , ''), then [decimal]. [decimal] understands a comma between thousands, so $1,299.00 needs nothing more.
  • Total is a recipe too. The Total column's calculated property can turn Qty and Price into numbers itself, then multiply them with * (read "times").
  • Piles, then sums. Counting and Grouping: Measure-Object and Group-Object and Columns You Make Yourself: Calculated Properties show how to add up each pile's Group.
  • Mind the depth. JSON: ConvertFrom-Json and ConvertTo-Json shows why the store hours need -Depth. Check the warning before you save.
  • Head and heading. In Reports: ConvertTo-Html and Out-GridView, -Head takes the title and the style, and -PreContent takes the heading.

When you're done

Every test is green, and Pester's last line says:

Tests Passed: 5, Failed: 0, Skipped: 0, Inconclusive: 0, NotRun: 0

Open your report with Invoke-Item report.html. Then, on Windows, try Import-Csv clean-sales.csv | Out-GridView and filter it to one shop: which product sold best at Hillcrest?

Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.

  1. On GitHub, click +, then New repository. Name it (for example exercise-powershell-the-bike-shops-monthly-report), choose Public, and click Create repository. Leave out the README: your folder already has one.

  2. In a terminal in your exercise folder (PowerShell works), send your work to your new repo. If you downloaded the ZIP:

    git init -b main
git add .
git commit -m "My solution"
git remote add origin https://github.com/your-name/exercise-powershell-the-bike-shops-monthly-report.git
git push -u origin main

    If you cloned the starter instead, point your copy at your own repo, then commit and push:

    git remote set-url origin https://github.com/your-name/exercise-powershell-the-bike-shops-monthly-report.git
git add .
git commit -m "My solution"
git push -u origin main

  3. Paste your repo's address, such as https://github.com/your-name/exercise-powershell-the-bike-shops-monthly-report, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

So what

A monthly report that used to take a morning of clicking is now five commands you can run again next month, on the next messy export, in a minute.

Key terms

Related