The Power BI Fellowship

Beginner Year 0 – 1

Everything here is done in Power BI Desktop with the mouse. If you can finish all five topics you are employable as a junior. Do not skip the date-table assignment: every later level assumes you did it.

Getting data and cleaning it in Power Query

Data: RawOrdersExport DimProduct

Assignments

Turn the messy export into a clean table

A · Guided 45 min · uses RawOrdersExport
  1. Load RawOrdersExport with Get Data → Enter Data.
  2. Remove the first row (report header) and the last row (TOTAL) using Remove Top Rows and Remove Bottom Rows, not by filtering on a value.
  3. Trim and Clean every text column. Rename headers to PascalCase with no spaces.
  4. Fix Order Dt: use Column From Examples or Locale-aware Change Type so all seven date formats become one Date column. Check every row.
  5. Strip $ and " USD" from Unit Price and convert to Decimal Number. Replace "N/A" with null, then convert Qty to Whole Number.
  6. Capitalize Each Word on customer name and Region, so "west " and "WEST" both become "West".
  7. Remove exact duplicate rows. Delete the fully empty LegacyFlag column.
Expected result: 27 rows, 8 columns, no errors, no text-typed numbers. Qty column shows 3 nulls (two "N/A" values and one blank). Exactly 4 distinct Region values.

Reproduce the same clean-up as a reusable query

A · Guided 20 min · uses RawOrdersExport
  1. Right-click your cleaned query → Reference. Name it "Orders Clean".
  2. In the original query, disable Enable Load so only the referenced query lands in the model.
  3. Change one input value in the Enter Data source (e.g. change $199.99 to $209.99) and refresh: the clean query must update without a single manual step.
Expected result: Model contains one table, "Orders Clean". Query pane shows the raw query in italics (not loaded).

Join product attributes

A · Guided 20 min · uses RawOrdersExport, DimProduct
  1. Load DimProduct.
  2. In Orders Clean, Merge Queries with DimProduct on product name. Notice that "TRAIL CHEF STOVE" does not match: fix casing before the merge, not after.
  3. Expand only Category and ProductKey.
  4. Use Left Outer, then check the row count. Then try Inner and see which rows disappeared and why.
Expected result: All 27 rows keep a Category after the casing fix. The Inner join initially loses 1 row before the fix.

Profile before you trust

A · Guided 10 min · uses RawOrdersExport
  1. Turn on Column quality, Column distribution and Column profile in the View ribbon.
  2. Change profiling from "top 1000 rows" to "entire data set".
  3. Write down, for Qty: % valid, % error, % empty, distinct count, unique count.
Expected result: You can explain the difference between distinct and unique out loud (distinct = different values, unique = values that occur exactly once).

Interview questions

  • What is the difference between Remove Duplicates and Remove Rows → Remove Errors, and when would each silently corrupt your data?
  • Why does Change Type to Date fail on "Jan 7 2026" and "06/02/2026" in the same column, and how do you fix it?
  • Someone applied steps in the wrong order: Change Type before Replace Values. What breaks?
  • What is query folding and how can a beginner check whether a step folds?
  • When is a Reference better than a Duplicate?

Assessment

After Remove Top Rows(1) and Remove Bottom Rows(1) on RawOrdersExport, how many rows remain before de-duplication?

  • 27
  • 28
  • 29
  • 30

Which step turns "$699.99" into a usable number with the fewest chances of error?

  • Change Type → Decimal Number directly
  • Replace "$" with "" then Change Type
  • Split column by delimiter "$"
  • Extract text after delimiter

Column profile says Qty has distinct = 9 and unique = 3. What does unique = 3 mean?

  • 3 rows have Qty
  • 3 different values of Qty appear only once
  • 3 nulls
  • 3 errors

Load RawOrdersExport and produce a table with exactly these column types: OrderRef Text, OrderDate Date, CustomerName Text, Product Text, Qty Whole Number, UnitPrice Decimal, Region Text, Notes Text. Zero error cells. Screenshot the Applied Steps pane.

Order of steps: remove rows → replace values → trim/clean/capitalize → change type → remove duplicates.

Data modeling: a star schema that behaves

Data: FactSales DimProduct DimCustomer DimRegion DimDate

Assignments

Build the Northwind star

A · Guided 30 min · uses FactSales + 4 dims
  1. Load FactSales, DimProduct, DimCustomer, DimRegion and DimDate.
  2. In Model view, delete every auto-detected relationship. Rebuild them yourself: DimProduct[ProductKey] → FactSales[ProductKey], same for Customer, Region and DimDate[Date] → FactSales[OrderDate].
  3. Set every relationship to One-to-Many, single direction, filter flowing from dimension to fact.
  4. Mark DimDate as a date table.
  5. Hide every key column in the fact table from Report view.
