# 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 November 10, 2026

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](https://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:

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

```text
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:

```powershell
Install-PSResource Pester
```

Then, from the exercise folder:

```powershell
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:

```text
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:

   ```powershell
   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:

   ```powershell
   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

- [Import-Csv](https://www.ask-codey.com/glossary/import-csv)
- [Export-Csv](https://www.ask-codey.com/glossary/export-csv)
- [ConvertTo-Json](https://www.ask-codey.com/glossary/convertto-json)
- [ConvertTo-Html](https://www.ask-codey.com/glossary/convertto-html)

## Related

- [Spreadsheets as Objects: Import-Csv](https://www.ask-codey.com/lessons/powershell-import-csv)
- [Saving Data: Export-Csv and Cleaning a CSV](https://www.ask-codey.com/lessons/powershell-export-csv)
- [JSON: ConvertFrom-Json and ConvertTo-Json](https://www.ask-codey.com/lessons/powershell-json)
- [Reports: ConvertTo-Html and Out-GridView](https://www.ask-codey.com/lessons/powershell-reports)

Source: https://www.ask-codey.com/lessons/exercise-powershell-the-bike-shops-monthly-report