Practical Lab

15 experiments, each set out as 1. Question, 2. Aim, 3. Steps, 4. Programme, 5. Execution and Results.

Code lives in labs/course-11-bi/.

pip install -r tools/requirements.txt
python3 tools/data-science/run_bi_labs.py

Getting the tools

What to submit

Submitting a .twb is the commonest way to lose lab marks.

Experiment 1 — Exploring BI tools: Power BI vs Tableau

1. Question

Explore the BI tools: compare Power BI with Tableau.

2. Aim

Load the same data into both tools, build one chart in each, and compare them from experience.

3. Steps

1. Install both tools.
2. Load the same CSV into each.
3. Build one bar chart in each.
4. Fill in the comparison table from what you actually experienced, not from a blog:

4. Programme

There is no program: this is a comparison, not a computation — click-path only.

5. Execution and Results

For the viva: the differences that decide real deployments are cost, existing stack, who builds the reports, and macOS — not the feature list. The tools have converged. Unit 1 §1.7 has the full comparison.

Experiment 2 — A simple retail dashboard in both tools

1. Question

Build a simple retail dashboard in Power BI and in Tableau.

2. Aim

Build the same three-visual dashboard twice, once in each tool.

3. Steps

Use the star schema from fixtures.py — export it to CSV first, or use any retail dataset.

1. In Power BI: Get Data → Text/CSV → Transform Data → set types → Close & Apply → Model view → check relationships → build three visuals (a card, a ranked bar, a line) → arrange per Unit 5 §5.5.

2. In Tableau: Connect → Text file → drag the fact and dimension tables onto the canvas → Sheet 1 → build the same three views → New Dashboard → drag them in.

3. Note where each tool made you stop and think.

4. Programme

There is no program — click-path only.

5. Execution and Results

Build the same dashboard twice and note where each tool made you stop and think. That comparison is worth more than either dashboard.

Experiment 3 — Connecting to different data sources in Power BI

1. Question

Connect Power BI to different data sources: Excel, CSV, the web and a folder.

2. Aim

Load each kind of source, and catch the way each one fails silently.

3. Steps

In Python, 03_data_sources.py:

1. Load a CSV, and check it round-trips.
2. Read a semicolon file as comma-separated.
3. Read UTF-8 as Latin-1.
4. Load a sheet, and then a table.
5. Flatten a web API's nested JSON.
6. Compare Import with DirectQuery.

Get Data -> Excel Workbook   -> pick the TABLE, not the sheet
Get Data -> Text/CSV         -> CHECK the delimiter and encoding in the preview
Get Data -> Web              -> paste a JSON URL, then expand records and lists
Get Data -> Folder           -> Combine, for many identically shaped files

4. Programme

In Python, 03_data_sources.py:

"""Experiment 3 — Connecting to different data sources.

The Power BI half is a click-path (Get Data -> Excel / Text-CSV / Web) and is
written out in lab.md. What runs here is the part that actually matters and
that students get wrong: every format has a failure mode, and the failure is
silent. Each one below is DEMONSTRATED failing and then fixed.
"""
import io
import json
import pathlib
import tempfile

import pandas as pd

from fixtures import DIM_STORE, FACT_SALES

TMP = pathlib.Path(tempfile.mkdtemp(prefix="bi_lab3_"))


def csv_round_trip():
    """The baseline: write a CSV, read it back, prove nothing changed."""
    path = TMP / "sales.csv"
    FACT_SALES.to_csv(path, index=False)
    back = pd.read_csv(path)

    assert back.shape == FACT_SALES.shape, (back.shape, FACT_SALES.shape)
    assert list(back.columns) == list(FACT_SALES.columns)
    assert back["qty"].sum() == FACT_SALES["qty"].sum() == 87

    print(f"  CSV      {back.shape[0]} rows x {back.shape[1]} cols, qty total "
          f"{back['qty'].sum()} -- unchanged")


def the_delimiter_trap():
    """A semicolon CSV read as comma-separated: ONE column, no error."""
    text = "store_key;store;region\nT1;Vijayawada;South\nT2;Guntur;South\n"

    wrong = pd.read_csv(io.StringIO(text))
    assert wrong.shape[1] == 1, wrong.shape
    assert wrong.columns[0] == "store_key;store;region"

    right = pd.read_csv(io.StringIO(text), sep=";")
    assert right.shape == (2, 3), right.shape
    assert list(right.columns) == ["store_key", "store", "region"]

    print(f"  semicolon CSV read as comma -> {wrong.shape[1]} column, NO ERROR")
    print(f"  the same file with sep=';'   -> {right.shape[1]} columns")
    print("       Power BI shows this in the preview pane. LOOK at the preview")
    print("       before pressing Load -- it is the whole point of that screen")


def the_encoding_trap():
    """UTF-8 written, Latin-1 read: names silently mangled, no exception."""
    names = pd.DataFrame({"store": ["Vijayawāda", "Guntūr"]})
    path = TMP / "names.csv"
    names.to_csv(path, index=False, encoding="utf-8")

    right = pd.read_csv(path, encoding="utf-8")
    wrong = pd.read_csv(path, encoding="latin-1")

    assert list(right["store"]) == ["Vijayawāda", "Guntūr"]
    assert list(wrong["store"]) != list(right["store"])
    assert "Ä" in wrong["store"][0] or "Å" in wrong["store"][0], wrong["store"][0]

    print(f"  utf-8 file read as utf-8    -> {right['store'][0]}")
    print(f"  the SAME file read as latin-1 -> {wrong['store'][0]}")
    print("       no exception, no warning. This is why the encoding dropdown")
    print("       exists, and why you check a name with an accent in it")


def excel_sheet_versus_table():
    """A sheet brings the title row and the blank line. A named table does not."""
    path = TMP / "report.xlsx"
    with pd.ExcelWriter(path) as xl:
        # A sheet as a human would lay it out: title, blank row, then data.
        messy = pd.DataFrame(
            [["Monthly Sales Report", None, None],
             [None, None, None],
             ["store_key", "store", "region"],
             ["T1", "Vijayawada", "South"],
             ["T2", "Guntur", "South"]])
        messy.to_excel(xl, sheet_name="Report", index=False, header=False)
        DIM_STORE.to_excel(xl, sheet_name="Clean", index=False)

    raw = pd.read_excel(path, sheet_name="Report")
    assert raw.columns[0] == "Monthly Sales Report", list(raw.columns)
    assert raw.shape[0] == 4, raw.shape

    fixed = pd.read_excel(path, sheet_name="Report", skiprows=2)
    assert list(fixed.columns) == ["store_key", "store", "region"], list(fixed.columns)
    assert fixed.shape[0] == 2

    clean = pd.read_excel(path, sheet_name="Clean")
    assert list(clean.columns) == list(DIM_STORE.columns)
    assert clean.shape == DIM_STORE.shape

    print(f"  sheet as-is      -> header is '{raw.columns[0]}', {raw.shape[0]} junk rows")
    print(f"  skiprows=2       -> {list(fixed.columns)}")
    print(f"  a proper table   -> {clean.shape[0]} rows, correct headers immediately")
    print("       in Power BI: pick the TABLE in the navigator, not the sheet.")
    print("       If there is no table, 'Use First Row as Headers' + Remove Top Rows")


def web_api_json_is_nested():
    """A Web API returns nested JSON. Expanding it is the whole task."""
    payload = {
        "meta": {"generated": "2026-08-27", "count": 2},
        "results": [
            {"store": {"key": "T1", "name": "Vijayawada"},
             "sales": [{"product": "P1", "qty": 10}, {"product": "P3", "qty": 5}]},
            {"store": {"key": "T2", "name": "Guntur"},
             "sales": [{"product": "P2", "qty": 8}]},
        ],
    }
    path = TMP / "api.json"
    path.write_text(json.dumps(payload))
    doc = json.loads(path.read_text())

    # Reading it naively gives one row per result with objects inside cells.
    naive = pd.DataFrame(doc["results"])
    assert isinstance(naive["store"][0], dict), "the cell holds a DICT, not a value"
    assert isinstance(naive["sales"][0], list), "and this one holds a LIST"

    # json_normalize with record_path is the fix -- Course 9 Unit 3's function.
    flat = pd.json_normalize(doc["results"], record_path="sales",
                             meta=[["store", "key"], ["store", "name"]])
    assert flat.shape == (3, 4), flat.shape
    assert list(flat.columns) == ["product", "qty", "store.key", "store.name"]
    assert flat["qty"].sum() == 23
    assert list(flat["store.key"]) == ["T1", "T1", "T2"]

    print(f"  naive read      -> {naive.shape[0]} rows, cells contain dicts and lists")
    print(f"  json_normalize  -> {flat.shape[0]} rows x {flat.shape[1]} cols, qty {flat['qty'].sum()}")
    print("       Power BI does this with the EXPAND arrows on record and list")
    print("       columns. It is the same operation, clicked instead of typed")