Expected result: Four relationships, all 1:*, all single-direction arrows pointing at FactSales. Card visual showing COUNTROWS(FactSales) = 100.

Replace DimDate with a DAX date table

A · Guided 20 min · uses DimDate
  1. Create a new table: Dates = CALENDAR(DATE(2025,1,1), DATE(2026,12,31)).
  2. Add columns Year, MonthNumber, MonthName, YearMonth (as "2026-03"), Quarter, IsWeekend.
  3. Sort MonthName by MonthNumber. Mark as date table. Relate to FactSales[OrderDate]. Delete the loaded DimDate.
  4. Build a column chart of FactSales quantity by MonthName: months must appear Jan, Feb, Mar (not alphabetically).
Expected result: 730 rows in Dates. Chart order Jan → Feb → Mar. Note: the loaded DimDate stops at 31 Mar 2026, which is exactly why you replaced it.

Find the join that lies

A · Guided 20 min · uses FactSales, DimCustomer, DimRegion
  1. Notice that both FactSales and DimCustomer contain a RegionKey.
  2. Create two relationships from DimRegion: one to FactSales[RegionKey], one to DimCustomer[RegionKey]. Power BI will make one of them inactive (dashed line).
  3. Build a table: RegionName, Sum of Quantity. Change which relationship is active and compare.
  4. Decide which is correct for "sales by region where the sale happened" versus "sales by region of the customer's home". Keep the one to FactSales active.
Expected result: Region totals change, but the grand total (333 units) does not: 79 of the 100 sales lines were sold by a rep whose region is not the customer's home region.

Role-playing date

A · Guided 15 min · uses FactSales, Dates
  1. Create a second relationship Dates[Date] → FactSales[ShipDate]. It will be inactive.
  2. Write: Shipped Qty = CALCULATE(SUM(FactSales[Quantity]), USERELATIONSHIP(Dates[Date], FactSales[ShipDate])).
  3. Matrix by MonthName with Sum of Quantity and Shipped Qty side by side.
Expected result: March shows more ordered than shipped because some late-March orders ship in April, outside the fact range.

Interview questions

  • Why single-direction relationships by default? Give one concrete bug bi-directional filtering causes.
  • What makes a table a "proper" date table and what breaks if it is not one?
  • Explain snowflake vs star and why Power BI prefers star.
  • You have a fact table with 100 rows and a Product dimension with 24 rows, but the visual total does not match the fact table sum. Name three causes.
  • When do you actually want a calculated column instead of a measure?

Assessment

FactSales has a ProductKey of 25 that does not exist in DimProduct. What does a table of Category by Quantity show?

  • An error
  • The row is dropped
  • A row with blank Category holding that quantity
  • The quantity spreads across all categories

Which relationship must be inactive?

  • DimRegion → FactSales
  • Dates → FactSales[OrderDate]
  • Dates → FactSales[ShipDate]
  • DimProduct → FactSales

Marking a table as a date table requires:

  • A column of type Date/Time
  • A unique Date column with no nulls and no gaps
  • At least 365 rows
  • A column named "Date"

Build the star and produce a matrix: rows = Category, columns = RegionName, values = Sum Quantity. Report the value for Water × West.

If your value is 0 or blank, check the direction of the Region relationship.

DAX fundamentals: measures that add up

Data: FactSales DimProduct DimCustomer

Assignments

The core measure set

A · Guided 30 min · uses FactSales, DimProduct
  1. Create a dedicated "_Measures" table (Enter Data, one dummy column, delete it after the first measure).
  2. Write: Total Qty, Gross Sales = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice]), Discount Amt = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice] * FactSales[Discount]), Net Sales = [Gross Sales] - [Discount Amt].
  3. Write COGS = SUMX(FactSales, FactSales[Quantity] * RELATED(DimProduct[UnitCost])) and Margin % = DIVIDE([Net Sales] - [COGS], [Net Sales]).
  4. Format every currency measure as $ with 2 decimals, Margin % as percentage with 1 decimal.
Expected result: A card for Net Sales shows a positive number. Margin % between 40% and 65% at the total level. Returns (negative quantity) reduce the totals automatically.

Counting things correctly

