Webbo3 Power BI · Cohort 1 · Day 6 of 16 · Lab
Day 6 Lab: Append, Merge and Loading to the Model
The two-year Sales table built together, loaded properly, and your first look at what a model is.
By the end of today you can
- Build the Append and the Merges as a class, checking every count
- Decide which queries load to the report and which do not
- Load with Close & Apply and check what arrived
- Explain what a data model is, and why one flat table is not enough
- Recognise the symptom of a missing relationship
Week 2 starts by finishing Week 1 properly: one Sales table for two years, clean, joined, and loaded into the model.
Hand in Assignment 1 before class if you have not. Today's lab starts from Sales_2023_Clean and Sales_2024_Clean; if yours differs from the class, the tutor will share a working file so nobody is left behind.
1. The Build, With Checkpoints
Definition
Same lab rules as Day 4: after each step, compare your row count with the class. Append and Merge have their own checks: an append must add up exactly, and a Left Outer merge must never change the row count of the left table.
Real-Life Example
An accountant adding two months of receipts together checks the total equals the two monthly totals added. If it does not, a receipt was lost or counted twice.
A Left Outer merge adds a row for every match. If the right table had two rows with the same key (two rows both called "Wuse Filling Station", say), every Wuse sale would appear twice. Master sheets must have one row per key. Check with Column profile: the key column's distinct count must equal its row count.
Picture the accountant: two monthly totals in, one combined total out, and they must agree.
2. Loading to the Model
Definition
Close & Apply runs every query and loads the ones marked Enable load into the model: Power BI's own storage, where the report reads from. A query with Enable load unticked still runs (other queries use it) but never appears in the report.
Real-Life Example
A kitchen prepares stock, chopped onions and blended pepper, but only the finished dishes go out to the dining room. The stock is essential, and no customer ever sees it.
Types: What Loads Today
| Query | Enable load | Why |
|---|---|---|
| Sales_2023_Clean | Off | A step on the way to Sales |
| Sales_2024_Clean | Off | A step on the way to Sales |
| Sales | On | The table every measure will add up |
| Station_Master | On | City, region and the yearly target for each station |
| Product_Master | On | Category (Fuel or Lubricant) and unit for each product |
| Attendant_Directory | On | Each attendant and the station they work at |
| Station_Targets | Off | Already merged into Station_Master on Day 5 |
| Check queries (Group By, Left Anti) | Off | For you, not for the report |
Click path
Sales has 12 columns: the original 9, plus City, Region and Category from the Day 5 merges. Station_Master has one more column than the sheet: the yearly target merged in on Day 5.
Picture the kitchen: stock on the stove, dishes through the door.
3. What Is a Data Model?
Definition
A data model is the set of tables you loaded, the relationships that connect them, and the measures that calculate from them. It is what sits behind every visual: a chart does not read your CSV files, it asks the model.
Real-Life Example
A school office. Pupils are in one file, classes in another, teachers in a third, and a timetable links them. Nobody writes the class teacher's phone number on every pupil's record; you look it up through the links. That office is a model. One enormous spreadsheet with everything on every line is not.
Types: What Goes in a Model
| Part | What it is | Webbo Oil example | Built on |
|---|---|---|---|
| Tables | The loaded queries | Sales, Station_Master, Product_Master, Attendant_Directory | Today |
| Relationships | Lines between tables on a shared key | Sales[ProductName] to Product_Master[ProductName] | Day 7 |
| Measures | Calculations written in DAX | Total Revenue | Day 8 |
| A Date table | One row per day, for time calculations | Date | Days 7 and 12 |
Why Not One Big Flat Table?
Day 5 merged Region and Category into Sales, which is handy. But it means "South West" is now stored on every sale at three stations instead of once, on Station_Master:
"South West" is written 2,653 times. If Webbo Oil moves Bodija into a new region tomorrow, a model that reads Region from Station_Master changes one row; a flat table has to be rebuilt. Power BI compresses repeated values well, so size is not the main problem. Clarity is: a flat table has no single place where a product's details live. Tomorrow's star schema fixes that.
The Symptom of a Missing Relationship
Power BI draws some relationships by itself on load: when two tables share a column name, it guesses they belong together. Sales and Station_Master both have StationName, so a line probably appeared already. Open Model view, click that line, and press Delete. Then put Station_Master[Region] in a Table visual next to the sum of Sales[TotalAmount_NGN]:
Every region shows the grand total, because Region cannot filter Sales without a relationship. Undo the delete (Ctrl+Z) and the same visual shows the real split:
The same number repeated on every row of a visual almost always means a missing or broken relationship. You will see it in every project you build.
Picture the school office: each fact kept once, in its own file, and found through the links.
Do This Now
- Rebuild the Append and both Merges with the class, checking every count.
- Check each lookup table has one row per key.
- Untick Enable load on the yearly queries, the check queries and Station_Targets, then Close & Apply.
- Check the four tables and their row counts in Table view.
- Build the Region and revenue table visual, delete the Station relationship to see the repeated total, then undo it.
Checkpoint
Q1. A Left Outer merge increased your row count. What is wrong?
The right table has more than one row for some key, so those sales matched twice. Fix the lookup table so each key appears once.
Q2. What does unticking Enable load do?
The query still runs and other queries can use it, but it is not loaded into the model or shown in the report.
Q3. What are the parts of a data model?
Tables, the relationships between them, and the measures that calculate from them (plus a Date table for time calculations).
Q4. Every row in your visual shows the same number. What do you check first?
The relationship between the table the rows come from and the table the number comes from.
Tick Before You Move On
- ☐ Sales has 6,181 rows after Close & Apply
- ☐ Only Sales, Station_Master, Product_Master and Attendant_Directory load
- ☐ Each master sheet has one row per key
- ☐ I have seen the repeated-total symptom for myself
- ☐ I can say what a data model is in one sentence
Quick Recap
An append must add up; a Left Outer merge must not change the row count. Enable load decides what reaches the report. A model is tables, relationships and measures. Keep each fact once, in its own table. The same number on every row means a missing relationship.
Final Scenario
You show the manager a table of revenue by region and every region says the same amount.
Before she can ask, you say "the tables are not linked yet" and open Model view. That sentence, said calmly, is the difference between a beginner and an analyst.
Tomorrow you draw the links.
Next, Day 7: the star schema, and creating relationships.