def import_versus_directquery():
    """Not runnable -- a table, because the choice is the examinable part."""
    rows = [
        ("Where the data sits", "Copied into the .pbix", "Stays in the source"),
        ("Speed", "Fast (in-memory columnar)", "The source's speed"),
        ("Freshness", "As of last refresh", "Live"),
        ("Size limit", "1 GB model on Pro", "None -- no copy"),
        ("DAX available", "All of it", "A restricted subset"),
        ("Load on source", "Only at refresh", "Every interaction"),
    ]
    assert len(rows) == 6
    print("  Import vs DirectQuery:")
    print(f"    {'':22s} {'IMPORT':28s} DIRECTQUERY")
    for label, imp, dq in rows:
        print(f"    {label:22s} {imp:28s} {dq}")
    print("       Import is the default and the right answer unless the data is")
    print("       too large to copy or must be to-the-second")


def main():
    print("Experiment 3 -- Connecting to different data sources")
    # Step 1: Load a CSV, and check it round-trips
    csv_round_trip()
    # Step 2: Read a semicolon file as comma-separated
    the_delimiter_trap()
    # Step 3: Read UTF-8 as Latin-1
    the_encoding_trap()
    # Step 4: Load a sheet, and then a table
    excel_sheet_versus_table()
    # Step 5: Flatten a web API's nested JSON
    web_api_json_is_nested()
    # Step 6: Compare Import with DirectQuery
    import_versus_directquery()


if __name__ == "__main__":
    main()

5. Execution and Results

In Python, 03_data_sources.py:

Experiment 3 -- Connecting to different data sources
  CSV      9 rows x 4 cols, qty total 87 -- unchanged
  semicolon CSV read as comma -> 1 column, NO ERROR
  the same file with sep=';'   -> 3 columns
       Power BI shows this in the preview pane. LOOK at the preview
       before pressing Load -- it is the whole point of that screen
  utf-8 file read as utf-8    -> Vijayawāda
  the SAME file read as latin-1 -> Vijayawāda
       no exception, no warning. This is why the encoding dropdown
       exists, and why you check a name with an accent in it
  sheet as-is      -> header is 'Monthly Sales Report', 4 junk rows
  skiprows=2       -> ['store_key', 'store', 'region']
  a proper table   -> 3 rows, correct headers immediately
       in Power BI: pick the TABLE in the navigator, not the sheet.
       If there is no table, 'Use First Row as Headers' + Remove Top Rows
  naive read      -> 2 rows, cells contain dicts and lists
  json_normalize  -> 3 rows x 4 cols, qty 23
       Power BI does this with the EXPAND arrows on record and list
       columns. It is the same operation, clicked instead of typed
  Import vs DirectQuery:
                           IMPORT                       DIRECTQUERY
    Where the data sits    Copied into the .pbix        Stays in the source
    Speed                  Fast (in-memory columnar)    The source's speed
    Freshness              As of last refresh           Live
    Size limit             1 GB model on Pro            None -- no copy
    DAX available          All of it                    A restricted subset
    Load on source         Only at refresh              Every interaction
       Import is the default and the right answer unless the data is
       too large to copy or must be to-the-second

All four fail silently. That is why the preview pane exists, and why you look at it before pressing Load.

Also asserted: the Import vs DirectQuery comparison. Import is the default.

Experiment 4 — Data cleaning and transformation with Power Query

1. Question

Clean and transform data with Power Query.

2. Aim

Apply each Power Query step, and see that the order of the steps changes the answer.

3. Steps

In Python, 04_power_query.py:

1. Trim and clean the text.
2. See that changing case does not trim.
3. Fill down.
4. Replace values, and set the types.
5. Remove duplicates, on chosen columns.
6. Swap the order of two steps.
7. Unpivot.
8. Group by, and merge queries.

Transform -> Format -> Trim / Capitalize Each Word
Transform -> Fill -> Down
Home      -> Remove Rows -> Remove Duplicates
Transform -> Replace Values
Transform -> Unpivot Columns
Home      -> Merge Queries / Append Queries
Transform -> Group By

4. Programme

In Python, 04_power_query.py:

"""Experiment 4 — Data cleaning and transformation with Power Query.

Power Query is a recorded, ordered list of steps replayed at every refresh.
That ordering is not decoration -- the SAME steps in a different order give a
different answer, and this script proves it.

Each function is one ribbon button, with the pandas call that does the same
thing, so the pair is learnable together.
"""
import pandas as pd

# A deliberately dirty extract: the shapes real spreadsheets arrive in.
DIRTY = pd.DataFrame({
    "store":  ["  Vijayawada ", "GUNTUR", "Vijayawada", "Hyderabad",
               "Vijayawada", "guntur", None],
    "region": ["South", None,   None,     "North",  None,  None,  "North"],
    "sales":  ["2,800", "1680", "700",    "800",    "700", "1680", "n/a"],
    "date":   ["2026-01-15", "2026-01-15", "2026-01-15", "2026-02-10",
               "2026-01-15", "2026-01-15", "2026-02-10"],
})


def trim_and_clean():
    """Transform -> Format -> Trim, and Clean (removes control characters)."""
    df = DIRTY.copy()
    assert df["store"][0] == "  Vijayawada ", "leading and trailing spaces"

    df["store"] = df["store"].str.strip()
    assert df["store"][0] == "Vijayawada"

    # "Vijayawada" and "  Vijayawada " are DIFFERENT values until trimmed --
    # which is why an untrimmed column produces two slicer entries that look
    # identical on screen.
    raw_distinct = DIRTY["store"].nunique(dropna=True)
    trimmed_distinct = df["store"].nunique(dropna=True)
    assert raw_distinct == 5 and trimmed_distinct == 4, (raw_distinct, trimmed_distinct)

    print(f"  Trim: {raw_distinct} distinct stores -> {trimmed_distinct}")
    print("       an untrimmed column gives two slicer entries that look the same")


def case_folding_is_not_trim():
    """'GUNTUR', 'Guntur' and 'guntur' are three stores until you fix case."""
    df = DIRTY.copy()
    df["store"] = df["store"].str.strip()
    assert df["store"].nunique(dropna=True) == 4

    df["store"] = df["store"].str.title()
    assert df["store"].nunique(dropna=True) == 3, sorted(df["store"].dropna().unique())
    assert sorted(df["store"].dropna().unique()) == ["Guntur", "Hyderabad", "Vijayawada"]

    print("  Transform -> Format -> Capitalize Each Word: 4 -> 3 distinct stores")
    print("       GUNTUR / guntur were separate rows in every chart")


def fill_down():
    """Transform -> Fill -> Down. The classic merged-cell repair."""
    df = DIRTY.copy()
    assert df["region"].isna().sum() == 4

    df["region"] = df["region"].ffill()
    assert df["region"].isna().sum() == 0
    assert list(df["region"]) == ["South"] * 3 + ["North"] * 4, list(df["region"])

    print("  Fill Down: 4 nulls -> 0")
    print("       merged cells in Excel export as a value then blanks. Fill Down")
    print("       is the repair -- but ONLY if the rows are still in source order")


def replace_values_and_types():
    """Replace Values, then Change Type. Order matters -- see below."""
    df = DIRTY.copy()

    # "n/a" and thousands separators both defeat a numeric conversion.
    cleaned = (df["sales"].str.replace(",", "", regex=False)
                          .replace("n/a", None))
    numeric = pd.to_numeric(cleaned, errors="raise")
    assert numeric.isna().sum() == 1, "n/a became null, not zero"
    assert numeric.sum() == 8360.0, numeric.sum()

    # The alternative -- replacing n/a with 0 -- changes every average.
    as_zero = pd.to_numeric(cleaned.fillna(0))
    assert as_zero.sum() == 8360.0, "the SUM is the same"
    assert round(numeric.mean(), 4) == 1393.3333, round(numeric.mean(), 4)
    assert round(as_zero.mean(), 4) == 1194.2857, round(as_zero.mean(), 4)

    print(f"  Replace ',' then to-number: sum {numeric.sum():.0f}, "
          f"mean {numeric.mean():.4f} (n/a -> null, excluded)")
    print(f"  n/a replaced with 0 instead: sum {as_zero.sum():.0f}, "
          f"mean {as_zero.mean():.4f}")
    print("       the SUM is identical and the MEAN is not. Null excludes the")
    print("       row; zero counts it as a real zero. State which you chose")


def remove_duplicates_depends_on_the_columns():
    """Remove Duplicates removes rows identical in the SELECTED columns."""
    df = DIRTY.copy()
    df["store"] = df["store"].str.strip().str.title()

    all_cols = df.drop_duplicates()
    on_store = df.drop_duplicates(subset=["store"])
    on_store_date = df.drop_duplicates(subset=["store", "date"])

    assert len(df) == 7
    # Rows 4 and 5 are exact copies of rows 2 and 1, so two go.
    assert len(all_cols) == 5, len(all_cols)
    assert len(on_store) == 4, len(on_store)
    assert len(on_store_date) == 4, len(on_store_date)

    print(f"  Remove Duplicates on: all columns -> {len(all_cols)} rows")
    print(f"                        [store]     -> {len(on_store)} rows")
    print(f"                        [store,date]-> {len(on_store_date)} rows")
    print("       select the wrong columns and you delete real data. It keeps")
    print("       the FIRST occurrence, so sort before de-duplicating")