A · Guided 20 min · uses FactSales
  1. Write Order Lines = COUNTROWS(FactSales), Orders = DISTINCTCOUNT(FactSales[OrderID]), Customers Buying = DISTINCTCOUNT(FactSales[CustomerKey]).
  2. Write Avg Order Value = DIVIDE([Net Sales], [Orders]).
  3. Put all four in a table by Channel, then by Category. In both, check whether the Orders total equals the sum of the rows. Channel is stored on each line here, so one order can span channels as well as categories.
Expected result: Order Lines total = 100. Orders total = 54. Orders by Channel adds up to 77 and Orders by Category to 89, both more than 54, because one order can span channels and categories. A distinct count never adds up across rows.

First CALCULATE

A · Guided 25 min · uses FactSales, DimProduct
  1. Write Camping Sales = CALCULATE([Net Sales], DimProduct[Category] = "Camping").
  2. Put it in a table by Category. Observe the same number on every row. Explain why to yourself before continuing.
  3. Write Sales All Categories = CALCULATE([Net Sales], REMOVEFILTERS(DimProduct[Category])) and % of Total = DIVIDE([Net Sales], [Sales All Categories]).
  4. Add a Region slicer. % of Total must still sum to 100% within the selected region.
Expected result: % of Total column sums to exactly 100% with or without a region selected.

Calculated columns you can defend

A · Guided 15 min · uses FactSales, DimCustomer
  1. On FactSales add Line Type = IF(FactSales[Quantity] < 0, "Return", "Sale").
  2. On DimCustomer add Tenure Years = DATEDIFF(DimCustomer[JoinDate], DATE(2026,3,31), YEAR).
  3. On DimCustomer add Tenure Band = SWITCH(TRUE(), [Tenure Years] >= 3, "3+ yrs", [Tenure Years] >= 1, "1–2 yrs", "New").
  4. Use Line Type as a slicer and Tenure Band on an axis. Now explain why neither could have been a measure.
Expected result: Slicer shows Sale / Return. Return count is 7.

Interview questions

  • Why SUMX(FactSales, Quantity * UnitPrice) rather than a calculated column Amount then SUM?
  • What does DIVIDE do that / does not?
  • Explain "filter context" with the Camping Sales example.
  • Why do DISTINCTCOUNT totals not add up across rows?
  • What is the difference between BLANK and 0 in a visual, and how do you show 0?

Assessment

CALCULATE([Net Sales], DimProduct[Category] = "Camping") in a table by Category shows:

  • Camping sales only on the Camping row
  • The Camping number on every row
  • An error
  • Blank on non-Camping rows

Which is correct for average price per unit sold?

  • AVERAGE(FactSales[UnitPrice])
  • DIVIDE([Gross Sales], [Total Qty])
  • AVERAGEX(FactSales, FactSales[UnitPrice])
  • SUM(UnitPrice) / COUNTROWS(FactSales)

Tenure Band should be a calculated column because:

  • Measures cannot use SWITCH
  • It must be usable on an axis and slicer
  • It is faster
  • Columns can reference other columns

Build a table: Segment, Net Sales, Margin %, Orders, Avg Order Value. Which Segment has the highest Avg Order Value? State the value.

Outfitter buys in larger quantities; check your DISTINCTCOUNT is on OrderID not SalesKey.

Visualisation and report design

Data: FactSales DimProduct DimCustomer DimRegion

Assignments

The one-page executive summary

A · Guided 45 min · uses Full star
  1. Page size 16:9. Add a title, four cards (Net Sales, Orders, Margin %, Customers Buying), a column chart Net Sales by MonthName, a bar chart Net Sales by Category sorted descending, a map or filled map by State, and a slicer for RegionName.
  2. Use one accent colour for the "good" series and grey for everything else. No more than two font sizes besides the title.
  3. Enable "Edit interactions" and make the category bar chart cross-highlight the month chart but not filter the cards.
  4. Add a page-level filter Line Type = Sale, and a card that shows Return count using CALCULATE with REMOVEFILTERS on that column.
Expected result: A colleague can answer "which region is behind?" within 5 seconds of looking at the page.

Conditional formatting that means something

A · Guided 20 min · uses Full star
  1. Table: ProductName, Net Sales, Margin %.
  2. Margin %: background colour rules → red below 40%, amber 40–55%, green above 55%.
  3. Net Sales: data bars.
  4. Add an icon set on Margin % using the same thresholds. Then remove the data bars: a table with three formats is noise.
Expected result: Discontinued products cluster in the red/amber bands.

Slicer behaviour

A · Guided 15 min · uses Full star
  1. Add a Category slicer (list), a Date slicer (between), a LoyaltyTier slicer (dropdown, multi-select with Ctrl off).
  2. Sync the Category slicer to a second page via View → Sync slicers.
  3. Add a "Clear all slicers" button using a bookmark with slicers reset.
