Week 12: Capstone — End-to-End Analytics Project

1. The Project Brief

Build a Regional Retail Performance Dashboard. You're handed twelve monthly CSV exports of order data across several regions and stores — messy, inconsistent, and never meant to be analyzed directly. It's small enough to finish in one sitting, but touches every module in this course: Power Query cleanup, SQL joins/aggregation/window functions, and a modeled, measured, published Power BI report.

The finished project needs to answer:

• Which regions and stores are growing, and which are declining, quarter over quarter?
• Which product categories drive the most revenue, and how has that mix shifted over the year?
• Who are the top-performing stores, ranked, with a visible trend line and the ability to drill into any one store's detail?

2. Phase 1: Clean & Shape in Excel

The raw monthly exports (Week 4 territory) share a structure but not a clean one: inconsistent date formats, a stray subtotal row at the bottom of some files, a store id column that's sometimes text and sometimes a number depending on the month it was exported.

1. Get Data → From Folder, pointing at the folder holding all 12 monthly CSVs
2. Combine & Transform -- Power Query appends every file automatically
3. Remove any row where OrderID is blank (catches stray subtotal rows)
4. Change StoreID's data type to Text explicitly, for every file consistently
5. Parse OrderDate with a fixed format, correcting any month that used
   a different date format than the rest
6. Remove Duplicates on OrderID, in case a month's export overlapped another

From Folder is Week 4's Append Queries idea taken one step further — instead of manually appending 12 separately-imported queries, it combines every file in a folder in one pass, and re-runs automatically if a 13th file is ever dropped in later. Export the cleaned result as one flat file — that's what SQL loads in Phase 2.

3. Phase 2: Query in SQL

With clean data loaded into orders (plus reference stores, regions and products tables), answer the brief's questions directly:

CREATE OR REPLACE VIEW region_quarterly_totals AS
SELECT r.region_name,
       DATE_TRUNC('quarter', o.order_date) AS quarter,
       SUM(o.amount) AS total_amount
FROM orders o
JOIN stores s  ON s.store_id  = o.store_id
JOIN regions r ON r.region_id = s.region_id
GROUP BY r.region_name, DATE_TRUNC('quarter', o.order_date);

SELECT region_name, quarter, total_amount,
       LAG(total_amount) OVER (PARTITION BY region_name ORDER BY quarter) AS prev_quarter,
       ROUND(
         100.0 * (total_amount - LAG(total_amount) OVER (PARTITION BY region_name ORDER BY quarter))
         / NULLIF(LAG(total_amount) OVER (PARTITION BY region_name ORDER BY quarter), 0), 1
       ) AS qoq_growth_pct
FROM region_quarterly_totals
ORDER BY region_name, quarter;

This is Week 8's view pattern plus Week 7's LAG window function, applied directly to the brief's first question — the same shape answers the second question by swapping region_name for category, and the third by ranking with RANK() over total revenue per store instead of computing growth.

4. Phase 3: Model & Visualize in Power BI

Connect Power BI to the SQL database (or the views built in Phase 2 directly) and build the star schema from Week 9: Orders as the fact table, Stores, Regions, Products and a generated DateTable as dimensions, related with one-to-many relationships.

Total Revenue = SUM(Orders[amount])

Revenue QoQ % Change =
VAR CurrentQuarter = [Total Revenue]
VAR PrevQuarter =
    CALCULATE([Total Revenue], DATEADD(DateTable[Date], -1, QUARTER))
RETURN DIVIDE(CurrentQuarter - PrevQuarter, PrevQuarter)

Store Rank = RANKX(ALL(Stores[store_name]), [Total Revenue], , DESC)

Build a summary page (regional trend line, category revenue mix as a stacked bar, a top-stores table sorted by Store Rank) and a store-detail drill-through page, exactly as practiced in Week 11 — then publish to Power BI Service.

5. Capstone Project

6. Course Recap

Twelve weeks, three tools, one connected skill set:

• Weeks 1, 3–4 — Excel formulas, lookups, PivotTables, and Power Query/Power Pivot at scale.
• Weeks 2, 5–8 — SQL from a first SELECT through joins, aggregation, window functions, views and query optimization.
• Week 9 — bringing Excel and SQL data into Power BI and modeling it as a proper star schema.
• Week 10 — DAX measures, filter context, and time intelligence.
• Week 11 — dashboard design, interactivity, and publishing with refresh and row-level security.
• Week 12 — all of it, together, in one project you built and can explain.

That's the full Excel, SQL & Power BI path. From here, the Python course pairs directly with what you've built here — automating the exact cleaning and analysis steps from Phases 1–2 with pandas instead of clicking through Power Query and writing every query by hand — and the LLM & ML course is the natural next stop once "describe what happened" starts turning into "predict what happens next."