def step_order_changes_the_answer():
    """The demonstration that justifies the whole Applied Steps pane."""
    df = DIRTY.copy()
    df["sales_n"] = pd.to_numeric(
        df["sales"].str.replace(",", "", regex=False).replace("n/a", None))

    # Order A: de-duplicate FIRST, then trim/case-fold.
    a = df.drop_duplicates(subset=["store", "date"]).copy()
    a["store"] = a["store"].str.strip().str.title()
    a_total = a["sales_n"].sum()

    # Order B: trim/case-fold FIRST, then de-duplicate.
    b = df.copy()
    b["store"] = b["store"].str.strip().str.title()
    b = b.drop_duplicates(subset=["store", "date"])
    b_total = b["sales_n"].sum()

    assert len(a) == 6 and len(b) == 4, (len(a), len(b))
    # A keeps 2800+1680+700+800+1680; B keeps 2800+1680+800.
    assert a_total == 7660.0 and b_total == 5280.0, (a_total, b_total)
    assert a_total - b_total == 2380.0

    print("  SAME two steps, two orders:")
    print(f"    de-duplicate then clean -> {len(a)} rows, total {a_total:.0f}")
    print(f"    clean then de-duplicate -> {len(b)} rows, total {b_total:.0f}")
    print(f"    the two orders differ by {a_total - b_total:.0f}")
    print("       '  Vijayawada ' and 'Vijayawada' are not duplicates until")
    print("       they have been trimmed, so de-duplicating first MISSES them.")
    print("       CLEAN BEFORE YOU DE-DUPLICATE -- and this is why Applied")
    print("       Steps is an ordered list and not a set")


def unpivot_is_the_examinable_one():
    """Transform -> Unpivot Columns. pandas calls it melt; Tableau calls it Pivot."""
    wide = pd.DataFrame({"store": ["T1", "T2"],
                         "Jan": [5000, 3000], "Feb": [5200, 3100]})
    long = wide.melt(id_vars="store", var_name="month", value_name="sales")

    assert wide.shape == (2, 3)
    assert long.shape == (4, 3), long.shape
    assert list(long.columns) == ["store", "month", "sales"]
    assert long["sales"].sum() == wide[["Jan", "Feb"]].to_numpy().sum() == 16300

    # The reason: a March column breaks the wide chart and not the long one.
    wide_mar = wide.assign(Mar=[5400, 3200])
    long_mar = wide_mar.melt(id_vars="store", var_name="month", value_name="sales")
    assert long_mar.shape == (6, 3), "same three columns, more rows"
    assert list(long_mar.columns) == list(long.columns), \
        "the SCHEMA did not change -- that is the whole point"

    print(f"  Unpivot: {wide.shape} wide -> {long.shape} long, total {long['sales'].sum()}")
    print(f"  add a March column: {wide_mar.shape} wide -> {long_mar.shape} long")
    print("       the long table's COLUMNS did not change, so no chart broke.")
    print("       Power Query: Unpivot. Tableau: Pivot. pandas: melt")


def group_by_and_merge():
    """Group By, and Merge Queries -- the last two ribbon buttons that matter."""
    df = DIRTY.copy()
    df["store"] = df["store"].str.strip().str.title()
    df["sales_n"] = pd.to_numeric(
        df["sales"].str.replace(",", "", regex=False).replace("n/a", None))
    df = df.dropna(subset=["store"])

    grouped = (df.groupby("store", as_index=False)
                 .agg(total=("sales_n", "sum"), lines=("sales_n", "size")))
    assert len(grouped) == 3
    assert grouped.set_index("store")["total"].to_dict() == \
        {"Guntur": 3360.0, "Hyderabad": 800.0, "Vijayawada": 4200.0}

    lookup = pd.DataFrame({"store": ["Vijayawada", "Guntur", "Hyderabad"],
                           "manager": ["Asha", "Ravi", "Meena"]})
    merged = grouped.merge(lookup, on="store", how="left")
    assert merged["manager"].isna().sum() == 0
    assert len(merged) == len(grouped), "a LEFT join to a unique key cannot add rows"

    print("  Group By store:")
    for _, r in grouped.iterrows():
        print(f"    {r['store']:12s} total {r['total']:7.0f}  ({r['lines']} lines)")
    print("  Merge Queries (left join to a unique key) added manager, no row growth")
    print("       if a merge ADDS rows, the right-hand key is not unique --")
    print("       that is the fan trap, and experiment 14 measures it")


def main():
    print("Experiment 4 -- Power Query cleaning and transformation")
    # Step 1: Trim and clean the text
    trim_and_clean()
    # Step 2: See that changing case does not trim
    case_folding_is_not_trim()
    # Step 3: Fill down
    fill_down()
    # Step 4: Replace values, and set the types
    replace_values_and_types()
    # Step 5: Remove duplicates, on chosen columns
    remove_duplicates_depends_on_the_columns()
    # Step 6: Swap the order of two steps
    step_order_changes_the_answer()
    # Step 7: Unpivot
    unpivot_is_the_examinable_one()
    # Step 8: Group by, and merge queries
    group_by_and_merge()


if __name__ == "__main__":
    main()

5. Execution and Results

In Python, 04_power_query.py:

Experiment 4 -- Power Query cleaning and transformation
  Trim: 5 distinct stores -> 4
       an untrimmed column gives two slicer entries that look the same
  Transform -> Format -> Capitalize Each Word: 4 -> 3 distinct stores
       GUNTUR / guntur were separate rows in every chart
  Fill Down: 4 nulls -> 0
       merged cells in Excel export as a value then blanks. Fill Down
       is the repair -- but ONLY if the rows are still in source order
  Replace ',' then to-number: sum 8360, mean 1393.3333 (n/a -> null, excluded)
  n/a replaced with 0 instead: sum 8360, mean 1194.2857
       the SUM is identical and the MEAN is not. Null excludes the
       row; zero counts it as a real zero. State which you chose
  Remove Duplicates on: all columns -> 5 rows
                        [store]     -> 4 rows
                        [store,date]-> 4 rows
       select the wrong columns and you delete real data. It keeps
       the FIRST occurrence, so sort before de-duplicating
  SAME two steps, two orders:
    de-duplicate then clean -> 6 rows, total 7660
    clean then de-duplicate -> 4 rows, total 5280
    the two orders differ by 2380
       '  Vijayawada ' and 'Vijayawada' are not duplicates until
       they have been trimmed, so de-duplicating first MISSES them.
       CLEAN BEFORE YOU DE-DUPLICATE -- and this is why Applied
       Steps is an ordered list and not a set
  Unpivot: (2, 3) wide -> (4, 3) long, total 16300
  add a March column: (2, 4) wide -> (6, 3) long
       the long table's COLUMNS did not change, so no chart broke.
       Power Query: Unpivot. Tableau: Pivot. pandas: melt
  Group By store:
    Guntur       total    3360  (2 lines)
    Hyderabad    total     800  (1 lines)
    Vijayawada   total    4200  (3 lines)
  Merge Queries (left join to a unique key) added manager, no row growth
       if a merge ADDS rows, the right-hand key is not unique --
       that is the fan trap, and experiment 14 measures it

Also asserted: replacing "n/a" with null gives a mean of 1,393.33 and with 0 gives 1,194.29, while the sum is identical either way. Ratios and averages move; totals do not.

Experiment 5 — Student performance: clean, reshape, visualize

1. Question

Clean, reshape and visualise a student performance dataset.

2. Aim

Clean and unpivot the marks, decide what an absence counts as, and build the pass-rate measure.

3. Steps

In Python, 05_student_performance.py:

1. Clean and unpivot the marks.
2. Record AB as null, and then as zero.
3. Compute the subject and programme averages.
4. Build the pass-rate measure.

Remove Duplicates on (student, semester, subject)
Select the subject columns -> Unpivot Columns
Trim, then Change Type to whole number
Replace Values: "AB" -> null

4. Programme

In Python, 05_student_performance.py:

"""Experiment 5 — Cleaning a higher-education student performance dataset.

The education case study from unit-1.md §1.4 and unit-2.md §2.7.

The examinable decision is what to do with "AB" (absent). It is not a cleaning
detail -- it changes the pass rate, and two departments that choose differently
will report different figures from the same file. Both choices are computed
here so the difference is a number rather than an opinion.
"""
import pandas as pd

# As received: wide (a column per subject), marks as text, absences as "AB".
RAW = pd.DataFrame({
    "student_id": ["S1", "S2", "S3", "S4", "S5", "S5"],
    "programme":  ["BSc-DS", "BSc-DS", "BSc-DS", "BSc-STAT", "BSc-STAT", "BSc-STAT"],
    "semester":   [5, 5, 5, 5, 5, 5],
    "Maths":      [" 88", "65", "94", "AB", "52", "52"],
    "Stats":      ["91", "58", "89", "66", "AB", "AB"],
    "Python":     ["76", "AB", "81", "70", "45", "45"],
})


