🎯 What You'll Learn

📋 Before You Begin

📋 Session Overview

🟢 Beginner ⏱ ~2 min

This 3-hour hands-on workshop is the final Excel session before students move to Power BI Desktop. It is structured as 20% demo / 80% guided practice, designed for students who already know Google Sheets and the Office.com interface.


Core philosophy: Everything taught today is a stepping stone. Tables → Datasets. PivotTables → Matrix Visuals. Power Query (Excel) → Power Query (Power BI). The tools change; the thinking doesn't.

💡 Instructor Mindset: Every 15 minutes, pause and ask — "How do you think we'll do this in Power BI?" Then reveal: "Great news — you already know 80% of it."

🔹 Block 1: Excel Tables & Formula Foundations

🟢 Beginner ⏱ 50 min
1
Quick Warm-up
⏰ 0–10 min

Open Sales_Raw.xlsx in Office.com. Identify at least 3 data quality issues — mixed case text, blank rows, inconsistent column names.

🔗 Power BI Bridge: Power BI needs clean, structured data — messy source files cause refresh errors and broken visuals.
2
Convert to Tables (Ctrl+T)
⏰ 10–25 min
  • Select your data → press Ctrl+T → check "My table has headers"
  • Rename the table: Table Design → Table Name: tbl_Sales
  • Add a new row at the bottom — observe how the table auto-expands
🔗 Power BI Bridge: Power BI imports named Excel Tables reliably. Unnamed cell ranges cause refresh errors and import inconsistencies.
3
Essential Formulas Lab
⏰ 25–40 min

Practice these formulas in new calculated columns:

  • =XLOOKUP([ProductID], tbl_Products[ProductID], tbl_Products[ProductName])
  • =IF([Quantity]>10, "Bulk", "Standard")
  • =SUMIFS(tbl_Sales[Revenue], tbl_Sales[Region], "East")
🔗 Power BI Bridge (DAX translation): XLOOKUP → RELATED() | IF → IF() | SUMIFS → CALCULATE()
4
Structured References Demo
⏰ 40–50 min

Compare =SUM(tbl_Sales[Revenue]) vs =SUM(B2:B100). Write 3 formulas using TableName[ColumnName] syntax.

🔗 Power BI Bridge: Power BI uses column references exclusively — Sales[Revenue] — you will never write B2:B100 again.
💡 Instructor Tip: "In Power BI, you'll never write B2:B100 again. Tables teach you to think in column names — and that's the entire mindset shift."

🔹 Block 2: PivotTables + Data Cleaning

⚡ Intermediate ⏱ 55 min
1
PivotTable Deep Dive
⏰ 50–70 min
  • Insert → PivotTable → Choose tbl_Sales as source
  • Drag: Rows = Region | Values = Sum of Revenue
  • Add filters: Date slicer + Product category dropdown
  • Change calculation: Show Values As → % of Grand Total
🔗 Power BI Bridge: PivotTables = Power BI's Matrix visual — same drag-and-drop aggregation logic, with added interactive cross-filtering.
2
Data Cleaning Lab (Power Query)
⏰ 70–85 min
  • Open Power Query: Data → From Table/Range
  • Remove unnecessary columns (e.g., internal notes)
  • Change data types: text → date for Order Date column
  • Filter rows: exclude test orders (rows where Notes contains "TEST")
  • Trim whitespace using Text.Trim() on CustomerID column
🔗 Power BI Bridge: The Power Query interface is identical in Excel and Power BI Desktop — skills transfer 1:1, including the M code behind every step.
3
Validation & Quality Checks
⏰ 85–105 min
  • Data → Data Validation: restrict Region column to an allowed list
  • Data → Remove Duplicates on CustomerID
  • Use =TRIM(), =PROPER(), =ISBLANK() to flag remaining issues
🔗 Power BI Bridge: Clean data in Excel means less transformation needed in Power BI — garbage in = garbage out in both tools.
🔄 Mini-Challenge: "Clean this messy customer list using Power Query, then summarize sales by cleaned region in a PivotTable."

