Hello, young data explorer! 👋 Have you ever tried to solve a puzzle but the pieces were all mixed up? Or maybe you had a big bag of colourful beads but they were tangled? That’s what messy data looks like. Power Query is a magical tool inside Excel that helps you untangle, clean, and organise data so you can find answers quickly. This module is your first step to becoming a Certified Power Query for Data Analysis Expert. We will learn together using stories, pictures, and lots of fun examples. Ready? Let’s go!
By the end of this module, you will be able to:
At Sunshine Primary School, the PTA decided to have a big bake sale. Everyone brought cakes, cookies, and doughnuts. But the list of who brought what was a mess! ✏️ Some names were spelled wrong (like "Chidi" and "Chidii"), some amounts were missing, and the treats were all mixed up. Mrs. Ade, the teacher, spent hours trying to count everything. Then her nephew, Tunde, said: “Aunty, why don’t you use Power Query? It can clean this in seconds!” Mrs. Ade was amazed. Power Query fixed the spelling, filled in the missing numbers, and even sorted the treats from most to least. The bake sale was a huge success, and Mrs. Ade became the Data Queen of the school! 👑
Definition: Data is just information – like numbers, words, or pictures. When data is organised neatly, we call it clean data. When it’s mixed up, it’s messy data.
Why important: If data is messy, we cannot make good decisions. Imagine trying to count your pocket money if all the coins were mixed with buttons!
Simple explanation: Data is like a drawer full of socks. If they are paired and folded, you find what you need. If they are tangled, you waste time.
Real-life example: A shopkeeper uses a list of items sold. If the list has mistakes, he might order too much or too little stock.
School example: Your teacher has a list of students and their test scores. If a name is missing, that student might not get a report card.
Home example: Your family’s grocery list. If it says “milk” twice and “egg” zero, you might end up with too much milk!
Nigerian example: A market woman in Lagos keeps a record of how many yams she sells each day. If she writes “3 yam” one day and “three yams” the next, her totals will be wrong.
Illustration (ASCII):
Messy data Clean data ------------ ----------- Chidi, 12 Chidi, 12 chidii, 12 Chidi, 12 (fixed) ChiDi, 12 Chidi, 12 (missing amount) Chidi, 10 (filled)
Mini summary: Data is information. Messy data is confusing. Power Query helps us make it clean and useful.
Definition: Power Query is a tool inside Microsoft Excel (and Power BI) that helps you get data, clean it, and change it into the shape you want.
Why important: It saves hours of manual work. Instead of fixing each mistake by hand, Power Query does it automatically!
Simple explanation: Think of Power Query as a robot that reads your messy data, follows your instructions, and gives you back a neat table.
Real-life example: A bank uses Power Query to combine daily transaction lists from many branches into one clean report.
School example: The librarian uses Power Query to combine book lists from different classrooms into one master list.
Home example: You use it to organise your collection of football cards – sort by team, then by player name.
Nigerian example: A small business owner in Kano uses Power Query to merge sales from her shop, her online store, and her WhatsApp orders.
Illustration (ASCII):
[ Messy data ] ➡️ [ POWER QUERY ] ➡️ [ Clean data ]
Mini summary: Power Query is a smart assistant that turns messy data into neat data.
Definition: "Get & Transform" is the name of the Power Query tool in Excel. Get means we bring data in. Transform means we change it to make it better.
Why important: Every data project starts with getting data, and the real magic happens during transformation.
Simple explanation: It’s like going to the market (Get) and then cooking the food (Transform) to make a tasty meal.
Real-life example: A weather scientist gets temperature data from satellites and transforms it into a weather forecast.
School example: Your class gets survey answers (Get) and then sorts them by favourite subject (Transform).
Home example: You get a list of your toys (Get) and then group them by colour (Transform).
Nigerian example: A farmer gets rainfall data from 10 different villages and transforms it into one table to see which village needs more water.
Illustration (ASCII):
GET TRANSFORM ⬇️ ⬇️ [ Excel table ] ➡️ [ filter, sort, rename ] ➡️ [ clean table ]
Mini summary: Get = bring data; Transform = improve data. Together they make Power Query powerful.
Definition: A data source is where your data lives – like an Excel file, a CSV, a database, or even a web page.
Why important: You cannot use Power Query without telling it where to find your data.
Simple explanation: It’s like telling your robot where the box of Lego is, so it can start building.
Real-life example: A doctor connects to a hospital database to get patient records.
School example: You connect to a shared folder where your class project files are saved.
Home example: You connect to a CSV file that has the family budget.
Nigerian example: A teacher connects to the school’s online portal to download the exam scores of all students.
Illustration (ASCII):
[ Excel file ] ──┐ [ CSV file ] ──┼──> Power Query ──> [ Clean data ] [ Web page ] ──┘
Mini summary: Data source is the home of your data. Power Query can connect to many different homes.
Definition: The Power Query Editor is a special window where you can see your data and perform transformation steps.
Why important: This is your control room. Everything you do to clean data happens here.
Simple explanation: It’s like the kitchen where you chop, stir, and cook your data.
Real-life example: A journalist uses the editor to remove duplicate names from a list of interview participants.
School example: You use it to remove empty rows from your science project data.
Home example: You use it to rename columns from “A” and “B” to “Item” and “Price”.
Nigerian example: A bank clerk uses the editor to combine first name and last name columns into one “Full Name” column.
Illustration (ASCII):
+------------------------------------+ | Power Query Editor | | [ Table view ] [ Steps list ] | | [ Filter ] [ Sort ] [ Add Column ] | +------------------------------------+
Mini summary: The editor is where you see and change your data step by step.
Definition: Columns are the vertical boxes in a table. Sometimes we have too many columns, and we don’t need all of them.
Why important: Fewer columns make our data easier to read and faster to process.
Simple explanation: It’s like taking out the toys you don’t play with from your toy box so you can find your favourite ones quickly.
Real-life example: A school removes the “middle name” column because they only need first and last names.
Home example: You remove the “price per kg” column and keep only “total price”.
Nigerian example: A shop removes the “supplier address” column because they only need the supplier name.
Illustration (ASCII):
Before: [ID] [First] [Last] [Address] [Phone] [Email] After: [ID] [First] [Last] [Phone] (removed Address & Email)
Mini summary: You can delete columns you don’t need to keep your data tidy.
Definition: Filtering means showing only the rows that meet a condition – like keeping only students with scores above 50.
Why important: It helps you focus on the most important data.
Simple explanation: Like using a strainer to keep the noodles and let the water go.
Real-life example: A shopkeeper filters to see only sales above ₦10,000.
School example: A teacher filters to see only students who passed the exam.
Home example: You filter your movie list to show only cartoons.
Nigerian example: A farmer filters his harvest records to see only the days when he harvested more than 50 yams.
Illustration (ASCII):
All rows Filter: Score > 50 [Chidi, 45] ➡️ [Ade, 78] [Ade, 78] ➡️ [Bola, 92] [Bola, 92] ➡️ [Tunde, 63] [Tunde, 63] ➡️
Mini summary: Filtering lets you keep only the rows that match your rule.
Definition: Sorting means arranging rows in a specific order – from A to Z, or from smallest to largest.
Why important: Sorted data is easier to scan and compare.
Simple explanation: Like arranging your books on a shelf by height or by colour.
Real-life example: A teacher sorts students by name to call attendance quickly.
School example: You sort your flashcards from easiest to hardest.
Home example: You sort your clothes by colour before putting them in the wardrobe.
Nigerian example: A market woman sorts her goods by price – cheapest first, so customers can see the deals.
Illustration (ASCII):
Before: [Bola, 45] [Ade, 78] [Chidi, 63] After (sort by name A→Z): [Ade, 78] [Bola, 45] [Chidi, 63]
Mini summary: Sorting arranges your data in a logical order.
Definition: Data type tells Power Query what kind of information is in a column – like text (words), numbers, or dates.
Why important: If Power Query thinks a number is text, you cannot add or multiply it. So we must set the right type.
Simple explanation: Like putting toys in the right boxes: Lego in the Lego box, cars in the car box.
Real-life example: A cashier sets the “Price” column to Decimal Number so he can calculate totals.
School example: You set the “Birthday” column to Date so you can sort by age.
Home example: You set the “Number of guests” column to Whole Number.
Nigerian example: A business owner sets the “Sales Amount” to Currency (₦) so it shows the Naira symbol.
Illustration (ASCII):
Column Old Type New Type --------- --------- --------- Name Text Text (stays) Age Text ➡️ Whole Number Salary Text ➡️ Currency (₦)
Mini summary: Correct data types help Power Query understand your data correctly.
Definition: Renaming means changing the heading of a column to something more meaningful.
Why important: Good names help you and others understand the data easily.
Simple explanation: Like putting a label on a box so you know what’s inside without opening it.
Real-life example: Change “Col1” to “Student Name”.
School example: Change “Marks” to “Test Score”.
Home example: Change “A” to “Day” and “B” to “Activity”.
Nigerian example: Change “AMT” to “Amount Paid (₦)”.
Illustration (ASCII):
Before: [ID] [Fname] [Lname] [Score] After: [ID] [First] [Last] [Test Score]
Mini summary: Clear column names make data easy to read.
Definition: Replace means to change one value to another – like changing “N/A” to “0” or “Lagos” to “LAG”.
Why important: It fixes typos and inconsistent entries.
Simple explanation: Like using a correction tape on your notebook to change a wrong word.
Real-life example: Replace “Kg” with “kg” to make it uniform.
School example: Replace “absent” with “0” in the attendance sheet.
Home example: Replace “Mum” with “Mother” in a family tree.
Nigerian example: Replace “Abj” with “Abuja” in an address column.
Illustration (ASCII):
Before: [Lagos] [LAG] [lagos] [Lagos State] After: [Lagos] [Lagos] [Lagos] [Lagos] (all fixed)
Mini summary: Replacing fixes errors and makes data consistent.
Definition: A custom column is a new column that you create by doing a calculation – like Price × Quantity = Total.
Why important: It helps you get new information from existing data.
Simple explanation: Like adding a new row in your Lego building plan to count how many bricks you used.
Real-life example: Add a “Profit” column by subtracting Cost from Sales.
School example: Add a “Total Score” column that adds Test + Homework.
Home example: Add a “Total Cost” column = Price × Quantity.
Nigerian example: Add “Price in Naira” by converting Dollars to Naira using an exchange rate.
Illustration (ASCII):
[Price] [Qty] ➡️ [Total] = Price * Qty 100 2 ➡️ 200 50 3 ➡️ 150
Mini summary: Custom columns let you calculate new values from existing ones.
Definition: Duplicates are rows that are exactly the same. Removing them keeps only one copy.
Why important: Duplicates can make your counts wrong – like counting the same student twice.
Simple explanation: Like removing extra copies of the same picture from your photo album.
Real-life example: A voter list has the same name twice. Remove duplicates to get a unique list.
School example: A list of book titles has duplicate entries. Remove them to see unique titles.
Home example: A guest list has “Uncle Tunde” twice. Remove one.
Nigerian example: A company’s client list has the same phone number for two rows. Remove the duplicate.
Illustration (ASCII):
Before: [Ade] [Ade] [Bola] [Ade] [Chidi] After: [Ade] [Bola] [Chidi] (unique)
Mini summary: Remove duplicates to keep only one unique row of each value.
Definition: Splitting means taking one column and dividing it into two – like splitting “Full Name” into “First Name” and “Last Name”.
Why important: Sometimes information is packed together; splitting makes it easier to work with.
Simple explanation: Like cutting a sandwich into two halves so you can share.
Real-life example: Split “Address” into “Street” and “City”.
School example: Split “Subject/Score” into “Subject” and “Score”.
Home example: Split “Item Colour” into “Item” and “Colour”.
Nigerian example: Split “Phone Number” into “Area Code” and “Number”.
Illustration (ASCII):
Before: [Full Name]
Adeola Ojo
Bola Smith
After: [First] [Last]
Adeola Ojo
Bola Smith
Mini summary: Splitting separates combined data into two or more columns.
Definition: Merging is the opposite of splitting – we combine two columns into one, like “First Name” + “Last Name” = “Full Name”.
Why important: It helps to create a single identifier or a more readable column.
Simple explanation: Like putting two pieces of a puzzle together to make one big piece.
Real-life example: Merge “City” and “State” into “Location”.
School example: Merge “Class” and “Section” into “Class-Section”.
Home example: Merge “Day” and “Month” into “Date”.
Nigerian example: Merge “LGA” and “State” into “Full Address”.
Illustration (ASCII):
Before: [First] [Last]
Adeola Ojo
After: [Full Name]
Adeola Ojo
Mini summary: Merging combines two columns into one.
| Feature | Messy Data | Clean Data |
|---|---|---|
| Spelling | Inconsistent (LAG, lagos, Lagos) | Uniform (Lagos) |
| Missing values | Some cells empty | Filled with 0 or N/A |
| Duplicates | Many duplicate rows | Unique rows |
| Sorting | Random order | Logical order (A→Z or 1→10) |
[ Start ]
│
▼
[ Get Data ] ← from Excel, CSV, Web, etc.
│
▼
[ Transform ] ← filter, sort, rename, etc.
│
▼
[ Load ] ← send clean data back to Excel
│
▼
[ Refresh ] ← update with new data later
Well done, young data expert! You have learned the first big steps in Power Query. You know what data is, how to connect to it, and how to clean it using columns, rows, filters, sorts, and data types. You also learned how to remove duplicates, split and merge columns, and even create new columns with calculations. Power Query is like a helpful robot that does the boring work so you can focus on the fun part – finding answers!
| Column A | Column B |
|---|---|
| 1. Filter | A. Arrange rows in order |
| 2. Sort | B. Show only rows that match a rule |
| 3. Data type | C. Tells if column is text or number |
| 4. Merge | D. Combine two columns into one |
| 5. Remove duplicates | E. Keep only one copy of each row |
Answers: 1-B, 2-A, 3-C, 4-D, 5-E
Scenario: Your school has a list of 200 students with their test scores. Some names are spelled differently, some scores are missing, and the list is not in order. Describe how you would use Power Query to fix this.
Scenario 2: You are helping your uncle who runs a grocery store. He has a spreadsheet with sales from three different shops. Each shop has its own format. How can Power Query help?
In groups of four, look at a sample dataset (provided by your teacher) with at least 20 rows and 5 columns. Together, use Power Query to:
Open a CSV file with your favourite game scores. Clean the data by removing duplicates, correcting the data type of the score column, and sorting by score. Write down the steps you took.
Project: Create a Power Query report for a small business that sells three products (e.g., pens, books, erasers). The raw data has the date, product name, quantity sold, and price. Clean the data, add a total sales column, and sort by date. Load the clean data into a new sheet.
Download a sample dataset from your teacher (or use any CSV file). Perform the following transformations: remove columns, filter rows, sort, change data types, remove duplicates, and add one custom column. Submit a screenshot of the Power Query Editor showing the steps.
Find a messy dataset online (e.g., weather data, sports stats). Use Power Query to clean it completely. Write a short report explaining each step you took. Present your findings to the class.
Fill-in-the-Blank answers: 1. Excel, 2. column, 3. filter, 4. Sort, 5. Data type, 6. replace, 7. Custom, 8. duplicates, 9. two, 10. one.
True/False: 1-F, 2-F, 3-T, 4-F, 5-T.
Multiple Choice: 1B, 2C, 3B, 4A, 5C, 6B, 7B, 8B, 9B, 10B, 11B, 12B, 13A, 14B, 15C.
In the next module, we will dive deeper into advanced transformations – like merging tables, grouping data, and writing our own formulas. You will also learn how to combine data from multiple files automatically. Keep practising the basics, and you’ll be ready for more exciting adventures with Power Query!
🌟 You have completed Module One – congratulations! On to Module Two! 🌟
Hello again, data explorer! 👋 In Module 1, we learned what Power Query is and how to do basic cleaning. Now, we will go deeper. We will learn how to fix common problems like extra spaces, wrong dates, and mixed text. We will also learn how to split columns and combine data from different sources. By the end of this module, you will be able to clean data like a professional! Let's dive in! 🏊♂️
In a small bakery in Ibadan, Nigeria, the baker, Mr. Ade, had a problem. He kept records of his daily bread sales, but his data was messy. Some dates were written as "12/05/2025" and others as "May 12, 2025". Some customer names had extra spaces like " Chidi ". He also had a column called "Full Name" but he needed separate "First Name" and "Last Name". He spent hours fixing these problems by hand. Then his daughter, Ngozi, who had just learned Power Query, said, "Daddy, let me help!" She used Power Query to trim spaces, fix dates, split names, and even combine data from two different files. In just a few minutes, everything was perfect. Mr. Ade was amazed and gave Ngozi an extra piece of cake! 🎂
This module will teach you all the tricks Ngozi used!
Definition: Trim means removing unwanted spaces from the beginning or end of text.
Why important: Extra spaces can make it hard to search or match data. "Chidi" and "Chidi " are not the same to a computer.
Simple explanation: It's like cleaning the edges of a paper before putting it in a frame.
Real-life example: A customer list has " Ada " – trim makes it "Ada".
School example: A teacher trims student names before printing certificates.
Home example: Your mom trims the grocery list to remove extra spaces.
Nigerian example: A bank trims account holder names to avoid mismatches.
Illustration:
Before: " Bola " → After: "Bola"
Mini summary: Trim removes extra spaces from text.
Definition: Clean removes characters that are not visible, like line breaks or special symbols.
Why important: These characters can cause errors in calculations or sorting.
Simple explanation: Like wiping dust off a table before placing a book.
Real-life example: Data copied from a website may have hidden symbols – Clean removes them.
School example: A project list from a website has weird characters – Clean fixes it.
Home example: A contact list copied from an email has extra symbols.
Nigerian example: A farmer's crop data from a PDF has hidden line breaks.
Illustration:
Before: "Mango🍎" (with hidden symbol) → After: "Mango"
Mini summary: Clean removes invisible characters.
Definition: Dates can be written in many ways. Power Query can change them to a standard format.
Why important: If dates are in different formats, you cannot sort or calculate correctly.
Simple explanation: It's like making sure all your friends write their birthdays the same way: DD/MM/YYYY.
Real-life example: Change "2025-12-31" to "31-Dec-2025".
School example: Make all exam dates look the same in the school calendar.
Home example: Fix the dates on your family's holiday photos.
Nigerian example: A school in Enugu standardises admission dates.
Illustration:
Before: 12/31/2025 → After: 31/12/2025 (consistent)
Mini summary: Standardise date formats for consistency.
Definition: Splitting means taking one column and dividing it into two or more columns.
Why important: Sometimes one column has too much information, like "Chidi Okonkwo" – we want "Chidi" and "Okonkwo".
Simple explanation: Like cutting a sandwich into two halves.
Real-life example: Split "Full Name" into "First Name" and "Last Name".
School example: Split "Subject-Grade" into "Subject" and "Grade".
Home example: Split "Address" into "City" and "Street".
Nigerian example: Split "Lagos-Nigeria" into "City" and "Country".
Illustration:
Before: "Chidi Okonkwo" → After: "Chidi" | "Okonkwo"
Mini summary: Split columns to separate information.
Definition: Combining merges two or more columns into one.
Why important: Sometimes you need a full name or full address from separate parts.
Simple explanation: Like joining two puzzle pieces to make a bigger piece.
Real-life example: Merge "First Name" and "Last Name" into "Full Name".
School example: Merge "Class" and "Section" into "Class-Section".
Home example: Merge "Street" and "Number" into "Address".
Nigerian example: Merge "LGA" and "State" into "Location".
Illustration:
Before: "Chidi" | "Okonkwo" → After: "Chidi Okonkwo"
Mini summary: Combine columns to create new information.
Definition: Replace Values changes specific text or numbers to something else.
Why important: You might have abbreviations or errors that need to be corrected.
Simple explanation: Like using a white-out pen to fix a mistake.
Real-life example: Change "N/A" to "0".
School example: Change "Absent" to "A".
Home example: Change "Kg" to "Kilogram".
Nigerian example: Change "LAG" to "Lagos".
Illustration:
Before: "N/A" → After: "0"
Mini summary: Replace values to fix errors or standardise.
Definition: A conditional column creates a new column based on a condition (if-then logic).
Why important: You can categorise data automatically.
Simple explanation: Like saying, "If it rains, take an umbrella; else, take a hat."
Real-life example: If sales > 1000, mark "High"; else "Low".
School example: If score ≥ 70, mark "Pass"; else "Fail".
Home example: If price > 500, mark "Expensive"; else "Cheap".
Nigerian example: If crop yield > 500kg, mark "Good Harvest".
Illustration:
Sales | Category 1500 | High 700 | Low
Mini summary: Conditional columns add categories based on rules.
Definition: Group By combines rows with the same value in a column and calculates something like sum, average, or count.
Why important: It summarises large data so you can see totals.
Simple explanation: Like grouping your toys by colour and counting how many of each colour.
Real-life example: Group sales by product to see total sales per product.
School example: Group students by class and count how many in each class.
Home example: Group expenses by category (food, rent) and sum them.
Nigerian example: Group farm produce by crop and sum the weight.
Illustration:
Product | Sales Mango | 100 Mango | 150 Orange | 200 Group By Product → Mango:250, Orange:200
Mini summary: Group By summarises data by categories.
Definition: Pivot turns a column's values into multiple columns.
Why important: It makes data wider and easier to compare.
Simple explanation: Like turning a list of months into separate columns for January, February, etc.
Real-life example: Pivot months to see each month as a column.
School example: Pivot subjects to see each subject as a column with grades.
Home example: Pivot types of chores to see time spent per type.
Nigerian example: Pivot regions to see sales per region.
Illustration:
Before: Month | Sales
Jan | 100
Feb | 200
After: Jan | Feb
100 | 200
Mini summary: Pivot makes data wider.
Definition: Unpivot is the opposite of pivot – it turns multiple columns into rows.
Why important: Sometimes you need data tall (long) instead of wide.
Simple explanation: Like taking a table with Jan, Feb, Mar and turning it into a list of Month-Sales.
Real-life example: Unpivot monthly sales columns into a single column of months.
School example: Unpivot subject columns into a list of subjects.
Home example: Unpivot expense columns into a list of expenses.
Nigerian example: Unpivot crop yields by month.
Illustration:
Before: Jan | Feb | Mar
100 | 200 | 150
After: Month | Sales
Jan | 100
Feb | 200
Mar | 150
Mini summary: Unpivot makes data taller (more rows).
Definition: Merging joins two queries (tables) from different sources using a common column.
Why important: You often have data in separate files – merging brings them together.
Simple explanation: Like sticking two photos together to make a bigger picture.
Real-life example: Merge customer data from Excel with order data from a database.
School example: Merge student list from one file with test scores from another.
Home example: Merge your toy list with a price list from a website.
Nigerian example: Merge school attendance from Excel with exam results from CSV.
Illustration:
Query1: StudentID, Name Query2: StudentID, Score Merge → StudentID, Name, Score
Mini summary: Merging combines queries from different sources.
Definition: Appending stacks two queries (tables) on top of each other.
Why important: If you have monthly sales in separate files, you can combine them into one.
Simple explanation: Like stacking building blocks.
Real-life example: Append January sales query with February sales query.
School example: Append test scores from Class A and Class B.
Home example: Append your allowance records from two years.
Nigerian example: Append daily sales from all branches.
Illustration:
Query Jan: Ada, 100 Query Feb: Bola, 200 Append → Ada, 100; Bola, 200
Mini summary: Append stacks queries vertically.
Definition: A custom column is a new column that you create using a formula.
Why important: You can calculate new data, like total price = quantity * price.
Simple explanation: Like using a calculator to add numbers and writing the answer in a new column.
Real-life example: Create "Total Sales" = Quantity * Unit Price.
School example: Create "Total Score" = Test1 + Test2.
Home example: Create "Total Cost" = Items * Price.
Nigerian example: Create "Total Harvest" = Bags * Weight.
Illustration:
Quantity | Price | Total (custom) 2 | 500 | 1000
Mini summary: Custom columns let you create new data with formulas.
Definition: M is the language behind Power Query. It writes the steps for you.
Why important: You can see the code and learn to write your own advanced steps.
Simple explanation: It's like the recipe behind your favourite dish. Power Query writes it for you.
Real-life example: When you click a button, Power Query writes M code in the background.
School example: Like the teacher writing notes on the board – M is the notes.
Home example: The instructions for a toy are like M code.
Nigerian example: A recipe for jollof rice is like M code – step-by-step.
Illustration:
let
Source = Excel.Workbook(...),
Table1 = Source{[Item="Sheet1"]}[Data]
in
Table1
Mini summary: M is the code behind your cleaning steps.
Definition: Refreshing updates your query to get the latest data.
Why important: If new data comes in, you can refresh to see it without redoing all steps.
Simple explanation: Like turning on the tap to get fresh water.
Real-life example: You refresh your sales query to include today's sales.
School example: Refresh attendance to see who is present today.
Home example: Refresh your chore list to see new tasks.
Nigerian example: Refresh crop data after a new harvest.
Illustration:
Click Refresh → New data appears
Mini summary: Refresh brings in new data.
Trim and Clean:
Split Column:
Combine Columns:
Replace Values:
Conditional Column:
Group By:
Pivot:
Unpivot:
Merge Queries:
Append Queries:
Custom Column:
[ Load Data ]
|
V
[ Trim & Clean ]
|
V
[ Split/Combine ]
|
V
[ Replace Values ]
|
V
[ Conditional Column ]
|
V
[ Group / Pivot ]
|
V
[ Merge / Append ]
|
V
[ Load to Excel ]
1. Trim → 2. Clean → 3. Split → 4. Combine
↓ ↓ ↓ ↓
5. Replace → 6. Conditional → 7. Group By
↓ ↓ ↓
8. Pivot → 9. Unpivot → 10. Merge/Append
| Feature | Pivot | Unpivot |
|---|---|---|
| Effect | Makes data wider | Makes data taller |
| Columns | Increases | Decreases |
| Rows | Decreases | Increases |
| Use case | Comparative analysis | Normalisation |
Congratulations! 🎉 You have completed Module 2. You now know how to:
You are now a Power Query cleaning expert! Keep practising with different datasets.
| Column A | Column B |
|---|---|
| 1. Trim | A. Remove invisible characters |
| 2. Clean | B. Divide a column |
| 3. Split | C. Remove extra spaces |
| 4. Combine | D. Merge columns |
| 5. Replace Values | E. Change specific text |
Answers: 1-C, 2-A, 3-B, 4-D, 5-E
Activity: In groups, find a messy dataset online (e.g., weather data). Load it into Power Query and apply at least 10 cleaning and transformation steps. Present your final table.
Activity: Create a dataset of 20 fictional students (Name, Subject, Score). Include extra spaces, inconsistent dates, and duplicate values. Clean and transform it using Power Query.
Project: Collect data from 10 of your classmates (Name, Age, Favourite Subject, Test Score). Clean it using Power Query: trim, split name, conditional column for pass/fail, group by subject to get average scores. Load the final report.
Assignment: Download a CSV file of sales data from your teacher. Load it into Power Query. Apply the following:
Submit the cleaned file.
Challenge: Use Power Query to get data from a web page (e.g., Wikipedia table). Clean the data: remove duplicates, split columns, and create a conditional column. Then merge it with another table you have. Show your final result.
All answers are provided within each exercise section above.
In Module 3, we will learn about Advanced Data Analysis – we will use Power Query to perform complex calculations, create dynamic reports, and automate workflows. We will also learn how to optimise queries for speed. Keep practising all the steps from Modules 1 and 2. You are doing great!
🎉 End of Module 2 – Keep cleaning and exploring! 🎉
Hello, amazing data explorer! 👋 You have already learned how to clean messy data in Modules 1 and 2. Now, we are going to become data shapers! Imagine you are a sculptor with a big block of clay. You can cut it, join pieces, and shape it into anything you want. Power Query lets you do the same with data. In this module, we will learn how to combine data from many different sources, shape it exactly the way we need, and even automate the whole process. By the end, you will be able to handle data like a true expert! Let's jump in! 🏊♂️
In a big school in Abuja, Nigeria, the principal, Mrs. Okafor, needed to combine exam results from all 30 classes. Each class teacher had sent an Excel file with marks. Mrs. Okafor was worried because she had to open each file, copy the data, and paste them into one big sheet. That would take hours! Her son, Tunde, who was 11 years old and loved computers, said, "Mummy, let me try something." He used Power Query to combine all the files from a folder with just a few clicks. He also used a parameter so that if new files were added, the query would automatically include them. In just 5 minutes, Tunde had a complete master sheet with all the results. Mrs. Okafor was so proud! 🏆
This module will teach you the magic Tunde used!
Definition: Combining data from multiple files means taking information from several workbooks and putting it into one table.
Why important: Often data is split across many files (e.g., sales per month). Combining saves you from copying and pasting.
Simple explanation: It's like collecting all your Lego sets and putting all the bricks into one big box.
Real-life example: A shop owner has a separate Excel file for each day's sales. Power Query can combine them into one file.
School example: Your teacher has exam scores in separate files for each class. You can combine them.
Home example: Your family has a budget file for each year. You can combine them.
Nigerian example: A farmer has crop yield data for each month in separate files. Combine them to see the whole year.
Illustration:
File1: Jan Sales File2: Feb Sales File3: Mar Sales
\ | /
\ | /
V V V
+-----------------------------------+
| Combined Sales (All Months) |
+-----------------------------------+
Mini summary: Combining files brings scattered data together.
Definition: This is a special feature where Power Query combines all files of a certain type (e.g., Excel or CSV) inside a folder.
Why important: It's super efficient – you don't even need to open the files.
Simple explanation: Like telling your friend to bring all the books from a shelf, not just one book.
Real-life example: You have a folder with 365 daily sales files. Power Query can combine them all.
School example: A folder with all student attendance sheets for the term.
Home example: A folder with photos (but for data, it's files!).
Nigerian example: A bank has transaction files for each branch in one folder.
Illustration:
📁 Folder ├── Jan.csv ├── Feb.csv ├── Mar.csv └── ... [Power Query] → One big table
Mini summary: Combining from a folder is like a bulk action.
Definition: Power Query has a built-in button called "Combine Files" that does everything automatically.
Why important: It saves you from setting up steps manually – it's a shortcut.
Simple explanation: Like a one-click magic wand.
Real-life example: Click "Combine Files" and Power Query detects the structure.
School example: Combine all class files with one click.
Home example: Combine your allowance records.
Nigerian example: Combine branch sales files easily.
Illustration:
Get Data → From Folder → Combine & Transform Data
Mini summary: "Combine Files" is a quick way to merge many files.
Definition: A parameter is a value you can change easily, like a year or a product name, to make your query flexible.
Why important: Instead of editing the query every time, you change the parameter.
Simple explanation: Like a variable in a video game – you can change it to get different results.
Real-life example: Create a parameter for "Year" – change it from 2025 to 2026 and the query updates.
School example: Parameter for "Class" – change to see results for different classes.
Home example: Parameter for "Month" – change to see expenses for that month.
Nigerian example: Parameter for "Region" – change to see data for Lagos, Abuja, or Kano.
Illustration:
Parameter: Year = 2025 Query uses Year to filter data. Change to 2026 → Query updates automatically.
Mini summary: Parameters make queries dynamic and reusable.
Definition: You create a parameter by going to Home → Manage Parameters → New Parameter.
Why important: It's easy to do and very powerful.
Simple explanation: You name the parameter and give it a default value.
Real-life example: Parameter "MinSales" = 1000.
School example: Parameter "PassMark" = 70.
Home example: Parameter "Budget" = 5000.
Nigerian example: Parameter "State" = "Lagos".
Illustration:
Manage Parameters → New Name: Year Type: Number Current Value: 2025
Mini summary: Creating parameters is simple and useful.
Definition: You can use parameters in filter, custom columns, or file paths.
Why important: This makes your query answer different questions without changing steps.
Simple explanation: Like using a variable in math – x + 2, where x is the parameter.
Real-life example: Filter sales where Year = [Year Parameter].
School example: Show only students with Score >= [PassMark].
Home example: Show expenses where Amount <= [Budget].
Nigerian example: Filter data for [State] = "Lagos".
Illustration:
Filter Rows → Where [Year] = [YearParam]
Mini summary: Parameters let you control your query from one place.
Definition: A function is a reusable set of steps that you can apply to different data.
Why important: Instead of writing the same steps again and again, you use a function.
Simple explanation: Like a recipe that you can use for different ingredients.
Real-life example: A function that cleans any table you give it.
School example: A function to calculate total marks for any subject.
Home example: A function to convert currencies.
Nigerian example: A function to calculate VAT on any price.
Illustration:
Function: CleanData (Input Table) → Returns Cleaned Table Apply to Table1, Table2, Table3.
Mini summary: Functions are reusable cleaning machines.
Definition: You create a function by writing M code, but Power Query can help you create it from a query.
Why important: You can automate repetitive tasks.
Simple explanation: You build a machine that does the same job on different inputs.
Real-life example: Create a function that removes duplicates from any table.
School example: Create a function that standardises names.
Home example: Create a function that categorises expenses.
Nigerian example: Create a function that formats phone numbers.
Illustration:
Query1 (cleans data) → Convert to Function Name: CleanFunction Parameter: InputTable
Mini summary: Functions are custom tools you build.
Definition: Once you have a function, you can invoke (call) it with different tables.
Why important: It saves time and ensures consistency.
Simple explanation: You call the function like you call a friend to help.
Real-life example: Invoke CleanFunction on SalesData.
School example: Invoke StandardiseName on student list.
Home example: Invoke CurrencyConverter on prices.
Nigerian example: Invoke PhoneFormatter on customer list.
Illustration:
Invoke Custom Function → Select Function → Apply
Mini summary: Using functions is like delegating work.
Definition: Dependencies show how queries are connected – which query uses which.
Why important: If you change one query, you know which others will be affected.
Simple explanation: Like a family tree – if grandma changes, everyone is affected.
Real-life example: Query A is used by Query B and C.
School example: ClassData is used by Report and Dashboard.
Home example: Expenses used by Summary and Chart.
Nigerian example: SalesData used by Profit and Revenue queries.
Illustration:
[BaseData] → [CleanedData] → [FinalReport]
| |
V V
[Backup] [Summary]
Mini summary: Dependencies help you manage your queries.
Definition: Optimising means making your query run faster and use less memory.
Why important: Large data can be slow – you want it to be quick.
Simple explanation: Like cleaning your room quickly by putting things in the right place.
Real-life example: Only load columns you need, not all columns.
School example: Only load the student names and scores, not their photos.
Home example: Only load the current year's expenses.
Nigerian example: Only load data for the current region.
Illustration:
✅ Good: Load only needed columns. ❌ Bad: Load all columns.
Mini summary: Optimisation makes queries faster.
Definition: Dynamic refresh means your query updates automatically when the source data changes.
Why important: You always have the latest data without doing anything.
Simple explanation: Like a news ticker that updates automatically.
Real-life example: Sales data refreshes every morning.
School example: Attendance refreshes each period.
Home example: Budget refreshes when you add new expenses.
Nigerian example: Stock data refreshes every hour.
Illustration:
Data Source → Power Query → Refresh (scheduled) → Updated Report
Mini summary: Dynamic refresh keeps data current.
Definition: A workflow is a series of steps from start to finish – load, clean, transform, combine, and output.
Why important: It gives you a structured way to handle data projects.
Simple explanation: Like a recipe – first gather ingredients, then chop, cook, and serve.
Real-life example: Load daily files → Clean → Combine → Generate report.
School example: Load class files → Standardise → Merge → Calculate averages.
Home example: Load receipts → Categorise → Summarise by month.
Nigerian example: Load branch data → Clean → Combine → Create dashboard.
Illustration:
1. Get Data (from Folder)
|
V
2. Combine Files
|
V
3. Clean & Transform
|
V
4. Group & Pivot
|
V
5. Load to Excel
Mini summary: A workflow organises your entire data process.
Definition: Error handling means managing problems when data is missing or in the wrong format.
Why important: Sometimes files are corrupt or have different structures – you can handle it gracefully.
Simple explanation: Like when you drop a glass, you clean it up instead of crying.
Real-life example: If a file is missing, Power Query can skip it and continue.
School example: If a student's data is missing, use a default value.
Home example: If a receipt is missing, estimate the amount.
Nigerian example: If a branch file is missing, use previous month's data.
Illustration:
Try: Load File If error → Skip file
Mini summary: Error handling makes your queries robust.
Definition: Documenting means adding notes or descriptions to your queries so others (or you) can understand them later.
Why important: After a few months, you might forget what a step does. Documentation helps.
Simple explanation: Like writing labels on storage boxes.
Real-life example: Add a comment in the M code: "This step removes duplicates".
School example: Write notes on your project steps.
Home example: Label your queries: "Jan Expenses", "Feb Expenses".
Nigerian example: Add comments to explain logic to colleagues.
Illustration:
// This step splits Full Name into First and Last // This step filters out rows with missing values
Mini summary: Documentation helps you and others understand your work.
Combine Files from a Folder:
Create a Parameter:
Use a Parameter in Filter:
Create a Function from a Query:
Optimise a Query:
[Start]
|
V
[Get Data from Folder]
|
V
[Combine Files]
|
V
[Clean & Transform]
|
V
[Load to Excel]
1. Load Files → 2. Combine → 3. Clean → 4. Transform
↓ ↓ ↓ ↓
5. Group/Pivot → 6. Load → 7. Refresh (optional)
| Feature | Manual | Power Query |
|---|---|---|
| Time for 10 files | 30 minutes | 2 minutes |
| Errors | High | Low |
| Reusability | No | Yes |
| Automation | No | Yes |
| Feature | Hard-coded | Parameter |
|---|---|---|
| Change value | Edit query | Change parameter |
| Flexibility | Low | High |
| Reusability | Low | High |
Fantastic work! 🎉 You have completed Module 3. You now know how to:
You are now a Power Query integration expert! You can handle data from anywhere and shape it perfectly.
| Column A | Column B |
|---|---|
| 1. Combine | A. Reusable steps |
| 2. Parameter | B. Dynamic value |
| 3. Function | C. Bring data together |
| 4. Optimise | D. Make faster |
| 5. Workflow | E. Series of steps |
Answers: 1-C, 2-B, 3-A, 4-D, 5-E
Activity: In groups, create a folder with 5 different Excel files containing fictional sales data. Each group member creates one file. Then, use Power Query to combine all files, clean the data, and create a summary table. Present your results.
Activity: Create a folder with 3 Excel files (e.g., Jan, Feb, Mar). Each file should have at least 10 rows of data. Use Power Query to combine them, then create a parameter for "Year" and filter the data accordingly. Save your query.
Project: Create a Power Query solution that combines data from at least 5 different Excel files in a folder. The files should have sales data for different regions. Clean the data, add a conditional column for "High" or "Low" sales, group by region, and create a summary. Load the final table into Excel. Document your steps.
Assignment: Your teacher will provide a folder with 10 Excel files containing student grades. Your task:
Challenge: Use Power Query to combine data from a folder of CSV files. But some files have extra columns. Write a function that cleans any file you give it, and then apply that function to all files while combining. This will test your skills in parameters and functions. Show your final combined table.
All answers are provided within each exercise section above.
In Module 4, we will dive into Advanced Data Analysis with Power Query – we will learn about complex calculations, data modelling, and creating interactive dashboards. We will also explore how to use Power Query with Power BI. You are now ready to take on more complex data challenges. Keep practising and stay curious!
🎉 End of Module 3 – You are now a data combining champion! 🎉
Welcome, young data explorer! In this module, we will learn how to take messy, mixed‑up data and turn it into neat, tidy tables – just like arranging your toys in the right boxes. We call this “data cleaning” and “data shaping”. Power Query is like a magic kitchen where we chop, mix, and cook raw ingredients (data) into a delicious meal (clean information). By the end of this chapter, you will be able to fix errors, split names, join tables, and make your data ready for answers.
Ade loves helping his grandma in the kitchen. One day, Grandma said, “Ade, please mix the spices for the jollof rice – but each spice is in a different bag, and some bags are not labelled!” Ade found bags with “salt”, “pepper”, “curry”, and one bag just said “????”. He had to sort, taste, and mix everything carefully. When he finished, the jollof rice was perfect!
Data is like those spices. It comes from different places, sometimes with missing labels. Power Query is your mixing bowl – it helps you sort, rename, and blend everything so you can cook up the right answers!
Definition: Data cleaning means fixing mistakes and removing things that don’t belong in your data table.
Why important: If your data is dirty, your answers will be wrong – like using salt instead of sugar in a cake!
Simple explanation: Imagine your school’s list of students has some names spelled wrong and some blank rows. Cleaning means you correct the names and delete the empty rows.
Real‑life example: A shopkeeper checks his sales list – some prices are missing, so he fills them in.
School example: The class register has two “Mary” entries – one is Mary A., the other Mary B. You fix it.
Home example: Your mom’s grocery list has “milk” written twice – you cross out the extra one.
Nigerian example: A local market seller records sales on paper. Some numbers are faded; she rewrites them clearly before typing into Excel.
Dirty data → Clean data "2,0" → 20 "N/A" → (empty) "LAGOS" → "Lagos"
Mini summary: Cleaning = fixing errors and removing clutter so your data tells the truth.
Definition: Empty rows are blank lines in your table. Empty columns have no data at all.
Why important: They waste space and can confuse Power Query.
Simple explanation: Like having empty chairs in a classroom – you don’t count them when taking attendance.
Real‑life example: A phone book with blank pages – you skip them.
School example: Your teacher’s mark sheet has empty rows for absent students – she removes them.
Home example: A list of chores with a blank line – you delete it.
Nigerian example: A Lagos bus conductor’s daily passenger count may have blank rows for trips that didn’t happen; he removes them.
How to in Power Query: Home → Remove Rows → Remove Blank Rows.
Before: Row1: Ade, 10 Row2: (blank) Row3: Bola, 12 After: Row1: Ade, 10 Row2: Bola, 12
Mini summary: Blank rows are like empty seats – take them out!
Definition: This means making sure all names and words are spelled the same way and have proper capital letters.
Why important: “LAGOS” and “lagos” are the same city – but the computer thinks they are different if we don’t fix them.
Simple explanation: Like writing your name with a capital letter – “Ade” not “ade”.
School example: Your class list has “John” and “jon” – you change both to “John”.
Home example: Your dad’s contact list has “Mum” and “mum” – you make them both “Mum”.
Nigerian example: Abuja is sometimes written “abuja” – we change it to “Abuja”.
Before: "ikeja", "IKEJA", "Ikeja" After: "Ikeja", "Ikeja", "Ikeja"
Mini summary: Always use the same spelling and capitals so your data stays neat.
Definition: Splitting means taking one column and dividing it into two columns – like separating first name and last name.
Why important: It helps you sort by last name or first name separately.
Simple explanation: Like separating your full name “Ade Ojo” into “Ade” and “Ojo”.
Real‑life example: A list of customers has “Mr. Ade Ojo” – you split into title, first name, last name.
School example: The teacher has “Maryam Bello” – split into “Maryam” and “Bello”.
Home example: “Chicken and rice” – split into “Chicken” and “rice”.
Nigerian example: “Lagos, Nigeria” – split into “Lagos” and “Nigeria”.
How to: Transform → Split Column → By Delimiter (e.g., space or comma).
FullName → FirstName LastName Ade Ojo → Ade Ojo Funke Ade → Funke Ade
Mini summary: Split separates data into smaller pieces for better handling.
Definition: Merging is the opposite of splitting – you combine two columns into one.
Why important: Sometimes you want a full name instead of separate first and last.
Simple explanation: Like putting two puzzle pieces together to see the whole picture.
School example: You have “First” and “Last” – merge to “Full Name”.
Home example: “Street” and “City” merge to “Address”.
Nigerian example: “State” and “LGA” merge to “Location”.
First Last → FullName Ade Ojo → Ade Ojo
Mini summary: Merging puts pieces back together when needed.
Definition: Data type tells Power Query what kind of data is in a column – words (text), numbers, or dates.
Why important: You can’t add text like “ten” to a number – the computer needs to know.
Simple explanation: Like sorting toys into boxes – words in one box, numbers in another.
Real‑life example: “1,000” as text vs. 1000 as a number – you can only add numbers.
School example: Marks are numbers, names are text – don’t mix them.
Home example: Date of birth is a date; age is a number.
Nigerian example: “₦500” is text (has symbol) but we need the number 500 to add prices.
Text: "10" → Number: 10 Date: "01/01/2025" → Date type
Mini summary: Always set the right data type so Power Query understands your data.
Definition: Fill Down takes a value from a cell and copies it into the empty cells below it.
Why important: In some tables, the value is only written once, but it applies to many rows.
Simple explanation: Like when your teacher says “Class 4” at the top – it means all students are in Class 4.
School example: A register with “Year 5” at the top – fill down so every row has “Year 5”.
Home example: A grocery list with “Store: Shoprite” at the top – fill down for all items.
Nigerian example: A list of students from “Lagos State” – fill down so all rows show Lagos.
Before: After: Region Region North North (blank) North South South (blank) South
Mini summary: Fill Down saves time – copy once, paste many!
Definition: A custom column is a new column you build using a formula, like combining or calculating.
Why important: It lets you create new information from existing columns.
Simple explanation: Like making a fruit salad – you take apples and oranges and mix them into a new dish.
School example: You have “Maths” and “English” marks – create a “Total” column by adding them.
Home example: You have “Price” and “Quantity” – create “Total Cost” = Price × Quantity.
Nigerian example: You have “Naira” and “Kobo” – create “Total Amount” in Naira.
Price Quantity → TotalCost 100 3 → 300 200 2 → 400
Mini summary: Custom columns let you create new facts from your data.
Definition: Merging tables means bringing two tables together based on a common column (like a student ID).
Why important: Sometimes information about the same thing is in two places – merging brings it together.
Simple explanation: Like having your friends’ names in one list and their phone numbers in another – you combine them.
School example: Table 1: Student ID and Name. Table 2: Student ID and Marks. Merge to get Name + Marks.
Home example: Table 1: Item names. Table 2: Prices. Merge to see item + price.
Nigerian example: Table 1: LGA names. Table 2: LGA population. Merge to get LGA + population.
Table1 (ID, Name) + Table2 (ID, Age) → Merged (ID, Name, Age) 1, Ade 1, 10 → 1, Ade, 10 2, Bola 2, 12 → 2, Bola, 12
Mini summary: Merging joins two tables using a shared key column.
Definition: Pivot turns rows into columns. Unpivot turns columns into rows.
Why important: Sometimes data is easier to read in a different shape.
Simple explanation: Like turning a line of people into a circle – same people, different arrangement.
School example: You have subjects as columns – pivot to make subjects rows.
Home example: You have months as columns – pivot to make months rows for easier charting.
Nigerian example: Sales data with months as columns – pivot to analyse by month.
Before (wide): Student Maths English Ade 80 70 After (long): Student Subject Marks Ade Maths 80 Ade English 70
Mini summary: Pivot and unpivot change how your table looks, not the data itself.
Definition: Replacing means swapping one value with another – like changing “N/A” to blank.
Why important: It standardises data so everything is consistent.
Simple explanation: Like changing “Mum” to “Mother” everywhere.
School example: Replace “Abs” with “Absent”.
Home example: Replace “fridge” with “refrigerator”.
Nigerian example: Replace “LAG” with “Lagos”.
Before: "LAG", "Lagos", "lagos" After: "Lagos", "Lagos", "Lagos"
Mini summary: Replace fixes inconsistent words quickly.
Definition: You can choose to remove columns you don’t need.
Why important: Fewer columns make it easier to focus on what matters.
Simple explanation: Like choosing only your favourite toys to play with.
School example: A report card has many columns – keep only Name and Total.
Home example: Shopping list with price, quantity, and aisle – keep only price and quantity.
Nigerian example: Population data with many details – keep only State and Population.
Before: ID, Name, Age, Grade, Teacher After: Name, Grade (removed ID, Age, Teacher)
Mini summary: Keep only the columns that answer your question.
Definition: Sorting arranges rows in order (A→Z, smallest→largest). Filtering shows only rows that meet a condition.
Why important: It helps you find the top scores or see only a specific group.
Simple explanation: Like lining up from shortest to tallest (sorting) or only picking kids who wear glasses (filtering).
School example: Sort marks from highest to lowest; filter to see only students with marks above 80.
Home example: Sort grocery list alphabetically; filter to show only fruits.
Nigerian example: Sort states by population; filter to show only states in the South‑South.
Sort: 5, 3, 9 → 3, 5, 9 Filter: [10, 20, 30] > 20 → [30]
Mini summary: Sorting and filtering help you find the data you need quickly.
Definition: Group By takes rows with the same value and summarises them – like counting or summing.
Why important: It gives you totals by category – like total sales per region.
Simple explanation: Like grouping your toys by colour and counting how many of each colour.
School example: Group students by class and count how many in each class.
Home example: Group shopping items by category (fruits, vegetables) and count.
Nigerian example: Group sales by state and find total sales per state.
Before: (Ade, Lagos), (Bola, Lagos), (Tunde, Abuja) After Group By State: Lagos: 2 Abuja: 1
Mini summary: Group By summarises data by categories.
Definition: Undo takes back your last step. Redo puts it back again.
Why important: You can experiment without fear – if you make a mistake, just undo!
Simple explanation: Like erasing a wrong answer and writing it again.
School example: You delete a column by mistake – undo brings it back.
Home example: You change “rice” to “beans” – undo changes it back.
Nigerian example: You replaced all “Lagos” with “Abuja” – undo to fix.
How to: Use the “Undo” button or Ctrl+Z.
Mini summary: Undo is your safety net – always available!
Data Cleaning Process
+-------------------+
| Dirty Data |
| (spelling errors,|
| blanks, mixed) |
+--------+----------+
|
V
+--------+----------+
| Clean Steps |
| - Remove blanks |
| - Fix spelling |
| - Split columns |
+--------+----------+
|
V
+--------+----------+
| Clean Data |
| (neat, correct, |
| ready to use) |
+-------------------+
Split Column – before and after +-----------------+ +-----------------+-----------------+ | FullName | | FirstName | LastName | +-----------------+ +-----------------+-----------------+ | Ade Ojo | → | Ade | Ojo | +-----------------+ +-----------------+-----------------+
| Operation | Use | Example |
|---|---|---|
| Split | Separate one column into two | FullName → First, Last |
| Merge | Combine two columns into one | First + Last → FullName |
| Group By | Summarise by category | Count students per class |
| Pivot | Rows to columns | Subject names as columns |
In this module, we learned how to make our data beautiful and useful. We removed blank rows, fixed spelling, split and merged columns, changed data types, used Fill Down, created custom columns, and even merged tables. We also explored Pivot, Unpivot, Group By, and sorting. Remember – clean data leads to correct answers. Always check your data types and keep only what you need. You are now ready to turn messy data into a clear story!
Match the term with its description:
| Term | Description |
|---|---|
| 1. Split | A. Combine two tables |
| 2. Merge | B. Separate one column into two |
| 3. Pivot | C. Turns rows into columns |
| 4. Group By | D. Summarise by category |
Answers: 1-B, 2-A, 3-C, 4-D
Scenario 1: You have a list of 100 students with names like “Ade Ojo”, “Bola”, “Chidi Eze” (missing last name). How would you clean this? (Answer: split full name where possible, leave missing as blank or fix manually.)
Scenario 2: Your teacher gave you two tables – one with student IDs and names, another with student IDs and test scores. How can you combine them? (Answer: merge on Student ID.)
In small groups, create a messy data table with at least 10 rows and 4 columns (include spelling errors, blanks, mixed capitals). Exchange tables with another group and clean each other’s data using the steps learned.
Open Excel or Power Query (if available). Take the class attendance list and clean it: remove empty rows, fix names, and change the date column to Date type.
Collect data from your classmates: Name, Age, Favourite Subject, and Score in Maths (out of 100). Type it into Excel. Use Power Query to: (1) Remove blank rows, (2) Split name into first and last, (3) Change Age to number, (4) Sort by Score descending. Show your clean table.
Download a sample dataset from the internet (or use a provided one). Clean it using at least 5 different operations (remove blanks, fix capitals, split, merge, change type). Submit the cleaned file and a short report of what you did.
You have a table with “Product”, “Region”, “Sales”, “Quarter”. Use Power Query to: (1) Unpivot the quarters, (2) Group by Region to find total sales, (3) Merge with a product category table (provided) to add category information. This is tricky – try your best!
Fill‑in‑the‑Blank: 1. errors 2. Fill Down 3. Data type 4. Merge 5. Pivot
True or False: 1-F, 2-T, 3-F, 4-F, 5-T
Multiple Choice: 1-b, 2-c, 3-a, 4-b, 5-b, 6-b, 7-b, 8-a, 9-b, 10-b, 11-a, 12-b, 13-a, 14-c, 15-a
In the next module, we will dive into “Data Transformation with M Language”. You will learn how to write simple code to automate all the cleaning steps we did manually. Get ready to become a Power Query wizard!
Note: This is Module Four. In our course, Module Three covers “Connecting to Data Sources”. We will now transition to Module Five, but you have mastered the art of cleaning – well done!
Hello, young data wizard! So far, we have been using buttons and menus to clean and shape our data. But did you know that behind every click, Power Query writes a little language called “M”? M is like a recipe for data. In this module, we will open the kitchen and look at the recipe itself. We will learn to read and write simple M code. Don’t worry – it is easier than you think! Think of M as giving instructions to a very smart robot. You tell it step by step what to do, and it does it perfectly every time.
Chidi loves cooking, but he is lazy. He wishes he had a robot that could chop, stir, and fry all by itself. One day, his uncle gives him a small robot. But the robot only follows written instructions. Chidi writes: “Step 1: Peel the yam. Step 2: Cut into cubes. Step 3: Fry until golden.” He places the paper in the robot, and the robot does exactly what he wrote!
M language is like those written instructions. Every click in Power Query creates a line of M code. When you learn M, you become the robot’s boss – you can write your own instructions and make the robot do exactly what you want, faster than using buttons!
Definition: M is a programming language used in Power Query to clean, combine, and transform data.
Why important: M gives you superpowers – you can do things that buttons cannot.
Simple explanation: M is like a recipe for data. You list steps, and Power Query cooks them in order.
Real‑life example: A baker writes a recipe – mix flour, add eggs, bake. M does the same for data.
School example: You write steps to solve a math problem – M writes steps to clean data.
Home example: You write a to‑do list for chores – M writes a to‑do list for data.
Nigerian example: A caterer writes steps to prepare jollof rice – M writes steps to prepare data.
// This is M code!
let
Source = Excel.Workbook(...),
Sheet1 = Source{...}[Data],
Cleaned = Table.RemoveRows(Sheet1, ...)
in
Cleaned
Mini summary: M is the language that tells Power Query what to do.
Definition: The Advanced Editor is where you can see and edit M code.
Why important: You can copy, paste, and write your own M code there.
Simple explanation: Like opening the hood of a car to see the engine.
Real‑life example: In a restaurant, you can watch the chef cook – the Advanced Editor shows you the cooking steps.
School example: Your teacher shows you the answer key – the Advanced Editor shows you the code.
Home example: You open your toy box to see all your toys – the Advanced Editor shows all your steps.
Nigerian example: A mechanic opens the car bonnet – you open the Advanced Editor.
How to: Home → Advanced Editor (or View → Advanced Editor).
let
// All your steps appear here
Source = ...
in
Source
Mini summary: Advanced Editor is your window to the M code.
Definition: “let” is where you define your steps. “in” is where you tell Power Query what to output.
Why important: This is the basic building block of every M script.
Simple explanation: “let” = list your tasks, “in” = show the final result.
Real‑life example: Your mom says “let me gather ingredients, then in the pot I cook.”
School example: “Let me read the question, then in my answer book I write.”
Home example: “Let me pick my clothes, then in the bag I pack.”
Nigerian example: “Let me buy the tomatoes, then in the stew I put them.”
let
Step1 = 5,
Step2 = Step1 + 3
in
Step2 // result is 8
Mini summary: “let” holds steps, “in” returns the final value.
Definition: A variable is a name you give to a value or a step, like “CleanedData”.
Why important: Variables help you remember what each step does.
Simple explanation: Like giving a name to your pet – you can call it by name later.
Real‑life example: You name your backpack “BlueBag” – then you say “Put books in BlueBag”.
School example: You name a math answer “Result” – then use it in another equation.
Home example: You name your toy box “ToyChest” – you know where to find toys.
Nigerian example: You name your market bag “Ade’s Bag” – everyone knows it’s yours.
let
MyNumber = 10,
MyResult = MyNumber * 2
in
MyResult // 20
Mini summary: Variables are like labels for your data steps.
Definition: Comments are notes you write for yourself (or others) that do not affect the code.
Why important: They help you remember what your code does.
Simple explanation: Like writing a note on your hand to remind you of something.
Real‑life example: A cook writes “add salt” on a sticky note.
School example: You write “this is the formula for area” in your notebook.
Home example: You put a sticky note on the fridge saying “buy milk”.
Nigerian example: A trader writes “price = 200” on a board.
// This is a single-line comment
let
Step1 = 5, // This is also a comment
Step2 = Step1 + 3
in
Step2
Mini summary: Comments are notes that help you and others understand your code.
Definition: Table.AddColumn creates a new column in your table.
Why important: It lets you add calculated values, just like we did with custom columns before.
Simple explanation: Like adding a new subject to your report card.
Real‑life example: A shop adds a “Discount” column to a sales table.
School example: You add a “Total” column to your marks.
Home example: You add a “Price per kg” column to a grocery list.
Nigerian example: Add a “VAT” column to a price list.
let
Source = Table.FromRecords({[Name="Ade", Score=80]}),
NewCol = Table.AddColumn(Source, "Grade", each if [Score] >= 70 then "A" else "B")
in
NewCol
Mini summary: Table.AddColumn creates new columns using formulas.
Definition: This function changes the data type of one or more columns.
Why important: It ensures numbers are numbers and dates are dates.
Simple explanation: Like changing a label from “text” to “number”.
School example: Change “10” (text) to 10 (number) so you can add marks.
Home example: Change “01/01/2025” to a date type.
Nigerian example: Change “₦500” to a number 500 for calculations.
let
Source = Table.FromRecords({[Price="500"]}),
Changed = Table.TransformColumnTypes(Source, {{"Price", type number}})
in
Changed
Mini summary: TransformColumnTypes fixes data types.
Definition: This function keeps only rows that meet a condition.
Why important: It helps you focus on specific data, like only students with marks above 80.
Simple explanation: Like picking only red balls from a box of coloured balls.
School example: Keep only students who passed.
Home example: Keep only items that cost less than 100.
Nigerian example: Keep only states in the South‑South region.
let
Source = Table.FromRecords({[Name="Ade", Score=90], [Name="Bola", Score=65]}),
Filtered = Table.SelectRows(Source, each [Score] >= 70)
in
Filtered // only Ade remains
Mini summary: SelectRows filters data based on a condition.
Definition: This function removes columns you don’t need.
Why important: It reduces clutter and makes your table easier to read.
Simple explanation: Like taking out toys you don’t play with from your toy box.
School example: Remove the “Teacher” column if you only need student names.
Home example: Remove the “Aisle” column from a shopping list.
Nigerian example: Remove the “LGA” column if you only need state.
let
Source = Table.FromRecords({[Name="Ade", Age=10, Teacher="Mr. Smith"]}),
Removed = Table.RemoveColumns(Source, {"Teacher"})
in
Removed // only Name and Age remain
Mini summary: RemoveColumns gets rid of unwanted columns.
Definition: Table.Combine stacks two tables on top of each other (like appending).
Why important: It combines rows from different tables with the same columns.
Simple explanation: Like putting two piles of books together.
School example: Combine Class A marks and Class B marks into one list.
Home example: Combine your grocery list with your sister’s grocery list.
Nigerian example: Combine sales data from two markets.
let
Table1 = Table.FromRecords({[Name="Ade", Score=80]}),
Table2 = Table.FromRecords({[Name="Bola", Score=90]}),
Combined = Table.Combine({Table1, Table2})
in
Combined // two rows
Mini summary: Table.Combine stacks rows from multiple tables.
Definition: Table.Join combines two tables based on a common column.
Why important: It brings together data from different sources using a key.
Simple explanation: Like matching people with their phone numbers using their names.
School example: Join student names with their scores using Student ID.
Home example: Join product names with prices using Product Code.
Nigerian example: Join LGA names with population using LGA Code.
let
Table1 = Table.FromRecords({[ID=1, Name="Ade"], [ID=2, Name="Bola"]}),
Table2 = Table.FromRecords({[ID=1, Age=10], [ID=2, Age=12]}),
Joined = Table.Join(Table1, "ID", Table2, "ID", JoinKind.LeftOuter)
in
Joined // ID, Name, Age
Mini summary: Table.Join merges tables using a key column.
Definition: “each” is a shortcut for writing a function that applies to every row. “_” represents the current row.
Why important: They save you typing and make code shorter.
Simple explanation: “each” means “for every row, do this”. “_” means “this row”.
Real‑life example: “Each student, write your name” – you use “_” to refer to that student.
School example: “Each answer, check if it’s correct.”
Home example: “Each item, add price.”
Nigerian example: “Each market, count customers.”
// Add 10 to every score Table.AddColumn(Source, "NewScore", each [Score] + 10) // Same as: each _[Score] + 10
Mini summary: “each” and “_” make it easy to work with every row.
Definition: A function is a block of code that does a specific task and can be reused.
Why important: Instead of writing the same code many times, you write it once and call it.
Simple explanation: Like a recipe for “eggs” – you can use it to make breakfast many times.
School example: A function to calculate average – use it for maths, English, etc.
Home example: A function to convert naira to dollars – use it whenever needed.
Nigerian example: A function to add VAT – use it for any product.
let
AddTen = (x) => x + 10,
Result = AddTen(5) // 15
in
Result
Mini summary: Functions are reusable code blocks.
Definition: Errors happen when something goes wrong – like trying to add text to a number.
Why important: Knowing how to handle errors makes your code strong and safe.
Simple explanation: Like wearing a helmet when riding a bike – it protects you.
School example: If a student’s score is missing, use 0 instead.
Home example: If an item has no price, set it to “free”.
Nigerian example: If a state name is missing, write “Unknown”.
// Using try ... otherwise
let
SafeDivide = (a, b) => if b = 0 then 0 else a / b
in
SafeDivide(10, 0) // returns 0 instead of error
Mini summary: Error handling keeps your code from breaking.
Definition: A record is like a row (with column names and values). A list is a collection of values.
Why important: Many M functions use records and lists.
Simple explanation: A record is like a student’s report card (Name, Score). A list is like a bag of marbles.
School example: Record = {Name="Ade", Score=80}. List = {80, 90, 70}.
Home example: Record = {Item="Bread", Price=200}. List = {200, 150, 300}.
Nigerian example: Record = {State="Lagos", Population=20M}. List = {"Lagos", "Abuja", "Kano"}.
let
MyRecord = [Name="Ade", Score=80],
MyList = {10, 20, 30},
FirstItem = MyList{0} // 10
in
FirstItem
Mini summary: Records hold row data; lists hold sequences of values.
NewStep = Table.AddColumn(PreviousStep, "NewColumnName", each [OldColumn] * 2)NewStep.Filtered = Table.SelectRows(Source, each [Score] >= 70)Filtered.M Code Structure (let … in) +-----------------------------------+ | let | | Step1 = ... | | Step2 = ... | | Step3 = ... | | in | | Step3 (this is the output) | +-----------------------------------+
Data Flow in M +----------+ +----------+ +----------+ | Source | -> | Cleaned | -> | Result | | (raw) | | (fixed) | | (final) | +----------+ +----------+ +----------+
| Button Action | M Code Equivalent |
|---|---|
| Remove Blank Rows | Table.SelectRows(Source, each Record.FieldCount(_) > 0) |
| Split Column by Space | Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" ")) |
| Change Data Type to Number | Table.TransformColumnTypes(Source, {{"Column", type number}}) |
| Add Custom Column | Table.AddColumn(Source, "New", each [A] + [B]) |
Congratulations! You have taken your first steps into the world of M language. You learned that M is the recipe behind Power Query. You can now read and write simple M expressions, use variables, add comments, and apply common functions like Table.AddColumn, Table.SelectRows, and Table.TransformColumnTypes. You also learned about the let…in structure, the power of “each” and “_”, and how to create your own functions. Remember, M gives you superpowers – the more you practice, the more data magic you can perform!
Match the M keyword/function with its description:
| Keyword/Function | Description |
|---|---|
| 1. let | A. Defines steps |
| 2. in | B. Returns the result |
| 3. each | C. Applies to every row |
| 4. Table.AddColumn | D. Adds a new column |
Answers: 1-A, 2-B, 3-C, 4-D
Scenario 1: You have a table with Product, Price, and Quantity. Write M code to add a Total column (Price * Quantity).
Scenario 2: You have a list of students with scores. Write M code to keep only students who scored 80 or above.
In groups, take a messy dataset and write M code to clean it. Each group presents their code and explains what each step does. Compare different approaches.
Open the Advanced Editor in Power Query and find the code generated by your last cleaning task. Add a comment to each step explaining what it does. Then, add a new step that filters the data further.
Create a Power Query that cleans a sample dataset using only M code. Include at least 5 steps (e.g., remove blanks, change types, add custom column, filter, and remove a column). Write your code in the Advanced Editor and test it.
Take the dataset from the previous module’s practical assignment. Instead of using buttons, write M code to perform the same cleaning steps. Submit the M code and a short report comparing the button approach vs. the code approach.
Write an M function that takes a table and a column name, and returns the table sorted by that column in descending order. Test it on a sample table. Hint: use Table.Sort.
Fill‑in‑the‑Blank: 1. Mashup 2. Advanced 3. let…in 4. variable 5. Comments
True or False: 1-T, 2-F, 3-T, 4-F, 5-T
Multiple Choice: 1-b, 2-b, 3-b, 4-a, 5-a, 6-a, 7-a, 8-c, 9-a, 10-a, 11-a, 12-b, 13-a, 14-a, 15-a
In Module Six, we will dive into “Advanced M – Working with Lists, Records, and Custom Functions”. You will learn how to loop through data, handle errors like a pro, and build complex transformations. Get ready to become a master of M!
Note: This is Module Five. Module Four covered cleaning and shaping. We now move to Module Six, where we will explore advanced M concepts. Keep practicing – you are doing great!
Welcome, young data champion! In Module Five, we learned the basics of M language – variables, steps, and simple functions. Now it is time to level up. In this module, we will explore lists (like bags of items), records (like cards with details), and custom functions (like recipes you invent yourself). You will learn how to loop through data, transform lists, and build your own powerful tools. By the end, you will be able to write M code that can handle almost any data challenge!
Fatima loves fixing things. Her grandfather gives her a toolbox with many compartments. One compartment holds screws (like a list of values). Another holds instruction cards (like records with details). She also has a special tool that can turn any screw and any card into a new creation – that’s like a custom function!
In this module, you will build your own toolbox. You will learn to store data in lists, organize it in records, and create functions that can work on any data you give them. Let’s start building!
Definition: A list is a collection of values enclosed in curly braces { }.
Why important: Lists let you store many items together and work with them all at once.
Simple explanation: Like a bag of marbles – you can count them, add more, or take some out.
Real‑life example: A shopping list: {"milk", "bread", "eggs"}.
School example: A list of test scores: {80, 90, 70}.
Home example: A list of chores: {"sweep", "wash", "cook"}.
Nigerian example: A list of states: {"Lagos", "Abuja", "Kano"}.
let
MyList = {10, 20, 30, 40}
in
MyList
Mini summary: A list is a collection of items inside { }.
Definition: You can get an item from a list using its position (index), starting from 0.
Why important: You often need to pick one specific item from a list.
Simple explanation: Like picking the third marble from your bag.
Real‑life example: The first item on a list is position 0.
School example: In a list of scores, {80,90,70}, the second score is at index 1 (90).
Home example: Your chore list – index 0 is “sweep”.
Nigerian example: List of states – index 0 is “Lagos”.
let
MyList = {10, 20, 30},
FirstItem = MyList{0} // 10
in
FirstItem
Mini summary: Use {index} to pick an item from a list.
Definition: List.Transform applies a function to each item in a list and returns a new list.
Why important: It lets you change all items at once – like doubling every number.
Simple explanation: Like giving every marble a coat of paint – all at once.
Real‑life example: Convert all prices from naira to dollars.
School example: Add 5 marks to every score.
Home example: Double the quantity of each item in a recipe.
Nigerian example: Add VAT (7.5%) to every price.
let
MyList = {10, 20, 30},
NewList = List.Transform(MyList, each _ * 2) // {20, 40, 60}
in
NewList
Mini summary: List.Transform changes every item in a list.
Definition: A record is a set of fields with names and values, like a row in a table.
Why important: Records organize data with labels, so you know what each value means.
Simple explanation: Like a student ID card – it has Name, Age, and Class.
Real‑life example: A product record: [Product="Milk", Price=200].
School example: A student record: [Name="Ade", Score=80].
Home example: A chore record: [Chore="Sweep", Time="10am"].
Nigerian example: A state record: [State="Lagos", Population=20M].
let
MyRecord = [Name="Ade", Age=10, Class="P5"]
in
MyRecord
Mini summary: A record is a labelled collection of values inside [ ].
Definition: You can get a field’s value using the dot notation or Record.Field.
Why important: You need to extract specific information from a record.
Simple explanation: Like looking at the “Name” on an ID card.
Real‑life example: Get the price from a product record.
School example: Get the score from a student record.
Home example: Get the time from a chore record.
Nigerian example: Get the population from a state record.
let
MyRecord = [Name="Ade", Score=80],
StudentName = MyRecord[Name] // or MyRecord.Name
in
StudentName
Mini summary: Use [FieldName] to get a value from a record.
Definition: Record.Combine merges two or more records into one.
Why important: You may have information in separate records that you want to bring together.
Simple explanation: Like sticking two cards together to make a bigger card.
Real‑life example: Combine product details and price records.
School example: Combine student name and marks records.
Home example: Combine chore name and time records.
Nigerian example: Combine state name and capital records.
let
Rec1 = [Name="Ade", Age=10],
Rec2 = [Score=80, Class="P5"],
Combined = Record.Combine({Rec1, Rec2})
in
Combined // [Name="Ade", Age=10, Score=80, Class="P5"]
Mini summary: Record.Combine merges records.
Definition: A list of records is like a table – each record is a row.
Why important: Many data sources give you a list of records.
Simple explanation: Like a class register – each row is a student record.
Real‑life example: A list of products, each with Name and Price.
School example: A list of students, each with Name and Score.
Home example: A list of chores, each with Chore and Time.
Nigerian example: A list of states, each with State and Capital.
let
MyTable = {
[Name="Ade", Score=80],
[Name="Bola", Score=90],
[Name="Chidi", Score=70]
}
in
MyTable
Mini summary: A list of records is a great way to store tabular data.
Definition: You can use List.Transform on a list of records to change or extract fields.
Why important: It lets you manipulate each row of your data.
Simple explanation: Like going through each student and adding 5 marks.
School example: Add 10 to every score in a list.
Home example: Add a “Done” field to every chore.
Nigerian example: Add a “Region” field to every state record.
let
Data = {
[Name="Ade", Score=80],
[Name="Bola", Score=90]
},
Updated = List.Transform(Data, each _ & [Score = [Score] + 5])
in
Updated // scores become 85 and 95
Mini summary: You can transform lists of records row by row.
Definition: A custom function is a block of code you write to perform a specific task.
Why important: You can reuse the same code many times with different inputs.
Simple explanation: Like a recipe for “fried rice” – you can make it whenever you want.
Real‑life example: A function to calculate discount.
School example: A function to calculate average.
Home example: A function to convert currency.
Nigerian example: A function to add VAT.
let
AddTen = (x) => x + 10,
Result = AddTen(5) // 15
in
Result
Mini summary: Functions are reusable recipes in M.
Definition: A function can take more than one input, like (x, y) => x + y.
Why important: Many tasks need more than one piece of information.
Simple explanation: Like adding two numbers – you need both numbers.
School example: A function to calculate total (maths + english).
Home example: A function to calculate total cost (price * quantity).
Nigerian example: A function to calculate total with VAT.
let
Add = (x, y) => x + y,
Sum = Add(5, 3) // 8
in
Sum
Mini summary: Functions can have many inputs.
Definition: You can pass a custom function to List.Transform to apply it to every item.
Why important: It gives you complete flexibility.
Simple explanation: Like having a special machine that can do any job you tell it.
School example: Apply a “pass/fail” function to every score.
Home example: Apply a “discount” function to every price.
Nigerian example: Apply a “region” function to every state.
let
AddTen = (x) => x + 10,
MyList = {10, 20, 30},
NewList = List.Transform(MyList, AddTen) // {20, 30, 40}
in
NewList
Mini summary: Custom functions make List.Transform very powerful.
Definition: “try” attempts a step; if it fails, “otherwise” gives a fallback value.
Why important: It prevents your code from crashing when something goes wrong.
Simple explanation: Like wearing a helmet – if you fall, it protects you.
School example: If a score is missing, use 0.
Home example: If an item has no price, set it to 100.
Nigerian example: If a state population is missing, use “Unknown”.
let
SafeDivide = (a, b) => try a / b otherwise 0,
Result = SafeDivide(10, 0) // 0 (no error)
in
Result
Mini summary: try…otherwise keeps your code safe from errors.
Definition: A full function can have multiple steps inside using let…in.
Why important: Complex tasks need multiple steps.
Simple explanation: Like a recipe with many steps – mix, knead, bake.
School example: A function to calculate grade (A, B, C) from score.
Home example: A function to calculate total with tax and discount.
Nigerian example: A function to clean a name (capitalize, remove spaces).
let
Grade = (score) =>
let
GradeText = if score >= 70 then "A" else "B"
in
GradeText,
Result = Grade(85) // "A"
in
Result
Mini summary: Functions can have multiple steps inside.
Definition: Record.Field gets a field value; Record.HasField checks if a field exists.
Why important: Sometimes you don’t know if a field is present.
Simple explanation: Like checking if a student has a “Score” field before using it.
School example: Only add marks if the “Score” field exists.
Home example: Only add a price if the “Price” field exists.
Nigerian example: Only add a capital if the “Capital” field exists.
let
MyRecord = [Name="Ade"],
HasScore = Record.HasField(MyRecord, "Score") // false
in
HasScore
Mini summary: Record.Field and HasField help you work safely with records.
Definition: Recursion is when a function calls itself to solve a problem.
Why important: It is useful for repetitive tasks like summing a list.
Simple explanation: Like counting marbles one by one until you reach the end.
School example: Sum all numbers in a list.
Home example: Count all items in a shopping list.
Nigerian example: Count all states in a list.
let
SumList = (lst) =>
if List.Count(lst) = 0 then 0
else List.First(lst) + SumList(List.Skip(lst, 1)),
Result = SumList({10, 20, 30}) // 60
in
Result
Mini summary: Recursion is a function that calls itself – powerful but advanced.
CleanName = (fullName) => Text.Proper(Text.Trim(fullName))CleanName(" ade ojo ") returns “Ade Ojo”.Record.HasField(record, "FieldName") to check.Record.Field(record, "FieldName").List as a Bag of Marbles +-----+-----+-----+-----+ | 10 | 20 | 30 | 40 | +-----+-----+-----+-----+ Index: 0 1 2 3
Record as an ID Card +------------------+ | Name: Ade | | Age: 10 | | Class: P5 | +------------------+
List of Records (Table) +----------+----------+----------+ | Name | Score | Grade | +----------+----------+----------+ | Ade | 80 | A | +----------+----------+----------+ | Bola | 90 | A | +----------+----------+----------+
| Concept | Syntax | Use |
|---|---|---|
| List | {1,2,3} | Collection of values |
| Record | [Name="Ade"] | Labelled values |
| List.Transform | List.Transform(list, each _ * 2) | Change every item |
| Record.Combine | Record.Combine({rec1, rec2}) | Merge records |
You have now mastered lists, records, and custom functions in M! You can create lists of any type, access items by index, and transform entire lists with List.Transform. You know how to build records, combine them, and work with lists of records. You have written your own functions with parameters and even used error handling to make your code safe. You are now equipped to write powerful, reusable M code that can handle complex data challenges. Well done!
Match the M concept with its description:
| Concept | Description |
|---|---|
| 1. List | A. A collection of values in { } |
| 2. Record | B. A set of labelled fields in [ ] |
| 3. List.Transform | C. Applies a function to every item |
| 4. try … otherwise | D. Error handling |
Answers: 1-A, 2-B, 3-C, 4-D
Scenario 1: You have a list of product records: [Product="Rice", Price=1000], etc. Write M code to add a 10% tax to each product’s price.
Scenario 2: You have a record with Name, Age, and Score. Write a function that returns the grade based on the score.
Each group creates a list of 10 records (e.g., students with Name, Score). Then, using List.Transform and a custom function, they transform the list to add a “Grade” column. Groups compare their approaches.
Write a custom function that cleans a list of names (capitalizes each, removes extra spaces). Test it on a list of your friends’ names.
Build a Power Query that takes a list of state records (Name, Capital, Population). Use a custom function to add a “Region” column (e.g., South, North) based on the state. Output the transformed list.
Using a sample dataset of employees (Name, Department, Salary), write M code to: (1) Create a list of records, (2) Add a “Bonus” column (10% of salary), (3) Filter to only employees with salary > 50000. Submit your M code.
Write a recursive function that sums all numbers in a list without using List.Sum. Test it with a list of 10 numbers.
Fill‑in‑the‑Blank: 1. curly 2. square 3. item 4. Record.Combine 5. function
True or False: 1-F, 2-F, 3-F, 4-T, 5-T
Multiple Choice: 1-b, 2-a, 3-a, 4-b, 5-b, 6-a, 7-a, 8-a, 9-a, 10-a, 11-a, 12-a, 13-a, 14-a, 15-a
In Module Seven, we will dive into “Performance and Efficiency in Power Query”. You will learn how to make your queries run faster, reduce memory usage, and handle big data like a pro. Get ready to optimize your data magic!
Note: This is Module Six. Module Five covered basic M. We now move to Module Seven, where we will focus on making our queries efficient and fast. Keep coding – you are becoming a true M master!
Welcome, young data explorer! In this module, we will learn how to make our data sparkly clean. Imagine you have a big bowl of fruits. Some fruits are rotten, some have dirt, and some are not even fruits! You wouldn't want to eat that, right? In the same way, when we work with data, we must clean it before we use it. Cleaning data means fixing mistakes, removing things we don't need, and making everything neat and tidy. This is a very important job for a data analyst. After this lesson, you will be a Data Cleaning Champion!
Chidi loves helping his mum at the market. One day, his mum gave him a messy list written on a crumpled paper. The list had things like:
milk, MILK, 2 milk, eggs, egg, 5 eggs, bread, bred, butter, buter, jam
Chidi was confused! Some items were repeated, some were spelled wrong, and some had numbers mixed in. He decided to clean the list. He crossed out the repeated "milk", fixed "bred" to "bread", and wrote "butter" correctly. Then he grouped all the eggs together. After cleaning, the list was perfect, and his mum was very happy. That is exactly what we do with data in Power Query – we clean it so that it makes sense!
Definition: Data cleaning is the process of finding and fixing errors in data.
Why important: Dirty data gives wrong answers. Clean data gives correct answers.
Simple explanation: It's like washing your hands before eating. You remove germs (errors) so you don't get sick (wrong results).
Real-life example: A teacher has a list of students. Some names are spelled wrong. She corrects them. That's cleaning.
School example: Your class register has duplicate names. You remove them.
Home example: You have a list of chores. Some chores are written twice. You delete the extras.
Nigerian example: A shopkeeper in Lagos records sales. Some entries say "N500" and others say "500 Naira". He makes them all "500 Naira".
Illustration:
Dirty Data ➡️ Clean Data ----------------- ----------------- Name: Tunde Name: Tunde Age: 25 Age: 25 City: Lagos City: Lagos Name: Tunde (dup) (removed)
Mini summary: Data cleaning fixes mistakes so our data is accurate and useful.
Definition: Duplicate rows are rows that are exactly the same as another row.
Why important: Duplicates make counts wrong (e.g., counting the same person twice).
Simple explanation: Imagine you have a list of your friends. If "Ade" appears twice, you might think you have two friends named Ade. But it's the same person. So we remove one.
Real-life example: A company sends newsletters. If email addresses are duplicated, people get two emails. They get annoyed.
School example: Your teacher has a list of students who brought books. If a student is listed twice, the count is wrong.
Home example: Your family grocery list has "rice" written three times. You keep only one.
Nigerian example: A bank in Abuja records transactions. The same transaction might be entered twice by mistake. Removing duplicates fixes the balance.
How to do it in Power Query: Select your table, go to the "Home" tab, click "Remove Rows", then "Remove Duplicates".
Illustration:
Before: After: +----+-------+ +----+-------+ | ID | Name | | ID | Name | +----+-------+ +----+-------+ | 1 | Bola | | 1 | Bola | | 2 | Tayo | | 2 | Tayo | | 1 | Bola | ← dup | 3 | Zain | | 3 | Zain | +----+-------+ +----+-------+
Mini summary: Use "Remove Duplicates" to delete rows that are identical.
Definition: Missing values are empty cells in your data.
Why important: Blanks can cause errors in calculations (e.g., sum of blank is zero, but maybe you wanted to ignore it).
Simple explanation: If a form asks for your age and you leave it blank, the computer doesn't know your age. We can fill it with something like "Unknown" or the average age.
Real-life example: A survey has some questions unanswered. We can fill them with "No answer".
School example: A test score list has a blank for a student who was absent. We can write "Absent".
Home example: A list of birthdays has a missing date. We can write "Not known".
Nigerian example: A hospital in Kano has patient records. Some have no phone number. They fill with "0000".
How to do it: In Power Query, use "Replace Values" to replace null or blank with something else. Or use "Fill Down" to copy the value from above.
Illustration:
Before: After (Fill Down): +----+-------+ +----+-------+ | Day| Temp | | Day| Temp | +----+-------+ +----+-------+ | Mon| 30° | | Mon| 30° | | Tue| | | Tue| 30° | ← copied from Mon | Wed| 28° | | Wed| 28° | +----+-------+ +----+-------+
Mini summary: Fill blanks with a value or copy from above to avoid empty cells.
Definition: Splitting means dividing one column into two or more columns.
Why important: Sometimes one column has too much information (e.g., "First Name Last Name"). We split it to separate first and last names.
Simple explanation: If you have a jar of mixed sweets, you separate the red ones from the green ones. That's splitting.
Real-life example: A column has "City, State". We split into "City" and "State".
School example: A column has "Subject-Grade" like "Math-A". Split into "Subject" and "Grade".
Home example: A list of "Item-Qty" like "Apples-5". Split into "Item" and "Qty".
Nigerian example: A column has "LGA, State" like "Ikeja, Lagos". Split into "LGA" and "State".
How to do it: Select the column, go to "Transform" tab, click "Split Column", choose "By Delimiter" (like a comma or space).
Illustration:
Before: After: +----------------+ +---------+---------+ | Full Name | | First | Last | +----------------+ +---------+---------+ | Chidi Okonkwo | | Chidi | Okonkwo | | Amina Bello | | Amina | Bello | +----------------+ +---------+---------+
Mini summary: Split columns to separate data that is combined.
Definition: Data type tells Power Query what kind of data is in a column (text, number, date, etc.).
Why important: If you have numbers stored as text, you cannot add them. So you must change to number type.
Simple explanation: It's like sorting toys: cars go in the car box, dolls in the doll box. You put each data in its correct box.
Real-life example: A column has "1000" but it is text. You change it to number so you can sum it.
School example: "Age" column has numbers but is stored as text. You change to number to calculate average age.
Home example: "Price" column has "₦500" but with a symbol. You remove the symbol and change to number.
Nigerian example: "Date of Birth" is stored as text "12-05-2010". Change to Date type to calculate age.
How to do it: Click the little icon next to the column header (like "ABC" or "123") and choose the correct type (Text, Whole Number, Decimal, Date).
Illustration:
Before (Text) After (Number) +-------+ +-------+ | Price | | Price | +-------+ +-------+ | 500 | | 500 | | 1000 | | 1000 | +-------+ +-------+ (Can't sum) (Can sum = 1500)
Mini summary: Always set the right data type so Power Query can work properly.
Definition: Renaming means changing the name of a column.
Why important: Sometimes column names are not clear (e.g., "Col1"). We rename to something meaningful like "Student Name".
Simple explanation: It's like putting a label on a box so you know what's inside.
Real-life example: A column named "B" actually contains "Age". Rename it to "Age".
School example: "X" column is "Score". Rename to "Math Score".
Home example: "Item" column is actually "Fruit". Rename to "Fruit".
Nigerian example: "Column1" is "State". Rename to "State of Origin".
How to do it: Double-click the column header and type the new name. Or right-click and choose "Rename".
Illustration:
Before: After: +------+ +---------+ | Col1 | | Student | +------+ +---------+ | Ade | | Ade | +------+ +---------+
Mini summary: Give columns clear, descriptive names.
Definition: Trimming removes unwanted spaces at the beginning or end of text.
Why important: " Bola " is not the same as "Bola". Extra spaces can cause problems when you compare or group.
Simple explanation: Imagine writing your name with a big space before it. That looks messy. We erase the space.
Real-life example: A list of cities has " Lagos" and "Lagos". They should be the same. Trim them.
School example: " John " and "John" are different to a computer. Trim to make them match.
Home example: " rice " and "rice" should be the same when searching.
Nigerian example: " Abuja" and "Abuja " are both Abuja after trimming.
How to do it: Select the column, go to "Transform" tab, click "Format", then "Trim".
Illustration:
Before: After: " Bola " "Bola" "Tunde " "Tunde" " Amina" "Amina"
Mini summary: Trim to remove extra spaces and make text clean.
Definition: Replacing means changing one value to another (e.g., change "N/A" to "Not Available").
Why important: Standardizes data. For example, "Yes", "Y", "y" all mean the same. We replace them all with "Yes".
Simple explanation: If you have different names for the same thing, you pick one name and use it everywhere.
Real-life example: "Male", "M", "m" all become "Male".
School example: "Absent", "A", "a" all become "Absent".
Home example: "Phone", "tel", "mobile" all become "Phone".
Nigerian example: "Naira", "NGN", "₦" all become "Naira".
How to do it: Right-click the column, choose "Replace Values", enter what to find and what to replace with.
Illustration:
Before: After: +-------+ +-------+ | Status| | Status| +-------+ +-------+ | Y | | Yes | | N | | No | | y | | Yes | +-------+ +-------+
Mini summary: Replace values to make your data uniform.
Definition: Remove rows means deleting certain rows from your table.
Why important: Sometimes you only need a portion of data (e.g., first 10 rows for a sample).
Simple explanation: If you have a long queue, you might only talk to the first 5 people.
Real-life example: You have 1000 rows but only want top 100 for testing.
School example: You have a list of 50 students but only need top 5.
Home example: You have a list of 20 groceries but only want the first 3 for a quick check.
Nigerian example: A store in Ibadan has 200 sales records but only wants the latest 10.
How to do it: Home tab → Remove Rows → Remove Top Rows, Remove Bottom Rows, or Remove Alternate Rows.
Illustration:
Before (10 rows) After Remove Top 3 (7 rows left) +----+-----+ +----+-----+ | 1 | A | | 4 | D | | 2 | B | ← remove | 5 | E | | 3 | C | ← remove | 6 | F | | 4 | D | | 7 | G | | 5 | E | | 8 | H | ... ... +----+-----+ +----+-----+
Mini summary: Remove rows you don't need to focus on important data.
Definition: Keep rows means you select and keep only certain rows, deleting all others.
Why important: This is the opposite of remove rows. You choose what to keep.
Simple explanation: It's like picking only the red marbles from a bag and leaving the rest.
Real-life example: You have sales data for all months but only want January to March.
School example: You have grades for all subjects but only want Science grades.
Home example: You have a list of all bills but only want electricity bills.
Nigerian example: You have data for all states but only want Lagos and Abuja.
How to do it: Home tab → Keep Rows → Keep Top Rows, Keep Bottom Rows, Keep Range of Rows.
Illustration:
Before (10 rows) After Keep Top 2 (only 2 rows) +----+-----+ +----+-----+ | 1 | A | ← keep | 1 | A | | 2 | B | ← keep | 2 | B | | 3 | C | remove | | | | 4 | D | remove | | | ... ... +----+-----+ +----+-----+
Mini summary: Keep only the rows that matter to you.
Definition: Fill Down copies the value from the cell above to the empty cells below.
Why important: Useful when you have a header that applies to multiple rows (e.g., a category).
Simple explanation: If you have a list of fruits, and "Apple" is written once, you can fill down so all apples have that label.
Real-life example: A sales table where the region is only written in the first row of each region. Fill down to fill all rows.
School example: A class list where the teacher's name is only in the first row. Fill down for all students.
Home example: A chore list where the day is written once. Fill down for all chores on that day.
Nigerian example: A table of LGA's, only the state is written once. Fill down to show state for every LGA.
How to do it: Select the column, go to "Transform" tab, click "Fill" and choose "Down".
Illustration: (See Lesson 3 illustration).
Mini summary: Fill Down copies the upper value to lower empty cells.
Definition: Fill Up copies the value from the cell below to the empty cells above.
Why important: Less common but useful when the first row is blank and the data starts later.
Simple explanation: Same as Fill Down but going upwards.
Real-life example: A table where the header is at the bottom. Fill up to move it to the top.
School example: A list where the last row has the correct value for all above.
Home example: A shopping list where the total is at the bottom, fill up to give each item the total.
Nigerian example: A report where the summary is at the bottom, fill up to apply to all.
How to do it: Transform tab → Fill → Up.
Illustration:
Before: After Fill Up: +-------+ +-------+ | | | 50 | ← copied from below | | | 50 | | 50 | | 50 | +-------+ +-------+
Mini summary: Fill Up copies the lower value to upper empty cells.
Definition: This means doing many cleaning actions one after another.
Why important: Real data needs many fixes. You combine them to get a perfect table.
Simple explanation: It's like a recipe: first wash the vegetables, then chop them, then cook them.
Real-life example: A data set: remove duplicates, trim spaces, change types, rename columns.
School example: A grade sheet: remove missing, change text to numbers, sort.
Home example: A budget: replace categories, split amounts, fill missing.
Nigerian example: A COVID-19 data: remove duplicate entries, fix date format, replace missing with "0".
How to do it: In Power Query, each step appears in the "Applied Steps" pane. You can reorder or delete steps.
Illustration: (Flow chart)
Start
|
V
Remove Duplicates
|
V
Trim Spaces
|
V
Change Data Types
|
V
Rename Columns
|
V
Done
Mini summary: Combine steps to clean data thoroughly.
Definition: Replace Errors changes error values (like #Error) to something else.
Why important: Errors stop calculations. Replacing them with 0 or "Unknown" lets you continue.
Simple explanation: If a math sum gives an error, you can say "let's treat it as zero".
Real-life example: A column has "5/0" which gives error. Replace with 0.
School example: A formula column has errors. Replace with "N/A".
Home example: A budget column has errors. Replace with 0.
Nigerian example: A financial report has division by zero errors. Replace with 0.
How to do it: Right-click the column → Replace Errors → type the replacement value.
Illustration:
Before: After: +-------+ +-------+ | #Error| | 0 | | 10 | | 10 | +-------+ +-------+
Mini summary: Replace errors to avoid breaking your analysis.
Definition: Pivot turns rows into columns. Unpivot turns columns into rows.
Why important: Sometimes data is in a "wide" format, but you need "long" format for analysis.
Simple explanation: If you have a table with months as columns, unpivot makes a "Month" column and a "Value" column.
Real-life example: Sales data with Jan, Feb, Mar as columns. Unpivot to have Month and Sales rows.
School example: A table with subjects as columns. Unpivot to have Subject and Score rows.
Home example: A budget with months as columns. Unpivot for easier charting.
Nigerian example: A table with states as columns. Unpivot to have State and Population rows.
How to do it: Select columns, right-click → Unpivot Columns. Or Pivot on a column.
Illustration:
Wide (Pivot) Long (Unpivot)
+----+-----+-----+ +----+-------+-------+
|Year| Jan | Feb | |Year| Month | Sales |
+----+-----+-----+ +----+-------+-------+
|2024| 100 | 150 | ➡️ |2024| Jan | 100 |
+----+-----+-----+ |2024| Feb | 150 |
+----+-------+-------+
Mini summary: Pivot/Unpivot reshapes your data for different needs.
How to remove duplicates:
How to split a column by delimiter:
Did you know that the word "data" comes from Latin, meaning "given" (like a gift). But dirty data is not a gift – it's a headache! So we clean it.
Start
|
V
Load Data
|
V
Remove Duplicates
|
V
Replace Missing Values
|
V
Split Columns (if needed)
|
V
Change Data Types
|
V
Trim Spaces
|
V
Rename Columns
|
V
Done 🎉
+----------------------------+ +----------------------------+ | Dirty Data | | Clean Data | +----------------------------+ +----------------------------+ | Name: " Ade " | | Name: "Ade" | | Age: "25" (text) | | Age: 25 (number) | | City: "Lagos, Nigeria" | | City: "Lagos" | | | | Country: "Nigeria" | | Duplicate row of Ade | | (Duplicate removed) | +----------------------------+ +----------------------------+
| Feature | Remove Rows | Keep Rows |
|---|---|---|
| What it does | Deletes selected rows | Keeps selected rows, deletes others |
| Use when | You want to exclude data | You want to focus on specific data |
| Example | Remove top 5 rows | Keep top 5 rows |
| Feature | Fill Down | Fill Up |
|---|---|---|
| Direction | Copies from above to below | Copies from below to above |
| Use when | Header is at the top | Header is at the bottom |
| Common | Very common | Less common |
Well done! You have completed Module Seven on Data Cleaning. You learned:
Cleaning data is like being a detective – you find clues (errors) and fix them. Now you are ready to clean any messy data!
Match the term to its definition:
| Term | Definition |
|---|---|
| 1. Trim | A. Copy value from above |
| 2. Fill Down | B. Remove extra spaces |
| 3. Duplicate | C. Divide one column into two |
| 4. Split | D. Identical row |
| 5. Data Type | E. Kind of data (text, number, date) |
Answers: 1-B, 2-A, 3-D, 4-C, 5-E
Scenario 1: You have a list of students with names, ages, and grades. Some ages are blank, some names have extra spaces, and there are duplicate rows for a student. What steps would you take to clean this data?
Scenario 2: A store in Lagos has sales data with columns: "Product", "Price (₦)", "Date". The "Price" column has "₦" symbols and spaces. How would you clean this data to calculate total sales?
In groups of 3, download a sample dataset with messy data (or create one). Assign each person a cleaning task: one removes duplicates, one trims and replaces, one changes data types. Then combine the cleaned data.
Take a messy list of 20 names (some with spaces, duplicates, typos). Use Power Query to clean it: trim, remove duplicates, fix typos, and rename the column. Write down the steps you took.
Project: Clean a School Dataset
You are given a dataset of 100 students with columns: StudentID, Name, Class, Score. The dataset has duplicates, missing scores, and inconsistent names (some have extra spaces, some are in uppercase/lowercase). Clean the dataset using Power Query. Present the cleaned table in class.
Download a CSV file from the internet (e.g., a sample sales dataset). Use Power Query to:
You have a dataset with sales for 5 years, each year as a separate column (2019, 2020, 2021, 2022, 2023). Unpivot this data to create a "Year" column and a "Sales" column. Then clean the data: remove any rows with sales less than 0, and replace any missing years with the average of the previous and next year.
Fill-in-the-Blank: 1. errors, 2. Duplicate, 3. Trim, 4. data type, 5. Fill Down
True/False: 1-F, 2-F, 3-T, 4-F, 5-F
Matching: 1-B, 2-A, 3-D, 4-C, 5-E
In Module Eight, we will learn about Combining Data – how to merge and append tables. We will join data from different sources, just like putting puzzle pieces together. Get ready to become a Data Merger!
Before the next class, practice cleaning at least three different datasets. The more you clean, the better you become.
© 2025 Certified Power Query for Data Analysis Expert – Module Seven
Hello, young data explorer! Welcome to Module Eight. In this module, we will learn how to combine data from different places. Imagine you have two puzzle pieces. Alone, they don't show the whole picture. But when you join them together, you see the complete image. That's what combining data does – it brings together information from different tables to give you a full story. We will learn about merging (like joining two lists side by side) and appending (like stacking one list on top of another). By the end of this module, you will be a Data Puzzle Master!
Ngozi is the class prefect. She has two lists. List A has student names and their test scores. List B has student names and their project scores. But the lists are separate. Ngozi wants to see each student's total score (test + project). She decides to combine the two lists. She matches each student from List A with the same student in List B. If a student is missing in one list, she writes "Absent". After combining, she has a complete table with all scores. That's what we will do with Power Query – combine tables to see the whole picture!
Definition: Combining data means taking two or more tables and joining them together.
Why important: Often your data is spread across different files. Combining helps you see everything in one place.
Simple explanation: It's like putting together pieces of a jigsaw puzzle to see the complete picture.
Real-life example: A company has a table of employees and a table of salaries. They combine them to get a full employee record.
School example: You have a list of students and a list of their favourite subjects. Combining gives you a list with both.
Home example: You have a list of family members and a list of their birthdays. Combine to see everyone's birthday.
Nigerian example: A bank has customer account details and transaction history. They combine them to see each customer's transactions.
Illustration:
Table 1 (Students) + Table 2 (Scores) = Combined Table +----+-------+ +----+-------+ +----+-------+-------+ | ID | Name | | ID | Score | | ID | Name | Score | +----+-------+ +----+-------+ +----+-------+-------+ | 1 | Bola | | 1 | 90 | | 1 | Bola | 90 | | 2 | Tunde | | 2 | 85 | | 2 | Tunde | 85 | +----+-------+ +----+-------+ +----+-------+-------+
Mini summary: Combining data brings separate tables together into one.
Definition: Merging adds columns from one table to another (side by side). Appending stacks rows from one table to another (top to bottom).
Why important: You need to know which method to use depending on what you want.
Simple explanation: Merging is like adding new clothes to your wardrobe (new columns). Appending is like adding more clothes of the same type (more rows).
Real-life example: Merging: adding phone numbers to a contact list. Appending: adding new contacts to the list.
School example: Merging: adding grades to student names. Appending: adding students from another class.
Home example: Merging: adding prices to grocery items. Appending: adding more grocery items.
Nigerian example: Merging: adding LGA to a list of states. Appending: adding data from another state.
Illustration:
Merging (side by side): Table A: ID, Name + Table B: ID, Age = Table C: ID, Name, Age Appending (top to bottom): Table A: ID, Name + Table B: ID, Name = Table C: ID, Name (more rows)
Mini summary: Merge = add columns. Append = add rows.
Definition: A key is a column that uniquely identifies each row (e.g., Student ID).
Why important: When merging, the key tells Power Query how to match rows from the two tables.
Simple explanation: It's like a secret code that two friends use to find each other in a crowd.
Real-life example: In a school, student ID is the key to match students with their grades.
School example: Your admission number is the key.
Home example: Your family member's name is a key to find their age.
Nigerian example: NIN (National Identification Number) is a key to match citizens with their records.
Illustration:
Table 1 (Key = ID) Table 2 (Key = ID) Merge on ID +----+-------+ +----+-------+ +----+-------+-------+ | ID | Name | | ID | Score | | ID | Name | Score | +----+-------+ +----+-------+ +----+-------+-------+ | 1 | Bola | | 1 | 90 | | 1 | Bola | 90 | | 2 | Tunde | | 2 | 85 | | 2 | Tunde | 85 | +----+-------+ +----+-------+ +----+-------+-------+
Mini summary: A key is a special column that matches rows from different tables.
Definition: Inner join keeps only rows that have a match in both tables.
Why important: Use this when you only want records that exist in both tables.
Simple explanation: If two friends both have the same secret code, they meet. If one doesn't have it, they don't meet.
Real-life example: You have a list of students and a list of those who paid fees. Inner join gives only students who have paid.
School example: List of subjects and list of teachers. Inner join gives subjects that have a teacher assigned.
Home example: List of family members and list of those who like pizza. Inner join gives family members who like pizza.
Nigerian example: Voter register and election results. Inner join gives only voters who voted.
Illustration:
Table A: ID, Name Table B: ID, Score 1, Bola 1, 90 2, Tunde 3, 88 ← ID 3 not in A Inner Join on ID → only ID 1 (Bola, 90) because ID 2 in A but not in B? Actually ID 2 in A, not in B, so not kept. Result: ID 1: Bola, 90
Mini summary: Inner join keeps only rows that appear in both tables.
Definition: Left outer join keeps all rows from the left table, and matching rows from the right. If no match, right side is null (empty).
Why important: Use this when you don't want to lose any rows from the main table.
Simple explanation: The left table is the boss. It keeps all its rows. The right table is a friend – if it has a match, it joins; if not, it leaves a blank.
Real-life example: You have a list of all students (left) and test scores (right). You want all students, even if they have no score.
School example: All students and their project scores. You want to see all students, even those who didn't submit a project.
Home example: Family members and their chores. You want all members, even if they have no chore.
Nigerian example: All customers and their orders. You want all customers, even those who haven't ordered.
Illustration:
Left Table: ID, Name Right Table: ID, Score 1, Bola 1, 90 2, Tunde 3, 88 3, Zain Left Outer Join → all from left: 1, Bola, 90 2, Tunde, null (no score) 3, Zain, null (no score)
Mini summary: Left outer join keeps all rows from the left table.
Definition: Right outer join keeps all rows from the right table, and matching rows from the left. If no match, left side is null.
Why important: Use this when the right table is your main focus.
Simple explanation: Now the right table is the boss.
Real-life example: You have a list of products (right) and a list of sales (left). You want all products, even if they have no sales.
School example: Subjects (right) and teachers (left). You want all subjects, even if no teacher assigned.
Home example: Chores (right) and family members (left). You want all chores, even if no one does them.
Nigerian example: States (right) and governors (left). You want all states, even if some have no governor data.
Illustration:
Left Table: ID, Name Right Table: ID, Score
1, Bola 1, 90
2, Tunde 3, 88
4, 95
Right Outer Join → all from right:
1, Bola, 90
3, null, 88
4, null, 95
Mini summary: Right outer join keeps all rows from the right table.
Definition: Full outer join keeps all rows from both tables. If a row doesn't have a match, the missing side is null.
Why important: Use when you want to see everything from both tables.
Simple explanation: Both tables are bosses. Everyone is included.
Real-life example: You have a list of employees and a list of departments. You want to see all employees and all departments, even if some employees are not assigned to a department.
School example: Students and clubs. You want all students and all clubs.
Home example: Family members and hobbies. You want all members and all hobbies.
Nigerian example: Companies and products. You want all companies and all products.
Illustration:
Left: 1, Bola ; 2, Tunde Right: 1, 90 ; 3, 88 Full Outer → 1, Bola, 90 2, Tunde, null 3, null, 88
Mini summary: Full outer join keeps all rows from both tables.
Definition: Merging in Power Query is done using the "Merge Queries" button.
Why important: This is the main tool you will use to combine tables.
Simple explanation: It's like clicking a magic button that joins your tables.
Real-life example: You have two Excel sheets. You merge them to create one.
School example: You have a class list and a grade list. Merge to get grades for each student.
Home example: You have a grocery list and a price list. Merge to get total cost.
Nigerian example: You have a list of LGAs and a list of populations. Merge to have population per LGA.
How to do it: In Power Query, go to the "Home" tab, click "Merge Queries", select the two tables, choose the key column, and select the join type.
Illustration:
Step 1: Home → Merge Queries Step 2: Select Table 1 and Table 2 Step 3: Choose key column (e.g., ID) from each Step 4: Choose join type (Inner, Left, etc.) Step 5: Click OK → new column with nested tables Step 6: Expand the new column to get the data.
Mini summary: Merge Queries is the tool to combine tables in Power Query.
Definition: Appending adds rows from one table to another. Both tables must have the same column structure.
Why important: Use this when you have data split across multiple files (e.g., sales by month).
Simple explanation: It's like stacking books on top of each other.
Real-life example: You have sales for January in one file and sales for February in another. Append them to get all sales.
School example: Class A attendance and Class B attendance. Append to get total attendance.
Home example: Expenses for week 1 and week 2. Append to get monthly expenses.
Nigerian example: COVID-19 data for Lagos and COVID-19 data for Abuja. Append to get national data.
How to do it: Home tab → Append Queries → Select the tables to append.
Illustration:
Table A: ID, Name Table B: ID, Name Append →
1, Bola 3, Zain 1, Bola
2, Tunde 4, Amina 2, Tunde
3, Zain
4, Amina
Mini summary: Append stacks rows.
Definition: Power Query can combine all files in a folder (e.g., all CSV files).
Why important: Saves time when you have many files.
Simple explanation: It's like putting all your toys from different boxes into one big toy box.
Real-life example: A company has daily sales files for a year. Power Query combines them into one table.
School example: You have homework files for each subject. Combine them into one file.
Home example: You have photos from different holidays. Combine them into one album.
Nigerian example: A bank has monthly account statements. Combine them into yearly statement.
How to do it: Get Data → From Folder → Browse to folder → Combine & Transform.
Illustration:
Folder: Sales
|-- Jan.csv
|-- Feb.csv
|-- Mar.csv
...
Power Query combines all into one Sales table.
Mini summary: Combine from folder is a powerful way to merge many files.
Definition: Mismatched keys happen when the key column has different values in the two tables (e.g., "1" vs "01").
Why important: If keys don't match, your merge will be wrong.
Simple explanation: If two friends have different secret codes, they can't find each other.
Real-life example: Student ID in one table is "100" and in another is "00100". They don't match.
School example: Your name is "Tunde" in one list and "Tunde O." in another. Not exactly the same.
Home example: "Dad" in one list and "Father" in another.
Nigerian example: Phone number has different formats (080 vs +23480).
How to fix: Before merging, transform the key columns to be the same (e.g., trim, format, convert to text).
Illustration:
Table A: ID = "001" Table B: ID = "1" → Convert both to text and trim. Then they match: "001" and "1" are different, so we might need to convert to number: 1 and 1 match.
Mini summary: Always ensure keys match exactly before merging.
Definition: After a merge, Power Query creates a new column containing nested tables. You need to expand it to see the data.
Why important: Without expanding, you only see the word "Table".
Simple explanation: It's like opening a gift box to see the present inside.
Real-life example: You merged, and you see a column with "Table". Click the expand icon to get the actual data.
School example: You merged grades, and you see "Table". Expand to see scores.
Home example: You merged prices, and you see "Table". Expand to see prices.
Nigerian example: You merged populations, expand to see population numbers.
How to do it: Click the expand icon (two arrows) on the new column header, select the columns you want, and click OK.
Illustration:
After Merge: +----+-------+-------------+ | ID | Name | Merged | +----+-------+-------------+ | 1 | Bola | Table | ← Click expand +----+-------+-------------+ After Expand: +----+-------+-------+ | ID | Name | Score | +----+-------+-------+ | 1 | Bola | 90 | +----+-------+-------+
Mini summary: Expand nested tables to see the actual data.
Definition: Sometimes you need to match on two or more columns (e.g., First Name and Last Name).
Why important: Single key might not be unique.
Simple explanation: You use two clues to find your friend: name and age.
Real-life example: Match customers by first name and last name together.
School example: Match students by class and roll number.
Home example: Match chores by day and person.
Nigerian example: Match citizens by state and LGA.
How to do it: In the merge dialog, select multiple columns from each table (hold Ctrl to select multiple).
Illustration:
Table A: First, Last, Age Table B: First, Last, Score Merge on First AND Last.
Mini summary: You can merge on multiple columns for a better match.
Definition: Fuzzy matching allows matches even if the key is not exactly the same (e.g., "Bola" and "Bolah").
Why important: Handles typos or slight differences.
Simple explanation: It's like guessing a friend's name even if you spell it a little wrong.
Real-life example: "Ade" and "Adeyemi" might be matched.
School example: "Maths" and "Mathematics" might be matched.
Home example: "Soda" and "Pop" might be matched.
Nigerian example: "Lagos" and "Lag" might be matched.
How to do it: In merge dialog, check "Use fuzzy matching" and adjust similarity threshold.
Illustration:
Table A: "Bola" Table B: "Bolah" Fuzzy match → treated as same.
Mini summary: Fuzzy matching helps when data has small differences.
Definition: Follow these tips for successful data combining.
Why important: Avoids errors and makes your work easier.
Simple explanation: Like following a recipe to bake a perfect cake.
Illustration:
Best Practice Flow: 1. Clean keys (trim, remove spaces) 2. Decide merge or append 3. Perform merge/append 4. Expand if needed 5. Check result 6. Rename columns
Mini summary: Good planning leads to successful combining.
How to Merge Two Tables:
How to Append Two Tables:
Did you know that Power Query can combine data from thousands of files in a folder in just a few clicks? That's powerful!
Merge (side by side):
+-------+-------+ +-------+-------+ +-------+-------+-------+
| ID | Name | | ID | Score | | ID | Name | Score |
+-------+-------+ +-------+-------+ +-------+-------+-------+
| 1 | Bola | | 1 | 90 | | 1 | Bola | 90 |
| 2 | Tunde | | 2 | 85 | | 2 | Tunde | 85 |
+-------+-------+ +-------+-------+ +-------+-------+-------+
Append (top to bottom):
+-------+-------+ +-------+-------+ +-------+-------+
| ID | Name | | ID | Name | | ID | Name |
+-------+-------+ +-------+-------+ +-------+-------+
| 1 | Bola | | 3 | Zain | | 1 | Bola |
| 2 | Tunde | | 4 | Amina | | 2 | Tunde |
+-------+-------+ +-------+-------+ | 3 | Zain |
| 4 | Amina |
+-------+-------+
Inner Join: Keeps only matching rows. Left Outer: Keeps all from left, matches from right. Right Outer: Keeps all from right, matches from left. Full Outer: Keeps all from both.
| Feature | Merge | Append |
|---|---|---|
| What it adds | Columns | Rows |
| Requires | Key column | Same column structure |
| Use when | You want to combine different information | You want to combine same information from different sources |
| Join Type | Keeps | Use case |
|---|---|---|
| Inner | Only matches | When you need only common records |
| Left Outer | All from left | When left table is main |
| Right Outer | All from right | When right table is main |
| Full Outer | All from both | When you need everything |
Congratulations! You have completed Module Eight on Combining Data. You learned:
Combining data is like being a master puzzle solver. You take different pieces and put them together to see the big picture. Now you can combine any data with confidence!
Match the term to its definition:
| Term | Definition |
|---|---|
| 1. Merge | A. Add rows |
| 2. Append | B. Add columns |
| 3. Key | C. Approximate match |
| 4. Inner Join | D. Unique identifier |
| 5. Fuzzy Matching | E. Keep only matches |
Answers: 1-B, 2-A, 3-D, 4-E, 5-C
Scenario 1: You have a table of students (ID, Name) and a table of grades (ID, Grade). You want a table with all students and their grades, but some students have no grade. Which join would you use? Write the steps.
Scenario 2: You have sales data for January and February in separate files. Both files have the same columns. How would you combine them? What is this operation called?
In groups, get two sample datasets (e.g., customer data and order data). Decide which join type to use. Perform the merge in Power Query. Present the result to the class.
Create two small tables in Excel: one with student IDs and names, another with student IDs and scores. Import them into Power Query, perform a Left Outer Join, and expand the result.
Project: Combine Sales Data
You have sales data for four quarters (Q1, Q2, Q3, Q4) in separate Excel files. Each file has the same columns: Product, Salesperson, Amount. Combine all four files into one table using Power Query (append). Then, merge this with a product details table (Product, Category) to add category information. Present the final combined table.
Download two datasets from the internet (e.g., customer list and purchase history). Use Power Query to:
You have three tables: Students (ID, Name), Subjects (SubjectID, SubjectName), and Scores (ID, SubjectID, Score). Write the steps to combine all three tables into one table that shows each student's name, subject, and score. Which joins would you use? Perform the steps in Power Query.
Fill-in-the-Blank: 1. Merge, 2. Append, 3. key, 4. Inner, 5. Left Outer
True/False: 1-F, 2-F, 3-T, 4-F, 5-T
Matching: 1-B, 2-A, 3-D, 4-E, 5-C
In Module Nine, we will learn about Grouping and Aggregating data. We will summarize data (like total sales per product) and group rows to get insights. Get ready to become a Data Summarizer!
Before the next class, practice merging and appending with different datasets. Try to combine data from at least two different sources.
© 2025 Certified Power Query for Data Analysis Expert – Module Eight
Hello, young data explorer! Welcome to Module Nine. In this module, we will learn how to group and summarize data. Imagine you have a big bag of colorful candies. You want to know how many red, blue, and green candies you have. You don't count each candy one by one – you group them by color and then count each group. That's what we do with data: we group rows that share something in common (like product type or region) and then we calculate totals, averages, or counts. This helps us see the big picture and make better decisions. By the end of this module, you will be a Data Summarizing Wizard!
Chidi runs a small candy shop. He has a long list of all the candies he sold. The list shows each sale: candy name, quantity, and price. But Chidi wants to know: "How many of each candy did I sell?" He groups his sales by candy name. Then he sums the quantities for each candy. He also finds the total money made for each candy. Now he can see which candy is the most popular and which makes the most money. That's grouping and summarizing!
Definition: Grouping means putting rows that have the same value in a column into one group.
Why important: Grouping helps us see patterns and totals for each category.
Simple explanation: It's like sorting your toys into groups: cars in one group, dolls in another.
Real-life example: A store groups sales by product to see which product sells the most.
School example: Your teacher groups students by class to count how many are in each class.
Home example: You group your chores by day of the week to see how many you have each day.
Nigerian example: A bank groups customers by state to see which state has the most customers.
Illustration:
Ungrouped Data: +-------+--------+-------+ | Candy | Qty | Price | +-------+--------+-------+ | Lolly | 5 | 10 | | Choco | 3 | 20 | | Lolly | 2 | 10 | | Choco | 4 | 20 | | Gum | 6 | 5 | +-------+--------+-------+ Grouped by Candy: +-------+--------+-------+ | Candy | Total | Total | | | Qty | Price | +-------+--------+-------+ | Lolly | 7 | 30 | | Choco | 7 | 40 | | Gum | 6 | 5 | +-------+--------+-------+
Mini summary: Grouping organizes data by common values.
Definition: Summarizing means calculating a single number for a group, like total, average, count, minimum, or maximum.
Why important: Summarizing turns lots of data into simple, useful numbers.
Simple explanation: It's like adding up all your pocket money to know how much you have in total.
Real-life example: Calculate total sales for each product.
School example: Calculate the average test score for each subject.
Home example: Count how many times you ate your favorite meal in a week.
Nigerian example: Find the minimum and maximum temperature in each state.
Illustration:
Summarizing: Group: Lolly → Total Qty = 7, Total Price = 30 Group: Choco → Total Qty = 7, Total Price = 40 Group: Gum → Total Qty = 6, Total Price = 5
Mini summary: Summarizing calculates totals, averages, and counts for each group.
Definition: The Group By feature in Power Query groups rows and performs aggregations.
Why important: It's the main tool for grouping and summarizing.
Simple explanation: It's a magic button that does all the grouping and summing for you.
Real-life example: You have a sales table. You use Group By to get total sales per product.
School example: You have a grade table. Group By to get average grade per subject.
Home example: You have an expense table. Group By to get total spent on food, transport, etc.
Nigerian example: You have election results. Group By to get total votes per party.
How to do it: In Power Query, go to the "Transform" tab, click "Group By". Choose the column to group by, and select the aggregation (Sum, Average, etc.).
Illustration:
Transform → Group By +-------------------+ | Group By Window | | Group by: Candy | | New column: Total | | Operation: Sum | | Column: Qty | +-------------------+
Mini summary: Group By is the tool to summarize data in Power Query.
Definition: Sum adds all the numbers in a group.
Why important: To find total sales, total quantity, total anything.
Simple explanation: If you have 2 apples, 3 apples, and 5 apples, sum is 10 apples.
Real-life example: Total sales for each product.
School example: Total marks for each student.
Home example: Total money spent on groceries.
Nigerian example: Total population per state.
How to do it: In Group By, choose Operation: Sum, Column: the number column.
Illustration:
Group: Candy → Sum Qty = 5+2 = 7
Mini summary: Sum adds up numbers in a group.
Definition: Average adds all numbers and divides by the count.
Why important: To find the typical value, like average score or average price.
Simple explanation: If you have 3, 5, and 7, average is (3+5+7)/3 = 5.
Real-life example: Average price of products.
School example: Average test score of a class.
Home example: Average temperature of the week.
Nigerian example: Average rainfall in each state.
How to do it: In Group By, choose Operation: Average.
Illustration:
Group: Candy → Average Price = (10+20+10+20+5)/5 = 13
Mini summary: Average finds the typical value in a group.
Definition: Count tells you how many rows are in a group.
Why important: To know how many items, customers, or transactions.
Simple explanation: If you have 5 candies in a group, count is 5.
Real-life example: Number of orders per product.
School example: Number of students in each class.
Home example: Number of times you ate out this week.
Nigerian example: Number of voters per polling unit.
How to do it: In Group By, choose Operation: Count Rows.
Illustration:
Group: Candy → Count = number of rows for that candy. Lolly has 2 rows, so Count = 2.
Mini summary: Count gives the number of rows in a group.
Definition: Minimum finds the smallest number in a group.
Why important: To find the lowest price, smallest size, etc.
Simple explanation: Among 3, 5, and 7, minimum is 3.
Real-life example: Lowest price of a product.
School example: Lowest test score in a class.
Home example: Minimum temperature of the week.
Nigerian example: Minimum salary in each state.
How to do it: In Group By, choose Operation: Minimum.
Illustration:
Group: Candy → Minimum Price = 10 (for Lolly)
Mini summary: Minimum finds the smallest value.
Definition: Maximum finds the largest number in a group.
Why important: To find the highest price, largest size, etc.
Simple explanation: Among 3, 5, and 7, maximum is 7.
Real-life example: Highest price of a product.
School example: Highest test score in a class.
Home example: Maximum temperature of the week.
Nigerian example: Maximum population in a state.
How to do it: In Group By, choose Operation: Maximum.
Illustration:
Group: Candy → Maximum Price = 20 (for Choco)
Mini summary: Maximum finds the largest value.
Definition: You can group by more than one column (e.g., Candy and Color).
Why important: To get more detailed summaries.
Simple explanation: First group by candy, then by color to see each candy's colors.
Real-life example: Group sales by Product and Month.
School example: Group grades by Subject and Teacher.
Home example: Group expenses by Category and Day.
Nigerian example: Group population by State and LGA.
How to do it: In Group By, click "Add Grouping" and select another column.
Illustration:
Group by: Candy, Color +-------+-------+-------+ | Candy | Color | Total | +-------+-------+-------+ | Lolly | Red | 10 | | Lolly | Blue | 5 | | Choco | Brown | 15 | +-------+-------+-------+
Mini summary: Group by multiple columns for deeper insights.
Definition: You can add more than one aggregation in the same Group By.
Why important: To get multiple summaries at once (e.g., Total and Average).
Simple explanation: You can get total sales and average sales per group.
Real-life example: Group by Product: get Total Sales, Average Price, and Count.
School example: Group by Subject: get Total Marks, Average Marks, and Highest Marks.
Home example: Group by Category: get Total Spent and Average Cost.
Nigerian example: Group by State: get Total Population, Average Income, and Count of LGAs.
How to do it: In Group By, click "Add Aggregation" to add more operations.
Illustration:
Group by: Candy +-------+--------+--------+--------+ | Candy | Total | Average| Count | | | Price | Price | | +-------+--------+--------+--------+ | Lolly | 30 | 15 | 2 | | Choco | 40 | 20 | 2 | +-------+--------+--------+--------+
Mini summary: Add multiple aggregations for a complete summary.
Definition: You can give new names to the columns created by Group By.
Why important: To make the column names clear and descriptive.
Simple explanation: Instead of "Sum of Qty", you name it "Total Quantity".
Real-life example: Rename "Average of Price" to "Avg Price".
School example: Rename "Count" to "Number of Students".
Home example: Rename "Maximum" to "Highest Cost".
Nigerian example: Rename "Sum of Population" to "Total Population".
How to do it: In the Group By dialog, type the new name in the "New column name" field.
Illustration:
Before: "Sum of Qty" After: "Total Quantity"
Mini summary: Rename aggregated columns for clarity.
Definition: After grouping, you can sort the groups by the summarized values.
Why important: To see which groups are the highest or lowest.
Simple explanation: Sort products by total sales from highest to lowest.
Real-life example: Find the best-selling product.
School example: Find the subject with the highest average.
Home example: Find the day you spent the most money.
Nigerian example: Find the state with the highest population.
How to do it: After Group By, click the sort icon on the column header.
Illustration:
Sort by Total Sales Descending: +-------+--------+ | Candy | Total | +-------+--------+ | Choco | 40 | | Lolly | 30 | | Gum | 5 | +-------+--------+
Mini summary: Sort groups to see the top or bottom performers.
Definition: You can filter groups based on the summarized values (e.g., only groups with total sales > 100).
Why important: To focus on important groups.
Simple explanation: Only show candies that sold more than 10 pieces.
Real-life example: Show only products with average price above 50.
School example: Show only students with total marks above 80.
Home example: Show only months with total expenses above 1000.
Nigerian example: Show only states with population above 1 million.
How to do it: After Group By, use the filter dropdown on the aggregated column.
Illustration:
Filter: Total Qty > 5 +-------+--------+ | Candy | Total | +-------+--------+ | Lolly | 7 | | Choco | 7 | +-------+--------+
Mini summary: Filter groups to focus on specific criteria.
Definition: Group By helps answer business questions like "What is the best-selling product?"
Why important: It turns data into answers.
Simple explanation: You ask a question, and Group By gives the answer.
Real-life example: "Which product has the highest total sales?" – Group by product, sum sales, sort descending.
School example: "Which subject has the highest average score?" – Group by subject, average score, sort.
Home example: "On which day do I spend the most?" – Group by day, sum expenses, sort.
Nigerian example: "Which state has the most voters?" – Group by state, count voters, sort.
Illustration:
Question: What is the total sales per product? Answer: Group By Product, Sum Sales.
Mini summary: Group By turns questions into answers.
Definition: Tips for effective grouping.
Why important: To avoid mistakes and get accurate summaries.
Simple explanation: Like following a recipe for a perfect dish.
Illustration:
1. Clean data 2. Choose group columns 3. Select aggregations 4. Name columns 5. Sort/filter as needed
Mini summary: Follow best practices for accurate grouping.
How to Group and Summarize Data:
Did you know that grouping is also called "aggregation" or "roll-up"? It's like rolling up a rug – you make it smaller and more compact.
Start
|
V
Load Data
|
V
Clean Data
|
V
Group By
|
V
Choose Group Column(s)
|
V
Choose Aggregations
|
V
Rename Columns
|
V
Sort / Filter (optional)
|
V
Done 🎉
+--------+--------+-------+ | State | LGA | Pop | +--------+--------+-------+ | Lagos | Ikeja | 500K | | Lagos | Surulere| 400K | | Ogun | Abeokuta| 300K | | Ogun | Ijebu | 200K | +--------+--------+-------+ Group by State, LGA → Sum Pop +--------+--------+-------+ | State | LGA | Total | | | | Pop | +--------+--------+-------+ | Lagos | Ikeja | 500K | | Lagos | Surulere| 400K | | Ogun | Abeokuta| 300K | | Ogun | Ijebu | 200K | +--------+--------+-------+
| Aggregation | What it does | Example |
|---|---|---|
| Sum | Adds numbers | Total sales |
| Average | Finds the mean | Average price |
| Count | Counts rows | Number of orders |
| Minimum | Smallest value | Lowest price |
| Maximum | Largest value | Highest score |
| Single Column | Multiple Columns |
|---|---|
| Less detailed | More detailed |
| Example: Group by Product | Example: Group by Product and Month |
| Fewer groups | More groups |
Congratulations! You have completed Module Nine on Grouping and Summarizing Data. You learned:
Grouping and summarizing turn raw data into useful information. Now you can answer any "what is the total?" or "what is the average?" question with ease!
Match the aggregation to its description:
| Aggregation | Description |
|---|---|
| 1. Sum | A. Smallest value |
| 2. Average | B. Largest value |
| 3. Count | C. Add all numbers |
| 4. Minimum | D. Sum divided by count |
| 5. Maximum | E. Number of rows |
Answers: 1-C, 2-D, 3-E, 4-A, 5-B
Scenario 1: You have a table of sales with columns: Product, Salesperson, Amount. You want to know the total amount sold by each salesperson. How would you do it? Write the steps.
Scenario 2: You have a table of student test scores with columns: Student, Subject, Score. You want to know the average score for each subject. Which aggregation would you use? Write the steps.
In groups, get a dataset of sales (Product, Region, Sales). Use Group By to find total sales per product and per region. Present your findings.
Create a small table of expenses (Category, Amount). Use Power Query to group by Category and Sum Amount. Also add a Count of expenses per category.
Project: Sales Summary
You have a dataset of 500 sales transactions with columns: OrderID, Product, Category, Salesperson, Region, Amount, Date. Use Power Query to:
Download a sample dataset (e.g., sales data). Use Power Query to group by at least two different columns and perform at least three aggregations. Save the summarized table.
You have a dataset with columns: Product, Month, Sales. Group by Product and Month, then calculate Total Sales. Then, for each product, find the month with the highest sales. Use grouping and sorting to achieve this.
Fill-in-the-Blank: 1. Grouping, 2. Sum, 3. Average, 4. Count, 5. Group By
True/False: 1-F, 2-T, 3-F, 4-T, 5-T
Matching: 1-C, 2-D, 3-E, 4-A, 5-B
In Module Ten, we will learn about Pivot and Unpivot – how to reshape your data from wide to long and vice versa. This will help you prepare data for charts and deeper analysis. Get ready to twist and turn your data!
Before the next class, practice grouping and summarizing with different datasets. Try to answer at least three business questions using grouping.
© 2025 Certified Power Query for Data Analysis Expert – Module Nine
Hello, young data explorer! Welcome to Module Ten. In this module, we will learn how to reshape our data. Sometimes data is in a "wide" format (many columns) and sometimes in a "long" format (many rows). We need to change from one to the other. This is called pivoting and unpivoting. Imagine you have a table where each month is a column. Unpivot turns those month columns into rows. Pivot does the opposite – it turns rows into columns. This helps us prepare data for charts and reports. By the end of this module, you will be a Data Reshaping Master!
Chidi has a sales table with months as columns: January, February, March. He wants to create a chart, but the chart expects months in a single column. So he unpivots the data – he turns the month columns into rows. Now he has a "Month" column and a "Sales" column. He can now make a beautiful chart. Later, his teacher wants a summary table with months as columns again. So he pivots the data back. That's the power of pivot and unpivot!
Definition: Wide data has many columns. Each column represents a different variable (e.g., Jan, Feb, Mar).
Why important: Wide data is easy to read for humans, but not always good for analysis.
Simple explanation: It's like a big table where each month has its own column.
Real-life example: A table with sales for each month as separate columns.
School example: A table with subjects as columns and student scores as rows.
Home example: A table with days of the week as columns and chores as rows.
Nigerian example: A table with states as columns and population as rows.
Illustration:
Wide Data (Months as columns) +--------+-----+-----+-----+ | Product| Jan | Feb | Mar | +--------+-----+-----+-----+ | Choco | 100 | 150 | 200 | | Lolly | 50 | 60 | 70 | +--------+-----+-----+-----+
Mini summary: Wide data has many columns.
Definition: Long data has fewer columns but many rows. One column identifies the variable (e.g., Month), and another holds the value (e.g., Sales).
Why important: Long data is great for charts and analysis.
Simple explanation: It's like a tall table where months are in a single column.
Real-life example: A table with a "Month" column and a "Sales" column.
School example: A table with "Subject" and "Score" columns.
Home example: A table with "Day" and "Expense" columns.
Nigerian example: A table with "State" and "Population" columns.
Illustration:
Long Data (Month in one column) +--------+-------+-------+ | Product| Month | Sales | +--------+-------+-------+ | Choco | Jan | 100 | | Choco | Feb | 150 | | Choco | Mar | 200 | | Lolly | Jan | 50 | | Lolly | Feb | 60 | | Lolly | Mar | 70 | +--------+-------+-------+
Mini summary: Long data has few columns but many rows.
Definition: Unpivot turns columns into rows. It takes wide data and makes it long.
Why important: Unpivot prepares data for charts and analysis.
Simple explanation: It's like taking a wide table and stacking it into a tall table.
Real-life example: Turning monthly sales columns into a Month column and a Sales column.
School example: Turning subject columns into a Subject column and a Score column.
Home example: Turning day columns into a Day column and a Chore column.
Nigerian example: Turning state columns into a State column and a Population column.
How to do it: Select the columns you want to unpivot, right-click, and choose "Unpivot Columns".
Illustration:
Wide → Unpivot → Long
+--------+-----+-----+-----+ +--------+-------+-------+
| Product| Jan | Feb | Mar | | Product| Month | Sales |
+--------+-----+-----+-----+ +--------+-------+-------+
| Choco | 100 | 150 | 200 | | Choco | Jan | 100 |
| Lolly | 50 | 60 | 70 | | Choco | Feb | 150 |
+--------+-----+-----+-----+ | Choco | Mar | 200 |
| Lolly | Jan | 50 |
| Lolly | Feb | 60 |
| Lolly | Mar | 70 |
+--------+-------+-------+
Mini summary: Unpivot turns columns into rows.
Definition: In Power Query, you can unpivot by selecting columns and using the "Unpivot Columns" option.
Why important: This is the main way to reshape data in Power Query.
Simple explanation: It's like clicking a button to change a wide table into a long table.
Real-life example: You have a table with months as columns. You unpivot to get a Month column.
School example: You have a table with subjects as columns. You unpivot to get a Subject column.
Home example: You have a table with days as columns. You unpivot to get a Day column.
Nigerian example: You have a table with states as columns. You unpivot to get a State column.
How to do it: Select the columns (e.g., Jan, Feb, Mar) → right-click → Unpivot Columns.
Illustration:
Select Jan, Feb, Mar → Right-click → Unpivot Columns
Mini summary: Use Unpivot Columns to turn columns into rows.
Definition: You can unpivot some columns while keeping others as identifiers (e.g., Product).
Why important: You often want to keep some columns as "keys" while unpivoting others.
Simple explanation: You keep the "Product" column as is, and unpivot the month columns.
Real-life example: Keep Product and Region, unpivot monthly sales.
School example: Keep Student Name, unpivot subject scores.
Home example: Keep Family Member, unpivot daily chores.
Nigerian example: Keep LGA, unpivot monthly rainfall.
How to do it: Select the columns to unpivot, right-click, and choose "Unpivot Columns". The other columns stay as identifiers.
Illustration:
Keep Product, unpivot Jan, Feb, Mar. Result: Product, Attribute (Month), Value (Sales).
Mini summary: Unpivot specific columns while keeping others as identifiers.
Definition: Pivot turns rows into columns. It takes long data and makes it wide.
Why important: Pivot helps create summary tables that are easy to read.
Simple explanation: It's like taking a tall table and spreading it out into a wide table.
Real-life example: Turning a Month column into separate columns for each month.
School example: Turning a Subject column into separate columns for each subject.
Home example: Turning a Day column into separate columns for each day.
Nigerian example: Turning a State column into separate columns for each state.
How to do it: In Power Query, select the column you want to pivot, go to Transform, and click "Pivot Column".
Illustration:
Long → Pivot → Wide +--------+-------+-------+ +--------+-----+-----+-----+ | Product| Month | Sales | | Product| Jan | Feb | Mar | +--------+-------+-------+ +--------+-----+-----+-----+ | Choco | Jan | 100 | | Choco | 100 | 150 | 200 | | Choco | Feb | 150 | | Lolly | 50 | 60 | 70 | | Choco | Mar | 200 | +--------+-----+-----+-----+ | Lolly | Jan | 50 | | Lolly | Feb | 60 | | Lolly | Mar | 70 | +--------+-------+-------+
Mini summary: Pivot turns rows into columns.
Definition: In Power Query, you pivot using the "Pivot Column" feature.
Why important: This is the main way to make wide tables from long tables.
Simple explanation: You choose a column whose values will become new columns.
Real-life example: You have a Month column. Pivot it to get columns for each month.
School example: You have a Subject column. Pivot it to get columns for each subject.
Home example: You have a Day column. Pivot it to get columns for each day.
Nigerian example: You have a State column. Pivot it to get columns for each state.
How to do it: Select the column to pivot (e.g., Month) → Transform → Pivot Column → Choose the values column (e.g., Sales).
Illustration:
Select Month → Pivot Column → Values: Sales → OK
Mini summary: Use Pivot Column to turn rows into columns.
Definition: You can pivot with more than one value column (e.g., Sales and Quantity).
Why important: Sometimes you need to pivot multiple measures.
Simple explanation: You can have both Sales and Quantity as values.
Real-life example: Pivot Month to get Sales and Quantity columns.
School example: Pivot Subject to get Score and Attendance.
Home example: Pivot Day to get Expenses and Calories.
Nigerian example: Pivot Year to get Population and GDP.
How to do it: In Pivot dialog, select "Advanced" and add multiple value columns.
Illustration:
Pivot Month, Values: Sales, Quantity Result: Jan_Sales, Jan_Qty, Feb_Sales, Feb_Qty, ...
Mini summary: Pivot can handle multiple value columns.
Definition: Unpivot makes data longer (more rows). Pivot makes data wider (more columns).
Why important: Choosing the right one depends on your goal.
Simple explanation: Unpivot when you need data for charts. Pivot when you need summary tables.
Real-life example: Unpivot for a line chart. Pivot for a summary report.
School example: Unpivot to compare scores. Pivot to show scores per subject.
Home example: Unpivot to track daily habits. Pivot to see weekly totals.
Nigerian example: Unpivot to analyze monthly trends. Pivot to show state-wise data.
Illustration:
Unpivot: Wide → Long (for charts) Pivot: Long → Wide (for summary)
Mini summary: Unpivot for analysis, Pivot for presentation.
Definition: After pivoting, you may want to rename the new columns.
Why important: To make column names clear and meaningful.
Simple explanation: Change "Jan" to "January Sales".
Real-life example: Rename "Jan" to "Jan-2025".
School example: Rename "Math" to "Math Score".
Home example: Rename "Monday" to "Monday Chores".
Nigerian example: Rename "Lagos" to "Lagos Population".
How to do it: Double-click the column header and type the new name.
Illustration:
Before: "Jan" After: "January Sales"
Mini summary: Rename pivoted columns for clarity.
Definition: You can keep multiple columns as identifiers when unpivoting.
Why important: Sometimes you need to keep more than one key column.
Simple explanation: Keep Product and Region, unpivot months.
Real-life example: Keep Product and Region, unpivot monthly sales.
School example: Keep Student and Class, unpivot subject scores.
Home example: Keep Family Member and Day, unpivot meals.
Nigerian example: Keep State and LGA, unpivot monthly rainfall.
How to do it: Select the columns to unpivot, the others stay as identifiers.
Illustration:
Identifiers: Product, Region Unpivot: Jan, Feb, Mar Result: Product, Region, Month, Sales
Mini summary: You can have multiple identifier columns when unpivoting.
Definition: Pivot has advanced options like aggregating values (e.g., Sum, Average).
Why important: If there are duplicate values, you need to aggregate.
Simple explanation: If you have two sales for the same month, sum them.
Real-life example: Sum sales for each month-product combination.
School example: Average scores for each subject-student combination.
Home example: Sum expenses for each day-category combination.
Nigerian example: Sum population for each state-LGA combination.
How to do it: In Pivot dialog, choose "Advanced" and select an aggregation.
Illustration:
Pivot Month, Values: Sales, Aggregation: Sum
Mini summary: Use advanced pivot options for aggregating.
Definition: Sometimes unpivot fails if columns have different data types.
Why important: To avoid errors, ensure all columns have the same data type.
Simple explanation: Make sure all month columns are numbers or all are text.
Real-life example: Jan is number, Feb is text – error. Fix by changing types.
School example: Math is number, English is text – error. Fix.
Home example: Monday is number, Tuesday is text – error. Fix.
Nigerian example: Lagos is number, Abuja is text – error. Fix.
How to fix: Change all columns to the same data type before unpivoting.
Illustration:
Before: Jan (number), Feb (text) → Error Fix: Change both to number → Unpivot works.
Mini summary: Ensure columns have the same data type before unpivoting.
Definition: You can pivot and then unpivot, or vice versa, to reshape data.
Why important: Sometimes you need multiple transformations.
Simple explanation: You might unpivot, clean data, then pivot back.
Real-life example: Unpivot to fix data, then pivot to create summary.
School example: Unpivot to analyze, then pivot to present.
Home example: Unpivot to track, then pivot to report.
Nigerian example: Unpivot for trends, then pivot for comparison.
Illustration:
Wide → Unpivot → Clean → Pivot → Wide (clean version)
Mini summary: Combine pivot and unpivot for powerful reshaping.
Definition: Tips to use pivot and unpivot effectively.
Why important: To avoid errors and get the desired shape.
Simple explanation: Like following a recipe for perfect cookies.
Illustration:
1. Check data types 2. Select identifiers 3. Choose pivot/unpivot 4. Rename columns 5. Verify result
Mini summary: Follow best practices for smooth reshaping.
How to Unpivot Data:
How to Pivot Data:
Did you know that the term "pivot" comes from sports – pivoting means turning around a fixed point. In data, you turn rows into columns!
Wide Data
+--------+-----+-----+-----+
| Product| Jan | Feb | Mar |
+--------+-----+-----+-----+
| Choco | 100 | 150 | 200 |
| Lolly | 50 | 60 | 70 |
+--------+-----+-----+-----+
|
V
Unpivot
|
V
Long Data
+--------+-------+-------+
| Product| Month | Sales |
+--------+-------+-------+
| Choco | Jan | 100 |
| Choco | Feb | 150 |
| Choco | Mar | 200 |
| Lolly | Jan | 50 |
| Lolly | Feb | 60 |
| Lolly | Mar | 70 |
+--------+-------+-------+
Long Data
+--------+-------+-------+
| Product| Month | Sales |
+--------+-------+-------+
| Choco | Jan | 100 |
| Choco | Feb | 150 |
| Choco | Mar | 200 |
| Lolly | Jan | 50 |
| Lolly | Feb | 60 |
| Lolly | Mar | 70 |
+--------+-------+-------+
|
V
Pivot
|
V
Wide Data
+--------+-----+-----+-----+
| Product| Jan | Feb | Mar |
+--------+-----+-----+-----+
| Choco | 100 | 150 | 200 |
| Lolly | 50 | 60 | 70 |
+--------+-----+-----+-----+
| Feature | Wide | Long |
|---|---|---|
| Number of columns | Many | Few |
| Number of rows | Few | Many |
| Best for | Reports, human reading | Charts, analysis |
| Operation | Direction | Result |
|---|---|---|
| Unpivot | Columns → Rows | Longer data |
| Pivot | Rows → Columns | Wider data |
Congratulations! You have completed Module Ten on Pivot and Unpivot. You learned:
Pivot and unpivot are powerful tools to reshape data. Now you can transform any data into the perfect shape for your analysis or report!
Match the term to its description:
| Term | Description |
|---|---|
| 1. Wide Data | A. Many rows |
| 2. Long Data | B. Many columns |
| 3. Unpivot | C. Rows to columns |
| 4. Pivot | D. Columns to rows |
Answers: 1-B, 2-A, 3-D, 4-C
Scenario 1: You have a table with months as columns (Jan, Feb, Mar) and products as rows. You want to create a line chart showing sales by month. How would you reshape the data? Write the steps.
Scenario 2: You have a table with a "Month" column and a "Sales" column. You want to present it as a summary table with months as columns. How would you do it? Write the steps.
In groups, get a wide dataset (e.g., sales by month). Unpivot it to long format, then pivot it back to wide. Discuss why you might need to do each operation.
Create a small wide table (e.g., with months as columns). Use Power Query to unpivot it. Then rename the columns to "Month" and "Sales".
Project: Reshape School Data
You have a wide table of student scores: Student, Math, English, Science, Art. Unpivot the subject columns to create a long table. Then pivot it back to wide, but with subjects as rows and students as columns. Compare the two formats.
Download a sample dataset with months as columns (e.g., sales data). Use Power Query to unpivot it. Then create a pivot table in Excel based on the unpivoted data. Submit both the unpivoted table and the pivot table.
You have a dataset with columns: Product, Year, Quarter, Sales. Pivot the Quarter column to get columns for Q1, Q2, Q3, Q4. Then unpivot the year columns to get a Year column. This is a double transformation – pivot and then unpivot.
Fill-in-the-Blank: 1. Wide, 2. Long, 3. Unpivot, 4. Pivot, 5. Attribute
True/False: 1-F, 2-T, 3-T, 4-F, 5-T
Matching: 1-B, 2-A, 3-D, 4-C
In Module Eleven, we will learn about Advanced Data Transformation – combining multiple techniques to solve complex problems. We will use all the skills from previous modules to clean, combine, group, and reshape data like true experts. Get ready to become a Power Query Master!
Before the next class, practice unpivoting and pivoting with different datasets. Try to create both a chart and a report from the same data.
© 2025 Certified Power Query for Data Analysis Expert – Module Ten
Hello, young data explorer! Welcome to Module Eleven. In this module, we will learn how to combine everything we have learned so far to perform advanced data transformations. Think of this as the "master chef" level. We will use cleaning, merging, grouping, and reshaping all together to solve complex problems. By the end of this module, you will be a true Power Query expert, able to turn messy data into valuable insights. Let's dive in!
Chidi loves cooking. He wants to cook a big feast for his family. He has many ingredients (raw data). He needs to wash (clean), chop (split), mix (merge), and cook (group) them. He also needs to add special spices (conditional logic) and use a secret recipe (custom formulas). This is exactly what advanced data transformation is about – combining many steps to create a perfect dish (final dataset).
Definition: A transformation pipeline is a series of steps applied to data from start to finish.
Why important: Real-world data requires many steps. A pipeline organizes them.
Simple explanation: It's like a recipe: first chop, then cook, then season.
Real-life example: A company gets sales data, cleans it, merges with product data, groups by region, and creates a report.
School example: A teacher takes raw grades, cleans them, calculates averages, and creates a report card.
Home example: You take a messy grocery list, clean it, group by category, and create a shopping plan.
Nigerian example: A bank takes transaction data, cleans it, merges with customer data, and detects fraud.
Illustration:
Transformation Pipeline
Start
|
V
Load Data
|
V
Clean Data (remove duplicates, trim)
|
V
Merge with other tables
|
V
Group and Summarize
|
V
Pivot/Unpivot (reshape)
|
V
Add Conditional Columns
|
V
Final Dataset
Mini summary: A pipeline is a sequence of transformation steps.
Definition: A conditional column creates a new column based on conditions (if this, then that).
Why important: Helps categorize or flag data based on rules.
Simple explanation: If you score above 70, you pass; else, you fail.
Real-life example: If sales > 1000, mark as "High"; else "Low".
School example: If grade > 80, "Excellent"; else "Good".
Home example: If expense > 500, "Major"; else "Minor".
Nigerian example: If rainfall > 100mm, "Heavy"; else "Light".
How to do it: Add Column → Conditional Column → Define rules.
Illustration:
If Score >= 70 then "Pass" else "Fail" +-------+--------+ | Score | Result | +-------+--------+ | 85 | Pass | | 60 | Fail | +-------+--------+
Mini summary: Conditional columns add logic to your data.
Definition: M language is the language behind Power Query. You can write custom formulas.
Why important: When built-in features aren't enough, you can write custom code.
Simple explanation: It's like writing a recipe yourself instead of using a box mix.
Real-life example: Calculate profit = (Price - Cost) * Quantity.
School example: Calculate final grade = (Exam * 0.6) + (Assignment * 0.4).
Home example: Calculate total cost = Price * Quantity + Tax.
Nigerian example: Calculate net salary = Gross - Tax - Pension.
How to do it: Add Column → Custom Column → Write M formula.
Illustration:
= [Price] * [Quantity] - [Discount]
Mini summary: Custom columns allow unlimited transformations.
Definition: try...otherwise handles errors when a formula fails.
Why important: Prevents your query from breaking.
Simple explanation: If something goes wrong, use a backup value.
Real-life example: try [Price] * [Quantity] otherwise 0.
School example: try [Score] / [Total] otherwise 0.
Home example: try [Amount] - [Discount] otherwise 0.
Nigerian example: try [Salary] / [Months] otherwise 0.
How to do it: In custom column, use try...otherwise.
Illustration:
= try [Price] * [Quantity] otherwise 0
Mini summary: try...otherwise prevents errors.
Definition: A parameter is a variable that you can change to alter query results.
Why important: Makes your query reusable for different inputs.
Simple explanation: It's like a knob you can turn to adjust the output.
Real-life example: A parameter for "Year" to filter data for different years.
School example: A parameter for "Class" to get data for a specific class.
Home example: A parameter for "Month" to see expenses for that month.
Nigerian example: A parameter for "State" to filter data by state.
How to do it: Home → Manage Parameters → Create parameter.
Illustration:
Parameter: Year = 2025 Query filters data for Year = [Year]
Mini summary: Parameters make queries flexible.
Definition: A function is a piece of code that you can reuse multiple times.
Why important: Saves time and reduces errors.
Simple explanation: It's like a stamp – you can stamp the same design many times.
Real-life example: A function to clean text (trim, lowercase) that you apply to many columns.
School example: A function to calculate grade (A, B, C) for any score.
Home example: A function to convert currency.
Nigerian example: A function to format NIN numbers.
How to do it: Create a new blank query, write M code, and turn it into a function.
Illustration:
(Score) => if Score >= 70 then "Pass" else "Fail"
Mini summary: Functions are reusable code blocks.
Definition: Performance means how fast your query runs.
Why important: Slow queries waste time. Fast queries are efficient.
Simple explanation: It's like a race car – you want it to go fast.
Real-life example: A company with millions of rows needs fast queries.
School example: A teacher with many students wants quick reports.
Home example: You want your budget to update quickly.
Nigerian example: A bank with many transactions needs fast analysis.
Tips: Remove unnecessary columns early, filter data early, and avoid heavy operations.
Illustration:
Before: Load all columns → Filter → Group (slow) After: Filter → Remove columns → Group (fast)
Mini summary: Optimize queries for speed.
Definition: You can reference one query from another to build complex pipelines.
Why important: Keeps your work organized and modular.
Simple explanation: It's like building with Lego – you combine small blocks to make a big structure.
Real-life example: Query A cleans sales data, Query B merges with product data.
School example: Query A cleans grades, Query B calculates averages.
Home example: Query A cleans expenses, Query B groups by category.
Nigerian example: Query A cleans election data, Query B combines with voter data.
How to do it: Right-click a query → Reference to create a new query based on it.
Illustration:
Query1: Raw Data Query2: Clean Data (references Query1) Query3: Final Report (references Query2)
Mini summary: Reference queries to build modular pipelines.
Definition: Custom aggregations go beyond Sum, Average, etc. You can write your own.
Why important: Sometimes you need special calculations.
Simple explanation: Instead of just sum, you can calculate weighted average.
Real-life example: Calculate weighted average price per product.
School example: Calculate GPA (Grade Point Average).
Home example: Calculate average cost per item after discount.
Nigerian example: Calculate voter turnout percentage per state.
How to do it: In Group By, choose "All Rows" as aggregation, then write custom code.
Illustration:
= Table.AddColumn(Grouped, "Weighted Avg", each List.Average([Sales]))
Mini summary: Custom aggregations give you flexibility.
Definition: You can use Inner, Left, Right, and Full Outer joins in the same pipeline.
Why important: Different joins are useful for different scenarios.
Simple explanation: You choose the right join for the job.
Real-life example: Inner join for customers who have orders, Left join for all customers.
School example: Inner join for students with grades, Left join for all students.
Home example: Inner join for family members with chores, Left join for all members.
Nigerian example: Inner join for voters who voted, Left join for all voters.
How to do it: In Merge dialog, choose the join type.
Illustration:
Merge Type: Left Outer (keeps all from left)
Mini summary: Choose join types strategically.
Definition: Extract parts of dates (year, month, day) or calculate differences.
Why important: Date analysis is common in many fields.
Simple explanation: You can find the difference between two dates in days.
Real-life example: Calculate days since last purchase.
School example: Calculate student age from date of birth.
Home example: Calculate days until a bill is due.
Nigerian example: Calculate days since COVID-19 vaccination.
How to do it: Add Column → Date → choose an option.
Illustration:
= Date.Year([Date]) = Date.Month([Date]) = Date.Day([Date]) = Duration.Days([End] - [Start])
Mini summary: Work with dates easily.
Definition: Extract parts of text, replace, or combine text.
Why important: Text data is often messy and needs cleaning.
Simple explanation: You can take first name from full name.
Real-life example: Extract first name from "Chidi Okonkwo".
School example: Extract initials from student names.
Home example: Combine first and last name.
Nigerian example: Extract LGA from address string.
How to do it: Add Column → Text → choose an option.
Illustration:
= Text.Middle([Name], 0, 5) // first 5 characters = Text.Replace([Name], " ", "_") = Text.Upper([Name])
Mini summary: Clean text data effectively.
Definition: Keep or remove a specific range of rows (e.g., rows 10 to 20).
Why important: Sometimes you need only a subset of rows.
Simple explanation: It's like taking a slice of a cake.
Real-life example: Keep top 10 best-selling products.
School example: Keep students who scored above 80.
Home example: Keep expenses above 1000.
Nigerian example: Keep states with population above 1 million.
How to do it: Home → Keep Rows → Keep Range / Remove Range.
Illustration:
Keep Range: Start 5, End 10
Mini summary: Keep or remove specific rows.
Definition: Fill Down copies values from above, but you can combine it with conditions.
Why important: Helps fill missing data intelligently.
Simple explanation: If a cell is blank, fill it with the value above, but only if some condition is met.
Real-life example: Fill missing region names based on state.
School example: Fill missing teacher names based on class.
Home example: Fill missing day names based on date.
Nigerian example: Fill missing LGA names based on state.
How to do it: Transform → Fill → Down, then apply a conditional step.
Illustration:
Fill Down, then conditional replace if value is "Unknown".
Mini summary: Combine Fill Down with conditions.
Definition: Add comments and descriptions to your steps.
Why important: Helps others understand your work.
Simple explanation: It's like leaving notes for yourself and others.
Real-life example: Add a comment explaining why you removed certain rows.
School example: Add a comment explaining a calculation.
Home example: Add a comment explaining your budget rules.
Nigerian example: Add a comment explaining data sources.
How to do it: In the "Applied Steps" pane, right-click a step and choose "Properties".
Illustration:
Step: Removed Duplicates (Comment: "Removed duplicate entries based on Order ID")
Mini summary: Document your query for clarity.
How to Add a Conditional Column:
How to Create a Parameter:
Did you know that you can create a "parameter table" to store multiple parameters in a single place? This makes your queries even more powerful!
Start
|
V
Load Sales Data
|
V
Clean Data (remove duplicates, trim)
|
V
Merge with Product Data
|
V
Group by Category (Sum Sales)
|
V
Add Conditional Column (High/Low Sales)
|
V
Pivot to Wide Format
|
V
Filter High Sales Only
|
V
Final Report
+--------+----------+----------------+ | Score | Outcome | (Conditional) | +--------+----------+----------------+ | 85 | Pass | if Score >= 70 | | 60 | Fail | else | | 75 | Pass | | +--------+----------+----------------+
| Feature | Conditional Column | Custom Column |
|---|---|---|
| Ease | Easy (point-and-click) | Requires writing M code |
| Flexibility | Limited to if-then-else | Full M language |
| Use case | Simple categorization | Complex calculations |
| Feature | Parameter | Function |
|---|---|---|
| What it does | Holds a single value | Holds a reusable piece of code |
| Use when | You need to filter or change a value | You need to repeat a transformation |
| Example | Year = 2025 | CleanText(text) = trim(lower(text)) |
Congratulations! You have completed Module Eleven on Advanced Data Transformation. You learned:
You are now a true Power Query expert! You can take any messy data and turn it into a clean, useful dataset for analysis.
Match the term to its description:
| Term | Description |
|---|---|
| 1. Pipeline | A. Reusable code |
| 2. Conditional Column | B. Variable that can be changed |
| 3. Parameter | C. If-then-else logic |
| 4. Function | D. Series of steps |
Answers: 1-D, 2-C, 3-B, 4-A
Scenario 1: You have a dataset of sales transactions. You need to clean it, merge with product data, group by product category, and add a column that flags products with sales above 1000 as "High" and below as "Low". Write the steps.
Scenario 2: You have a dataset that is updated monthly. You want to create a parameter for the month so you can easily refresh data for different months. Write the steps to create and use the parameter.
In groups, build a complete transformation pipeline for a sample dataset. Include cleaning, merging, grouping, and adding a conditional column. Document each step and explain your choices.
Take a messy dataset (e.g., sales data). Build a complete pipeline with at least 5 steps. Include at least one conditional column and one custom column. Document your work.
Project: Complete Sales Analysis Pipeline
You have sales data, product data, and customer data. Build a complete pipeline that:
Download a large dataset (e.g., from Kaggle). Build a complete transformation pipeline with at least 10 steps. Include cleaning, merging, grouping, conditional logic, and custom columns. Document every step.
Create a function that takes a table and a column name, and returns the table with that column cleaned (trimmed, lowercased). Use this function in a pipeline to clean multiple columns.
Fill-in-the-Blank: 1. pipeline, 2. conditional, 3. M, 4. parameter, 5. function
True/False: 1-F, 2-T, 3-F, 4-T, 5-F
Matching: 1-D, 2-C, 3-B, 4-A
In Module Twelve, we will learn about Data Integration and Automation – how to connect to live data sources and automate your queries. You will learn how to schedule refreshes and build dataflows. Get ready to become an automation expert!
Before the next class, practice building a complete pipeline with at least 10 steps. Try to use parameters and functions.
© 2025 Certified Power Query for Data Analysis Expert – Module Eleven
Hello, young data explorer! Welcome to Module Twelve. In this module, we will learn how to connect to live data and automate our data updates. Imagine you have a magic machine that fetches new data every day and updates your reports automatically. That's what automation does! You don't have to repeat the same steps again and again. Instead, Power Query does the work for you. By the end of this module, you will be able to build automated data pipelines that save time and effort.
Chidi has a magic mailbox. Every morning, a new letter arrives with sales data. He doesn't have to go to the store to pick it up – it comes automatically. He then reads the letter and updates his sales report. This is exactly what data integration and automation do. Power Query connects to data sources (like the mailbox) and refreshes your reports automatically. You set it up once, and it works forever!
Definition: Data integration is the process of combining data from different sources into a single, unified view.
Why important: Data is often scattered across multiple places. Integration brings it together.
Simple explanation: It's like collecting all your toys from different rooms into one toy box.
Real-life example: A company integrates sales data from its website, stores, and partners.
School example: A teacher integrates grades from different subjects into one report card.
Home example: You integrate your expenses from different apps into one budget.
Nigerian example: A bank integrates customer data from branches across the country.
Illustration:
Data Sources → Integration → Unified Dataset Excel → ↓ CSV → [Power Query] → Final Table Web → ↑ DB → ↓
Mini summary: Integration combines data from multiple sources.
Definition: You can connect to many data sources: Excel, CSV, Text files, databases, web, and more.
Why important: Power Query works with many different types of data.
Simple explanation: It's like having a universal charger for all your devices.
Real-life example: Connect to a SQL database for sales data.
School example: Connect to a CSV file of student grades.
Home example: Connect to an Excel file of your budget.
Nigerian example: Connect to a web service for exchange rates.
How to do it: Get Data → Choose the source type.
Illustration:
Get Data → From File → From Excel Get Data → From Database → From SQL Server Get Data → From Web → From Web Page
Mini summary: Power Query can connect to many data sources.
Definition: Some data sources require a username and password (credentials) to access.
Why important: You need to provide the right credentials to connect.
Simple explanation: It's like a password to your email.
Real-life example: Connect to a company database with your username and password.
School example: Connect to a school portal with your login.
Home example: Connect to an online bank account.
Nigerian example: Connect to a government database with a username.
How to do it: In the connection dialog, enter your credentials.
Illustration:
Server: myserver Database: sales Username: user1 Password: ********
Mini summary: Provide credentials to access secure data sources.
Definition: Refreshing data means running your query again to get the latest data.
Why important: Data changes over time. You need the latest information.
Simple explanation: It's like checking your mailbox again for new letters.
Real-life example: Refresh sales data every day to see new orders.
School example: Refresh grades after a new test.
Home example: Refresh your budget after a new expense.
Nigerian example: Refresh election data after new results come in.
How to do it: Right-click the query → Refresh.
Illustration:
Query Name: Sales Right-click → Refresh (Data updates with latest information)
Mini summary: Refresh gets the latest data.
Definition: Scheduling means setting a specific time for the query to refresh automatically.
Why important: You don't have to remember to refresh. It happens automatically.
Simple explanation: It's like setting an alarm clock to wake you up.
Real-life example: Schedule a refresh every morning at 6 AM.
School example: Schedule a refresh after school hours.
Home example: Schedule a refresh every Sunday evening.
Nigerian example: Schedule a refresh at the end of each month.
How to do it: In Power BI, go to Settings → Scheduled Refresh.
Illustration:
Scheduled Refresh: Daily at 6:00 AM Daily at 6:00 PM
Mini summary: Schedule refreshes to automate updates.
Definition: ETL is a process that Extract data, Transform it, and Load it into a final destination.
Why important: ETL is the foundation of data integration.
Simple explanation: It's like cooking: get ingredients (Extract), prepare them (Transform), and serve (Load).
Real-life example: Extract sales data, transform it by cleaning, and load it into a report.
School example: Extract grades, transform by calculating averages, and load into a report card.
Home example: Extract expenses, transform by categorizing, and load into a budget.
Nigerian example: Extract voter data, transform by cleaning, and load into an analysis.
Illustration:
Extract → Transform → Load ↓ ↓ ↓ Raw Cleaned Final Data Data Table
Mini summary: ETL is Extract, Transform, Load.
Definition: Dataflows are a way to share and reuse Power Query transformations in the cloud.
Why important: Dataflows make your work reusable and scalable.
Simple explanation: It's like a shared recipe that everyone can use.
Real-life example: A company creates a dataflow to clean customer data, and all teams can use it.
School example: A teacher creates a dataflow to calculate grades, and other teachers can use it.
Home example: You create a dataflow for your budget, and you can use it on different devices.
Nigerian example: A government agency creates a dataflow for population data.
How to do it: In Power BI, create a Dataflow (Preview).
Illustration:
Dataflow: CleanCustomerData Used by: Sales Report, Marketing Report, Finance Report
Mini summary: Dataflows are reusable transformations.
Definition: Data sources can change (e.g., file name changes, columns added). You need to handle these changes.
Why important: If the source changes, your query might break.
Simple explanation: It's like your favorite store moving to a new location – you need to update your directions.
Real-life example: A CSV file is renamed from "Sales_2024.csv" to "Sales_2025.csv".
School example: A grade sheet adds a new subject column.
Home example: You start a new expense category.
Nigerian example: A database adds a new field.
How to fix: Update the data source settings or use parameters.
Illustration:
Old Source: Sales_2024.csv New Source: Sales_2025.csv Update in Power Query: Change file path.
Mini summary: Update queries when data sources change.
Definition: Parameters can be used to change the data source dynamically (e.g., choose a file by year).
Why important: Makes your query flexible for different inputs.
Simple explanation: It's like a dial you can turn to choose what data to load.
Real-life example: A parameter for "Year" to load sales for that year.
School example: A parameter for "Class" to load grades for that class.
Home example: A parameter for "Month" to load expenses for that month.
Nigerian example: A parameter for "State" to load data for that state.
How to do it: Create a parameter, then use it in the file path or query.
Illustration:
Parameter: Year = 2025 File Path: C:\Data\Sales_[Year].csv
Mini summary: Parameters make data sources dynamic.
Definition: You can share your queries by saving them as .pq files or publishing to Power BI.
Why important: Collaboration makes work faster and better.
Simple explanation: It's like sharing your recipe with a friend so they can cook the same dish.
Real-life example: A team shares a query for cleaning sales data.
School example: Teachers share a query for grading.
Home example: Family members share a budget query.
Nigerian example: Analysts share a query for election data.
How to do it: Export query as .pq file or publish to Power BI.
Illustration:
Export Query → Save as .pq → Share via email
Mini summary: Share queries for collaboration.
Definition: Power Automate can trigger Power Query refreshes based on events (e.g., when a new file is added).
Why important: Full automation without manual intervention.
Simple explanation: It's like a robot that does the work for you.
Real-life example: When a new sales file is uploaded to a folder, Power Automate triggers a refresh.
School example: When a teacher uploads grades, the report updates automatically.
Home example: When you add a new expense, your budget updates.
Nigerian example: When new election results are published, the dashboard updates.
How to do it: Use Power Automate to trigger Power BI dataset refresh.
Illustration:
New File → Power Automate → Refresh Query → Updated Report
Mini summary: Power Automate triggers automated refreshes.
Definition: Automated pipelines need error handling to work smoothly.
Why important: If an error occurs, you want to be notified, not have the pipeline break.
Simple explanation: It's like having a backup plan if something goes wrong.
Real-life example: If the data source is unavailable, send an alert.
School example: If a file is missing, send a notification.
Home example: If a connection fails, send a text message.
Nigerian example: If a database is down, send an email.
How to do it: Use try...otherwise and alerts.
Illustration:
try Refresh Query otherwise Send Alert: "Refresh failed"
Mini summary: Handle errors to keep pipelines running.
Definition: Large queries can take a long time to refresh. Optimize to make them faster.
Why important: Fast refreshes save time.
Simple explanation: It's like making your car faster with better parts.
Real-life example: A company has millions of rows. They optimize to refresh in minutes.
School example: A teacher has many students. They optimize to get reports quickly.
Home example: You have many expenses. You optimize to get your budget fast.
Nigerian example: A bank has many transactions. They optimize for speed.
Tips: Reduce data early, remove unnecessary columns, use incremental refresh.
Illustration:
Before: 10 minutes After: 2 minutes
Mini summary: Optimize refreshes for speed.
Definition: Incremental refresh loads only new or changed data instead of reloading everything.
Why important: Saves time and resources for large datasets.
Simple explanation: Instead of reading the whole book again, you only read the new pages.
Real-life example: A company with 5 years of sales data only loads the last 3 months.
School example: A teacher only loads the latest test scores.
Home example: You only load this month's expenses.
Nigerian example: A bank only loads transactions from the last 7 days.
How to do it: In Power BI, set up incremental refresh policies.
Illustration:
Incremental Refresh: Load last 3 months Refresh daily
Mini summary: Incremental refresh loads only new data.
Definition: An automated data pipeline is a complete system that extracts, transforms, and loads data automatically.
Why important: A pipeline runs without manual intervention, saving time and ensuring consistency.
Simple explanation: It's like an assembly line in a factory.
Real-life example: Daily sales pipeline: extract from database, transform, load to dashboard.
School example: Weekly grade pipeline: extract from grade book, transform, load to report card.
Home example: Monthly budget pipeline: extract from bank, transform, load to budget tracker.
Nigerian example: Election results pipeline: extract from polling units, transform, load to national dashboard.
Steps: Plan, build, test, deploy, monitor.
Illustration:
Plan → Build → Test → Deploy → Monitor
Mini summary: A pipeline automates the entire ETL process.
How to Set Up Scheduled Refresh:
How to Create a Parameter for Dynamic File Path:
Did you know that you can use Power Query to connect to data from Facebook, Google Analytics, and many other web services? It's like a super connector!
Extract Transform Load
↓ ↓ ↓
Raw Data → Cleaned Data → Final Report
| | |
Sales DB Remove Errors Dashboard
Excel Merge Tables Power BI
Web Calculate Excel
Data Sources (Excel, CSV, DB)
|
V
Power Query (Transform)
|
V
Load to Power BI
|
V
Scheduled Refresh (Daily)
|
V
Updated Dashboard
| Feature | Manual | Automated |
|---|---|---|
| Time | High | Low |
| Error rate | High | Low |
| Consistency | Varies | High |
| Scalability | Low | High |
| Feature | Full Refresh | Incremental Refresh |
|---|---|---|
| Data loaded | All data | Only new data |
| Time | Long | Short |
| Resource usage | High | Low |
| Best for | Small datasets | Large datasets |
Congratulations! You have completed Module Twelve on Data Integration and Automation. You learned:
You can now build systems that automatically bring in fresh data, transform it, and deliver insights without manual effort. This is a superpower in the world of data analysis!
Match the term to its description:
| Term | Description |
|---|---|
| 1. ETL | A. Reusable transformation |
| 2. Dataflow | B. Extract, Transform, Load |
| 3. Parameter | C. Loading only new data |
| 4. Incremental Refresh | D. Variable that can be changed |
Answers: 1-B, 2-A, 3-D, 4-C
Scenario 1: You have a daily sales report that needs to be updated every morning at 8 AM. You have a sales database that updates overnight. How would you set this up in Power Query and Power BI?
Scenario 2: Your data source (a CSV file) changes name every month (e.g., Sales_Jan.csv, Sales_Feb.csv). How would you use a parameter to handle this?
In groups, design an automated data pipeline for a scenario of your choice (e.g., school grades, store sales). Include data sources, transformation steps, refresh schedule, and handling of changes.
Set up a simple automated pipeline with a CSV file that updates daily. Use Power Query to clean and transform the data, and schedule refresh in Power BI. Document your setup.
Project: Automated Sales Dashboard
Build a complete automated sales dashboard. Use at least two data sources (e.g., Excel and CSV). Clean and merge the data. Set up scheduled refresh. Add a parameter for year. Present the dashboard to the class.
Download a dataset that updates daily (e.g., COVID-19 data). Build a pipeline that cleans the data, transforms it, and loads it into Power BI. Set up scheduled refresh. Write a report on your pipeline.
Create a parameterized dataflow that can connect to different databases based on a parameter (e.g., database name). Use this dataflow in a pipeline. Demonstrate how changing the parameter updates the entire pipeline.
Fill-in-the-Blank: 1. Integration, 2. Transform, 3. Refresh, 4. dataflow, 5. Incremental
True/False: 1-F, 2-F, 3-T, 4-F, 5-F
Matching: 1-B, 2-A, 3-D, 4-C
In Module Thirteen, we will learn about Data Modeling and Relationships – how to build a data model with relationships between tables. This is the foundation of creating amazing reports and dashboards. Get ready to become a Data Modeler!
Before the next class, practice setting up a scheduled refresh for a simple dataset. Try using a parameter for the file path.
© 2025 Certified Power Query for Data Analysis Expert – Module Twelve
Hello, young data explorer! Welcome to Module Thirteen. In this module, we will learn how to build a data model and create relationships between tables. Think of this as building the skeleton of your report. Just like your skeleton holds your body together, a data model holds your data together. By the end of this module, you will be able to connect different tables, set up relationships, and build a solid foundation for amazing reports and dashboards.
Chidi has three tables: one with customer names, one with orders, and one with products. He wants to see which customer bought which product. He needs to connect these tables. He uses the customer ID to link customers to orders, and the product ID to link orders to products. Now he can see the full picture – who bought what! That's exactly what data modeling does – it connects tables to tell a complete story.
Definition: A data model is a framework that organizes and defines how different tables are connected to each other.
Why important: A good data model makes your reports accurate, fast, and easy to understand.
Simple explanation: It's like a map that shows how different parts of your data fit together.
Real-life example: A company has customer, order, and product tables. The data model connects them.
School example: A teacher has student, grade, and subject tables. The data model connects them.
Home example: You have family member, chore, and day tables. The data model connects them.
Nigerian example: A bank has customer, account, and transaction tables. The data model connects them.
Illustration:
Data Model Diagram:
+----------+ +--------+ +----------+
| Customers| | Orders | | Products |
+----------+ +--------+ +----------+
| CustID |──────▶| CustID | | ProdID |
| Name | | OrderID|──────▶| ProdID |
| City | | ProdID | | Name |
+----------+ | Amount | | Price |
+--------+ +----------+
Mini summary: A data model connects tables.
Definition: A relationship is a connection between two tables based on a common column.
Why important: Relationships allow you to combine data from different tables to answer questions.
Simple explanation: It's like a bridge that connects two islands.
Real-life example: Customers table and Orders table are connected by Customer ID.
School example: Students table and Grades table are connected by Student ID.
Home example: Family Members table and Chores table are connected by Member ID.
Nigerian example: Citizens table and Voting table are connected by Voter ID.
Illustration:
+----------+ +----------+ | Customers| | Orders | +----------+ +----------+ | CustID |───(Relationship)───▶| CustID | | Name | | OrderID | +----------+ +----------+
Mini summary: Relationships connect tables.
Definition: A primary key is a column that uniquely identifies each row in a table. A foreign key is a column that links to a primary key in another table.
Why important: Keys are the building blocks of relationships.
Simple explanation: A primary key is like a student's ID number – unique to them. A foreign key is like the same ID number used in another list to refer to that student.
Real-life example: Customer ID in Customers table is a primary key. Customer ID in Orders table is a foreign key.
School example: Student ID in Students table is a primary key. Student ID in Grades table is a foreign key.
Home example: Member ID in Family table is a primary key. Member ID in Chores table is a foreign key.
Nigerian example: NIN in Citizens table is a primary key. NIN in Voting table is a foreign key.
Illustration:
+----------+ +----------+ | Customers| | Orders | +----------+ +----------+ | CustID (PK)│◀────────| CustID (FK)│ | Name | | OrderID | +----------+ +----------+
Mini summary: Primary key uniquely identifies rows. Foreign key links to another table.
Definition: A one-to-one relationship means that one row in Table A is linked to exactly one row in Table B.
Why important: Used when you want to split a table into two for security or organization.
Simple explanation: Each person has exactly one birth certificate.
Real-life example: Employee table and Employee Details table – each employee has one detail record.
School example: Student table and Student Medical Record – each student has one medical record.
Home example: Family Member table and Passport table – each member has one passport.
Nigerian example: Citizen table and NIN table – each citizen has one NIN.
Illustration:
+----------+ +----------+ | Employee | | Details | +----------+ +----------+ | EmpID (PK)│──────────│ EmpID (FK)│ | Name | | Address | +----------+ +----------+ Each employee has exactly one detail record.
Mini summary: One-to-one: each row matches exactly one row in the other table.
Definition: A one-to-many relationship means that one row in Table A can link to many rows in Table B.
Why important: This is the most common type of relationship.
Simple explanation: One customer can have many orders.
Real-life example: One customer can have many orders.
School example: One teacher can teach many students.
Home example: One parent can have many children.
Nigerian example: One state can have many LGAs.
Illustration:
+----------+ +----------+ | Customers| | Orders | +----------+ +----------+ | CustID (PK)│──────────│ CustID (FK)│ | Name | | OrderID | +----------+ +----------+ One customer can have many orders.
Mini summary: One-to-many: one row links to many rows in the other table.
Definition: A many-to-many relationship means that many rows in Table A can link to many rows in Table B.
Why important: Need a junction table to connect them.
Simple explanation: Many students can take many subjects.
Real-life example: Students and Subjects – a student takes many subjects, and a subject is taken by many students.
School example: Students and Clubs – a student can join many clubs, a club can have many students.
Home example: Family Members and Hobbies – a member can have many hobbies, a hobby can be shared by many members.
Nigerian example: Citizens and Services – a citizen can use many services, a service can be used by many citizens.
How to handle: Create a junction table (e.g., Student_Subject) with the keys from both tables.
Illustration:
+----------+ +------------------+ +----------+ | Students | | Student_Subject | | Subjects | +----------+ +------------------+ +----------+ | StuID (PK)│──────▶| StuID (FK) |◀──────| SubID (PK)│ | Name | | SubID (FK) | | Name | +----------+ +------------------+ +----------+ Many students, many subjects.
Mini summary: Many-to-many: many rows link to many rows, requiring a junction table.
Definition: In Power BI, you can create relationships between tables visually.
Why important: Power BI uses these relationships to build reports.
Simple explanation: You drag and drop to connect tables.
Real-life example: You have a Customers table and an Orders table. You drag CustID from Customers to CustID in Orders.
School example: You have Students and Grades. You drag StudentID from Students to StudentID in Grades.
Home example: You have Members and Chores. You drag MemberID from Members to MemberID in Chores.
Nigerian example: You have States and LGAs. You drag StateID from States to StateID in LGAs.
How to do it: In Power BI, go to Model View, drag a field from one table to another.
Illustration:
Model View: Drag CustID from Customers to CustID in Orders. A line appears between the tables.
Mini summary: Drag fields to create relationships in Power BI.
Definition: Cardinality defines the type of relationship (one-to-one, one-to-many, many-to-many).
Why important: It tells Power BI how to use the relationship.
Simple explanation: It's like a traffic sign telling cars which way to go.
Real-life example: One customer to many orders = one-to-many.
School example: One teacher to many students = one-to-many.
Home example: One parent to many children = one-to-many.
Nigerian example: One state to many LGAs = one-to-many.
How to set: In the relationship dialog, choose the cardinality.
Illustration:
Cardinality Options: One-to-One: (1:1) One-to-Many: (1:*) Many-to-One: (*:1) Many-to-Many: (*:*)
Mini summary: Cardinality defines the relationship type.
Definition: Cross-filter direction tells Power BI how filters flow between tables.
Why important: It determines how filtering affects other tables.
Simple explanation: It's like a one-way or two-way street.
Real-life example: If you filter customers, the orders table should be filtered too.
School example: If you filter a subject, the grades for that subject appear.
Home example: If you filter a family member, their chores appear.
Nigerian example: If you filter a state, its LGAs appear.
How to set: In the relationship dialog, choose single or both directions.
Illustration:
Cross-Filter Direction: Single: Filter flows one way. Both: Filter flows both ways.
Mini summary: Cross-filter direction controls how filters propagate.
Definition: A star schema is a data model with one central fact table and many dimension tables.
Why important: Star schemas are fast and easy to understand.
Simple explanation: The fact table is like the sun in the center, and dimension tables are like planets orbiting around it.
Real-life example: Fact table: Sales. Dimension tables: Products, Customers, Time.
School example: Fact table: Grades. Dimension tables: Students, Subjects, Teachers.
Home example: Fact table: Expenses. Dimension tables: Categories, Members, Months.
Nigerian example: Fact table: Votes. Dimension tables: Candidates, States, Parties.
Illustration:
+----------+
| Products |
+----------+
|
+----------+----------+----------+
| Customers|──│ Sales │──│ Time |
+----------+----------+----------+
|
+----------+
| Regions |
+----------+
Mini summary: Star schema: one fact table, many dimension tables.
Definition: Fact tables contain numerical data (sales, scores). Dimension tables contain descriptive data (names, categories).
Why important: This is the foundation of a star schema.
Simple explanation: Fact tables answer "how much?" Dimension tables answer "who? what? where?"
Real-life example: Fact: Sales Amount. Dimension: Product Name, Customer Name, Date.
School example: Fact: Score. Dimension: Student Name, Subject Name, Teacher Name.
Home example: Fact: Amount Spent. Dimension: Category, Member, Day.
Nigerian example: Fact: Vote Count. Dimension: Party, State, Candidate.
Illustration:
Fact Table: Sales (Amount, Qty) Dimension Tables: Product (Name), Customer (Name), Date (Year, Month)
Mini summary: Fact tables have numbers, dimension tables have descriptions.
Definition: A snowflake schema is a star schema where dimension tables are further normalized into sub-dimensions.
Why important: Used when dimensions are very large.
Simple explanation: It's like a star schema with extra branches.
Real-life example: Product dimension is split into Product, Brand, and Category.
School example: Student dimension is split into Student, Class, and Grade.
Home example: Expense dimension is split into Expense, Category, and Sub-category.
Nigerian example: State dimension is split into State, Region, and Zone.
Illustration:
+----------+
| Products |
+----------+
|
+----------+ +----------+
| Brands |──────────| Category |
+----------+ +----------+
Mini summary: Snowflake schema has normalized dimensions.
Definition: Inactive relationships are relationships that are not used by default.
Why important: Sometimes you need to switch relationships for different reports.
Simple explanation: It's like having a backup bridge you only use sometimes.
Real-life example: You have relationships between OrderDate and DeliveryDate. You can use one at a time.
School example: You have relationships between ExamDate and AssignmentDate.
Home example: You have relationships between PurchaseDate and UseDate.
Nigerian example: You have relationships between RegistrationDate and VotingDate.
How to use: Use DAX functions like USERELATIONSHIP to activate.
Illustration:
Active: Relationship on OrderDate Inactive: Relationship on DeliveryDate Use USERELATIONSHIP to switch.
Mini summary: Inactive relationships can be activated when needed.
Definition: Many-to-many relationships require a junction table.
Why important: They are common and need special handling.
Simple explanation: You need a bridge table to connect them.
Real-life example: Students and Subjects – junction table StudentSubject.
School example: Students and Clubs – junction table StudentClub.
Home example: Members and Hobbies – junction table MemberHobby.
Nigerian example: Citizens and Services – junction table CitizenService.
How to do it: Create a junction table with the primary keys of both tables.
Illustration:
Students (StuID) ─── StudentSubject (StuID, SubID) ─── Subjects (SubID)
Mini summary: Use a junction table for many-to-many relationships.
Definition: Tips for building a solid data model.
Why important: A good model leads to accurate and fast reports.
Simple explanation: It's like building a strong foundation for a house.
Illustration:
1. Identify Fact and Dimension tables. 2. Create relationships using keys. 3. Set cardinality and cross-filter direction. 4. Test with sample reports.
Mini summary: Follow best practices for a robust data model.
How to Create a Relationship in Power BI:
How to Build a Star Schema:
Did you know that a well-designed data model can make your reports 10 times faster than a poorly designed one? That's why data modeling is so important!
+----------+
| Products |
+----------+
|
+----------+----------+----------+
| Customers|──│ Sales │──│ Time |
+----------+----------+----------+
|
+----------+
| Stores |
+----------+
+----------+ +----------+ | Customers| | Orders | +----------+ +----------+ | CustID (PK)│──────────│ CustID (FK)│ | Name | | OrderID | +----------+ +----------+
| Feature | Fact Table | Dimension Table |
|---|---|---|
| Data type | Numerical | Descriptive |
| Size | Large (many rows) | Small (few rows) |
| Example | Sales amount | Product name |
| Purpose | Measure performance | Provide context |
| Feature | Star Schema | Snowflake Schema |
|---|---|---|
| Complexity | Simple | More complex |
| Performance | Faster | Slower |
| Normalization | Denormalized | Normalized |
| Use when | Most cases | Very large dimensions |
Congratulations! You have completed Module Thirteen on Data Modeling and Relationships. You learned:
You are now ready to build robust data models that will make your reports accurate, fast, and easy to understand!
Match the term to its description:
| Term | Description |
|---|---|
| 1. Primary Key | A. Links to a primary key |
| 2. Foreign Key | B. Unique identifier |
| 3. One-to-Many | C. One row links to many |
| 4. Star Schema | D. Fact and dimension tables |
Answers: 1-B, 2-A, 3-C, 4-D
Scenario 1: You have tables: Students (StudentID, Name), Grades (StudentID, Subject, Score). What type of relationship is this? How would you connect them?
Scenario 2: You have tables: Products (ProdID, Name), Orders (OrderID, ProdID, Qty), Customers (CustID, Name). Design a star schema with these tables.
In groups, choose a scenario (e.g., school data, store data). Design a data model with at least three tables. Draw it on paper and present it to the class. Identify fact and dimension tables, primary and foreign keys, and relationship types.
Load two or three related tables into Power BI. Create relationships between them. Then create a simple report that uses all tables. Document your model.
Project: Build a Sales Data Model
You have four tables: Sales (OrderID, CustomerID, ProductID, DateID, Amount), Customers (CustomerID, Name, City), Products (ProductID, Name, Category), Dates (DateID, Year, Month, Day). Build a star schema in Power BI. Create relationships and set cardinality. Then create a simple sales report.
Download a dataset with multiple related tables. Build a data model in Power BI. Create at least three relationships. Document your model with a diagram and explanation of keys and cardinality.
You have a dataset with sales data that has a many-to-many relationship between products and customers (a product can be bought by many customers, a customer can buy many products). Use a junction table to resolve this relationship. Build a report that shows which customer buys which product.
Fill-in-the-Blank: 1. Primary, 2. Foreign, 3. one-to-many, 4. star, 5. Dimension
True/False: 1-F, 2-T, 3-T, 4-F, 5-F
Matching: 1-B, 2-A, 3-C, 4-D
In Module Fourteen, we will learn about DAX (Data Analysis Expressions) – the language that creates powerful calculations in Power BI. You will learn how to write formulas that make your reports even more intelligent. Get ready to become a DAX wizard!
Before the next class, practice building a data model with at least three tables and one relationship.
© 2025 Certified Power Query for Data Analysis Expert – Module Thirteen
Hello, young data explorer! Welcome to Module Fourteen. In this module, we will learn about DAX – Data Analysis Expressions. DAX is a language that helps us create powerful calculations in Power BI. Think of DAX as a magic wand that can answer any question you have about your data. You can create new columns, make calculations, and build advanced measures. By the end of this module, you will be able to write your own DAX formulas and make your reports smarter!
Chidi has a magic calculator. He can ask it questions like: "What is the total sales for this year?" or "What is the average score of students?" and it answers instantly. The magic calculator is like DAX – it can answer any question about your data. You just need to write the right formula, and the answer appears!
Definition: DAX stands for Data Analysis Expressions. It's a language used to create calculations in Power BI.
Why important: DAX allows you to ask questions and get answers from your data.
Simple explanation: It's like a magic language that turns data into answers.
Real-life example: A store uses DAX to calculate total sales.
School example: A teacher uses DAX to calculate average scores.
Home example: You use DAX to calculate total expenses.
Nigerian example: A bank uses DAX to calculate total deposits.
Illustration:
Data + DAX = Answers Sales Table + DAX = Total Sales
Mini summary: DAX is the language for creating calculations in Power BI.
Definition: Calculated columns are added to a table and evaluated row by row. Measures are calculated on the fly based on the context.
Why important: Knowing the difference helps you use DAX correctly.
Simple explanation: Calculated columns are like permanent additions. Measures are like temporary answers that change.
Real-life example: Calculated column: "Profit" = [Sales] - [Cost]. Measure: Total Sales = SUM([Sales]).
School example: Calculated column: "Total Score" = [Exam] + [Assignment]. Measure: Average Score = AVERAGE([Total Score]).
Home example: Calculated column: "Adjusted Price" = [Price] + [Tax]. Measure: Total Budget = SUM([Adjusted Price]).
Nigerian example: Calculated column: "Net Salary" = [Gross] - [Tax]. Measure: Total Payroll = SUM([Net Salary]).
Illustration:
Calculated Column: Adds data to a table (row by row). Measure: Calculates data on the fly (dynamic).
Mini summary: Calculated columns are row-level; measures are dynamic.
Definition: SUM adds all the numbers in a column.
Why important: It's the most used function for totals.
Simple explanation: It adds up numbers, just like a calculator.
Real-life example: Total Sales = SUM(Sales[Amount]).
School example: Total Marks = SUM(Grades[Score]).
Home example: Total Expenses = SUM(Budget[Amount]).
Nigerian example: Total Votes = SUM(Votes[Count]).
How to write: Measure = SUM(Table[Column]).
Illustration:
Sales[Amount] = [100, 150, 200] SUM(Sales[Amount]) = 450
Mini summary: SUM adds numbers.
Definition: AVERAGE calculates the average of a column.
Why important: To find the typical value.
Simple explanation: It adds all numbers and divides by the count.
Real-life example: Average Sales = AVERAGE(Sales[Amount]).
School example: Average Score = AVERAGE(Grades[Score]).
Home example: Average Expense = AVERAGE(Budget[Amount]).
Nigerian example: Average Rainfall = AVERAGE(Weather[Rainfall]).
How to write: Measure = AVERAGE(Table[Column]).
Illustration:
Sales[Amount] = [100, 150, 200] AVERAGE(Sales[Amount]) = 150
Mini summary: AVERAGE finds the mean.
Definition: COUNT counts the number of rows in a column.
Why important: To know how many items.
Simple explanation: It counts rows.
Real-life example: Number of Orders = COUNT(Orders[OrderID]).
School example: Number of Students = COUNT(Students[StudentID]).
Home example: Number of Expenses = COUNT(Budget[Amount]).
Nigerian example: Number of Voters = COUNT(Voters[VoterID]).
How to write: Measure = COUNT(Table[Column]).
Illustration:
Sales[Amount] = [100, 150, 200] COUNT(Sales[Amount]) = 3
Mini summary: COUNT counts rows.
Definition: MAX finds the highest value, MIN finds the lowest value.
Why important: To know the extremes.
Simple explanation: MAX gives the biggest, MIN gives the smallest.
Real-life example: Highest Sales = MAX(Sales[Amount]).
School example: Highest Score = MAX(Grades[Score]).
Home example: Highest Expense = MAX(Budget[Amount]).
Nigerian example: Maximum Rainfall = MAX(Weather[Rainfall]).
How to write: Measure = MAX(Table[Column]).
Illustration:
Sales[Amount] = [100, 150, 200] MAX = 200, MIN = 100
Mini summary: MAX finds the largest, MIN finds the smallest.
Definition: CALCULATE changes the context in which a calculation is performed.
Why important: It's the most powerful function in DAX.
Simple explanation: It's like a magnifying glass that focuses on specific data.
Real-life example: Total Sales for Year 2025 = CALCULATE(SUM(Sales[Amount]), Sales[Year] = 2025).
School example: Average Score for Math = CALCULATE(AVERAGE(Grades[Score]), Grades[Subject] = "Math").
Home example: Total Expenses for Food = CALCULATE(SUM(Budget[Amount]), Budget[Category] = "Food").
Nigerian example: Total Votes in Lagos = CALCULATE(SUM(Votes[Count]), Votes[State] = "Lagos").
How to write: Measure = CALCULATE(expression, filter).
Illustration:
CALCULATE(SUM(Sales[Amount]), Sales[Year] = 2025) → Sum of sales only for 2025.
Mini summary: CALCULATE changes the calculation context.
Definition: FILTER returns a table that is filtered based on a condition.
Why important: It's used inside CALCULATE to apply complex filters.
Simple explanation: It's like a sieve that only keeps specific rows.
Real-life example: Total Sales for Products with Price > 100 = CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[Price] > 100)).
School example: Average Score for Students with Attendance > 90% = CALCULATE(AVERAGE(Grades[Score]), FILTER(Students, Students[Attendance] > 0.9)).
Home example: Total Expenses for Categories with Amount > 1000 = CALCULATE(SUM(Budget[Amount]), FILTER(Budget, Budget[Amount] > 1000)).
Nigerian example: Total Votes for States with Population > 1M = CALCULATE(SUM(Votes[Count]), FILTER(States, States[Population] > 1000000)).
Illustration:
FILTER(Products, Products[Price] > 100) → Only keeps products with price > 100.
Mini summary: FILTER creates a filtered table.
Definition: TOTALYTD calculates the year-to-date total.
Why important: To track cumulative totals over time.
Simple explanation: It adds up values from the start of the year to the current date.
Real-life example: Sales YTD = TOTALYTD(SUM(Sales[Amount]), Dates[Date]).
School example: Marks YTD = TOTALYTD(SUM(Grades[Score]), Dates[Date]).
Home example: Expenses YTD = TOTALYTD(SUM(Budget[Amount]), Dates[Date]).
Nigerian example: Revenue YTD = TOTALYTD(SUM(Revenue[Amount]), Dates[Date]).
How to write: Measure = TOTALYTD(expression, dates column).
Illustration:
TOTALYTD(SUM(Sales[Amount]), Calendar[Date]) → Total sales from Jan 1 to today.
Mini summary: TOTALYTD gives year-to-date totals.
Definition: SAMEPERIODLASTYEAR returns the same period in the previous year.
Why important: To compare performance year-over-year.
Simple explanation: It gives you the same dates but one year ago.
Real-life example: Sales Last Year = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Dates[Date])).
School example: Scores Last Year = CALCULATE(AVERAGE(Grades[Score]), SAMEPERIODLASTYEAR(Dates[Date])).
Home example: Expenses Last Year = CALCULATE(SUM(Budget[Amount]), SAMEPERIODLASTYEAR(Dates[Date])).
Nigerian example: Revenue Last Year = CALCULATE(SUM(Revenue[Amount]), SAMEPERIODLASTYEAR(Dates[Date])).
Illustration:
CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date])) → Sales for the same period last year.
Mini summary: SAMEPERIODLASTYEAR compares with the previous year.
Definition: ALL removes all filters from a table or column.
Why important: Useful for calculating percentages of totals.
Simple explanation: It ignores any filters and looks at everything.
Real-life example: % of Total Sales = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales))).
School example: % of Total Marks = DIVIDE(SUM(Grades[Score]), CALCULATE(SUM(Grades[Score]), ALL(Grades))).
Home example: % of Total Expenses = DIVIDE(SUM(Budget[Amount]), CALCULATE(SUM(Budget[Amount]), ALL(Budget))).
Nigerian example: % of Total Votes = DIVIDE(SUM(Votes[Count]), CALCULATE(SUM(Votes[Count]), ALL(Votes))).
Illustration:
DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales))) → Percentage of each product's sales out of total.
Mini summary: ALL removes filters.
Definition: Row context refers to a single row being evaluated. Filter context refers to the filters applied to the data.
Why important: Understanding context is key to writing correct DAX.
Simple explanation: Row context is like looking at one student's score. Filter context is like looking at all students from a particular class.
Real-life example: Row context: calculating profit for each product. Filter context: calculating total sales for a region.
School example: Row context: calculating total marks for a student. Filter context: calculating average marks for a subject.
Home example: Row context: calculating cost per item. Filter context: calculating total cost for a category.
Nigerian example: Row context: calculating tax per citizen. Filter context: calculating total tax for a state.
Illustration:
Row Context: Each row is evaluated individually. Filter Context: All rows that meet certain criteria.
Mini summary: Row context = one row; Filter context = filtered rows.
Definition: DIVIDE handles division safely, returning an alternate value if the denominator is zero.
Why important: Prevents errors in calculations.
Simple explanation: It's like a safety net for division.
Real-life example: Sales per Product = DIVIDE(SUM(Sales[Amount]), COUNT(Products[ProductID]), 0).
School example: Average per Student = DIVIDE(SUM(Grades[Score]), COUNT(Students[StudentID]), 0).
Home example: Average per Category = DIVIDE(SUM(Budget[Amount]), COUNT(Categories[CategoryID]), 0).
Nigerian example: Average per State = DIVIDE(SUM(Population[Total]), COUNT(States[StateID]), 0).
How to write: DIVIDE(numerator, denominator, alternate result).
Illustration:
DIVIDE(10, 0, 0) → 0 DIVIDE(10, 2, 0) → 5
Mini summary: DIVIDE safely handles division by zero.
Definition: Common patterns are frequently used DAX formulas.
Why important: They save time and are proven to work.
Simple explanation: It's like using a recipe instead of inventing a new dish.
Illustration:
Measure: Total Sales = SUM(Sales[Amount]) Measure: Sales YTD = TOTALYTD([Total Sales], Calendar[Date]) Measure: % of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales)))
Mini summary: Use common patterns for efficient DAX.
Definition: Tips for writing efficient and accurate DAX formulas.
Why important: Good DAX is fast, accurate, and easy to understand.
Simple explanation: It's like following rules to build a strong house.
Illustration:
1. Use measures for dynamic calculations. 2. Use CALCULATE for context changes. 3. Use DIVIDE for safe division. 4. Document your measures.
Mini summary: Follow best practices for quality DAX.
How to Create a Measure in Power BI:
How to Create a Calculated Column:
Did you know that DAX was originally developed for Power Pivot in Excel? Now it's used in Power BI, Analysis Services, and even in Excel!
Calculated Column: +----------+----------+----------+ | Product | Price | Tax | +----------+----------+----------+ | Toy | 100 | = Price*0.1 (calculated row by row) | +----------+----------+----------+ Measure: Total Sales = SUM(Sales[Amount]) (dynamic calculation)
Expression → CALCULATE → Result ↓ ↓ SUM(Sales) Filter: Year = 2025 ↓ ↓ → CALCULATE(SUM(Sales), Sales[Year]=2025) → Total Sales for 2025
| Feature | Calculated Column | Measure |
|---|---|---|
| Evaluation | Row by row | On the fly |
| Storage | Stored in table | Not stored |
| Performance | Slower | Faster |
| Use case | Row-level calculations | Aggregations |
| Function | What it does | Example |
|---|---|---|
| SUM | Adds numbers | SUM(Sales[Amount]) |
| AVERAGE | Calculates mean | AVERAGE(Sales[Amount]) |
| COUNT | Counts rows | COUNT(Sales[OrderID]) |
| MAX | Finds highest | MAX(Sales[Amount]) |
| MIN | Finds lowest | MIN(Sales[Amount]) |
Congratulations! You have completed Module Fourteen on Introduction to DAX. You learned:
You now have the skills to create powerful calculations and make your reports truly intelligent!
Match the function to its description:
| Function | Description |
|---|---|
| 1. SUM | A. Calculates the mean |
| 2. AVERAGE | B. Adds numbers |
| 3. COUNT | C. Finds the highest value |
| 4. MAX | D. Counts rows |
| 5. CALCULATE | E. Changes context |
Answers: 1-B, 2-A, 3-D, 4-C, 5-E
Scenario 1: You have a sales table with columns: Product, Amount, Date. Write a DAX measure to calculate total sales for the year 2025.
Scenario 2: You have a student grades table with columns: Student, Subject, Score. Write a DAX measure to calculate the average score for Math.
In groups, choose a dataset (e.g., sales data). Create at least five DAX measures: Total Sales, Average Sales, Max Sales, Min Sales, and Sales YTD. Present your measures and explain how they work.
Load a sample dataset into Power BI. Create a calculated column (e.g., Profit = Sales - Cost) and a measure (e.g., Total Sales = SUM(Sales)). Write at least three more measures of your choice.
Project: DAX Sales Dashboard
Build a sales dashboard using DAX. Include measures for: Total Sales, Total Sales YTD, Sales Last Year, Average Sales per Product, and % of Total Sales. Add a chart that shows sales by month. Use time intelligence functions.
Download a dataset with at least 1,000 rows. Create a data model and write at least 10 DAX measures. Include measures that use CALCULATE, FILTER, and time intelligence. Document your measures.
Write a DAX measure that calculates the moving average of sales over the last 3 months. Use CALCULATE and FILTER. Test it with sample data.
Fill-in-the-Blank: 1. DAX, 2. measure, 3. SUM, 4. CALCULATE, 5. TOTALYTD
True/False: 1-T, 2-F, 3-F, 4-T, 5-F
Matching: 1-B, 2-A, 3-D, 4-C, 5-E
In Module Fifteen, we will learn about Advanced DAX Patterns – we will explore more complex calculations like ranking, running totals, and dynamic segmentation. You will become a true DAX master!
Before the next class, practice writing DAX measures with CALCULATE and time intelligence. Try to create at least five different measures.
© 2025 Certified Power Query for Data Analysis Expert – Module Fourteen
Hello, young data explorer! Welcome to Module Fifteen. In this module, we will learn advanced DAX patterns. These are powerful formulas that solve complex problems. Think of them as special tricks that make your reports even smarter. You will learn how to rank products, calculate running totals, and compare different time periods. By the end of this module, you will be a DAX master!
Chidi loves sports. He has a scoreboard that shows players' scores. But he wants more: he wants to rank players, show cumulative scores, and compare this season with last season. He uses DAX to add these features. Now his scoreboard is the smartest one in the whole school! That's what advanced DAX patterns do – they make your reports smarter and more insightful.
Definition: Advanced DAX patterns are complex formulas that solve specific analytical problems.
Why important: They help you get deeper insights from your data.
Simple explanation: It's like learning advanced magic spells after you've mastered the basics.
Real-life example: Ranking top-selling products.
School example: Ranking students by grades.
Home example: Ranking expenses by category.
Nigerian example: Ranking states by population.
Illustration:
Basic DAX: SUM, AVERAGE Advanced DAX: RANKX, RUNNING TOTAL, COMPARE PERIODS
Mini summary: Advanced patterns solve complex problems.
Definition: RANKX returns the rank of a value in a list.
Why important: To see who is first, second, etc.
Simple explanation: It tells you the position of an item (1st, 2nd, 3rd).
Real-life example: Rank products by sales.
School example: Rank students by score.
Home example: Rank expenses by amount.
Nigerian example: Rank states by population.
How to write: Rank = RANKX(ALL(Table[Column]), SUM(Table[Value]), , DESC).
Illustration:
Products: A(100), B(200), C(150) RANKX → A=3, B=1, C=2
Mini summary: RANKX gives ranks to items.
Definition: When two items have the same value, they get the same rank.
Why important: To handle ties fairly.
Simple explanation: If two students have the same score, they both get the same rank.
Real-life example: Two products with same sales both get rank 1.
School example: Two students with same score both get rank 1.
Home example: Two expenses with same amount both get rank 1.
Nigerian example: Two states with same population both get rank 1.
How to handle: Use RANKX with ties parameter or calculate dense rank.
Illustration:
Scores: 100, 100, 90 RANKX → 1, 1, 3 (skip 2) Dense Rank → 1, 1, 2 (no skip)
Mini summary: Ties can be handled with rank options.
Definition: A running total adds up values cumulatively over time.
Why important: To track cumulative performance.
Simple explanation: It's like a countdown that keeps adding.
Real-life example: Cumulative sales over months.
School example: Cumulative marks over tests.
Home example: Cumulative expenses over weeks.
Nigerian example: Cumulative revenue over years.
How to write: Running Total = CALCULATE(SUM(Table[Value]), FILTER(ALL(Table[Date]), Table[Date] <= MAX(Table[Date]))).
Illustration:
Month Sales: Jan=100, Feb=150, Mar=200 Running Total: Jan=100, Feb=250, Mar=450
Mini summary: Running totals add cumulatively.
Definition: A moving average calculates the average over a moving window of time.
Why important: Smooths out fluctuations to see trends.
Simple explanation: It's like a smooth line through your data.
Real-life example: 3-month moving average of sales.
School example: Moving average of test scores.
Home example: Moving average of daily expenses.
Nigerian example: Moving average of monthly rainfall.
How to write: Moving Avg = AVERAGEX(DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -3, MONTH), [Total Sales]).
Illustration:
Sales: Jan=100, Feb=150, Mar=200, Apr=180 3-Month Moving Avg (Apr): (150+200+180)/3 = 176.7
Mini summary: Moving averages smooth data.
Definition: EARLIER returns the value of a column from the outer row context.
Why important: Useful when you need to compare a row to other rows.
Simple explanation: It looks at the current row from an earlier context.
Real-life example: Calculate how much each product contributes to total sales.
School example: Calculate how much each student contributes to total marks.
Home example: Calculate how much each category contributes to total expenses.
Nigerian example: Calculate how much each state contributes to total population.
How to write: Contribution = [Value] / CALCULATE(SUM(Table[Value]), ALL(Table)).
Illustration:
Product Sales: A=100, B=200, C=300 Contribution: A=100/600=16.7%, B=33.3%, C=50%
Mini summary: EARLIER accesses outer context.
Definition: Dynamic segmentation groups data into categories based on values (e.g., High, Medium, Low).
Why important: To classify data for better understanding.
Simple explanation: It's like sorting items into boxes based on size.
Real-life example: Segment customers by spending amount.
School example: Segment students by score (A, B, C).
Home example: Segment expenses by amount (High, Medium, Low).
Nigerian example: Segment states by population (Large, Medium, Small).
How to write: Segment = SWITCH(TRUE(), [Value] > 100, "High", [Value] > 50, "Medium", "Low").
Illustration:
Sales: 150 → High, 75 → Medium, 30 → Low
Mini summary: Dynamic segmentation categorizes data.
Definition: Compare current period with previous period (e.g., this month vs last month).
Why important: To measure growth or decline.
Simple explanation: It's like comparing your scores from today and yesterday.
Real-life example: Sales this month vs last month.
School example: Scores this test vs previous test.
Home example: Expenses this week vs last week.
Nigerian example: Revenue this quarter vs previous quarter.
How to write: Previous Period = CALCULATE(SUM(Table[Value]), PREVIOUSMONTH(Calendar[Date])).
Illustration:
Current Month Sales: 200 Previous Month Sales: 150 Difference = 50
Mini summary: Compare current and previous periods.
Definition: VAR allows you to store a value in a variable for reuse.
Why important: Makes formulas cleaner and faster.
Simple explanation: It's like storing a number in your memory to use later.
Real-life example: VAR TotalSales = SUM(Sales[Amount]); RETURN TotalSales * 0.1.
School example: VAR TotalMarks = SUM(Grades[Score]); RETURN TotalMarks / 100.
Home example: VAR TotalExpenses = SUM(Budget[Amount]); RETURN TotalExpenses * 0.2.
Nigerian example: VAR TotalVotes = SUM(Votes[Count]); RETURN TotalVotes / 10000.
How to write: VAR name = expression RETURN expression.
Illustration:
VAR TotalSales = SUM(Sales[Amount]) VAR TotalCost = SUM(Sales[Cost]) RETURN TotalSales - TotalCost
Mini summary: VAR makes DAX cleaner and reusable.
Definition: GROUPBY creates a summary table based on grouping columns.
Why important: To create custom aggregations.
Simple explanation: It's like Group By in Power Query but in DAX.
Real-life example: Group sales by product and sum.
School example: Group grades by subject and average.
Home example: Group expenses by category and sum.
Nigerian example: Group votes by state and count.
How to write: GROUPBY(Table, Column, "NewColumn", SUMX(CURRENTGROUP(), [Value])).
Illustration:
GROUPBY(Sales, Product, "TotalSales", SUMX(CURRENTGROUP(), Sales[Amount]))
Mini summary: GROUPBY creates custom summaries.
Definition: SUMMARIZE creates a summary table with grouping and aggregations.
Why important: More powerful than GROUPBY.
Simple explanation: It's like GROUPBY but with more options.
Real-life example: SUMMARIZE(Sales, Product, Category, "TotalSales", SUM(Sales[Amount])).
School example: SUMMARIZE(Grades, Subject, Teacher, "AvgScore", AVERAGE(Grades[Score])).
Home example: SUMMARIZE(Budget, Category, Month, "Total", SUM(Budget[Amount])).
Nigerian example: SUMMARIZE(Votes, State, Party, "TotalVotes", SUM(Votes[Count])).
Illustration:
SUMMARIZE(Sales, Product, "TotalSales", SUM(Sales[Amount]))
Mini summary: SUMMARIZE is a powerful grouping function.
Definition: Combine CALCULATE with time functions for powerful time-based analysis.
Why important: To answer questions like "Sales for the same period last year".
Simple explanation: It's like asking "What happened this time last year?"
Real-life example: Sales LY = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date])).
School example: Scores LY = CALCULATE(AVERAGE(Grades[Score]), SAMEPERIODLASTYEAR(Calendar[Date])).
Home example: Expenses LY = CALCULATE(SUM(Budget[Amount]), SAMEPERIODLASTYEAR(Calendar[Date])).
Nigerian example: Votes LY = CALCULATE(SUM(Votes[Count]), SAMEPERIODLASTYEAR(Calendar[Date])).
Illustration:
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))
Mini summary: CALCULATE with time intelligence is powerful.
Definition: Debugging means finding and fixing errors in your DAX formulas.
Why important: Everyone makes mistakes. You need to fix them.
Simple explanation: It's like finding a needle in a haystack.
Real-life example: A measure returns wrong results. You check the formula.
School example: A calculation is incorrect. You review the logic.
Home example: A total is wrong. You trace back.
Nigerian example: A report has errors. You debug.
Tips: Use variables to break down complex formulas. Test with sample data. Check context.
Illustration:
1. Check the formula. 2. Test with small data. 3. Use variables to simplify. 4. Check filter context.
Mini summary: Debugging fixes errors in DAX.
Definition: Patterns that are frequently used in real-world scenarios.
Why important: They save time and are proven to work.
Simple explanation: It's like using a template for your formulas.
Illustration:
Pattern: Rank Products by Sales Rank = RANKX(ALL(Products[ProductID]), [Total Sales], , DESC)
Mini summary: Common patterns make DAX easier.
Definition: Tips for writing efficient and accurate advanced DAX.
Why important: Good DAX is fast, reliable, and easy to maintain.
Simple explanation: It's like following rules to build a strong building.
Illustration:
1. Use VAR to break down. 2. Test with sample. 3. Use CALCULATE wisely. 4. Document your code.
Mini summary: Best practices lead to quality DAX.
How to Create a Ranking Measure:
How to Create a Running Total:
Did you know that you can use DAX to create a "ranking" that updates automatically as your data changes? That's the power of dynamic calculations!
Products: A(100), B(200), C(150) Rank: A=3, B=1, C=2 +----------+----------+--------+ | Product | Sales | Rank | +----------+----------+--------+ | A | 100 | 3 | | B | 200 | 1 | | C | 150 | 2 | +----------+----------+--------+
Month Sales: Jan=100, Feb=150, Mar=200 Running Total: Jan=100, Feb=250, Mar=450 +----------+----------+-------------+ | Month | Sales | Running | | | | Total | +----------+----------+-------------+ | Jan | 100 | 100 | | Feb | 150 | 250 | | Mar | 200 | 450 | +----------+----------+-------------+
| Value | RANKX | DENSE RANK |
|---|---|---|
| 100 | 1 | 1 |
| 100 | 1 | 1 |
| 90 | 3 | 2 |
| Feature | Running Total | Moving Average |
|---|---|---|
| Purpose | Cumulative sum | Smooth trends |
| Formula | Adds all previous | Average of window |
| Use when | Tracking progress | Identifying trends |
Congratulations! You have completed Module Fifteen on Advanced DAX Patterns. You learned:
You are now a DAX master! You can solve any analytical problem with these advanced patterns.
Match the function to its description:
| Function | Description |
|---|---|
| 1. RANKX | A. Cumulative sum |
| 2. Running Total | B. Gives ranks |
| 3. VAR | C. Stores a value |
| 4. GROUPBY | D. Groups and aggregates |
Answers: 1-B, 2-A, 3-C, 4-D
Scenario 1: You have a sales table. Write a DAX measure to rank products by total sales. Use RANKX.
Scenario 2: You have a student grades table. Write a DAX measure to calculate the running total of marks.
In groups, choose a dataset and create at least five advanced DAX measures: ranking, running total, moving average, dynamic segmentation, and comparison with previous period. Present your findings.
Load a dataset into Power BI. Create a ranking measure, a running total, and a moving average. Use at least one VAR in your formulas. Document your work.
Project: Advanced Sales Dashboard
Build a sales dashboard that includes: ranking of products by sales, running total of sales, 3-month moving average of sales, dynamic segmentation of products (High, Medium, Low), and comparison of sales with previous year. Use at least 5 advanced DAX measures.
Download a dataset with at least 5,000 rows. Create a data model and write at least 10 DAX measures, including ranking, running total, moving average, dynamic segmentation, and time intelligence. Document your measures.
Write a DAX measure that calculates a 4-week moving average of sales. Use variables to make the formula clean and efficient. Test it with sample data.
Fill-in-the-Blank: 1. RANKX, 2. running, 3. VAR, 4. GROUPBY, 5. EARLIER
True/False: 1-T, 2-F, 3-T, 4-F, 5-T
Matching: 1-B, 2-A, 3-C, 4-D
In Module Sixteen, we will learn about Performance Optimization – how to make your Power Query and Power BI solutions run faster and more efficiently. You will learn tips and tricks to handle large datasets with ease. Get ready to become a performance expert!
Before the next class, practice writing advanced DAX measures with at least two different patterns.
© 2025 Certified Power Query for Data Analysis Expert – Module Fifteen
Hello, young data explorer! Welcome to Module Sixteen. In this module, we will learn how to make our Power Query and Power BI solutions faster and more efficient. Imagine you have a car. You want it to go fast and use less fuel. Performance optimization is like tuning your car for speed and efficiency. By the end of this module, you will be able to handle large datasets with ease and make your reports load quickly.
Chidi runs a delivery service. He wants to deliver packages as fast as possible. He learns to take shortcuts, reduce the weight of packages, and use faster vehicles. This is exactly what performance optimization does for data – it makes everything faster and more efficient!
Definition: Performance is how fast your data loads, refreshes, and responds.
Why important: Slow reports are frustrating and waste time.
Simple explanation: It's like waiting in a long queue – it's annoying and takes forever.
Real-life example: A company with a slow dashboard loses productivity.
School example: A slow grade calculator makes teachers wait.
Home example: A slow budget tracker is no fun.
Nigerian example: A slow election dashboard delays decisions.
Illustration:
Slow Report: 5 minutes to load Fast Report: 5 seconds to load
Mini summary: Performance matters because time is valuable.
Definition: Only load the data you actually need.
Why important: Less data means faster processing.
Simple explanation: It's like only taking what you need from a buffet, not everything.
Real-life example: Instead of loading all columns, only load the ones you use.
School example: Instead of loading all student data, only load current students.
Home example: Instead of loading all transactions, only load this year's.
Nigerian example: Instead of loading all states, only load the ones you need.
How to do it: Use filters, select only necessary columns.
Illustration:
Before: Load 100 columns, 1M rows After: Load 10 columns, 100K rows
Mini summary: Load only what you need.
Definition: Delete columns and rows that are not needed.
Why important: Reduces memory usage and speeds up processing.
Simple explanation: It's like throwing away things you don't use.
Real-life example: Remove columns like "CreatedBy" if you don't use them.
School example: Remove columns like "MiddleName" if not needed.
Home example: Remove columns like "StoreID" if not relevant.
Nigerian example: Remove columns like "BranchCode" if not needed.
How to do it: Use Remove Columns, Remove Rows.
Illustration:
Before: 50 columns After: 10 columns (remove 40)
Mini summary: Remove unnecessary data.
Definition: Query folding pushes transformations back to the source database, so they run faster.
Why important: Databases are optimized for data operations.
Simple explanation: It's like letting a chef cook in the kitchen instead of you trying to cook outside.
Real-life example: Filtering data in SQL before loading into Power Query.
School example: Filtering grades in the database before loading.
Home example: Filtering expenses in the database before loading.
Nigerian example: Filtering election results in the database before loading.
How to do it: Use native database queries or ensure steps are foldable.
Illustration:
No Query Folding: Load all data, then filter. Query Folding: Filter at the database, then load.
Mini summary: Query folding uses the database for faster processing.
Definition: Use the most efficient data type for each column.
Why important: The wrong data type can slow down processing.
Simple explanation: It's like using the right tool for the job.
Real-life example: Use Integer instead of Text for numeric columns.
School example: Use Date type for dates, not Text.
Home example: Use Number for amounts, not Text.
Nigerian example: Use Integer for population, not Text.
How to do it: Change data types to the most efficient.
Illustration:
Text: "100" → 3 bytes Integer: 100 → 4 bytes (actually more efficient for calculations)
Mini summary: Use efficient data types.
Definition: Some operations are slow (e.g., merging large tables). Avoid them if possible.
Why important: Expensive operations can make your query very slow.
Simple explanation: It's like taking the long way home instead of the shortcut.
Real-life example: Avoid merging very large tables if you can.
School example: Avoid complex joins on large student tables.
Home example: Avoid merging huge budget files.
Nigerian example: Avoid merging massive election datasets.
How to do it: Filter before merging, use smaller tables.
Illustration:
Expensive: Merge 1M rows with 1M rows. Better: Filter both to 10K rows, then merge.
Mini summary: Avoid slow operations when possible.
Definition: Incremental refresh loads only new or changed data.
Why important: Saves time and resources for large datasets.
Simple explanation: Instead of reading the whole book, you only read new pages.
Real-life example: A company with 5 years of sales data only loads the last 3 months.
School example: A teacher only loads the latest test scores.
Home example: You only load this month's expenses.
Nigerian example: A bank only loads transactions from the last 7 days.
How to do it: In Power BI, set up incremental refresh policies.
Illustration:
Incremental Refresh: Load last 3 months Refresh daily
Mini summary: Incremental refresh loads only new data.
Definition: Write DAX measures efficiently to avoid slow calculations.
Why important: Slow DAX slows down the entire report.
Simple explanation: It's like using a fast calculator instead of counting on your fingers.
Real-life example: Use CALCULATE with simple filters instead of complex FILTER.
School example: Use simple SUM instead of complex calculations.
Home example: Use simple totals instead of complex conditions.
Nigerian example: Use simple aggregations instead of complex logic.
Tips: Use VAR, avoid nested CALCULATE, use efficient functions.
Illustration:
Slow: CALCULATE(SUM(Sales), FILTER(All(Sales), Sales[Year] = 2025)) Fast: CALCULATE(SUM(Sales), Sales[Year] = 2025)
Mini summary: Optimize DAX for speed.
Definition: Performance Analyzer is a tool in Power BI that shows how long each part of a report takes to load.
Why important: It helps you find bottlenecks.
Simple explanation: It's like a timer that tells you where time is being wasted.
Real-life example: Use Performance Analyzer to see which visual is slow.
School example: Use it to see which calculation is slow.
Home example: Use it to see which chart takes time.
Nigerian example: Use it to see which part of the dashboard is slow.
How to do it: In Power BI, go to View → Performance Analyzer.
Illustration:
Performance Analyzer: Visual 1: 1 second Visual 2: 0.5 seconds Visual 3: 10 seconds ← slow!
Mini summary: Performance Analyzer finds slow parts.
Definition: Variables (VAR) store values for reuse, making DAX faster.
Why important: Variables avoid recalculating the same thing multiple times.
Simple explanation: It's like storing a number in your memory to use later.
Real-life example: VAR TotalSales = SUM(Sales[Amount]); RETURN TotalSales * 0.1.
School example: VAR TotalMarks = SUM(Grades[Score]); RETURN TotalMarks / 100.
Home example: VAR TotalExpenses = SUM(Budget[Amount]); RETURN TotalExpenses * 0.2.
Nigerian example: VAR TotalVotes = SUM(Votes[Count]); RETURN TotalVotes / 10000.
How to write: VAR name = expression RETURN expression.
Illustration:
Without VAR: CALCULATE(SUM(Sales), Sales[Year]=2025) is calculated multiple times. With VAR: VAR YearSales = CALCULATE(SUM(Sales), Sales[Year]=2025) RETURN YearSales * 2.
Mini summary: VAR makes DAX faster and cleaner.
Definition: Calculated columns are evaluated row by row, which is slow.
Why important: Measures are faster than calculated columns.
Simple explanation: It's like doing work row by row vs doing it all at once.
Real-life example: Use measures instead of calculated columns when possible.
School example: Use measures for averages, not calculated columns.
Home example: Use measures for totals, not calculated columns.
Nigerian example: Use measures for aggregations, not calculated columns.
How to do it: Use measures instead of calculated columns for aggregations.
Illustration:
Calculated Column: [Profit] = [Sales] - [Cost] (row by row) Measure: Total Profit = SUM(Sales) - SUM(Cost) (dynamic)
Mini summary: Use measures instead of calculated columns.
Definition: A star schema with fact and dimension tables is faster for reporting.
Why important: Star schemas are optimized for Power BI.
Simple explanation: It's like having a well-organized library.
Real-life example: Fact table: Sales. Dimensions: Products, Customers, Time.
School example: Fact table: Grades. Dimensions: Students, Subjects.
Home example: Fact table: Expenses. Dimensions: Categories, Months.
Nigerian example: Fact table: Votes. Dimensions: Parties, States.
How to do it: Build a star schema with one fact and many dimensions.
Illustration:
+----------+
| Products |
+----------+
|
+----------+----------+----------+
| Customers|──│ Sales │──│ Time |
+----------+----------+----------+
|
+----------+
| Regions |
+----------+
Mini summary: Star schemas are fast and efficient.
Definition: Import loads data into Power BI. DirectQuery queries the data source live.
Why important: Import is faster for reporting, but requires storage. DirectQuery is slower but always up-to-date.
Simple explanation: Import is like having a copy of the book. DirectQuery is like reading the book online.
Real-life example: Use Import for fast reports, DirectQuery for live data.
School example: Use Import for historical grades, DirectQuery for current grades.
Home example: Use Import for budget, DirectQuery for bank transactions.
Nigerian example: Use Import for election results, DirectQuery for live updates.
How to choose: Use Import when possible. Use DirectQuery when you need live data.
Illustration:
Import: Fast, but data is copied. DirectQuery: Slower, but data is always current.
Mini summary: Choose Import for speed, DirectQuery for live data.
Definition: Continuously monitor performance and make adjustments.
Why important: Performance can degrade over time.
Simple explanation: It's like checking your car's engine regularly.
Real-life example: Check refresh times and optimize.
School example: Check report load times and optimize.
Home example: Check budget update times and optimize.
Nigerian example: Check dashboard load times and optimize.
How to do it: Use Performance Analyzer, check refresh times, and make improvements.
Illustration:
Monitor: Refresh time = 10 minutes Tune: Optimize query → Refresh time = 2 minutes
Mini summary: Monitor and tune for ongoing performance.
Definition: Key practices to keep your reports fast.
Why important: Following best practices ensures consistent performance.
Simple explanation: It's like following a recipe for a perfect cake every time.
Illustration:
1. Load only what you need. 2. Use query folding. 3. Use efficient data types. 4. Use measures. 5. Build star schema. 6. Use incremental refresh. 7. Optimize DAX. 8. Monitor performance.
Mini summary: Follow best practices for optimal performance.
How to Use Performance Analyzer:
How to Set Up Incremental Refresh:
Did you know that Power BI has a "Performance Analyzer" that can tell you exactly how long each visual takes to load? It's like a stopwatch for your reports!
Start
|
V
Load Data (only what you need)
|
V
Use Query Folding (if possible)
|
V
Use Efficient Data Types
|
V
Use Measures (not calculated columns)
|
V
Build Star Schema
|
V
Use Incremental Refresh
|
V
Monitor Performance
|
V
Done 🎉
+----------+
| Products |
+----------+
|
+----------+----------+----------+
| Customers|──│ Sales │──│ Time |
+----------+----------+----------+
|
+----------+
| Regions |
+----------+
| Feature | Import | DirectQuery |
|---|---|---|
| Speed | Fast | Slower |
| Data freshness | Depends on refresh | Always live |
| Storage | Requires storage | No storage |
| Best for | Reporting | Live data |
| Feature | Calculated Column | Measure |
|---|---|---|
| Speed | Slower | Faster |
| Storage | Uses storage | No storage |
| Evaluation | Row by row | On the fly |
| Best for | Row-level calculations | Aggregations |
Congratulations! You have completed Module Sixteen on Performance Optimization. You learned:
You now have the skills to make your Power Query and Power BI solutions fast and efficient!
Match the term to its description:
| Term | Description |
|---|---|
| 1. Query Folding | A. Loading only new data |
| 2. Incremental Refresh | B. Pushing operations to the database |
| 3. Star Schema | C. One fact, many dimensions |
| 4. Performance Analyzer | D. Tool to measure load times |
Answers: 1-B, 2-A, 3-C, 4-D
Scenario 1: You have a report that takes 10 minutes to refresh. How would you optimize it? List three steps.
Scenario 2: Your report has a slow visual. How would you find the cause and fix it?
In groups, take a slow report and optimize it. Use Performance Analyzer to identify bottlenecks, reduce data load, and apply optimizations. Present the before and after results.
Load a dataset into Power Query. Apply performance optimizations: remove unnecessary columns, use query folding if possible, and optimize data types. Measure the improvement.
Project: Optimize a Sales Dashboard
You have a sales dashboard that is slow. Apply performance optimization techniques: reduce data load, use query folding, build a star schema, use incremental refresh, and optimize DAX. Show the improvement in load time.
Download a large dataset. Build a report and apply performance optimizations. Use Performance Analyzer to measure load times before and after. Document your optimization steps.
Take a dataset with 10 million rows. Apply query folding, incremental refresh, and star schema. Optimize DAX measures. Aim for a refresh time under 2 minutes.
Fill-in-the-Blank: 1. Performance, 2. Query folding, 3. Incremental refresh, 4. star, 5. Performance Analyzer
True/False: 1-F, 2-T, 3-F, 4-T, 5-F
Matching: 1-B, 2-A, 3-C, 4-D
In Module Seventeen, we will learn about Power Query for Data Analysis Expert – Capstone. You will apply everything you've learned to a real-world project. You will build a complete data pipeline from start to finish. Get ready to showcase your skills!
Before the next class, review all modules and practice performance optimization on a sample dataset.
© 2025 Certified Power Query for Data Analysis Expert – Module Sixteen