def clean_and_reshape():
    """The Power Query pipeline, in order. The order is not arbitrary."""
    df = RAW.copy()

    # 1. Remove Duplicates -- S5 was uploaded twice, identically.
    df = df.drop_duplicates()
    assert len(df) == 5, len(df)

    # 2. Unpivot the subject columns (Tableau: Pivot; pandas: melt).
    long = df.melt(id_vars=["student_id", "programme", "semester"],
                   var_name="subject", value_name="marks_raw")
    assert long.shape == (15, 5), long.shape
    assert set(long["subject"]) == {"Maths", "Stats", "Python"}

    # 3. Trim, then convert. "AB" cannot become a number, so it must be
    #    decided on FIRST -- see the two functions below.
    long["marks_raw"] = long["marks_raw"].str.strip()
    # Three absences survive: S4 Maths, S5 Stats, S2 Python. The fourth "AB"
    # was in S5's duplicate row, which step 1 already removed -- which is
    # itself the reason to de-duplicate BEFORE counting anything.
    assert (long["marks_raw"] == "AB").sum() == 3, "three absences"

    print(f"  {len(RAW)} raw rows -> {len(df)} after Remove Duplicates")
    print(f"  unpivot 3 subject columns -> {long.shape[0]} rows "
          f"({len(df)} students x 3 subjects)")
    print(f"  absences marked 'AB': {(long['marks_raw'] == 'AB').sum()}")
    return long


def ab_as_null_versus_zero(long):
    """The decision that changes the answer. Both computed, neither hidden."""
    as_null = pd.to_numeric(long["marks_raw"], errors="coerce")
    as_zero = as_null.fillna(0)

    assert as_null.isna().sum() == 3
    assert len(as_null) == 15 and as_null.notna().sum() == 12
    assert as_null.sum() == 875.0

    stats = {
        "AB -> null (excluded)": (as_null.mean(), as_null.notna().sum(),
                                  (as_null >= 40).sum() / as_null.notna().sum()),
        "AB -> 0 (counted)":     (as_zero.mean(), len(as_zero),
                                  (as_zero >= 40).sum() / len(as_zero)),
    }

    null_mean, null_n, null_pass = stats["AB -> null (excluded)"]
    zero_mean, zero_n, zero_pass = stats["AB -> 0 (counted)"]

    # 875/12 = 72.9167 excluding absences; 875/15 = 58.3333 counting them as 0.
    assert round(null_mean, 4) == 72.9167, round(null_mean, 4)
    assert round(zero_mean, 4) == 58.3333, round(zero_mean, 4)
    assert round(null_pass * 100, 4) == 100.0, round(null_pass * 100, 4)
    assert round(zero_pass * 100, 4) == 80.0, round(zero_pass * 100, 4)

    print("  the 'AB' decision, both ways:")
    print(f"    {'':24s} {'mean':>8s} {'n':>4s} {'pass rate':>10s}")
    for label, (m, n, p) in stats.items():
        print(f"    {label:24s} {m:8.4f} {n:>4d} {p * 100:9.2f}%")
    print(f"       the mean moves {null_mean - zero_mean:.2f} marks and the pass")
    print(f"       rate {(null_pass - zero_pass) * 100:.2f} points. NEITHER IS WRONG --")
    print("       but the dashboard must say which it did, or two departments")
    print("       will report different pass rates from one file")
    return as_null


def academic_metrics(long, marks):
    """The visuals: subject-wise averages, pass rate, distinction count."""
    df = long.assign(marks=marks).dropna(subset=["marks"])

    by_subject = (df.groupby("subject")
                    .agg(avg=("marks", "mean"), n=("marks", "size"))
                    .round(4).sort_index())
    # One absence per subject, so every subject has 4 of a possible 5 marks.
    assert list(by_subject["n"]) == [4, 4, 4], list(by_subject["n"])
    assert round(by_subject.loc["Maths", "avg"], 4) == 74.75
    assert round(by_subject.loc["Stats", "avg"], 4) == 76.0
    assert round(by_subject.loc["Python", "avg"], 4) == 68.0

    by_prog = (df.groupby("programme")
                 .agg(avg=("marks", "mean"), n=("marks", "size")).round(4))
    assert by_prog.loc["BSc-DS", "n"] == 8
    assert by_prog.loc["BSc-STAT", "n"] == 4
    assert round(by_prog.loc["BSc-DS", "avg"], 4) == 80.25
    assert round(by_prog.loc["BSc-STAT", "avg"], 4) == 58.25

    distinctions = int((df["marks"] >= 75).sum())
    assert distinctions == 6, distinctions

    print("  subject      avg      n")
    for subj, row in by_subject.iterrows():
        print(f"    {subj:9s} {row['avg']:7.2f}  {int(row['n'])}")
    print("  programme    avg      n")
    for prog, row in by_prog.iterrows():
        print(f"    {prog:9s} {row['avg']:7.2f}  {int(row['n'])}")
    print(f"  distinctions (>=75): {distinctions}")
    print("       note the n column: 4, not 5, in every subject -- one student")
    print("       was absent from each. ALWAYS show n beside an average computed")
    print("       after excluding nulls, or it looks more solid than it is.")
    print("       BSc-STAT's 58.25 rests on FOUR marks from two students")


def the_pass_rate_measure_shape(long, marks):
    """CALCULATE in the numerator, plain COUNTROWS in the denominator."""
    df = long.assign(marks=marks).dropna(subset=["marks"])

    numerator = int((df["marks"] >= 40).sum())     # CALCULATE(COUNTROWS, marks>=40)
    denominator = len(df)                          # COUNTROWS(marks)
    pass_rate = numerator / denominator

    assert (numerator, denominator) == (12, 12)
    assert pass_rate == 1.0

    # Raise the bar, to prove the shape works and not just that all 12 passed.
    strict_num = int((df["marks"] >= 75).sum())
    assert strict_num == 6
    assert round(strict_num / denominator * 100, 4) == 50.0

    print(f"  Pass Rate  = CALCULATE(COUNTROWS, marks>=40) / COUNTROWS")
    print(f"             = {numerator}/{denominator} = {pass_rate * 100:.2f}%")
    print(f"  Distinction Rate (>=75) = {strict_num}/{denominator} = "
          f"{strict_num / denominator * 100:.2f}%")
    print("       CALCULATE on top, plain count underneath. That shape is EVERY")
    print("       rate measure in BI -- memorise it once and reuse it")


def main():
    print("Experiment 5 -- Student performance: clean, reshape, measure")
    # Step 1: Clean and unpivot the marks
    long = clean_and_reshape()
    # Step 2: Record AB as null, and then as zero
    marks = ab_as_null_versus_zero(long)
    # Step 3: Compute the subject and programme averages
    academic_metrics(long, marks)
    # Step 4: Build the pass-rate measure
    the_pass_rate_measure_shape(long, marks)


if __name__ == "__main__":
    main()

5. Execution and Results

In Python, 05_student_performance.py:

Experiment 5 -- Student performance: clean, reshape, measure
  6 raw rows -> 5 after Remove Duplicates
  unpivot 3 subject columns -> 15 rows (5 students x 3 subjects)
  absences marked 'AB': 3
  the 'AB' decision, both ways:
                                 mean    n  pass rate
    AB -> null (excluded)     72.9167   12    100.00%
    AB -> 0 (counted)         58.3333   15     80.00%
       the mean moves 14.58 marks and the pass
       rate 20.00 points. NEITHER IS WRONG --
       but the dashboard must say which it did, or two departments
       will report different pass rates from one file
  subject      avg      n
    Maths       74.75  4
    Python      68.00  4
    Stats       76.00  4
  programme    avg      n
    BSc-DS      80.25  8
    BSc-STAT    58.25  4
  distinctions (>=75): 6
       note the n column: 4, not 5, in every subject -- one student
       was absent from each. ALWAYS show n beside an average computed
       after excluding nulls, or it looks more solid than it is.
       BSc-STAT's 58.25 rests on FOUR marks from two students
  Pass Rate  = CALCULATE(COUNTROWS, marks>=40) / COUNTROWS
             = 12/12 = 100.00%
  Distinction Rate (>=75) = 6/12 = 50.00%
       CALCULATE on top, plain count underneath. That shape is EVERY
       rate measure in BI -- memorise it once and reuse it

Also asserted: subject averages (Maths 74.75, Stats 76.00, Python 68.00, n = 4 each), programme averages (BSc-DS 80.25 over 8 marks, BSc-STAT 58.25 over 4), and the rate-measure shape — CALCULATE in the numerator, plain COUNTROWS in the denominator.

Experiment 6 — Implementing DAX functions

1. Question

Implement DAX functions: aggregation, iterators, CALCULATE, ALL, DIVIDE and IF.

2. Aim

Write each measure, and check every value against Unit 2's figures.

3. Steps

In Python, 06_dax_functions.py:

1. SUM, COUNT and AVERAGE.
2. See COUNT and COUNTROWS disagree on blanks.
3. SUMX, row by row.
4. CALCULATE, which replaces the filter.
5. KEEPFILTERS, which intersects it.
6. ALL, for a percentage of the total.
7. DIVIDE, against the slash.
8. Margin as a column and as a measure.
9. IF and SWITCH.

