Power Query, Star Schema & Data Modelling (Adventure Cycles)

Why modelling matters more than visuals

Beautiful visuals on a bad model give wrong or slow answers. In Power BI the data model decides what numbers mean: how tables relate, which table filters which, and how fast DAX runs. The golden rule: model as a star schema.

	Fact table	Dimension table
Describes	Events or transactions (a sale, a click, a shipment)	The "who, what, where, when" of those events
Rows	Many (millions)	Fewer (thousands)
Columns	Keys + numeric measures (quantity, amount, cost)	Keys + descriptive attributes (name, category, city)
Changes	Grows constantly	Changes rarely
Example	fact_sales	dim_product, dim_customer, dim_date

Why a star beats one big flat table: smaller file (text stored once), faster visuals, one place to fix a name, correct filtering across several facts, and simpler DAX. A snowflake (dimensions normalised into sub-dimensions) works but adds joins; prefer a star in Power BI.

The Adventure Cycles dataset

A fictional bicycle company sells bikes, components, clothing and accessories in India, the UAE and Singapore. The model has one fact table and four dimensions (plus the date table):

PythonCopy

import pandas as pd
D = "adventure_cycles/"
fact = pd.read_csv(D + "fact_sales.csv", parse_dates=["order_date"]); prod = pd.read_csv(D + "dim_product.csv")
cust = pd.read_csv(D + "dim_customer.csv"); terr = pd.read_csv(D + "dim_territory.csv"); dates = pd.read_csv(D + "dim_date.csv", parse_dates=["date"])
for name, df in [("fact_sales", fact), ("dim_product", prod), ("dim_customer", cust), ("dim_territory", terr), ("dim_date", dates)]:
    print(f"{name:14s} {df.shape[0]:>6,} rows × {df.shape[1]} columns")
fact.head(4)

Output

fact_sales      9,000 rows × 9 columns
dim_product        45 rows × 6 columns
dim_customer    1,200 rows × 5 columns
dim_territory       6 rows × 3 columns
dim_date        1,096 rows × 6 columns

	order_number	order_date	product_key	customer_key	territory_key	quantity	unit_price	sales_amount	total_cost
0	SO50387	2023-01-01	11	103	1	3	114,400	343,200	201,360
1	SO55797	2023-01-01	32	798	1	3	200	570	390
2	SO51236	2023-01-01	9	750	5	1	126,700	120,365	78,380
3	SO55082	2023-01-01	13	759	1	1	79,600	75,620	46,670

4 rows × 9 columns

Power Query: prepare each table

Open Transform Data. Typical steps for each table (all recorded in Applied Steps):

Query settings (right pane) shows each step; click any step to see the data at that point, and delete a step to undo it. Rename queries (Sales, Product…) because those names become table names in the model.

Common transformations (from class and beyond)

What is "query folding"?

When the source is a database (SQL Server, Snowflake), Power Query translates your steps into SQL and lets the database do the work, instead of downloading everything first. A step that cannot be translated "breaks folding" and everything after it runs locally. Check by right-clicking a step → View Native Query. Keep foldable steps (filter, select columns, merge, group) before non-foldable ones.

let
    Source   = Csv.Document(File.Contents("C:\Data\fact_sales.csv"), [Delimiter=",", Encoding=65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Typed    = Table.TransformColumnTypes(Promoted, {{"order_date", type date}, {"quantity", Int64.Type},
                   {"sales_amount", Currency.Type}, {"total_cost", Currency.Type}}),
    Profit   = Table.AddColumn(Typed, "profit", each [sales_amount] - [total_cost], Currency.Type)
in
    Profit

Relationships

A relationship links a column in one table to a column in another so that filters can flow between them. In Model view, drag dim_product[product_key] onto fact_sales[product_key]. Power BI usually detects relationships on same-named columns (Home → Manage relationships → Autodetect).

Cardinality

Check uniqueness before creating a one-to-many: if the "one" side has duplicates Power BI will complain.

checks = {"dim_product[product_key]": prod.product_key, "dim_customer[customer_key]": cust.customer_key, "dim_territory[territory_key]": terr.territory_key, "dim_date[date]": dates.date}
for name, col in checks.items(): print(f"{name:30s} unique: {col.is_unique}")
orphans = {"product_key": ~fact.product_key.isin(prod.product_key), "customer_key": ~fact.customer_key.isin(cust.customer_key), "territory_key": ~fact.territory_key.isin(terr.territory_key)}
for k, m in orphans.items(): print(f"fact rows with no matching {k}: {m.sum()}")

dim_product[product_key]       unique: True
dim_customer[customer_key]     unique: True
dim_territory[territory_key]   unique: True
dim_date[date]                 unique: True
fact rows with no matching product_key: 0
fact rows with no matching customer_key: 0
fact rows with no matching territory_key: 0

Unique keys on the "one" side and no orphans on the "many" side: the model is safe.

Filter direction

Seeing the model work

Total sales by product category and by country, using the relationships (a join in pandas):

m = fact.merge(prod, on="product_key").merge(terr, on="territory_key")
m["profit"] = m.sales_amount - m.total_cost
out = m.pivot_table(index="category", columns="country", values="sales_amount", aggfunc="sum", margins=True, margins_name="Total") / 1e6
out.round(1).rename_axis("Sales (₹ million)")