Webbo3 Power BI · Cohort 1 · Day 3 of 16
Day 3: Data Transformation in Power Query
Cleaning made the data right. Transforming makes it useful: new columns, new shapes, and totals you can check.
By the end of today you can
- Add a column that multiplies two others, and use it to check the file's own totals
- Add a conditional column that labels each sale before or after the fuel subsidy removal
- Pull the year and month out of a date
- Extract part of a text, and split one column into two
- Summarise a table with Group By
- Turn a long table wide with Pivot, and back again with Unpivot
Cleaning answers "is this data right?" Transforming answers "is it in the shape I need to answer Alhaji Musa Bello?"
Today you work on your clean Sales_2023 query from Day 2 (3,116 rows). Every transformation is still an Applied Step, so it still replays on refresh, and every one is made with a button.
1. Columns From Other Columns
Definition
The Add Column tab builds a new column from the ones you already have, row by row, without changing the originals. Standard does arithmetic on the columns you select: add, subtract, multiply, divide.
Real-Life Example
An auditor checking receipts. She does not trust the total printed at the bottom; she multiplies quantity by price herself and compares. If they agree, she trusts the receipt.
Types: Where New Columns Come From
| Button | Makes | Today |
|---|---|---|
| Standard (Multiply, Subtract...) | Arithmetic across the selected columns | A check on TotalAmount_NGN |
| Conditional Column | A label chosen by rules, without typing a formula | Before or after the subsidy |
| Date | Year, month, day and more from a date | Year, Month, Month Name |
| Extract | Part of a text, into a new column | A short station name |
Checking the File's Own Arithmetic
Click path
Filter the Check column for anything other than 0: 0 rows. Every total in the sheet is exactly quantity times price, so TotalAmount_NGN can be trusted as the revenue. Now delete the Multiplication and Check columns (right-click, Remove): they were for checking, and a model should only carry what the report needs.
Picture the auditor with her calculator: trust the total only after you have done the sum.
2. Conditional Columns
Definition
A conditional column gives each row a label chosen by rules you set in a dialog: if this, then that, else something else. The rules are tested from the top, and the first one that is true wins.
Real-Life Example
The fuel subsidy was removed in June 2023, and pump prices tripled almost overnight. Alhaji Musa Bello wants every sale split into "before" and "after", so he can compare the two worlds his business has lived in.
Click path
Set the new column's type to Text. Tomorrow and all through Week 2 this one column lets any visual compare before and after with a single click.
Picture a line drawn across the calendar at 1 June 2023, and every sale landing on one side of it.
3. Working With Dates
Definition
Because Date is a real date (Day 2), Power Query can read its parts for you: the year, the month number, the month name, the quarter, the day of the week.
Real-Life Example
A calendar on the wall already knows that 13 October 2023 is a Friday in the fourth quarter. You do not work it out; you look.
Click path
Month Name sorts alphabetically (April, August, December...). You keep the Month number column so that on Day 7 you can tell Power BI to sort the names in calendar order.
4. Extract and Split
Definition
Extract copies part of a text into a new column and leaves the original alone. Split Column cuts one column into two or more, and the original is replaced.
Real-Life Example
"Independence Layout Filling Station" is a lot of words for a chart label when every station is a filling station. A short name, "Independence Layout", reads better on a phone.
Types
| Button | Keeps the original? | Use it when |
|---|---|---|
| Extract, Text Before Delimiter | Yes, adds a new column | You want a shorter label as well |
| Split Column, By Delimiter | No, replaces it | One column really holds two things |
Click path
Keep StationName. On Day 5 it is the key that joins each sale to Station_Master, and the short name is only for labels.
Split: First Name and Surname
Click path
Split was for practice. Delete the step now: AttendantName must stay whole, because it is what each sale is joined on later.
Picture 7 short names on a phone screen, each one readable at a glance.
5. Group By
Definition
Group By turns many rows into one row per group, with a total, count or average for each. It throws the detail away, so you do it on a copy.
Real-Life Example
The end-of-month report. The station manager does not read Head Office every receipt; he reads one line per product with the total.
Click path
A reference query starts from the result of Sales_2023 and adds its own steps, so the original keeps all its rows. Unticking Enable load keeps the check out of the model: it is for you, not for the report.
Filter ProductName to PMS (Petrol) first, then Group By Price Period with Sum of Quantity, Sum of TotalAmount_NGN and Count Distinct of Month. The price per litre is revenue divided by litres; you will build it properly as a measure in Week 2.
Picture the monthly report: one line per group, the receipts filed away behind it.
6. Pivot and Unpivot
Definition
A long table has one column for the label and one for the value. A wide table spreads one kind of value across many columns, such as a column for each quarter. People like to read wide tables; Power BI needs long ones. Pivot turns long into wide; Unpivot turns wide back into long.
Real-Life Example
A school timetable is wide: days across the top, periods down the side, easy to read on a wall. But to ask "how many Maths lessons are there a week?" you would rather have a list: one line per lesson.
Build a wide table to practise on
It reads well, but you cannot draw one line of revenue over the quarters from it, because the quarter is hiding in the column names. Unpivot moves it back into the data:
Click path
Why Unpivot Other Columns rather than selecting Q1 to Q4? If next year's sheet adds another column, "other columns" picks it up automatically; a list of named columns would miss it.
Picture the timetable on the wall, and the same timetable as a list you can count.
Do This Now
- Multiply Quantity by UnitPrice_NGN, round, subtract TotalAmount_NGN, and check the result is always 0. Then remove both helper columns.
- Add the Price Period conditional column.
- Add Year, Month and Month Name.
- Extract the short Station name. Split AttendantName for practice, then delete that step.
- Make the two reference queries and Group By, untick their Enable load, and check your totals.
- Build the quarterly wide table with Pivot and turn it back with Unpivot.
Checkpoint
Q1. Why check TotalAmount_NGN against quantity times price before trusting it?
Because it was typed or calculated by someone else. The check found 0 differences, so the totals can be used as revenue.
Q2. Why does the order of rules in a conditional column matter?
Rules are tested top to bottom and the first true one wins.
Q3. Why Group By on a reference and not on the query you load?
Group By throws away the detail rows. The report needs them so visuals can slice them any way.
Q4. What did the petrol table show about the price jump?
Prices per litre roughly tripled after June 2023, but litres sold per month did not fall. Customers kept buying about the same amount at a much higher price.
Tick Before You Move On
- ☐ My check column was 0 on every row, and I removed it
- ☐ Price Period labels every sale before or after 1 June 2023
- ☐ Year, Month and Month Name come from the Date column
- ☐ My Check_By_Station total is ₦57,056,529
- ☐ I turned a table wide with Pivot and long again with Unpivot
Quick Recap
Add Column, Standard does arithmetic with clicks; use it to check a file before trusting it. Conditional columns label rows by rules tested in order. A real date gives you its year, month and name for free. Extract adds a column and keeps the original; Split replaces it. Group By summarises on a reference copy; Pivot makes wide, Unpivot makes long.
Final Scenario
Alhaji Musa Bello asks: "When the price jumped, did people just buy less fuel?"
Your petrol table answers in two rows: about ₦185 a litre before, ₦585 after, and litres per month did not drop. Customers paid the new price and kept coming.
One conditional column and one Group By, and nothing typed by hand.
Next, Day 4 Lab: the whole Sales_2023 cleaning built together as a class, with the mistakes everyone makes and how to find them. Assignment 1 is released at the end.