Expected result: Changing Category on page 1 changes page 2; clicking the button resets both.

Matrix mastery

A · Guided 20 min · uses Full star
  1. Matrix rows: Category → SubCategory, columns: MonthName, values: Net Sales.
  2. Turn on stepped layout off, subtotals per level, expand all one level down.
  3. Add Margin % as a second value and switch "Show on rows" so values stack vertically.
  4. Apply conditional formatting to only Net Sales, not to subtotals (there is a toggle).
Expected result: Matrix reads like a financial statement, not a spreadsheet dump.

Interview questions

  • When is a pie chart acceptable?
  • What is the difference between cross-filter and cross-highlight?
  • How do you make a report load faster from the visual side alone?
  • Why should you avoid implicit measures (dragging a numeric column onto a visual)?
  • A stakeholder asks for a "dashboard". What do you ask before you build?

Assessment

You want the total row of a table to show "Total" instead of blank in the first column. Where?

  • Format the column
  • Format visual → Totals → Row label
  • Write a measure
  • Not possible

A visual shows 24 products but the stakeholder wants the top 5 by Net Sales. Best approach:

  • Sort descending and hope
  • Visual-level filter Top N on ProductName by Net Sales
  • Calculated column IsTop5
  • Filter the query in Power Query

Which visual best shows Net Sales this month versus last month for 4 regions?

  • Pie
  • Clustered bar
  • Stacked bar
  • Gauge

Build the executive summary page. Add a Bookmark called "Water only" that pre-selects Category = Water and shows a hidden text box saying "Water category view". Attach it to a button.

Bookmark must be set with Data on and the Selection pane used to hide the text box in the default state.

Power BI Service basics

Data: FactSales

Assignments

Publish and share properly

A · Guided 25 min · uses Your report
  1. Create a workspace "Northwind Dev". Publish the report.
  2. Open the semantic model settings. Note that Enter Data sources cannot be refreshed and that this is fine for now.
  3. Share the report with a colleague (or a second account) with "Allow recipients to share" off and "Build" permission off.
  4. Have them open it. Confirm they cannot see the workspace itself.
Expected result: The colleague sees only the report, not the model or workspace.

App versus share

A · Guided 20 min · uses Your report
  1. Create an App from the workspace with the report inside. Add an audience.
  2. Compare what the consumer sees via the App link versus via direct share.
  3. Update the report, republish, and update the App. Note that until you update the App, consumers see the old version.
Expected result: You can explain when a change is visible to App users and when it is not.

Dashboard versus report

A · Guided 15 min · uses Your report
  1. Pin the four cards to a new dashboard "Northwind KPI".
  2. Set a data alert on the Net Sales tile.
  3. Try to add a slicer to the dashboard. Notice that you cannot.
Expected result: You can state two things a dashboard does that a report cannot (alerts, tiles from multiple reports) and two things it cannot do (slicers, interactions).

Refresh that actually works

A · Guided 20 min · uses Any CSV on OneDrive
  1. Save one of the datasets as CSV in OneDrive for Business. Point a new report at it via Get Data → Web using the OneDrive share link ending in ?download=1, or via the SharePoint folder connector.
  2. Publish. Set credentials on the semantic model (OAuth). Schedule refresh daily at 6:00 in your timezone.
  3. Edit the CSV, wait for or trigger a refresh, confirm the report changed.
Expected result: Refresh history shows Completed. The edited value appears in the Service without republishing.

Interview questions

  • What is the difference between a report, a semantic model, a dashboard and an app?
  • A viewer says "I can't see the report". Walk through your checklist.
  • Why does a scheduled refresh fail for a file on your desktop?
  • What does Build permission allow?
  • What are the workspace roles and which one would you never give a business user?

Assessment

You republish a report from Desktop. Which is true?

  • App users see changes instantly
  • Direct-share users see changes instantly, App users after App update
  • Nobody sees changes until refresh
  • Both see changes after refresh

Data alerts can be set on:

  • Any visual in a report
  • Cards, KPIs and gauges pinned to a dashboard
  • Slicers
  • Matrix cells

Minimum requirement for a colleague to view a report in a Pro workspace:

  • Nothing
  • A free account
  • A Pro or PPU license
  • Fabric admin

Configure scheduled refresh for a OneDrive CSV, force a failure by renaming the file, read the error message, fix it, and write down the exact error text you saw.

The error mentions the path or 404; knowing what it looks like saves you 30 minutes in production.