From messy files to a published dashboard.

Download the data pack

Everything you need for the hands-on lessons and the capstone. Each file is deliberately messy, the flaws are the curriculum. Open the CSVs in Notepad (not Excel) to see the mess before you fix it in Power Query.

Instructor & extra resources

For anyone teaching or running this as a workshop.

Setup, the interface & how licensing actually works

Install the tool, learn the map of the screen, and understand the license landscape before you build, because the license you have changes how you publish later.

Install Power BI Desktop (free)

Power BI Desktop is where you build, and it's free.

• Best on Windows: Microsoft Store → search "Power BI Desktop" → Install. The Store version auto-updates monthly, which matters. Power BI ships features every month.
• Alternative: download the installer from powerbi.microsoft.com.
• On a Mac? No native version. You'll need Windows via a VM (Parallels) or a cloud PC. The browser Service lets you view, not fully build.

The three views + one door

• Report view (chart icon), build visuals and the dashboard.
• Data view (grid icon), inspect the actual loaded rows.
• Model view (relationship icon), see tables and the lines between them.
• Transform data (Home ribbon), opens the Power Query Editor, the cleaning room.

Licensing, the 3-minute version

• Desktop, free, build anything locally.
• Free account (Service), publish to your own My Workspace; generally can't share.
• Pro, publish to shared workspaces and share reports (viewers also need Pro, unless the workspace is on Premium).
• Premium Per User (PPU). Pro plus bigger/faster features.
• Premium / Fabric capacity, org buys capacity; then viewers can be free. How big companies share widely.

Module 8 turns this into a decision tree.

Getting data, and the emailed-file problem

Connect to files the right way, and learn the single most important sharing concept: static vs. dynamic connections.

Connect to the main file (CSV)

1. Home → Get data → Text/CSV → pick Sales_Raw.csv → Open.
2. A preview appears. Power BI guesses types, and will guess wrong on this messy file. Expected.
3. Do NOT click Load. Click Transform Data.

Static vs. dynamic: the concept that separates amateurs from pros

When you connect to a file, where that file lives determines whether your report can refresh by itself.

Static (fragile): you connect to a path only your machine can see, C:\Users\you\Downloads\Sales_Raw.csv. When you publish to the cloud, the cloud can't reach your Downloads. To update, you open Desktop, re-import, and re-publish by hand every time.

Dynamic (robust): you connect to a place the cloud can also reach, OneDrive for Business, SharePoint, a gateway'd network path, or a database. Now you can schedule automatic refresh: the report re-reads the source on a timer, no human needed.

Why the file's location decides everything

So when a colleague emails you Sales_Report.xlsx every Monday, you have three tiers:

Connect to the emailed Excel dimension

1. Get data → Excel workbook → Products.xlsx → Open.
2. In the Navigator, tick the Products sheet/table.
3. Transform Data (again, always).

Power Query: fixing bad data

Turn Sales_Raw.csv from garbage into a trustworthy table. This is where 70% of real Power BI work happens, and where beginners under-invest.

The same rows, before & after Power Query

