Webbo3 Power BI · Cohort 1 · Day 5 of 16
Day 5: Appending and Merging Queries
Two ways of joining tables: stacking more rows underneath, or bringing more columns alongside.
By the end of today you can
- Say the difference between Append and Merge in one sentence
- Append the clean 2023 and 2024 queries into one Sales table
- Merge Sales with Station_Master and Product_Master on the cleaned names
- Merge Station_Targets into Station_Master on StationID, and sense-check what it says
- Choose the right join kind, and use Left Anti to find rows that do not match
Week 1 ends with one table. Two clean years, joined to what Webbo Oil knows about each station and each product.
Open your file with Sales_2023_Clean and Sales_2024_Clean (Day 4 and Assignment 1) and the four master sheets.
1. Append and Merge: The Difference
Definition
Append stacks one table underneath another: same columns, more rows. Merge puts columns from another table alongside, by matching a column the two tables share: same rows, more columns.
Real-Life Example
Append is stapling February's register under January's: the columns are the same, there are just more names. Merge is looking up each student's home address in the admissions file and writing it next to their name.
| Append | Merge | |
|---|---|---|
| Adds | Rows | Columns |
| Needs | The same column names | A column both tables share |
| Webbo Oil | 2023 and 2024 sales into one table | City and Region onto each sale |
Picture the stapler for Append, and the address book for Merge.
2. Append Queries
Definition
Append Queries as New makes a new query from two or more queries stacked in order. Columns are matched by name, so both clean years must have exactly the same column names, spelled the same way.
Types
| Button | What it does | Use it when |
|---|---|---|
| Append Queries | Adds rows onto the query you are on | Rarely: it changes a query others may use |
| Append Queries as New | Makes a separate, new query | Almost always |
Click path
Untick Enable load on the two yearly queries: they are ingredients now, and loading all three would put every sale in the model twice.
A new column full of null at the end of the table means one year has a column the other lacks, often a spelling difference such as "Totalamount_NGN". Rename it to match and the extra column disappears.
3. Merge Queries
Definition
Merge Queries looks up each row's value in another table and brings matching columns across. You choose the column to match in each table, the join kind, and then which columns to expand.
Real-Life Example
The address book again: you look up "Wuse Filling Station", find its row, and copy "Abuja" and "North Central" next to every Wuse sale.
Merge on Names Only After They Are Clean
Webbo Oil's sales sheets have no StationID or ProductID: they only hold names. A merge matches letter for letter, including capitals and spaces. That is why Day 2 had to make every name identical to the master sheets. The next section proves it.
Click path
Power Query reports how many rows matched at the bottom of the Merge window. You want "The selection matches 6,181 of 6,181 rows". Anything less means a name that is not spelled as on the master sheet.
Now the products
Picture the address book open on the desk: every sale looks up its station and copies the city across.
4. The Targets: Merge on an ID, Then Ask if It Makes Sense
Definition
Station_Targets holds one target per station. It has a StationID, and so does Station_Master, so the two can be merged on an ID, which is the safest kind of key: typed by a system, never by a person.
Click path
The Sense Check
Before any number goes into a report, ask whether it could possibly be true. The column is called MonthlyRevenueTarget_NGN. Put it beside what each station really earns in an average month:
No station comes close to its "monthly" target: on average each reaches between about 4% and 13% of it, and even its best single month never reaches a quarter (the highest is 22%). That is not seven failing stations; it is a label that is wrong. Read as a yearly target, the figures make sense: some stations beat it and some fall short. Head Office confirms the targets are for a year. So you rename the column, in Power Query, where the fix is recorded as a step:
Click path
If you remember "it is really yearly" but leave the column name alone, the next analyst to open the file builds a monthly comparison on it. The renamed column, and the step that renamed it, are the record of what you found.
Picture a scale showing 200 kg for a child: you do not report it, you check the scale.
5. Join Kinds
Definition
The join kind decides which rows survive a merge: the ones in the first table, the second, both, or only the ones that do not match.
Real-Life Example
A guest list and the list of people who arrived. Do you want everyone invited, everyone who came, only those on both lists, or only the invited who did not come?
Types
| Join Kind | Keeps | Use it for |
|---|---|---|
| Left Outer | Every row of the first table; empty where there is no match | Adding details (the default) |
| Inner | Only rows that match in both | Keeping only what matches |
| Right Outer | Every row of the second table | Rarely |
| Full Outer | Every row of both | Comparing two lists |
| Left Anti | Rows of the first table with no match | Finding what failed to match |
Left Anti: Proving Why We Cleaned First
Click path
On the raw sheet 59% of sales would have found no station. After Capitalize Each Word the 440 left over are all "Gra Filling Station", the one the five Replace Values steps fix. A Left Anti merge that comes back empty is the proof your cleaning worked.
Run the Left Anti merge on your own clean Sales query against Station_Master, and again against Product_Master on ProductName. Both should return 0 rows. Delete the test queries afterwards.
Do This Now
- Append Sales_2023_Clean and Sales_2024_Clean as a new query called Sales; untick load on the two years.
- Merge Station_Master into Sales on StationName: expand City and Region.
- Merge Product_Master into Sales on ProductName: expand Category.
- Merge Station_Targets into Station_Master on StationID, do the sense check, and rename the column.
- Run a Left Anti merge to prove every station and product matched.
Checkpoint
Q1. Append or Merge: adding 2024 sales under 2023?
Append: same columns, more rows.
Q2. Append or Merge: adding each sale's region?
Merge: same rows, more columns, matched on StationName.
Q3. Why could the station merge only work after Day 2?
A merge matches letter for letter. Until the names were trimmed, capitalised and the abbreviations fixed, most sales matched no station at all.
Q4. How did you know the target column was mislabelled?
In 24 months, no station ever reached even a quarter of its "monthly" target. Read as yearly, the figures make sense. A number that could not possibly be true is a label problem.
Tick Before You Move On
- ☐ Sales has 6,181 rows: 3,116 plus 3,065
- ☐ Every sale has a City, a Region and a Category
- ☐ Station_Master has AnnualRevenueTarget_NGN, renamed from the monthly label
- ☐ My Left Anti merges return 0 rows
- ☐ The two yearly queries have Enable load unticked
Quick Recap
Append adds rows; Merge adds columns. Append Queries as New, then untick load on the ingredients. A merge matches letter for letter: cleaning comes first. IDs are the safest key; names work once they are clean. Sense-check every number, and fix a wrong label in Power Query.
Final Scenario
Alhaji Musa Bello glances at your first region table and says, "So the targets are monthly, and every station is failing?"
You show him the sense check: in two years no station reached even a quarter of a "monthly" target. He laughs: Head Office always sets them for the year. The column is already renamed, and the step that renamed it is in Applied Steps for the next analyst to see.
The most useful question in analysis is "could this possibly be true?"
Next, Day 6 Lab: Append and Merge together, Close & Apply, and what a data model actually is.