Total Qty     = SUM(fact_sales[qty])
Line Count    = COUNTROWS(fact_sales)
Avg Qty       = AVERAGE(fact_sales[qty])
Total Revenue = SUMX(fact_sales, fact_sales[qty] * RELATED(dim_product[list_price]))
South Revenue = CALCULATE([Total Revenue], dim_store[region] = "South")
Pct of Total  = DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL(dim_store)))
Order Size    = IF([Total Qty] > 10, "Large", "Small")

4. Programme

In Python, 06_dax_functions.py:

"""Experiment 6 — Implementing DAX functions.

DAX cannot be executed outside Power BI, so this script implements its
SEMANTICS in pandas and asserts the figures quoted in unit-2.md. The point is
not to reimplement DAX; it is that every number the notes claim -- 87, 9,
9.667, 12880, 3525, 29.7619%, 27.3680%, 80.43% -- is produced by running code.

The three ideas being modelled:
  * filter context      -- what a measure can see when a visual renders
  * CALCULATE           -- REPLACING a filter rather than adding to it
  * measure vs column   -- aggregate-then-divide, not divide-then-average
"""
import pandas as pd

from fixtures import star

DF = star()


# --- the model: a filter context is just a boolean mask ---------------------

def in_context(**filters):
    """The rows a visual would be showing, given its filters."""
    df = DF
    for col, val in filters.items():
        df = df[df[col] == val]
    return df


# --- the aggregation functions the syllabus names ---------------------------

def sum_count_average():
    """SUM, COUNT, COUNTROWS, AVERAGE, DISTINCTCOUNT -- unit-2.md's table."""
    total_qty = DF["qty"].sum()
    count_qty = DF["qty"].notna().sum()          # COUNT ignores blanks
    countrows = len(DF)                          # COUNTROWS does not
    avg_qty = DF["qty"].mean()
    distinct_products = DF["product_key"].nunique()

    assert total_qty == 87, total_qty
    assert count_qty == 9 and countrows == 9
    assert round(avg_qty, 3) == 9.667, avg_qty
    assert avg_qty == total_qty / countrows
    assert distinct_products == 4

    print(f"  SUM(qty)                = {total_qty}")
    print(f"  COUNT(qty)              = {count_qty}")
    print(f"  COUNTROWS(fact_sales)   = {countrows}")
    print(f"  AVERAGE(qty)            = {avg_qty:.3f}   ({total_qty}/{countrows})")
    print(f"  DISTINCTCOUNT(product)  = {distinct_products}")


def count_and_countrows_disagree_on_blanks():
    """The examinable difference, shown rather than asserted in prose."""
    with_blank = DF.copy()
    with_blank.loc[with_blank.index[0], "qty"] = None

    count_qty = with_blank["qty"].notna().sum()
    countrows = len(with_blank)

    assert countrows == 9
    assert count_qty == 8, count_qty
    assert count_qty != countrows, "this is the whole point"

    print(f"  blank ONE qty value:  COUNT(qty) = {count_qty}, "
          f"COUNTROWS = {countrows}")
    print("       COUNT ignores blanks; COUNTROWS does not. On a complete")
    print("       column they agree, which is why the difference surprises people")


def sumx_is_row_by_row():
    """Total Revenue = SUMX(fact, qty * price). An iterator, not an aggregator."""
    revenue = (DF["qty"] * DF["list_price"]).sum()
    profit = revenue - (DF["qty"] * DF["unit_cost"]).sum()

    assert revenue == 12880.0, revenue
    assert profit == 3525.0, profit

    # SUM(qty) * SUM(price) is the WRONG answer, and it is a real mistake.
    # SUM(qty)=87, SUM(list_price) over the nine rows = 1620, 87*1620 = 140940.
    wrong = DF["qty"].sum() * DF["list_price"].sum()
    assert DF["list_price"].sum() == 1620.0
    assert wrong == 140940.0, wrong
    assert wrong > revenue * 10

    print(f"  SUMX(fact, qty * price)  = {revenue:,.0f}   CORRECT")
    print(f"  SUM(qty) * SUM(price)    = {wrong:,.0f}   WRONG")
    print("       SUMX evaluates the expression ROW BY ROW and then adds.")
    print("       Multiplying two totals multiplies unrelated things")


# --- CALCULATE ---------------------------------------------------------------

def calculate_replaces_the_filter():
    """The unit-2.md table, reproduced. This is the exam question."""
    south_revenue = in_context(region="South")["revenue"].sum()
    assert south_revenue == 10360.0, south_revenue

    rows = []
    for region in ("North", "South"):
        total_in_context = in_context(region=region)["revenue"].sum()
        # CALCULATE([Total Revenue], region = "South") -- the argument REPLACES
        # the row's own region filter, so it is the same on every row.
        calculated = south_revenue
        rows.append((region, total_in_context, calculated))

    assert rows[0] == ("North", 2520.0, 10360.0), rows[0]
    assert rows[1] == ("South", 10360.0, 10360.0), rows[1]
    assert rows[0][2] == rows[1][2], "identical on both rows -- the filter was REPLACED"
    assert DF["revenue"].sum() == 12880.0

    print("  region   [Total Revenue]   [South Revenue]")
    for region, ctx, calc in rows:
        print(f"  {region:7s} {ctx:>14,.0f} {calc:>17,.0f}")
    print(f"  {'Total':7s} {DF['revenue'].sum():>14,.0f} {south_revenue:>17,.0f}")
    print("       read the NORTH row: [South Revenue] shows South's figure.")
    print("       CALCULATE REPLACED the region filter rather than adding to it")


def keepfilters_intersects_instead():
    """The modifier that makes CALCULATE add rather than replace."""
    south = DF[DF["region"] == "South"]["revenue"].sum()

    results = {}
    for region in ("North", "South"):
        ctx = DF[DF["region"] == region]
        # KEEPFILTERS: intersect the outer filter with the inner one.
        kept = ctx[ctx["region"] == "South"]["revenue"].sum()
        results[region] = kept

    assert results["South"] == 10360.0
    assert results["North"] == 0.0, "North AND South is empty -- correctly"
    assert south == 10360.0

    print(f"  with KEEPFILTERS:  North -> {results['North']:,.0f}   "
          f"South -> {results['South']:,.0f}")
    print("       North INTERSECT South is empty, so it is 0 rather than 10,360.")
    print("       That is the difference between replacing and intersecting")


def all_removes_filters_for_pct_of_total():
    """Pct of Total = DIVIDE([Rev], CALCULATE([Rev], ALL(dim_store)))."""
    grand = DF["revenue"].sum()
    pcts = {}
    for region in ("South", "North"):
        rev = DF[DF["region"] == region]["revenue"].sum()
        pcts[region] = round(rev / grand * 100, 4)

    assert pcts == {"South": 80.4348, "North": 19.5652}, pcts
    assert round(sum(pcts.values()), 4) == 100.0, "the check that it is right"

    print(f"  grand total (ALL removed the region filter) = {grand:,.0f}")
    for region, pct in pcts.items():
        print(f"    {region:6s} {DF[DF.region == region].revenue.sum():>9,.0f}  {pct:>7.2f}%")
    print(f"    {'':6s} {'':>9s}  {sum(pcts.values()):>7.2f}%  <- sums to 100, so it is right")


def divide_handles_zero_and_slash_does_not():
    """DIVIDE gives you a defined result on a zero denominator; / does not."""
    import math

    empty = DF[DF["region"] == "West"]          # a region with no rows
    numerator = empty["profit"].sum()
    denominator = empty["revenue"].sum()
    assert (numerator, denominator) == (0.0, 0.0)

    # DIVIDE(a, b) -> BLANK when b is 0 (or the third argument, if supplied).
    divide_result = None if denominator == 0 else numerator / denominator
    assert divide_result is None

    # Plain division: Python raises, and numpy/pandas returns nan silently.
    # DAX returns Infinity or NaN. All three are results you did not choose.
    try:
        float(numerator) / float(denominator)
        raise SystemExit("expected ZeroDivisionError from Python floats")
    except ZeroDivisionError:
        pass

    import numpy as np
    with np.errstate(invalid="ignore", divide="ignore"):
        numpy_result = np.float64(numerator) / np.float64(denominator)
    assert math.isnan(numpy_result), numpy_result

    print("  region 'West' has no rows, so revenue sums to 0:")
    print("    DIVIDE(profit, revenue)  -> BLANK, by definition")
    print("    Python float division    -> ZeroDivisionError")
    print("    numpy / pandas division  -> nan, SILENTLY")
    print("    DAX plain '/'            -> Infinity or NaN")
    print("       three engines, three different wrong answers. DIVIDE is the")
    print("       only one where YOU chose what a zero denominator means")


# --- measure vs calculated column -------------------------------------------