Fix data types (the #1 source of wrong numbers)

• OrderDate: text (mixed formats). Change type → Date. Ambiguous US/UK dates? Right-click → Change Type → Using Locale → English (United States). Blanks become null, correct.
• UnitPrice ($1,518.50): Replace $ → nothing, replace , → nothing, then change type → Decimal.
• Units (1,200, N/A): replace ,; convert to Whole Number; then Replace Errors with null (a bad count is unknown, not zero).

Trim & clean whitespace (the invisible bug)

" North " and " Enterprise " look identical to North but are different values, they'll split your totals into two bars. Select the columns → Transform → Format → Trim, then Format → Clean.

Standardize typos & abbreviations

Region still has north, EAST, S., Norht. Quick fix: Replace Values, one per variant. Brittle, a new typo next month slips through.

Remove duplicate rows & handle Discount

• Select all columns → Home → Remove Rows → Remove Duplicates (removes the ~20 exact-duplicate orders).
• Discount mixes blank / 0 / 10% / 0.1. Replace blanks with 0; convert 10% text to 0.10 via a custom column (if Text.Contains([Discount],"%") then …).

Reshaping data into something meaningful

The moves that turn "technically clean" into "actually useful", including your exact ask: collapse many organizations into HR vs Other.

Add revenue, duplicate columns, extract parts

• Custom Column LineRevenue = [Units] * [UnitPrice] * (1 - [Discount]).
• Duplicate Column (right-click) when you need the same source shaped two ways, keep the original and a grouped/extracted version. This sets up the HR grouping below.
• Column From Examples, type the output you want for a few rows; Power Query writes the logic.

★ Grouping many categories into few: HR vs Other

Collapse ~22 organizations into two meaningful buckets

Employees_Raw.csv's Organization column has ~22 values (Human Resources, HR, People & Culture, Talent Acquisition, Recruiting, Learning & Development, HR Operations, People Ops, plus Finance, Engineering, IT…). You want just HR vs Other.

Step 1, duplicate Organization → rename the copy OrgGroup (keep the detail).

Step 2, collapse it. Three methods, worst → best:

• A · Replace Values, map each HR variant to HR. Awkward to turn "everything else" into Other.
• B · Conditional Column (recommended). Add Column → Conditional Column: If Organization equals each HR variant Then HR; Else Other. Reads like plain English and is easy to extend. Trim/Clean first so equals matches.
• C · Mapping table (scales to 200 categories), a two-column lookup (Organization → OrgGroup, shipped at data/reference/Org_Grouping_Key.csv), then Merge Queries on Organization and expand OrgGroup. Grouping logic lives in a spreadsheet a business user can edit, no query surgery when a new department appears.

Unpivot: fix "months as columns"

Targets_Wide.csv has Team then one column per month. Power BI wants long data: one row per Team per Month. Select Team → right-click → Unpivot Other Columns. Rename Attribute → MonthLabel, Value → Target; convert MonthLabel to a real date.

Wide → long: what "unpivot" actually does

Append the "next month" emailed file

Sales_December_2025.xlsx just arrived. Manual way: Append Queries. Dynamic way: the Folder connector from Module 1, drop next month's file in the folder, hit Refresh, done. Append needs matching columns/types, exactly why we cleaned types deliberately in Module 2. Then Home → Close & Apply.

The data model & relationships, the "why"

Understand why you split data across tables and connect them, the concept that makes everything after it possible.

The problem relationships solve

Sales has RepID = 1017. That number is meaningless alone, who is 1017? What team? When hired? That lives in Reps. A relationship tells Power BI "the RepID in Sales points to the RepID in Reps." Now every order silently knows its rep's name, team, and region, and you can slice sales by team even though Sales has no team column.

Why not one big table?

Jamming rep name, product, category onto every order, a "flat" table, causes bloat & slowness (names repeat on thousands of rows), inconsistency (same rep spelled two ways), and rigidity (no clean list of all reps, only those who sold). The pro shape is a star schema: one fact table (events + keys + numbers) surrounded by dimension tables (the descriptions you filter by).

Create relationships

Model view → drag Sales[RepID] onto Reps[RepID]; drag Sales[ProductID] onto Products[ProductID]. Double-click a line to inspect:

• Cardinality: Many-to-one (∗:1), many sales to one rep. The healthy default.
• Cross-filter direction: Single (the "1" side filters the "many"). Leave it single unless you have a reason.

Build a proper Date table (do not skip)

Time intelligence (YoY, YTD, running totals) needs a dedicated Date dimension, one row per day, related to Sales[OrderDate].

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2024,1,1), DATE(2025,12,31)),
    "Year",      YEAR([Date]),
    "MonthNo",   MONTH([Date]),
    "Month",     FORMAT([Date],"MMM"),
    "MonthYear", FORMAT([Date],"MMM yyyy"),
    "Quarter",   "Q" & FORMAT([Date],"Q")
)

Sort Month by MonthNo; relate Date[Date] → Sales[OrderDate]; then Table tools → Mark as Date Table.

Measures & DAX

Write calculations that respond to whatever the user filters. This is where a report becomes intelligent.

Measure vs. calculated column

A column is computed once per row at refresh, and stored (use for row attributes you filter by). A measure is computed on the fly for the current filter context (use for all aggregations).

Core measures, build in order

Total Revenue   = SUM ( Sales[LineRevenue] )
Total Units     = SUM ( Sales[Units] )
Order Count     = DISTINCTCOUNT ( Sales[OrderID] )
Avg Order Value = DIVIDE ( [Total Revenue], [Order Count] )

Use DIVIDE, never /, it handles divide-by-zero gracefully (returns blank, not an error).

SUMX, then CALCULATE, the most important function in DAX

Total Revenue (SUMX) =
    SUMX ( Sales, Sales[Units] * Sales[UnitPrice] * (1 - Sales[Discount]) )

Enterprise Revenue = CALCULATE ( [Total Revenue], Reps[Team] = "Enterprise" )

% of Region Total =
    DIVIDE ( [Total Revenue], CALCULATE ( [Total Revenue], ALL ( Sales[Region] ) ) )

SUMX walks each row then sums. CALCULATE changes the filter context, add a filter, or remove one with ALL.

Time intelligence & % of target