🔹 Block 3: Data Modeling + Power BI Preview

🔴 Advanced ⏱ 75 min
1
Primary / Foreign Keys Lab
⏰ 105–130 min
  • Use two tables: tbl_Orders + tbl_Customers
  • Identify Primary Key: CustomerID in tbl_Customers — each value is unique
  • Identify Foreign Key: CustomerID in tbl_Orders — same value repeats across orders
  • Simulate relationship: use XLOOKUP to pull CustomerName into the Orders table
  • Naming convention: use TableNameID format for primary keys
🔗 Power BI Bridge: Power BI's Model View uses these exact concepts — relationships replace VLOOKUP/XLOOKUP entirely. You drag PK to FK and the engine handles the rest.
2
Power Query: Merge Queries
⏰ 130–155 min
  • Open Power Query Editor: Home → Merge Queries
  • Join tbl_Orders + tbl_Customers on CustomerID
  • Expand: select CustomerName and Region columns from the joined table
  • Load to worksheet as: tbl_Orders_Enriched
🔗 Power BI Bridge: This is exactly how you build relationships in Power BI — but visually, without any formulas. The Merge Queries step becomes a Model View relationship.
3
Power BI Preview + Wrap-up
⏰ 155–180 min
  • 🎬 Instructor demo: Import tbl_Orders_Enriched into Power BI Desktop
  • Show: PivotTable logic → Matrix visual (same rows/values fields)
  • Show: Excel slicers → Power BI slicer visuals
  • Show: Relationships view = what we simulated with XLOOKUP
🔗 Final Message: "Everything you practiced today is the foundation. Next week, we scale it."

📦 Materials Checklist

🟢 Reference ⏱ ~3 min

✅ Assessment & Readiness Rubric

⚡ Intermediate ⏱ ~4 min

Formative Checks (During Session)

Exit Ticket (Last 10 mins)

In your own words:

  1. Why do we use Excel Tables instead of ranges for Power BI?
  2. What does Power Query do that regular Excel formulas can't?
  3. How is a Foreign Key different from a Primary Key?

Power BI Readiness Rubric

SkillNot ReadyGetting TherePower BI Ready
Tables Uses ranges only Converts to Table but doesn't name it Names Tables + uses structured references
Cleaning Manual find/replace Uses basic Power Query steps Chains 3+ transformations logically
Modeling Doesn't distinguish keys Identifies PK/FK in examples Simulates relationship with XLOOKUP
Mindset Thinks in cells (B2, C5) Mix of cells + column names Thinks in columns & tables exclusively

🧠 Practice Quiz

⚡ Intermediate ⏱ ~5 min

Test your Power BI Readiness — 6 questions covering all three blocks.

1. What keyboard shortcut converts a cell range into a structured Excel Table?

Ctrl+E
Ctrl+T
Ctrl+L
Alt+T

2. Which Excel formula syntax does Power BI's DAX most closely mirror?

=SUM(B2:B100) — cell range references
=SUM(tbl_Sales[Revenue]) — structured column references
=SUM(INDIRECT("B2:B100")) — dynamic cell ranges
=ARRAYFORMULA(SUM(B:B)) — Google Sheets syntax

3. In Power BI, what replaces the need to use XLOOKUP or VLOOKUP formulas between tables?

Named Ranges
Power Query Merge
Model View Relationships using Primary/Foreign Keys
CALCULATE() function

4. Which column would be the Foreign Key in a tbl_Orders table that links to tbl_Customers?

OrderID — unique per row in tbl_Orders
CustomerID — repeats across multiple orders
Revenue — numeric aggregation column
OrderDate — timestamp column

5. Which Power Query transformation would you use to combine two tables on a shared column?

Append Queries — stacks rows vertically
Merge Queries — joins tables horizontally on a key column
Pivot Column — rotates values into new columns
Group By — aggregates rows by a category

6. What is the Power BI equivalent of an Excel PivotTable?

Bar Chart visual
Card visual
Matrix visual
Table visual

🏁 Key Takeaways