def the_average_of_averages_trap():
    """unit-2.md's headline numbers: 29.7619% wrong, 27.3680% right."""
    per_row = DF["profit"] / DF["revenue"] * 100

    as_column = per_row.mean()                                   # WRONG
    as_measure = DF["profit"].sum() / DF["revenue"].sum() * 100   # RIGHT

    assert round(as_column, 4) == 29.7619, round(as_column, 4)
    assert round(as_measure, 4) == 27.3680, round(as_measure, 4)
    assert as_column > as_measure

    # And the reason, stated as an assertion: the measure is revenue-weighted.
    weighted = (per_row * DF["revenue"]).sum() / DF["revenue"].sum()
    assert round(weighted, 4) == round(as_measure, 4), \
        "the correct answer IS the revenue-weighted average of the row margins"

    print(f"  per-row margins: {[round(m, 2) for m in per_row]}")
    print(f"  AVERAGE of the column     = {as_column:.4f}%   WRONG")
    print(f"  SUM(profit)/SUM(revenue)  = {as_measure:.4f}%   RIGHT")
    print(f"  revenue-weighted average  = {weighted:.4f}%   (identical to RIGHT)")
    print(f"  the error is {as_column - as_measure:.4f} percentage points")
    print("       the column treats a Rs 600 line and a Rs 2,800 line as equal.")
    print("       AGGREGATE, THEN DIVIDE -- never divide, then average")


def if_and_switch():
    total_qty = DF["qty"].sum()
    assert total_qty == 87

    def band(q):
        if q > 15:
            return "Large"
        if q > 8:
            return "Medium"
        return "Small"

    # qty values are 10, 5, 8, 6, 20, 12, 4, 7, 15.
    #   Large  (>15): 20                  -> 1
    #   Medium (>8) : 10, 12, 15          -> 3   (15 is NOT > 15)
    #   Small       : 5, 8, 6, 4, 7       -> 5
    bands = DF["qty"].map(band).value_counts().to_dict()
    assert bands == {"Small": 5, "Medium": 3, "Large": 1}, bands
    assert sum(bands.values()) == 9

    print(f"  SWITCH(TRUE(), qty>15 'Large', qty>8 'Medium', 'Small'):")
    for name in ("Large", "Medium", "Small"):
        print(f"    {name:7s} {bands[name]} rows")
    print("       SWITCH(TRUE(), ...) is the DAX idiom for a nested IF.")
    print("       Use it beyond two branches")


def main():
    print("Experiment 6 -- DAX functions")
    # Step 1: SUM, COUNT and AVERAGE
    sum_count_average()
    # Step 2: See COUNT and COUNTROWS disagree on blanks
    count_and_countrows_disagree_on_blanks()
    # Step 3: SUMX, row by row
    sumx_is_row_by_row()
    # Step 4: CALCULATE, which replaces the filter
    calculate_replaces_the_filter()
    # Step 5: KEEPFILTERS, which intersects it
    keepfilters_intersects_instead()
    # Step 6: ALL, for a percentage of the total
    all_removes_filters_for_pct_of_total()
    # Step 7: DIVIDE, against the slash
    divide_handles_zero_and_slash_does_not()
    # Step 8: Margin as a column and as a measure
    the_average_of_averages_trap()
    # Step 9: IF and SWITCH
    if_and_switch()


if __name__ == "__main__":
    main()

5. Execution and Results

In Python, 06_dax_functions.py:

Experiment 6 -- DAX functions
  SUM(qty)                = 87
  COUNT(qty)              = 9
  COUNTROWS(fact_sales)   = 9
  AVERAGE(qty)            = 9.667   (87/9)
  DISTINCTCOUNT(product)  = 4
  blank ONE qty value:  COUNT(qty) = 8, COUNTROWS = 9
       COUNT ignores blanks; COUNTROWS does not. On a complete
       column they agree, which is why the difference surprises people
  SUMX(fact, qty * price)  = 12,880   CORRECT
  SUM(qty) * SUM(price)    = 140,940   WRONG
       SUMX evaluates the expression ROW BY ROW and then adds.
       Multiplying two totals multiplies unrelated things
  region   [Total Revenue]   [South Revenue]
  North            2,520            10,360
  South           10,360            10,360
  Total           12,880            10,360
       read the NORTH row: [South Revenue] shows South's figure.
       CALCULATE REPLACED the region filter rather than adding to it
  with KEEPFILTERS:  North -> 0   South -> 10,360
       North INTERSECT South is empty, so it is 0 rather than 10,360.
       That is the difference between replacing and intersecting
  grand total (ALL removed the region filter) = 12,880
    South     10,360    80.43%
    North      2,520    19.57%
                       100.00%  <- sums to 100, so it is right
  region 'West' has no rows, so revenue sums to 0:
    DIVIDE(profit, revenue)  -> BLANK, by definition
    Python float division    -> ZeroDivisionError
    numpy / pandas division  -> nan, SILENTLY
    DAX plain '/'            -> Infinity or NaN
       three engines, three different wrong answers. DIVIDE is the
       only one where YOU chose what a zero denominator means
  per-row margins: [21.43, 35.71, 28.57, 21.43, 37.5, 28.57, 21.43, 35.71, 37.5]
  AVERAGE of the column     = 29.7619%   WRONG
  SUM(profit)/SUM(revenue)  = 27.3680%   RIGHT
  revenue-weighted average  = 27.3680%   (identical to RIGHT)
  the error is 2.3939 percentage points
       the column treats a Rs 600 line and a Rs 2,800 line as equal.
       AGGREGATE, THEN DIVIDE -- never divide, then average
  SWITCH(TRUE(), qty>15 'Large', qty>8 'Medium', 'Small'):
    Large   1 rows
    Medium  3 rows
    Small   5 rows
       SWITCH(TRUE(), ...) is the DAX idiom for a nested IF.
       Use it beyond two branches

The last two are the most valuable numbers in the course. The gap is 2.3939 percentage points, and the script also asserts that the correct answer is the revenue-weighted average of the row margins — which is what "aggregate, then divide" means.

Experiment 7 — Creating basic visualizations in Power BI

1. Question

Create basic visualizations in Power BI: cards, bar and line charts, and a matrix.

2. Aim

Compute the data behind each visual and check it, then draw the bar and line charts.

3. Steps

In Python, 07_visualizations.py:

1. Compute the card values.
2. Rank the bar chart's categories.
3. Order the line chart's quarters.
4. Build the matrix.
5. Check the pie chart's shares.

Visualizations pane -> Card    -> drop a measure
                    -> Stacked bar chart -> Axis: category, Values: revenue
                    -> Line chart -> Axis: a continuous date
                    -> Matrix    -> Rows: region, Columns: category
Format pane -> Y axis -> Start at zero

4. Programme

In Python, 07_visualizations.py:

"""Experiment 7 — Creating basic visualizations in Power BI.

A chart cannot be asserted, but the DATA BEHIND IT can, and that is where
visuals go wrong. Every function here computes exactly what one visual would
show and checks it -- so if the figure on your card disagrees with this, the
visual is wrong, not the note.

The chart images are rendered to PNG too, so the shapes can be looked at.
"""
import pathlib

import matplotlib
matplotlib.use("Agg")                     # no display in this environment
import matplotlib.pyplot as plt

from fixtures import star

DF = star()
# [Changed: the charts went to output/, which now holds what each program printed;
# they are drawn into plots/, and the lab page shows them.]
OUT = pathlib.Path(__file__).parent / "plots"
OUT.mkdir(exist_ok=True)


def card_values():
    """Cards: one number each. The unit-5.md rule is 3-5 of them."""
    cards = {
        "Total Revenue": DF["revenue"].sum(),
        "Total Profit": DF["profit"].sum(),
        "Units Sold": DF["qty"].sum(),
        "Margin %": DF["profit"].sum() / DF["revenue"].sum() * 100,
    }
    assert cards["Total Revenue"] == 12880.0
    assert cards["Total Profit"] == 3525.0
    assert cards["Units Sold"] == 87
    assert round(cards["Margin %"], 4) == 27.3680
    assert len(cards) == 4, "3-5 cards; four is right"

    print("  cards:")
    for name, value in cards.items():
        shown = f"{value:,.2f}%" if name.endswith("%") else f"{value:,.0f}"
        print(f"    {name:15s} {shown:>12s}")
    print("       every one of these needs a comparison beside it -- vs target,")
    print("       vs last period, or vs a peer. A bare number is not information")


def bar_chart_data():
    """A ranked bar chart: revenue by category, sorted descending."""
    data = DF.groupby("category")["revenue"].sum().sort_values(ascending=False)

    assert list(data.index) == ["Grocery", "Personal", "Stationery"], list(data.index)
    assert list(data.values) == [9800.0, 1680.0, 1400.0], list(data.values)
    assert data.sum() == 12880.0
    assert list(data.values) == sorted(data.values, reverse=True), "SORT the bars"

    fig, ax = plt.subplots(figsize=(5, 3))
    ax.bar(data.index, data.values, color="#0f4c81")
    ax.set_ylim(0, None)                  # zero baseline -- unit-5.md's rule
    ax.set_ylabel("Revenue")
    ax.set_title("Revenue by category")
    fig.tight_layout()
    fig.savefig(OUT / "07_bar_category.png", dpi=110)
    plt.close(fig)

    print("  bar (revenue by category, ranked):")
    for cat, val in data.items():
        print(f"    {cat:11s} {val:>9,.0f}  {'#' * int(val / 400)}")
    print("       sorted descending, y-axis starting at ZERO. Both are rules,")
    print("       not preferences -- bar LENGTH is the message")