Revenue LY  = CALCULATE ( [Total Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Revenue YoY % = DIVIDE ( [Total Revenue] - [Revenue LY], [Revenue LY] )
Revenue YTD = TOTALYTD ( [Total Revenue], 'Date'[Date] )

Total Target = SUM ( Targets[Target] )
Revenue vs Target % = DIVIDE ( [Total Revenue], [Total Target] )

Visualizations: how to think about them

Stop asking "what chart looks cool" and start asking "what question am I answering." Chart choice is a communication decision, not decoration.

Start from the question, then pick the visual

When to use what, the six workhorses

Data storytelling, what you show is the story

The same numbers can inform or mislead depending on which data you show and how you display it. That choice is the real analysis, the chart is your argument, not decoration. A dashboard that shows everything says nothing; a good one answers a decision at a glance.

Two habits carry most of it: (1) show the comparison that answers the question and cut the rest, and (2) make the takeaway readable in seconds, sort it, start value axes at zero, use one colour that means something, label directly, and write the title as the insight ("Laptop Pro drives 22%"), not the field name.

Same data, two ways, the display makes or breaks the story

Build one, then format so it earns its keep

• Clustered column → Reps[Team] on X, [Total Revenue] on Y. Add a Year slicer, it responds live.
• Sort bars by value descending (the "…" menu). Sorted bars are instantly readable.
• Axis starts at zero for bar/column, truncating exaggerates differences (that's how charts lie).
• Titles as answers, not field names. Kill leftover "Sum of LineRevenue."
• Color means something (highlight the max, red/green vs target), never rainbow decoration.

Assembling an impressive dashboard

Go from "a page of charts" to something designed that answers a decision. Design guides the eye to the decision fast, it isn't decoration.

Decide the story first

Write the headline before placing a visual: "How are we tracking to target this year, who's driving it, and where's the risk?" Everything on the page serves that sentence; anything else goes on a detail page.

Layout: the F-pattern & hierarchy

• Top strip: 3–5 KPI cards, the headline numbers (Revenue, YoY %, vs Target %, Orders). The "answer in 3 seconds" row.
• Upper-middle: the main trend (revenue over time vs. target), biggest real estate.
• Lower: breakdowns, by team, region, product.
• Consistent slicers (Year, Region, Team) where users expect them.
• Alignment & whitespace, snap to a grid. Aligned edges read as "designed"; ragged edges read as "amateur."

A consistent visual system

• Theme: View → Themes, define the palette once; every visual inherits it.
• Color with meaning: one accent; reserve red/green strictly for bad/good.
• Typography: one font, sized for hierarchy, big KPI numbers, medium titles, small labels.
• Bookmarks + buttons: build toggles ("By Region" vs "By Product"), a slide-in slicer panel, a reset button, page navigation.

Publishing & sharing

Get your dashboard off your laptop and into colleagues' hands, with refresh, and pick the right license path.

The mental model: Desktop → Service → Audience

You build in Desktop (a .pbix), publish to the Service (app.powerbi.com), where reports live in workspaces. You share by granting workspace access, or by packaging a workspace into an App.

How a report gets from your laptop to an audience

Personal vs. Pro vs. Workspace

Which license do I actually need?

• Just me, my machine: Desktop (free).
• Just me, in the cloud/phone: free account, My Workspace.
• Share with a few colleagues: Pro for you and each viewer.
• Share widely without buying everyone Pro: Premium/Fabric capacity → viewers can be free.
• You need big models / faster refresh / paginated reports: Premium Per User (PPU).

Scheduled refresh, closing the static-vs-dynamic loop

1. In the Service, find your semantic model → Settings.
2. Data source credentials: sign in to the source.
3. Scheduled refresh: on, pick times (Pro: 8/day; Premium: 48/day).
4. Only works if the source is cloud-reachable, exactly why Module 1 insisted on OneDrive/SharePoint/Folder.

Build & publish a "Sales Performance" dashboard

1. Get data from all files; append Sales_December_2025.xlsx (bonus: via a Folder connection).
2. Clean in Power Query: types, dates, currency text, whitespace, region typos, duplicates.
3. Reshape: LineRevenue; unpivot Targets; produce OrgGroup = HR vs Other.
4. Model: star schema with a marked Date table; relate both facts → Date; handle orphan RepID 9999.
5. Measures: Revenue, Orders, AOV, YoY %, YTD, vs Target %.
6. Visualize: KPI row, trend vs target, by team/category/region, sorted, formatted, cross-filtering.
7. Design: one theme, aligned grid, story titles, Year/Region/Team slicers.
8. Publish to a shared workspace; set scheduled refresh; note which license each viewer needs.

Grading rubric

Quick reference

Power Query cleanup

DAX starter pack

Total Revenue       = SUM ( Sales[LineRevenue] )
Order Count         = DISTINCTCOUNT ( Sales[OrderID] )
Avg Order Value     = DIVIDE ( [Total Revenue], [Order Count] )
Revenue LY          = CALCULATE ( [Total Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Revenue YoY %       = DIVIDE ( [Total Revenue] - [Revenue LY], [Revenue LY] )
Revenue YTD         = TOTALYTD ( [Total Revenue], 'Date'[Date] )
Total Target        = SUM ( Targets[Target] )
Revenue vs Target % = DIVIDE ( [Total Revenue], [Total Target] )

You built a real thing, from messy files to a published dashboard, and you understand why each step exists. That "why" is what makes you dangerous with Power BI, now go build the real one.