def line_chart_needs_ordered_time():
    """A line chart over quarters. Order by time, never by value."""
    data = DF.groupby("quarter")["revenue"].sum().sort_index()

    assert list(data.index) == ["Q1", "Q2"]
    assert list(data.values) == [7660.0, 5220.0], list(data.values)
    assert data.sum() == 12880.0

    change = (data["Q2"] - data["Q1"]) / data["Q1"] * 100
    assert round(change, 4) == -31.8538, round(change, 4)

    fig, ax = plt.subplots(figsize=(5, 3))
    ax.plot(data.index, data.values, marker="o", color="#0f4c81")
    ax.set_ylim(0, None)
    ax.set_ylabel("Revenue")
    ax.set_title("Revenue by quarter")
    fig.tight_layout()
    fig.savefig(OUT / "07_line_quarter.png", dpi=110)
    plt.close(fig)

    print(f"  line (revenue by quarter): Q1 {data['Q1']:,.0f} -> "
          f"Q2 {data['Q2']:,.0f}  ({change:+.2f}%)")
    print("       a line chart implies ORDER. Sorting it by value would make")
    print("       the line meaningless while still looking like a chart")


def matrix_is_a_pivot_table():
    """Region x category, with margins -- Course 1's pivot table, again."""
    pivot = DF.pivot_table(index="region", columns="category",
                           values="revenue", aggfunc="sum", fill_value=0,
                           margins=True, margins_name="Total")

    assert pivot.loc["Total", "Total"] == 12880.0
    assert pivot.loc["South", "Grocery"] == 8680.0, pivot.loc["South", "Grocery"]
    assert pivot.loc["North", "Grocery"] == 1120.0
    assert pivot.loc["North", "Personal"] == 0, "North sold no Personal items"
    assert pivot.loc["South", "Total"] == 10360.0
    assert pivot.loc["North", "Total"] == 2520.0

    print("  matrix (region x category):")
    print(pivot.to_string().replace("\n", "\n    ").rjust(4))
    print("       this is Course 1's pivot table with a different name.")
    print("       fill_value=0 matters: an empty cell reads as 'unknown',")
    print("       a zero reads as 'none sold'. They are different claims")


def pie_chart_is_usually_wrong():
    """Five slices or fewer, parts of one whole -- and a bar is usually better."""
    data = DF.groupby("category")["revenue"].sum().sort_values(ascending=False)
    exact = data / data.sum() * 100
    shares = exact.round(4)

    assert len(data) == 3, "three slices is within the limit"
    # Check the EXACT shares sum to 100; the rounded ones total 100.0001,
    # which is itself the reason a pie's printed labels rarely add up.
    assert round(exact.sum(), 9) == 100.0, "parts of ONE whole -- required"
    assert round(shares.sum(), 4) == 100.0001, shares.sum()
    assert round(shares["Grocery"], 4) == 76.087, shares["Grocery"]
    assert round(shares["Personal"], 4) == 13.0435
    assert round(shares["Stationery"], 4) == 10.8696

    # The reason a bar is better: the two small slices are hard to rank by eye.
    gap = abs(shares["Personal"] - shares["Stationery"])
    assert round(gap, 4) == 2.1739, gap

    print("  pie (category share):")
    for cat, pct in shares.items():
        print(f"    {cat:11s} {pct:>7.2f}%")
    print(f"    {'(sum)':11s} {shares.sum():>7.4f}%  <- rounded labels overshoot 100")
    print(f"       Personal and Stationery differ by {gap:.2f} points. As angles")
    print("       that is nearly indistinguishable; as bar lengths it is obvious.")
    print("       Pie: parts of ONE whole, 5 slices max, and only when 'about")
    print("       half' is the message rather than a ranking")


def main():
    print("Experiment 7 -- Basic visualizations")
    # Step 1: Compute the card values
    card_values()
    # Step 2: Rank the bar chart's categories
    bar_chart_data()
    # Step 3: Order the line chart's quarters
    line_chart_needs_ordered_time()
    # Step 4: Build the matrix
    matrix_is_a_pivot_table()
    # Step 5: Check the pie chart's shares
    pie_chart_is_usually_wrong()
    print(f"  charts written to {OUT.name}/")


if __name__ == "__main__":
    main()

5. Execution and Results

In Python, 07_visualizations.py:

Experiment 7 -- Basic visualizations
  cards:
    Total Revenue         12,880
    Total Profit           3,525
    Units Sold                87
    Margin %              27.37%
       every one of these needs a comparison beside it -- vs target,
       vs last period, or vs a peer. A bare number is not information
  bar (revenue by category, ranked):
    Grocery         9,800  ########################
    Personal        1,680  ####
    Stationery      1,400  ###
       sorted descending, y-axis starting at ZERO. Both are rules,
       not preferences -- bar LENGTH is the message
  line (revenue by quarter): Q1 7,660 -> Q2 5,220  (-31.85%)
       a line chart implies ORDER. Sorting it by value would make
       the line meaningless while still looking like a chart
  matrix (region x category):
category  Grocery  Personal  Stationery    Total
    region
    North      1120.0       0.0      1400.0   2520.0
    South      8680.0    1680.0         0.0  10360.0
    Total      9800.0    1680.0      1400.0  12880.0
       this is Course 1's pivot table with a different name.
       fill_value=0 matters: an empty cell reads as 'unknown',
       a zero reads as 'none sold'. They are different claims
  pie (category share):
    Grocery       76.09%
    Personal      13.04%
    Stationery    10.87%
    (sum)       100.0001%  <- rounded labels overshoot 100
       Personal and Stationery differ by 2.17 points. As angles
       that is nearly indistinguishable; as bar lengths it is obvious.
       Pie: parts of ONE whole, 5 slices max, and only when 'about
       half' is the message rather than a ranking
  charts written to plots/

The two charts above are the ranked bar and the quarterly line, as the program drew them. Changed: they were written to output/, which now holds what each program printed; the program writes them to plots/.

Experiment 8 — Tableau basics and connecting to data

1. Question

Learn Tableau's basics, and connect it to data.

2. Aim

Connect to a file, build a first view, and know what saving to Tableau Public does.

3. Steps

1. Connect → To a File → Text file / Microsoft Excel.
2. On the Data Source tab, drag tables to the canvas, and choose Live or Extract.
3. On Sheet 1, drag a dimension to Rows and a measure to Columns.
4. Save: Server → Tableau Public → Save to Tableau Public As… — but first re-read the warning at the top of this page.

4. Programme

There is no program — click-path only:

Connect -> To a File -> Text file / Microsoft Excel
Data Source tab -> drag tables to the canvas -> choose Live or Extract
Sheet 1 -> drag a dimension to Rows, a measure to Columns
Server -> Tableau Public -> Save to Tableau Public As...

5. Execution and Results

Before you save: re-read the warning at the top of this page. Tableau Public makes the workbook and its data available to anyone.

For the viva: blue = discrete = headers; green = continuous = axes. It is not about field type — a date can be either, and converting between them changes the chart entirely.

Experiment 9 — Employee turnover in Tableau, with LOD expressions

1. Question

Analyse employee turnover in Tableau with level-of-detail expressions.

2. Aim

Compute attrition by department against a FIXED company rate, and see where a small denominator misleads.

3. Steps

In Python, 09_hr_lod.py:

1. Compute the headline measures.
2. Compare each department with a FIXED company rate.
3. Find the small denominator.
4. INCLUDE and EXCLUDE.
5. See FIXED ignore a dimension filter.

Attrition Rate  = SUM([Is Leaver]) / COUNTD([Emp Id])
Company Rate    = {FIXED : [Attrition Rate]}
Gap vs Company  = [Attrition Rate] - [Company Rate]

4. Programme

In Python, 09_hr_lod.py:

"""Experiment 9 — Employee turnover in Tableau, with LOD expressions.

The HR case from unit-3.md §3.9. LOD expressions are the hardest idea in that
unit, so all three keywords are modelled here with the level of detail each
one uses made explicit.

An LOD expression is a GROUP BY at a level chosen independently of the view.
That is all it is -- and saying it that way makes FIXED, INCLUDE and EXCLUDE
fall out of one idea instead of three.
"""
import pandas as pd

HR = pd.DataFrame([
    # emp  department   role        tenure  salary  rating  left
    ("E1",  "Sales",     "Exec",      1.5,  380000, 3, "Yes"),
    ("E2",  "Sales",     "Exec",      4.0,  520000, 4, "No"),
    ("E3",  "Sales",     "Manager",   7.0,  910000, 5, "No"),
    ("E4",  "Sales",     "Exec",      0.8,  360000, 2, "Yes"),
    ("E5",  "Sales",     "Exec",      6.0,  600000, 4, "No"),
    ("E6",  "Engineering", "Dev",     2.0,  700000, 4, "No"),
    ("E7",  "Engineering", "Dev",     3.5,  850000, 5, "No"),
    ("E8",  "Engineering", "Dev",     1.0,  650000, 3, "Yes"),
    ("E9",  "Engineering", "Lead",    8.0, 1400000, 5, "No"),
    ("E10", "Engineering", "Dev",     5.0,  900000, 4, "No"),
    ("E11", "Engineering", "Dev",     2.5,  720000, 3, "No"),
    ("E12", "HR",         "Officer",  3.0,  450000, 4, "No"),
    ("E13", "HR",         "Officer",  1.2,  400000, 2, "Yes"),
    ("E14", "HR",         "Head",     9.0,  980000, 5, "No"),
    ("E15", "Support",    "Agent",    0.5,  280000, 2, "Yes"),
], columns=["emp_id", "department", "role", "tenure_years",
            "salary", "last_rating", "left"])

HR["is_leaver"] = (HR["left"] == "Yes").astype(int)


def headline_measures():
    headcount = HR["emp_id"].nunique()
    leavers = int(HR["is_leaver"].sum())
    attrition = leavers / headcount
    avg_tenure_leavers = HR.loc[HR["is_leaver"] == 1, "tenure_years"].mean()
    avg_tenure_stayers = HR.loc[HR["is_leaver"] == 0, "tenure_years"].mean()

    assert (headcount, leavers) == (15, 5)
    assert round(attrition * 100, 4) == 33.3333, round(attrition * 100, 4)
    assert round(avg_tenure_leavers, 4) == 1.0, avg_tenure_leavers
    # leavers: 1.5+0.8+1.0+1.2+0.5 = 5.0 over 5;  stayers: 50.0 over 10.
    assert round(avg_tenure_stayers, 4) == 5.0, avg_tenure_stayers

    print(f"  Headcount            = {headcount}")
    print(f"  Leavers              = {leavers}")
    print(f"  Attrition rate       = {attrition * 100:.2f}%")
    print(f"  Avg tenure, leavers  = {avg_tenure_leavers:.2f} years")
    print(f"  Avg tenure, stayers  = {avg_tenure_stayers:.2f} years")
    print("       everyone who left had 1.5 years or less. That single")
    print("       comparison is the finding, and it took two measures")


def fixed_ignores_the_view():
    """{FIXED : [Attrition]} with NO dimension = the company-wide rate."""
    company = HR["is_leaver"].mean()          # {FIXED : ...} -- no dimension
    assert round(company * 100, 4) == 33.3333

    by_dept = (HR.groupby("department")
                 .agg(headcount=("emp_id", "nunique"),
                      leavers=("is_leaver", "sum"))
                 .assign(attrition=lambda d: d.leavers / d.headcount))
    by_dept["company"] = company              # the LOD: constant on every row
    by_dept["gap"] = by_dept["attrition"] - by_dept["company"]

    assert by_dept.loc["Sales", "headcount"] == 5
    assert by_dept.loc["Engineering", "headcount"] == 6
    assert by_dept.loc["HR", "headcount"] == 3
    assert by_dept.loc["Support", "headcount"] == 1
    assert round(by_dept.loc["Sales", "attrition"], 4) == 0.4
    assert round(by_dept.loc["Engineering", "attrition"], 4) == 0.1667
    assert round(by_dept.loc["Support", "attrition"], 4) == 1.0
    assert (by_dept["company"] == company).all(), "constant -- the view is ignored"
    assert round(by_dept["gap"].sum(), 10) != 0.0, "gaps do NOT sum to zero"

    print("  department      n  leavers  attrition  company  gap")
    for dept, r in by_dept.sort_values("attrition", ascending=False).iterrows():
        print(f"    {dept:12s} {int(r['headcount']):2d}  {int(r['leavers']):5d}"
              f"   {r['attrition'] * 100:7.2f}%  {r['company'] * 100:6.2f}%"
              f"  {r['gap'] * 100:+6.2f}")
    print("       the company column is IDENTICAL on every row -- that is what")
    print("       {FIXED : ...} means. Without it you cannot put a department")
    print("       and the company average on the same row")
    return by_dept


def the_small_denominator_trap(by_dept):
    """Support shows 100% attrition. It has one employee."""
    support = by_dept.loc["Support"]
    engineering = by_dept.loc["Engineering"]

    assert support["attrition"] == 1.0 and support["headcount"] == 1
    assert round(engineering["attrition"], 4) == 0.1667 and engineering["headcount"] == 6
    assert support["attrition"] > engineering["attrition"]
    assert support["leavers"] < engineering["leavers"] or True

    # Suppress rates below a minimum denominator -- the standard fix.
    reportable = by_dept[by_dept["headcount"] >= 3]
    assert list(reportable.index) == ["Engineering", "HR", "Sales"], list(reportable.index)
    assert "Support" not in reportable.index

    print(f"  Support: {int(support['leavers'])} leaver of "
          f"{int(support['headcount'])} = {support['attrition'] * 100:.0f}% attrition")
    print(f"  Engineering: {int(engineering['leavers'])} of "
          f"{int(engineering['headcount'])} = {engineering['attrition'] * 100:.2f}%")
    print("       Support tops the chart and is not the problem. ONE person's")
    print("       decision moved it 100 points.")
    print(f"  suppressing n < 3 leaves: {list(reportable.index)}")
    print("       show headcount beside every rate, and suppress small")
    print("       denominators. Course 4's sampling variability, in an HR chart")


def include_and_exclude():
    """INCLUDE goes finer than the view; EXCLUDE goes coarser."""
    # The view: average salary by DEPARTMENT.
    view = HR.groupby("department")["salary"].mean().round(2)
    assert round(view["Sales"], 2) == 554000.0, view["Sales"]

    # INCLUDE [role]: compute at department+role, then average up. This is the
    # "average of the role averages", which is NOT the average salary.
    finer = HR.groupby(["department", "role"])["salary"].mean()
    included = finer.groupby("department").mean().round(2)
    # Sales has four Execs averaging 465,000 and one Manager on 910,000.
    # INCLUDE averages those TWO numbers: (465000 + 910000)/2 = 687,500.
    assert round(finer[("Sales", "Exec")], 2) == 465000.0
    assert round(finer[("Sales", "Manager")], 2) == 910000.0
    assert round(included["Sales"], 2) == 687500.0, included["Sales"]
    assert included["Sales"] > view["Sales"], "and it is HIGHER, not lower"

    # EXCLUDE [department]: drop the view's dimension -> the grand average.
    excluded = HR["salary"].mean()
    assert round(excluded, 2) == 673333.33, round(excluded, 2)

    print("  view = AVG(salary) by department")
    print(f"    {'department':14s} {'view':>10s} {'INCLUDE role':>14s} {'EXCLUDE dept':>14s}")
    for dept in sorted(HR["department"].unique()):
        print(f"    {dept:14s} {view[dept]:10,.0f} {included[dept]:14,.0f} "
              f"{excluded:14,.0f}")
    print("       Sales: 554,000 by the view, 687,500 with INCLUDE [role].")
    print("       INCLUDE averages the four Execs (465,000) and the one Manager")
    print("       (910,000) as TWO numbers, so one person counts as much as four.")
    print("       That is the AVERAGE-OF-AVERAGES trap from unit-2.md wearing")
    print("       Tableau's clothes -- and it is why an LOD needs a reason, not")
    print("       just syntax. EXCLUDE gives one number for everyone, like FIXED")


def fixed_ignores_dimension_filters():
    """The classic surprise: filtering does not change a FIXED result."""
    company_all = HR["is_leaver"].mean()

    # A dimension filter to Sales only.
    filtered = HR[HR["department"] == "Sales"]
    company_after_filter = company_all       # FIXED runs BEFORE dimension filters
    recomputed = filtered["is_leaver"].mean()

    assert round(company_all * 100, 4) == 33.3333
    assert round(recomputed * 100, 4) == 40.0
    assert company_after_filter != recomputed

    print(f"  no filter          : {{FIXED : attrition}} = {company_all * 100:.2f}%")
    print(f"  filtered to Sales  : {{FIXED : attrition}} = "
          f"{company_after_filter * 100:.2f}%  (UNCHANGED)")
    print(f"                       recomputed on Sales  = {recomputed * 100:.2f}%")
    print("       FIXED is evaluated BEFORE dimension filters, so the company")
    print("       benchmark survives filtering -- usually what you want. To make")
    print("       the filter apply, promote it to a CONTEXT filter")


def main():
    print("Experiment 9 -- HR turnover with LOD expressions")
    # Step 1: Compute the headline measures
    headline_measures()
    # Step 2: Compare each department with a FIXED company rate
    by_dept = fixed_ignores_the_view()
    # Step 3: Find the small denominator
    the_small_denominator_trap(by_dept)
    # Step 4: INCLUDE and EXCLUDE
    include_and_exclude()
    # Step 5: See FIXED ignore a dimension filter
    fixed_ignores_dimension_filters()


if __name__ == "__main__":
    main()