🔄 Module Two: Data Transformation with Power Query (M)

Welcome back, young data explorer! Today we learn how to clean and shape our data.

🌟 Module Introduction

In Module One, you learned what Power BI is and how to create simple reports. But here is a secret: data is not always clean. Sometimes it has mistakes, missing values, or is in the wrong format. That is where Power Query comes in.

Imagine you are baking a cake. You need to wash and prepare the ingredients before you start. Data is the same. You need to clean and prepare it before you can make beautiful charts.

Power Query is the tool that helps you clean and prepare your data. It is like a magic kitchen where you chop, mix, and prepare your data ingredients.

In this module, you will learn about the Power Query Editor, the M language, and how to clean, shape, and transform your data. By the end, you will be able to turn messy data into clean, analysis-ready data.

🎯 Learning Objectives

By the time you finish this module, you will be able to:

• Explain what Power Query is and why it is important.
• Navigate the Power Query Editor.
• Clean data by removing duplicates, filtering, and handling nulls.
• Shape data by pivoting, unpivoting, and merging.
• Understand the M language and its role in Power Query.
• Apply data transformations to real datasets.
• Give examples of how Power Query is used in Nigeria.

📖 Warm-up Story: Nneka's Messy Data

Nneka is a 12-year-old girl who loves to collect data. She asked her classmates about their favourite foods and wrote down their answers. But her notebook was messy. Some names were spelled wrong. Some answers were missing. Some were written in different ways.

Nneka tried to make a chart, but it was hard. She showed her notebook to her uncle, who works with data. Her uncle said, "Nneka, your data is messy. You need to clean it before you can make a chart."

He opened Power BI and showed her the Power Query Editor. Together, they fixed the misspelled names, filled in missing answers, and made everything consistent. Then they created a beautiful chart showing the favourite foods of the class.

Nneka learned that cleaning data is just as important as making charts. Without clean data, your charts can be wrong.

📚 Main Lessons

1. What is Power Query?

Definition: Power Query is a tool in Power BI that helps you clean, shape, and transform your data before you use it in reports.

Why it is important: Real-world data is often messy. Power Query helps you fix it so your reports are accurate.

Simple explanation: Think of Power Query like a car wash for your data. It cleans off the dirt and makes your data shiny and ready to use.

Illustration:

    +-------------------+
    |  MESSY DATA       |  ← Data with errors, blanks, and mistakes
    +-------------------+
           |
           V
    +-------------------+
    |  POWER QUERY      |  ← The cleaning tool
    +-------------------+
           |
           V
    +-------------------+
    |  CLEAN DATA       |  ← Ready for analysis
    +-------------------+
    

2. The Power Query Editor

Definition: The Power Query Editor is the window where you clean and transform your data. It shows you a preview of your data and the steps you are applying.

Why it is important: The Power Query Editor gives you full control over how your data is transformed.

Simple explanation: Think of the Power Query Editor like a kitchen. You can see all your ingredients and decide what to do with them.

• Query List: Shows all the data sources you are working with.
• Data Preview: Shows a sample of your data.
• Applied Steps: Shows the list of transformations you have applied.
• Formula Bar: Shows the M code for the current step.

3. What is the M Language?

Definition: M is the language that Power Query uses to perform transformations. Every time you click a button in Power Query, it writes M code behind the scenes.

Why it is important: Understanding M helps you create more powerful transformations.

Simple explanation: Think of M like the recipe for your data. It tells Power Query exactly what to do.

• M is automatic: You don't need to write M code unless you want to.
• M is powerful: You can do almost anything with M.
• M is reusable: You can use the same M code on different datasets.

4. Opening the Power Query Editor

Definition: To start cleaning your data, you need to open the Power Query Editor.

Why it is important: You can't clean your data without opening the editor.

Simple explanation: Think of it like opening a toolbox to fix something.

• Step 1: Open Power BI Desktop.
• Step 2: Click "Get Data" and choose your data source.
• Step 3: Before loading the data, click "Transform Data".
• Step 4: The Power Query Editor opens.

5. Cleaning Data — Removing Duplicates

Definition: Removing duplicates means deleting rows that are exactly the same.

Why it is important: Duplicates can make your data inaccurate and cause you to count things twice.

Simple explanation: Imagine you have a list of students and one student is listed twice. You would delete the duplicate so you don't count them twice.

• Step 1: In the Power Query Editor, select the column(s) where you want to remove duplicates.
• Step 2: Click "Remove Duplicates" in the Home tab.
• Step 3: Power Query removes the duplicate rows.

6. Cleaning Data — Filtering Rows

Definition: Filtering rows means keeping only the rows that meet certain conditions.

Why it is important: Filtering helps you focus on the data that matters.

Simple explanation: Imagine you have a list of all students, but you only want to see those in Grade 5. You would filter the list to show only Grade 5 students.

• Step 1: In the Power Query Editor, click the dropdown arrow on the column you want to filter.
• Step 2: Select the values you want to keep.
• Step 3: Power Query filters the rows.

7. Cleaning Data — Handling Nulls

Definition: A null is a blank or missing value in your data. Handling nulls means deciding what to do with them.

Why it is important: Nulls can cause errors in your reports.

Simple explanation: Imagine you have a list of students, but one student's grade is missing. You need to decide what to do — fill in a grade, or remove that student.

• Step 1: In the Power Query Editor, select the column with nulls.
• Step 2: Click "Replace Values" and replace null with a value (like 0 or "Unknown").
• Step 3: Or, click "Remove Rows" → "Remove Rows with Errors".

8. Shaping Data — Pivoting and Unpivoting

Definition: Pivoting turns rows into columns. Unpivoting turns columns into rows.

Why it is important: Sometimes your data is in the wrong shape for analysis. Pivoting and unpivoting fix that.

Simple explanation: Imagine you have a table with sales for each month in columns. You might want to have a single column for months and another for sales. That is unpivoting.

• Pivot: Select a column → Transform → Pivot Column.
• Unpivot: Select columns → Transform → Unpivot Columns.

9. Merging and Appending Data

Definition: Merging combines two tables based on a common column. Appending stacks two tables on top of each other.

Why it is important: Sometimes your data is in multiple tables. Merging and appending help you combine them.

Simple explanation: Imagine you have a list of students and a list of grades. You merge them to have one table with students and their grades.

• Merge: Home → Merge Queries.
• Append: Home → Append Queries.

10. Applied Steps — Tracking Your Transformations

Definition: Applied steps are the list of transformations you have applied to your data. You can see them on the right side of the Power Query Editor.

Why it is important: Applied steps help you track what you have done and make changes if needed.

Simple explanation: Think of applied steps like a recipe log. It shows each step you took to cook your data.

• Each step: Shows the transformation applied.
• Reorder: You can drag steps to change the order.
• Delete: You can delete steps that are not needed.
• Rename: You can rename steps to make them clearer.

📖 Key Vocabulary

Word	Simple Meaning
Power Query	A tool for cleaning and shaping data.
Power Query Editor	The window where you clean and transform data.
M Language	The language used by Power Query.
Data Transformation	Changing data to make it ready for analysis.
Data Cleaning	Removing errors and fixing mistakes in data.
Data Shaping	Changing the structure of data.
Duplicate	A row that appears more than once.
Filter	Keeping only certain rows.
Null	A blank or missing value.
Pivot	Turning rows into columns.
Unpivot	Turning columns into rows.
Merge	Combining two tables based on a common column.
Append	Stacking two tables on top of each other.
Applied Steps	The list of transformations applied.
M Code	The code written in the M language.

🧩 Important Concepts

• Power Query is used to clean and shape data.
• M is the language of Power Query.
• Removing duplicates keeps data accurate.
• Filtering helps you focus on what matters.
• Handling nulls prevents errors.
• Pivoting and unpivoting change data shape.
• Merging and appending combine data.
• Applied steps help you track transformations.

📌 Step-by-Step Explanations

How to clean data in Power Query

1. Open Power BI Desktop: Click the icon on your computer.
2. Get Data: Click "Get Data" and choose your data source.
3. Open Editor: Click "Transform Data" to open the Power Query Editor.
4. Remove Duplicates: Select the column(s) and click "Remove Duplicates".
5. Filter Rows: Click the dropdown arrow on a column and select values to keep.
6. Handle Nulls: Click "Replace Values" to fill nulls or "Remove Rows" to delete them.
7. Apply Changes: Click "Close & Apply" to save your changes.

How to shape data in Power Query

1. Open Power Query Editor: As above.
2. Pivot: Select a column, click "Transform", and choose "Pivot Column".
3. Unpivot: Select columns, click "Transform", and choose "Unpivot Columns".
4. Merge: Click "Merge Queries" and select the second table.
5. Append: Click "Append Queries" and select the second table.
6. Apply Changes: Click "Close & Apply".

🌍 Real-life Examples

• School: A teacher cleans student data by removing duplicates and filling in missing grades.
• Hospital: A hospital cleans patient data by removing errors and filling in missing information.
• Restaurant: A restaurant cleans sales data by filtering out cancelled orders.
• Shop: A shop cleans inventory data by removing duplicate product entries.

🇳🇬 Nigerian Examples

• Paystack: Uses Power Query to clean payment data.
• Flutterwave: Uses Power Query to clean transaction data.
• MTN Nigeria: Uses Power Query to clean customer data.
• A Lagos supermarket: Uses Power Query to clean sales data.
• A Nigerian bank: Uses Power Query to clean branch data.

🎈 Fun Examples for You

• Your toy collection: You can clean your toy list by removing duplicate toys.
• Your game scores: You can clean your game scores by removing errors.
• Your reading log: You can clean your reading log by filtering out incomplete entries.
• Your chores: You can clean your chore list by removing duplicates.

🏠 Everyday Examples

• At home: Your parents clean expense data by removing duplicate entries.
• At school: Your teacher cleans student data by removing errors.
• In your community: Local businesses clean sales data by filtering out returns.
• In your own life: You can clean your personal data by removing duplicates.

👩‍🏫 Teacher Notes

• Encourage students to think about messy data they have encountered.
• Use the warm-up story to spark interest in data cleaning.
• Demonstrate the Power Query Editor with a simple dataset.
• Discuss why cleaning data is important for accurate reports.
• Ask students to think about how they would clean different types of data.

👪 Parent Tips

• Talk to your child about how you clean data in your work or daily life.
• Show your child how you clean expense or other data.
• Download Power BI Desktop and explore Power Query together.
• Encourage your child to think about how they would clean different types of data.
• Share examples of Nigerian businesses using Power Query.

🧠 Interesting Facts

• Power Query was first introduced in Excel in 2010.
• The M language is named after the "M" in "Data Mashup."
• Power Query can connect to over 100 different data sources.
• Power Query is used by millions of people worldwide.
• You can use Power Query in Excel and Power BI.

💡 Did You Know?

• Did you know that Power Query can clean data from Nigerian bank statements?
• Did you know that you can use Power Query to clean data from web pages?
• Did you know that the M language is case-sensitive?
• Did you know that Power Query can automatically detect data types?
• Did you know that many Nigerian businesses use Power Query to clean data?

🔔 Remember This

• Power Query is for cleaning and shaping data.
• M is the language of Power Query.
• Removing duplicates keeps data accurate.
• Filtering helps you focus on what matters.
• Handling nulls prevents errors.
• Pivoting and unpivoting change data shape.
• Merging and appending combine data.
• Applied steps help you track transformations.

⚠️ Common Mistakes

• Not cleaning data: Using dirty data leads to wrong reports.
• Removing the wrong duplicates: Always check which rows you are removing.
• Over-filtering: Removing too much data can lead to incomplete reports.
• Ignoring nulls: Nulls can cause errors in your reports.
• Not renaming steps: It is hard to understand what you did if steps are not named.

✨ Best Practices for Power Query

• Always preview your data before cleaning.
• Remove duplicates carefully.
• Filter data to focus on what matters.
• Handle nulls to avoid errors.
• Rename your applied steps for clarity.
• Test your transformations on a small sample first.
• Keep a backup of your original data.

📊 Clear Illustrations

1. The Data Cleaning Process

    +-------------------+
    |  MESSY DATA       |  ← Data with errors, blanks, and mistakes
    +-------------------+
           |
           V
    +-------------------+
    |  REMOVE DUPLICATES |  ← Delete duplicate rows
    +-------------------+
           |
           V
    +-------------------+
    |  FILTER ROWS      |  ← Keep only what you need
    +-------------------+
           |
           V
    +-------------------+
    |  HANDLE NULLS     |  ← Fill or remove blanks
    +-------------------+
           |
           V
    +-------------------+
    |  CLEAN DATA       |  ← Ready for analysis
    +-------------------+
    

2. Pivoting vs. Unpivoting

    +-------------------+
    |  BEFORE           |  ← Region | Q1 | Q2 | Q3 | Q4
    +-------------------+
           |
           V
    +-------------------+
    |  UNPIVOT          |  ← Region | Quarter | Sales
    +-------------------+
    

3. Merging vs. Appending

    +-------------------+
    |  MERGE            |  ← Combine tables side by side
    +-------------------+
    |
    +-------------------+
    |  APPEND           |  ← Stack tables on top of each other
    +-------------------+
    

4. Comparison: Clean vs. Dirty Data

Dirty Data	Clean Data
Duplicates	No duplicates
Missing values	No missing values
Errors	No errors
Inconsistent formatting	Consistent formatting
Hard to analyse	Easy to analyse

📝 Lesson Summaries

📘 End-of-Module Summary

❓ Frequently Asked Questions

📝 Review Questions (15)

1. What is Power Query?
2. What is the Power Query Editor?
3. What is the M language?
4. Why is data cleaning important?
5. How do you remove duplicates?
6. What is filtering?
7. What are nulls?
8. How do you handle nulls?
9. What is the difference between pivoting and unpivoting?
10. What is the difference between merging and appending?
11. What are applied steps?
12. Give an example of a Nigerian business using Power Query.
13. What is the first step in cleaning data?
14. Why should you rename your applied steps?
15. What is the best practice for testing transformations?

✏️ Fill-in-the-Blank Exercises

1. Power Query is a tool for __________ and shaping data.
2. The __________ language is used by Power Query.
3. Removing __________ keeps data accurate.
4. Filtering helps you focus on what __________.
5. __________ are blank or missing values.

✅ True or False Exercises

1. Power Query is only for Excel. (False)
2. M is the language of Power Query. (True)
3. Removing duplicates keeps data accurate. (True)
4. Filtering removes all data. (False)
5. Handling nulls prevents errors. (True)

🔘 Multiple Choice Questions

1. What is Power Query?
   A) A tool for cleaning data B) A game C) A type of food D) A sport
   Answer: A
2. What is the M language?
   A) The language of Power Query B) A type of chart C) A database D) A filter
   Answer: A
3. What is the Power Query Editor?
   A) Where you clean data B) A game C) A type of food D) A sport
   Answer: A
4. Why is data cleaning important?
   A) It prevents errors B) It makes data messy C) It deletes data D) It hides data
   Answer: A
5. How do you remove duplicates?
   A) Click "Remove Duplicates" B) Click "Add Duplicates" C) Click "Delete All" D) Click "Save"
   Answer: A
6. What is filtering?
   A) Keeping only certain rows B) Deleting all rows C) Adding new rows D) Changing column names
   Answer: A
7. What are nulls?
   A) Blank or missing values B) Duplicate values C) Numbers D) Text
   Answer: A
8. What is the difference between pivoting and unpivoting?
   A) Pivoting turns rows into columns, unpivoting turns columns into rows B) They are the same C) Pivoting deletes data D) Unpivoting deletes data
   Answer: A
9. What is the difference between merging and appending?
   A) Merging combines tables side by side, appending stacks them B) They are the same C) Merging deletes data D) Appending deletes data
   Answer: A
10. What are applied steps?
    A) The list of transformations B) The data itself C) The charts D) The filters
    Answer: A
11. Which of these is a Nigerian business using Power Query?
    A) Paystack B) Flutterwave C) MTN D) All of the above
    Answer: D
12. What is the first step in cleaning data?
    A) Preview the data B) Delete the data C) Create a chart D) Save the data
    Answer: A
13. Why should you rename your applied steps?
    A) To make them clearer B) To delete them C) To hide them D) To change the data
    Answer: A
14. What is the best practice for testing transformations?
    A) Test on a small sample B) Test on all data C) Do not test D) Test after loading
    Answer: A
15. What is the purpose of Power Query?
    A) To clean and shape data B) To create charts C) To play games D) To send emails
    Answer: A

🔗 Matching Exercises

Match the word on the left with the correct meaning on the right:

Word	Meaning
Power Query	The language of Power Query
M Language	A tool for cleaning data
Duplicate	A row that appears more than once
Null	A blank or missing value

Answers: Power Query → A tool for cleaning data; M Language → The language of Power Query; Duplicate → A row that appears more than once; Null → A blank or missing value.

📝 Short Answer Questions

1. Explain Power Query in your own words.
2. Why is data cleaning important?
3. What is the M language?
4. How do you remove duplicates?
5. Give an example of a Nigerian business using Power Query.

🎭 Scenario-based Exercises

Scenario 1: Nneka has a list of students with their grades. Some students are listed twice, and some grades are missing. What should Nneka do? How can Power Query help her?

Scenario 2: A Lagos supermarket has sales data in different tables. They want to combine the data to see total sales by product. How can Power Query help them?

👥 Group Activity

In groups of 4–5, discuss a type of messy data you have encountered. Think about how you could use Power Query to clean it. Present your ideas to the class.

🧑 Individual Activity

Think about a dataset you could clean. Write a short paragraph (about 100 words) about how you would use Power Query to clean it.

🗣️ Classroom Discussion Questions

1. Why is data cleaning important?
2. What kind of messy data have you seen?
3. How can Power Query help Nigerian businesses?
4. What is the most interesting thing you learned about Power Query?
5. How do you think Power Query will change in the future?

🛠️ Mini Project

Clean a Dataset

Find a messy dataset (e.g., a list of sales with duplicates and missing values). Use Power Query to clean it. Document the steps you took and the final result.

📋 Practical Assignment

Download a sample dataset with errors. Use Power Query to clean it by removing duplicates, handling nulls, and filtering. Write a short report (about 150 words) about what you did and what you learned.

🏆 Challenge Exercise

The Challenge: Imagine you are a business analyst in Lagos. You have sales data from four different stores, each in a different format. Use Power Query to clean and combine the data. Create a single table with all sales data.

🔍 Quiz Answers

Multiple Choice: 1-A, 2-A, 3-A, 4-A, 5-A, 6-A, 7-A, 8-A, 9-A, 10-A, 11-D, 12-A, 13-A, 14-A, 15-A

Fill-in-the-Blank: 1. cleaning, 2. M, 3. duplicates, 4. matters, 5. Nulls

True or False: 1. False, 2. True, 3. True, 4. False, 5. True

Matching: Power Query → A tool for cleaning data; M Language → The language of Power Query; Duplicate → A row that appears more than once; Null → A blank or missing value.

🎯 Key Takeaways

• Power Query is a tool for cleaning and shaping data.
• M is the language of Power Query.
• Removing duplicates keeps data accurate.
• Filtering helps you focus on what matters.
• Handling nulls prevents errors.
• Pivoting and unpivoting change data shape.
• Merging and appending combine data.
• Applied steps help you track transformations.
• Nigerian businesses use Power Query to clean data.
• Anyone can learn to use Power Query.

🔜 Preparation for Module Three

In the next module, we will explore Data Modeling and Relationships. You will learn how to build robust data models and create relationships between tables. Before then, try to think about how different tables of data can be connected.

---

🎉 Congratulations! You have completed Module Two of the Power BI for Data Analysis Expert course.

👏 You are now ready to move to Module Three: Data Modeling and Relationships.

📐 Module Three: Data Modeling and Relationships

Welcome back, young data builder! Today we learn how to build a data model and create relationships.

🌟 Module Introduction

In Module Two, you learned how to clean and shape your data using Power Query. Now you have clean data. But how do you connect different tables together? That is what data modeling is all about.

Imagine you have a box of Lego bricks. You can build anything if you know how the pieces fit together. Data modeling is like that — it is about connecting your data tables so they work together.

A data model is the structure of your data. It shows how different tables are related. Relationships are the connections between tables.

In this module, you will learn about the star schema, how to create relationships, and how to build hierarchies. By the end, you will be able to build a robust data model for your reports.

🎯 Learning Objectives

By the time you finish this module, you will be able to:

• Explain what a data model is and why it is important.
• Understand the star schema design pattern.
• Identify fact tables and dimension tables.
• Create relationships between tables.
• Build hierarchies for drill-down.
• Create date tables for time intelligence.
• Give examples of data modeling in Nigeria.

📖 Warm-up Story: Ada's Lego Castle

Ada is a 12-year-old girl who loves building with Lego. She has many boxes of Lego bricks — some with bricks for walls, some for roofs, and some for doors. She wanted to build a big castle. But she had to figure out how to connect all the different pieces.

Her uncle, who works with data, watched her. He said, "Ada, building a castle is like building a data model. You have different pieces (tables) that need to fit together. You need to know how they connect."

Ada learned that each Lego piece has a special way to connect to others. In data modeling, tables connect through relationships.

Ada built her castle. Her uncle said, "Now you understand data modeling!"

📚 Main Lessons

1. What is a Data Model?

Definition: A data model is the structure of your data. It shows how different tables are connected to each other.

Why it is important: A good data model makes your reports fast, accurate, and easy to build.

Simple explanation: Think of a data model like a map of your data. It shows how different pieces of information are connected.

Illustration:

    +-------------------+
    |  DATA MODEL       |  ← The structure of your data
    +-------------------+
    |  Table 1          |  ← Connected to Table 2
    |  Table 2          |  ← Connected to Table 3
    |  Table 3          |  ← Connected to Table 1
    +-------------------+
    

2. The Star Schema

Definition: The star schema is a common data modeling pattern. It has fact tables in the centre and dimension tables around them.

Why it is important: The star schema is the best way to organize your data for fast and easy analysis.

Simple explanation: Think of the star schema like a star — the fact table is in the centre, and the dimension tables are the points of the star.

• Fact Table: Contains numbers and measures (e.g., Sales).
• Dimension Table: Contains descriptive information (e.g., Products, Customers).

3. Fact Tables

Definition: A fact table contains numbers and measures. It is the centre of your data model.

Why it is important: Fact tables store the data you want to analyze.

Simple explanation: Think of a fact table like the scoreboard of a game. It shows the numbers.

• Sales Amount: How much you sold.
• Quantity: How many items you sold.
• Profit: How much money you made.
• Foreign Keys: Links to dimension tables (e.g., ProductID, CustomerID).

4. Dimension Tables

Definition: A dimension table contains descriptive information. It provides context for the numbers in the fact table.

Why it is important: Dimension tables help you understand your data.

Simple explanation: Think of a dimension table like the labels on a chart. It tells you what the numbers mean.

• Product Name: The name of the product.
• Customer Name: The name of the customer.
• Region: Where the sale happened.
• Date: When the sale happened.

5. Relationships — Connecting Tables

Definition: A relationship is a connection between two tables. It tells Power BI how the tables are related.

Why it is important: Relationships allow you to combine data from different tables in your reports.

Simple explanation: Think of a relationship like a bridge between two islands. It connects them so you can travel between them.

• One-to-Many: One row in a table relates to many rows in another table.
• Many-to-One: Many rows relate to one row.
• Many-to-Many: Many rows relate to many rows.

6. Creating Relationships in Power BI

Definition: In Power BI, you create relationships in the Model View.

Why it is important: Creating relationships is essential for combining data from different tables.

Simple explanation: Think of it like connecting Lego pieces. You find the right pieces and snap them together.

• Step 1: Open Power BI Desktop.
• Step 2: Click on the Model View icon.
• Step 3: Drag from one table to another to create a relationship.
• Step 4: Select the columns that match.
• Step 5: Click OK.

7. Hierarchies — Drill-Down

Definition: A hierarchy is a group of columns that allow you to drill down from summary to detail.

Why it is important: Hierarchies make it easy for users to explore data at different levels.

Simple explanation: Think of a hierarchy like a family tree. You can start at the top and go down to see more detail.

• Date Hierarchy: Year → Quarter → Month → Day.
• Geography Hierarchy: Country → Region → City.
• Product Hierarchy: Category → Subcategory → Product.

8. Date Tables

Definition: A date table is a special table that contains dates and date-related columns (year, month, quarter).

Why it is important: Date tables are essential for time intelligence calculations.

Simple explanation: Think of a date table like a calendar. It helps you organize your data by time.

• Date: The actual date.
• Year: The year of the date.
• Month: The month of the date.
• Quarter: The quarter of the date.

9. Calculated Tables

Definition: A calculated table is a new table created from a DAX formula.

Why it is important: Calculated tables allow you to create new data that is not in your original data.

Simple explanation: Think of a calculated table like a recipe. You take existing ingredients and make something new.

• Date Table: Created using CALENDAR or CALENDARAUTO.
• Top Products: Created using TOPN.
• Summary Table: Created using GROUPBY.

10. Data Modeling in Nigerian Businesses

Definition: Nigerian businesses use data modeling to organize their data for analysis.

Why it is important: Data modeling helps Nigerian businesses make better decisions.

Simple explanation: Think of it like building a house. You need a good foundation.

• Banks: Model customer, account, and transaction data.
• Telecom: Model subscriber, usage, and billing data.
• Retail: Model product, store, and sales data.
• Government: Model citizen, service, and budget data.

📖 Key Vocabulary

Word	Simple Meaning
Data Model	The structure of your data.
Star Schema	A data model with a fact table and dimension tables.
Fact Table	A table with numbers and measures.
Dimension Table	A table with descriptive information.
Relationship	A connection between two tables.
One-to-Many	One row relates to many rows.
Hierarchy	A group of columns for drill-down.
Date Table	A table with dates and date-related columns.
Calculated Table	A new table created from a formula.
DAX	The formula language of Power BI.
Model View	The view where you create relationships.
Foreign Key	A link to another table.
Primary Key	A unique identifier for a row.
Time Intelligence	Analyzing data by time.
Schema	The structure of a database.

🧩 Important Concepts

• A data model is the structure of your data.
• The star schema has a fact table and dimension tables.
• Fact tables contain numbers.
• Dimension tables provide context.
• Relationships connect tables.
• Hierarchies allow drill-down.
• Date tables are for time analysis.
• Calculated tables create new data.
• Nigerian businesses use data modeling.

📌 Step-by-Step Explanations

How to create a star schema

1. Identify the fact table: Which table has the numbers (e.g., Sales)?
2. Identify the dimension tables: Which tables provide context (e.g., Product, Customer, Date)?
3. Create relationships: Connect the fact table to each dimension table.
4. Create hierarchies: Build hierarchies in dimension tables (e.g., Year → Quarter → Month).
5. Create a date table: If you don't have one, create a date table.
6. Test your model: Build a simple report to test your model.

How to create a relationship

1. Open Power BI Desktop: Open your report.
2. Click Model View: Click the Model View icon on the left.
3. Find the tables: Find the two tables you want to connect.
4. Drag: Drag from one table to the other.
5. Select columns: Choose the columns that match.
6. Click OK: Click OK to create the relationship.

🌍 Real-life Examples

• School: A data model for students, classes, and grades.
• Hospital: A data model for patients, doctors, and appointments.
• Restaurant: A data model for orders, customers, and menu items.
• Shop: A data model for sales, products, and customers.

🇳🇬 Nigerian Examples

• Paystack: A data model for payments, merchants, and customers.
• Flutterwave: A data model for transactions, users, and currencies.
• MTN Nigeria: A data model for subscribers, usage, and billing.
• A Lagos supermarket: A data model for sales, products, and categories.
• A Nigerian bank: A data model for accounts, customers, and transactions.

🎈 Fun Examples for You

• Your toy collection: A data model for toys, categories, and storage.
• Your game scores: A data model for games, scores, and dates.
• Your reading log: A data model for books, authors, and dates.
• Your chores: A data model for chores, categories, and completion dates.

🏠 Everyday Examples

• At home: A data model for expenses, categories, and dates.
• At school: A data model for students, classes, and grades.
• In your community: A data model for local businesses, products, and sales.
• In your own life: A data model for your goals, tasks, and dates.

👩‍🏫 Teacher Notes

• Encourage students to think about how different pieces of information can be connected.
• Use the Lego analogy to explain data modeling.
• Demonstrate creating relationships in Power BI.
• Discuss the importance of star schemas.
• Ask students to think about data models for Nigerian businesses.

👪 Parent Tips

• Talk to your child about how different pieces of information are connected in your work.
• Show your child how you organize data in spreadsheets.
• Encourage your child to think about how they would connect different pieces of information.
• Share examples of data modeling in Nigerian businesses.
• Help your child create a simple data model with paper and pen.

🧠 Interesting Facts

• The star schema was invented in the 1990s.
• Fact tables can have billions of rows.
• Dimension tables are usually much smaller than fact tables.
• Date tables are essential for time intelligence.
• Data modeling is used in almost every industry.

💡 Did You Know?

• Did you know that Nigerian banks use data modeling to detect fraud?
• Did you know that data modeling helps you create faster reports?
• Did you know that the star schema is the most common data model in Power BI?
• Did you know that you can create a date table with just one line of DAX?
• Did you know that data modeling is used in almost every business in Nigeria?

🔔 Remember This

• A data model is the structure of your data.
• The star schema has a fact table and dimension tables.
• Fact tables contain numbers.
• Dimension tables provide context.
• Relationships connect tables.
• Hierarchies allow drill-down.
• Date tables are for time analysis.
• Nigerian businesses use data modeling.

⚠️ Common Mistakes

• Not creating relationships: Without relationships, tables cannot work together.
• Using the wrong relationship type: One-to-many is most common.
• Not using a date table: Time analysis is hard without a date table.
• Not using hierarchies: Users need to drill down.
• Ignoring the star schema: Other schemas are less efficient.

✨ Best Practices for Data Modeling

• Use the star schema design pattern.
• Create relationships between fact and dimension tables.
• Use a date table for time analysis.
• Build hierarchies in dimension tables.
• Name your tables and columns clearly.
• Test your model with a simple report.
• Keep your model simple and easy to understand.

📊 Clear Illustrations

1. The Star Schema

2. One-to-Many Relationship

    +-------------------+        +-------------------+
    |  PRODUCT          |        |  SALES            |
    |  (One)            |        |  (Many)           |
    |  - ProductID      |  1   N |  - ProductID      |
    |  - ProductName    |<------>|  - SalesAmount    |
    +-------------------+        +-------------------+
    

3. Hierarchy Example

    +-------------------+
    |  YEAR             |  ← Top level
    +-------------------+
           |
           V
    +-------------------+
    |  QUARTER          |  ← Middle level
    +-------------------+
           |
           V
    +-------------------+
    |  MONTH            |  ← Bottom level
    +-------------------+
    

4. Comparison: Good vs Bad Data Model

Good Data Model	Bad Data Model
Star schema	No clear structure
Clear relationships	No relationships
Date table	No date table
Hierarchies	No hierarchies
Fast reports	Slow reports

📝 Lesson Summaries

📘 End-of-Module Summary

❓ Frequently Asked Questions

📝 Review Questions (15)

1. What is a data model?
2. What is the star schema?
3. What is a fact table?
4. What is a dimension table?
5. What is a relationship?
6. What is a one-to-many relationship?
7. What is a hierarchy?
8. What is a date table?
9. What is a calculated table?
10. How do you create a relationship in Power BI?
11. Give an example of a fact table.
12. Give an example of a dimension table.
13. Why is a date table important?
14. Give an example of a Nigerian business using data modeling.
15. What is the best practice for data modeling?

✏️ Fill-in-the-Blank Exercises

1. A __________ table contains numbers and measures.

2. A __________ table provides descriptive information.

3. A __________ is a connection between two tables.

4. A __________ is a group of columns for drill-down.

5. A __________ table is needed for time analysis.

✅ True or False Exercises

1. Fact tables contain numbers. (True)
2. Dimension tables provide context. (True)
3. Relationships are not needed in data models. (False)
4. Hierarchies allow drill-down. (True)
5. A date table is optional. (False)

🔘 Multiple Choice Questions

1. What is a fact table?
   A) A table with numbers B) A table with descriptions C) A table with dates D) A table with text
   Answer: A
2. What is a dimension table?
   A) A table with numbers B) A table with descriptions C) A table with dates D) A table with text
   Answer: B
3. What is a relationship?
   A) A connection between tables B) A type of chart C) A filter D) A date
   Answer: A
4. What is a one-to-many relationship?
   A) One row relates to many rows B) Many rows relate to one row C) Many rows relate to many rows D) One row relates to one row
   Answer: A
5. What is a hierarchy?
   A) A group of columns for drill-down B) A type of chart C) A filter D) A date
   Answer: A
6. Why do you need a date table?
   A) For time analysis B) For numbers C) For text D) For charts
   Answer: A
7. What is a calculated table?
   A) A new table from a formula B) A table with numbers C) A table with text D) A table with dates
   Answer: A
8. Which of these is a Nigerian business using data modeling?
   A) Paystack B) Flutterwave C) MTN D) All of the above
   Answer: D
9. What is the star schema?
   A) A fact table with dimension tables B) A single table C) A type of chart D) A filter
   Answer: A
10. What is the best practice for data modeling?
    A) Use the star schema B) Use no relationships C) Use no date table D) Use no hierarchies
    Answer: A
11. What is the Model View used for?
    A) Creating relationships B) Creating charts C) Creating filters D) Creating dates
    Answer: A
12. What is a foreign key?
    A) A link to another table B) A type of chart C) A filter D) A date
    Answer: A
13. What is a primary key?
    A) A unique identifier B) A type of chart C) A filter D) A date
    Answer: A
14. What is time intelligence?
    A) Analyzing data by time B) Analyzing data by numbers C) Analyzing data by text D) Analyzing data by charts
    Answer: A
15. What is a schema?
    A) The structure of a database B) A type of chart C) A filter D) A date
    Answer: A

🔗 Matching Exercises

Match the word on the left with the correct meaning on the right:

Word	Meaning
Fact Table	Provides context
Dimension Table	Contains numbers
Relationship	Connects tables
Hierarchy	Allows drill-down

Answers: Fact Table → Contains numbers; Dimension Table → Provides context; Relationship → Connects tables; Hierarchy → Allows drill-down.

📝 Short Answer Questions

1. Explain what a data model is.
2. What is the star schema?
3. What is the difference between a fact table and a dimension table?
4. Why is a date table important?
5. Give an example of a Nigerian business using data modeling.

🎭 Scenario-based Exercises

Scenario 1: A Lagos supermarket has sales data and product data. They want to see sales by product category. How should they model their data?

Scenario 2: A Nigerian bank has customer data and transaction data. They want to analyze transactions by customer region. How should they model their data?

👥 Group Activity

In groups of 4–5, design a data model for a school. Include fact tables and dimension tables. Present your model to the class.

🧑 Individual Activity

Think about a data model for a Nigerian business. Write a short paragraph (about 100 words) describing the fact table and dimension tables.

🗣️ Classroom Discussion Questions

1. Why is data modeling important?
2. What is the most common data model in Power BI?
3. How can Nigerian businesses benefit from good data modeling?
4. What is the most interesting thing you learned about data modeling?
5. How do you think data modeling will change in the future?

🛠️ Mini Project

Design a Data Model

Design a data model for a business of your choice. Include a fact table, dimension tables, relationships, and hierarchies. Present your model to the class.

📋 Practical Assignment

Use Power BI Desktop to create a data model with at least two tables and a relationship. Write a short report (about 150 words) about what you did and what you learned.

🏆 Challenge Exercise

The Challenge: Imagine you are a data analyst in Lagos. You have sales data, product data, and customer data. Create a star schema data model. Include a date table. Use the model to answer a business question.

🔍 Quiz Answers

Multiple Choice: 1-A, 2-B, 3-A, 4-A, 5-A, 6-A, 7-A, 8-D, 9-A, 10-A, 11-A, 12-A, 13-A, 14-A, 15-A

Fill-in-the-Blank: 1. fact, 2. dimension, 3. relationship, 4. hierarchy, 5. date

True or False: 1. True, 2. True, 3. False, 4. True, 5. False

Matching: Fact Table → Contains numbers; Dimension Table → Provides context; Relationship → Connects tables; Hierarchy → Allows drill-down.

🎯 Key Takeaways

• A data model is the structure of your data.
• The star schema has a fact table and dimension tables.
• Fact tables contain numbers.
• Dimension tables provide context.
• Relationships connect tables.
• Hierarchies allow drill-down.
• Date tables are for time analysis.
• Nigerian businesses use data modeling.

🔜 Preparation for Module Four

In the next module, we will explore DAX — Advanced Data Analysis Expressions. You will learn how to create measures, use time intelligence, and write powerful DAX formulas.

---

🎉 Congratulations! You have completed Module Three of the Certified Power BI for Data Analysis Expert course.

👏 You are now ready to move to Module Four: DAX — Advanced Data Analysis Expressions.

📐 Module Four: DAX — Advanced Data Analysis Expressions

Welcome back, young data analyst! Today we learn the language of Power BI — DAX.

🌟 Module Introduction

In Module Three, you learned how to build a data model and create relationships. Now you have a solid foundation. But how do you create calculations in Power BI? That is where DAX comes in.

DAX stands for Data Analysis Expressions. It is the formula language of Power BI. Think of DAX like the secret sauce that makes your reports powerful.

Imagine you have a box of ingredients (your data). DAX is the recipe that tells you how to combine them to make something delicious (insights).

In this module, you will learn about measures, calculated columns, CALCULATE, time intelligence, and variables. By the end, you will be able to create powerful calculations in Power BI.

🎯 Learning Objectives

By the time you finish this module, you will be able to:

• Explain what DAX is and why it is important.
• Understand the difference between measures and calculated columns.
• Create measures using SUM, AVERAGE, COUNT, and other functions.
• Use CALCULATE to modify filter context.
• Use time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR.
• Use variables to write cleaner DAX.
• Give examples of DAX in Nigerian businesses.

📖 Warm-up Story: Ada's Magic Recipe

Ada is a 12-year-old girl who loves to bake. She has a recipe book with simple recipes. One day, she wanted to make a special cake. She had all the ingredients, but she needed a special recipe to combine them.

Her uncle, who works with data, said, "Ada, DAX is like a recipe. You have your ingredients (data), and DAX tells you how to combine them to get the result you want."

Ada started learning DAX. She learned how to create measures (like total sales) and calculated columns (like profit margin). She discovered that DAX was the secret sauce that made her reports powerful.

Ada baked her cake and used DAX to analyze the sales of her bakery. She learned that DAX helps you understand your data.

📚 Main Lessons

1. What is DAX?

Definition: DAX (Data Analysis Expressions) is the formula language of Power BI. It is used to create calculations and measures.

Why it is important: DAX is what makes Power BI powerful. Without DAX, you can only do basic analysis.

Simple explanation: Think of DAX like a recipe for your data. It tells Power BI how to combine and calculate your data.

2. Measures vs. Calculated Columns

Definition: Measures are calculations that are performed at query time. Calculated columns are calculations that are performed at load time.

Why it is important: Understanding the difference helps you choose the right tool for the job.

Simple explanation: Think of measures like on-the-fly calculations. They are calculated when you need them. Calculated columns are pre-calculated and stored in your data.

Feature	Measures	Calculated Columns
When calculated	At query time (dynamic)	At load time (static)
Performance	Fast	Slower
Storage	No storage	Uses storage
Use case	Aggregations, KPIs	Row-level logic
Example	Total Sales = SUM(Sales[Amount])	Profit = [Sales] - [Cost]

3. Creating Measures

Definition: A measure is a calculation that you create in Power BI. It is used to summarize your data.

Why it is important: Measures are the most common way to create calculations in Power BI.

Simple explanation: Think of a measure like a calculator that always gives you the right answer based on the current context.

• Step 1: In Power BI Desktop, select a table in the Fields pane.
• Step 2: Right-click and select "New Measure".
• Step 3: Write your DAX formula.
• Step 4: Name your measure and press Enter.

4. Creating Calculated Columns

Definition: A calculated column is a new column added to a table using a DAX formula.

Why it is important: Calculated columns are useful for row-level calculations.

Simple explanation: Think of a calculated column like a new ingredient added to your recipe. It is calculated once and stored.

• Step 1: In Power BI Desktop, select a table in the Fields pane.
• Step 2: Click "New Column" in the Table Tools tab.
• Step 3: Write your DAX formula.
• Step 4: Name your column and press Enter.

5. CALCULATE — The Most Powerful DAX Function

Definition: CALCULATE is a DAX function that modifies the filter context of a calculation.

Why it is important: CALCULATE is the most important function in DAX. It allows you to create complex calculations.

Simple explanation: Think of CALCULATE like a magnifying glass that focuses on specific parts of your data.

• Syntax: CALCULATE(expression, filter1, filter2, ...)
• All: Removes filters.
• Filter: Adds a filter.

6. Time Intelligence Functions

Definition: Time intelligence functions are DAX functions that help you analyze data over time.

Why it is important: Time intelligence is essential for business analysis.

Simple explanation: Think of time intelligence like a calendar that helps you see how your data changes over time.

• TOTALYTD: Year-to-date total.
• SAMEPERIODLASTYEAR: Same period last year.
• DATESBETWEEN: Dates between two values.
• DATEADD: Add or subtract dates.

7. Variables in DAX

Definition: Variables are used to store intermediate results in DAX formulas.

Why it is important: Variables make your DAX formulas easier to read and faster to run.

Simple explanation: Think of variables like containers that hold values while you work.

• VAR: Declares a variable.
• RETURN: Returns the result.
• Benefits: Readability, performance, and reusability.

8. Common DAX Functions

Definition: DAX has many functions for different tasks. Here are some of the most common ones.

Why it is important: Knowing common functions helps you write DAX faster.

Simple explanation: Think of functions like tools in a toolbox. Each one does a specific job.

• SUM: Adds numbers.
• AVERAGE: Calculates the average.
• COUNT: Counts rows.
• MAX: Finds the maximum value.
• MIN: Finds the minimum value.
• DISTINCTCOUNT: Counts distinct values.
• IF: Conditional logic.
• SWITCH: Multiple conditions.

9. DAX in Nigerian Businesses

Definition: Nigerian businesses use DAX to create powerful calculations in Power BI.

Why it is important: DAX helps Nigerian businesses make better decisions.

Simple explanation: Think of it like a secret weapon for Nigerian businesses.

• Banks: Use DAX to calculate NPL ratios.
• Telecom: Use DAX to calculate churn rates.
• Retail: Use DAX to calculate sales growth.
• Government: Use DAX to track budget performance.

10. Best Practices for DAX

Definition: Best practices are the recommended ways to write DAX.

Why it is important: Following best practices makes your DAX faster and easier to maintain.

Simple explanation: Think of best practices like rules of the road that keep everyone safe.

• Use measures over calculated columns: Measures are more performant.
• Use variables: Variables improve readability and performance.
• Avoid using CALCULATE unnecessarily: CALCULATE is powerful but can be slow.
• Use the right function: Choose the right function for the job.
• Test your formulas: Test your DAX with sample data.

📖 Key Vocabulary

Word	Simple Meaning
DAX	The formula language of Power BI.
Measure	A dynamic calculation.
Calculated Column	A static calculation.
CALCULATE	The most powerful DAX function.
Time Intelligence	Analyzing data over time.
Variable	A container for intermediate results.
SUM	Adds numbers.
AVERAGE	Calculates the average.
COUNT	Counts rows.
DISTINCTCOUNT	Counts distinct values.
IF	Conditional logic.
SWITCH	Multiple conditions.
TOTALYTD	Year-to-date total.
ALL	Removes filters.
DIVIDE	Divides two numbers.

🧩 Important Concepts

• DAX is the formula language of Power BI.
• Measures are dynamic; calculated columns are static.
• CALCULATE modifies filter context.
• Time intelligence functions analyze data over time.
• Variables make DAX cleaner and faster.
• DAX has many functions for different tasks.
• Nigerian businesses use DAX to make better decisions.
• Following best practices makes your DAX better.

📌 Step-by-Step Explanations

How to create a measure

1. Open Power BI Desktop: Open your report.
2. Select a table: In the Fields pane, select a table.
3. Right-click: Right-click and select "New Measure".
4. Write the formula: Write your DAX formula.
5. Name the measure: Give it a name and press Enter.
6. Test: Drag the measure into a visualization to test it.

How to use CALCULATE

1. Write the expression: Start with the expression you want to calculate.
2. Add a filter: Add a filter to modify the context.
3. Test: Test your CALCULATE formula with different filters.

🌍 Real-life Examples

• School: A teacher uses DAX to calculate average grades.
• Hospital: A hospital uses DAX to calculate patient satisfaction scores.
• Restaurant: A restaurant uses DAX to calculate total sales.
• Shop: A shop uses DAX to calculate profit margins.

🇳🇬 Nigerian Examples

• Paystack: Uses DAX to calculate transaction volumes.
• Flutterwave: Uses DAX to calculate payment success rates.
• MTN Nigeria: Uses DAX to calculate churn rates.
• A Lagos supermarket: Uses DAX to calculate year-over-year growth.
• A Nigerian bank: Uses DAX to calculate NPL ratios.

🎈 Fun Examples for You

• Your pocket money: Use DAX to calculate total pocket money.
• Your game scores: Use DAX to calculate average score.
• Your reading log: Use DAX to count books read.
• Your chores: Use DAX to calculate completion rate.

🏠 Everyday Examples

• At home: Use DAX to calculate total expenses.
• At school: Use DAX to calculate average grades.
• In your community: Use DAX to calculate sales totals.
• In your own life: Use DAX to track your goals.

👩‍🏫 Teacher Notes

• Encourage students to think about DAX as a recipe.
• Use the warm-up story to spark interest in DAX.
• Demonstrate creating a measure in Power BI.
• Discuss the importance of CALCULATE.
• Ask students to think about how DAX can be used in Nigerian businesses.

👪 Parent Tips

• Talk to your child about how you use calculations in your work.
• Show your child how you calculate totals and averages.
• Encourage your child to think about how they would use DAX.
• Share examples of DAX in Nigerian businesses.
• Help your child create a simple measure in Power BI.

🧠 Interesting Facts

• DAX was first introduced in 2010.
• DAX is similar to Excel formulas.
• DAX has over 200 functions.
• CALCULATE is the most used function.
• Time intelligence functions are essential for business analysis.

💡 Did You Know?

• Did you know that DAX stands for Data Analysis Expressions?
• Did you know that CALCULATE can modify filter context?
• Did you know that time intelligence functions require a date table?
• Did you know that variables can improve DAX performance?
• Did you know that Nigerian banks use DAX for financial analysis?

🔔 Remember This

• DAX is the formula language of Power BI.
• Measures are dynamic; calculated columns are static.
• CALCULATE modifies filter context.
• Time intelligence functions analyze data over time.
• Variables make DAX cleaner and faster.
• Nigerian businesses use DAX to make better decisions.

⚠️ Common Mistakes

• Using calculated columns for everything: Use measures for aggregations.
• Not using CALCULATE: CALCULATE is essential for many calculations.
• Ignoring time intelligence: Time intelligence is crucial for business analysis.
• Not using variables: Variables improve readability and performance.
• Using the wrong function: Choose the right function for the job.

✨ Best Practices for DAX

• Use measures over calculated columns.
• Use variables to improve readability.
• Use CALCULATE when you need to modify filter context.
• Use time intelligence functions for date analysis.
• Test your DAX formulas with sample data.
• Name your measures clearly.
• Document complex formulas.

📊 Clear Illustrations

1. Measures vs. Calculated Columns

    +-------------------+        +-------------------+
    |  MEASURES         |        |  CALCULATED       |
    |  (Dynamic)        |        |  COLUMNS          |
    |                   |        |  (Static)         |
    |  Calculated at    |        |  Calculated at    |
    |  query time       |        |  load time        |
    +-------------------+        +-------------------+
    

2. CALCULATE Function

    +-------------------+
    |  CALCULATE        |
    |  (Expression,     |
    |   Filter1,        |
    |   Filter2, ...)   |
    +-------------------+
           |
           V
    +-------------------+
    |  Modified         |
    |  Filter Context   |
    +-------------------+
    

3. Time Intelligence

    +-------------------+
    |  TODAY            |
    +-------------------+
           |
           V
    +-------------------+
    |  YTD              |  ← Year-to-date
    +-------------------+
           |
           V
    +-------------------+
    |  Last Year        |  ← Same period last year
    +-------------------+
    

4. Comparison: Good vs Bad DAX

Good DAX	Bad DAX
Uses measures	Uses calculated columns for aggregations
Uses variables	No variables
Uses CALCULATE correctly	Overuses CALCULATE
Clear naming	Unclear naming
Fast performance	Slow performance

📝 Lesson Summaries

📘 End-of-Module Summary

❓ Frequently Asked Questions

📝 Review Questions (15)

1. What is DAX?
2. What is the difference between a measure and a calculated column?
3. What is CALCULATE?
4. What are time intelligence functions?
5. What are variables in DAX?
6. What is the most powerful DAX function?
7. Why do you need a date table?
8. What is SUM?
9. What is DISTINCTCOUNT?
10. Give an example of a measure.
11. Give an example of a calculated column.
12. How do Nigerian businesses use DAX?
13. What is the benefit of using variables?
14. What is the syntax of CALCULATE?
15. What is the best practice for using measures?

✏️ Fill-in-the-Blank Exercises

1. DAX stands for __________ __________ Expressions.
2. A __________ is a dynamic calculation.
3. A __________ column is a static calculation.
4. __________ is the most powerful DAX function.
5. __________ intelligence functions analyze data over time.

✅ True or False Exercises

1. DAX is the formula language of Power BI. (True)
2. Measures are static. (False)
3. Calculated columns are dynamic. (False)
4. CALCULATE modifies filter context. (True)
5. Time intelligence functions require a date table. (True)

🔘 Multiple Choice Questions

1. What does DAX stand for?
   A) Data Analysis Expressions B) Data Advanced Expressions C) Digital Analysis Expressions D) Data Analysis Extensions
   Answer: A
2. What is a measure?
   A) A dynamic calculation B) A static calculation C) A table D) A chart
   Answer: A
3. What is a calculated column?
   A) A dynamic calculation B) A static calculation C) A table D) A chart
   Answer: B
4. What is CALCULATE?
   A) A function that modifies filter context B) A function that creates tables C) A function that creates charts D) A function that deletes data
   Answer: A
5. What are time intelligence functions?
   A) Functions that analyze data over time B) Functions that analyze data by location C) Functions that analyze data by product D) Functions that analyze data by customer
   Answer: A
6. What is the benefit of using variables?
   A) Readability and performance B) Slower performance C) More storage D) Less flexibility
   Answer: A
7. What is SUM?
   A) A function that adds numbers B) A function that subtracts numbers C) A function that multiplies numbers D) A function that divides numbers
   Answer: A
8. What is DISTINCTCOUNT?
   A) A function that counts distinct values B) A function that counts all values C) A function that counts duplicates D) A function that counts errors
   Answer: A
9. Which of these is a Nigerian business using DAX?
   A) Paystack B) Flutterwave C) MTN D) All of the above
   Answer: D
10. What is the most powerful DAX function?
    A) CALCULATE B) SUM C) AVERAGE D) COUNT
    Answer: A
11. What is the syntax of CALCULATE?
    A) CALCULATE(expression, filter1, filter2, ...) B) CALCULATE(expression) C) CALCULATE(filter) D) CALCULATE(table)
    Answer: A
12. What is the best practice for using measures?
    A) Use measures for aggregations B) Use measures for row-level logic C) Use measures for static data D) Use measures for text
    Answer: A
13. What is the purpose of a date table?
    A) For time intelligence B) For text analysis C) For number analysis D) For chart creation
    Answer: A
14. What is IF in DAX?
    A) A conditional function B) A math function C) A text function D) A date function
    Answer: A
15. What is SWITCH in DAX?
    A) A function for multiple conditions B) A function for single conditions C) A math function D) A text function
    Answer: A

🔗 Matching Exercises

Match the word on the left with the correct meaning on the right:

Word	Meaning
DAX	Modifies filter context
Measure	The formula language of Power BI
Calculated Column	A dynamic calculation
CALCULATE	A static calculation

Answers: DAX → The formula language of Power BI; Measure → A dynamic calculation; Calculated Column → A static calculation; CALCULATE → Modifies filter context.

📝 Short Answer Questions

1. Explain DAX in your own words.
2. What is the difference between a measure and a calculated column?
3. What is CALCULATE and why is it important?
4. What are time intelligence functions?
5. Give an example of a Nigerian business using DAX.

🎭 Scenario-based Exercises

Scenario 1: A Lagos supermarket wants to calculate year-to-date sales. What DAX formula should they use?

Scenario 2: A Nigerian bank wants to calculate the percentage of total sales by region. What DAX formula should they use?

👥 Group Activity

In groups of 4–5, discuss how you would use DAX to analyze sales data. Create a list of measures you would create. Present your ideas to the class.

🧑 Individual Activity

Think about a business question you would like to answer with DAX. Write a short paragraph (about 100 words) about the measure you would create.

🗣️ Classroom Discussion Questions

1. Why is DAX important in Power BI?
2. What is the most useful DAX function and why?
3. How can Nigerian businesses benefit from DAX?
4. What is the most interesting thing you learned about DAX?
5. How do you think DAX will change in the future?

🛠️ Mini Project

Create a DAX Measure

Choose a business question and create a DAX measure to answer it. Include the formula, explanation, and a screenshot of the result.

📋 Practical Assignment

Use Power BI Desktop to create a measure that calculates total sales. Create a second measure that calculates year-to-date sales. Write a short report (about 150 words) about what you did and what you learned.

🏆 Challenge Exercise

The Challenge: Imagine you are a data analyst in Lagos. You have sales data for four regions. Create a DAX measure that calculates the percentage of total sales for each region. Use CALCULATE and ALL.

🔍 Quiz Answers

Multiple Choice: 1-A, 2-A, 3-B, 4-A, 5-A, 6-A, 7-A, 8-A, 9-D, 10-A, 11-A, 12-A, 13-A, 14-A, 15-A

Fill-in-the-Blank: 1. Data Analysis, 2. measure, 3. calculated, 4. CALCULATE, 5. Time

True or False: 1. True, 2. False, 3. False, 4. True, 5. True

Matching: DAX → The formula language of Power BI; Measure → A dynamic calculation; Calculated Column → A static calculation; CALCULATE → Modifies filter context.

🎯 Key Takeaways

• DAX is the formula language of Power BI.
• Measures are dynamic; calculated columns are static.
• CALCULATE modifies filter context.
• Time intelligence functions analyze data over time.
• Variables make DAX cleaner and faster.
• Nigerian businesses use DAX to make better decisions.
• Following best practices makes your DAX better.

🔜 Preparation for Module Five

In the next module, we will explore Data Visualizations and Reports. You will learn how to create powerful visualizations and build interactive reports.

---

🎉 Congratulations! You have completed Module Four of the Certified Power BI for Data Analysis Expert course.

👏 You are now ready to move to Module Five: Data Visualizations and Reports.

📊 Module Five: Data Visualizations and Reports

Welcome back, young data artist! Today we learn how to tell stories with data using visualizations.

🌟 Module Introduction

In Module Four, you learned how to create powerful calculations with DAX. Now you have the numbers. But how do you show them to others? That is where data visualizations come in.

Imagine you have a story to tell. You could just read it out loud, but it would be much more interesting with pictures. Data visualizations are the pictures for your data.

Power BI has many types of visualizations. You can use bar charts, line charts, pie charts, maps, and many more. Each one is good for showing different things.

In this module, you will learn about the core visuals, advanced visuals, and how to format them. You will also learn about slicers, filters, and bookmarks to make your reports interactive.

🎯 Learning Objectives

By the time you finish this module, you will be able to:

• Explain what data visualizations are and why they are important.
• Identify the core visuals in Power BI.
• Create bar charts, line charts, pie charts, and other visuals.
• Use advanced visuals like scatter plots and decomposition trees.
• Format and customize your visuals.
• Use conditional formatting to highlight important data.
• Add slicers and filters to make reports interactive.
• Use bookmarks and buttons for navigation.
• Give examples of visualizations in Nigerian businesses.

📖 Warm-up Story: Ada's Art Gallery

Ada is a 12-year-old girl who loves art. She wanted to show her family her drawings. She put them all on the wall, but they were messy and hard to see.

Her uncle, who works with data, said, "Ada, you need to organize your art. You should group similar drawings together and put them in a nice gallery."

Ada organized her drawings by colour, size, and subject. She made a beautiful gallery. Her family could see her art clearly and appreciate it.

Her uncle said, "Data visualizations are like your art gallery. They help people see and understand your data."

📚 Main Lessons

1. What are Data Visualizations?

Definition: Data visualizations are pictures that show your data. They include charts, graphs, and maps.

Why it is important: Visualizations make it easy to understand data. A picture is worth a thousand numbers.

Simple explanation: Think of visualizations like pictures that tell a story about your data.

2. Core Visuals in Power BI

Definition: Core visuals are the basic chart types in Power BI. They include bar charts, line charts, pie charts, and more.

Why it is important: Core visuals are the most commonly used charts. You will use them in almost every report.

Simple explanation: Think of core visuals like the basic tools in a toolbox. You will use them all the time.

• Bar Chart: Best for comparing categories.
• Line Chart: Best for showing trends over time.
• Pie Chart: Best for showing proportions.
• Matrix: Best for cross-tabulations.
• Table: Best for detailed data.
• Map: Best for geographic data.

3. Advanced Visuals

Definition: Advanced visuals are more complex chart types. They include scatter plots, waterfall charts, and decomposition trees.

Why it is important: Advanced visuals help you answer more complex questions.

Simple explanation: Think of advanced visuals like special tools for specific jobs.

4. Creating a Visualization

Definition: Creating a visualization means dragging fields to build a chart.

Why it is important: This is how you turn data into pictures.

Simple explanation: Think of it like painting with data.

• Step 1: Select a visual from the Visualizations pane.
• Step 2: Drag a field to the Axis or Category area.
• Step 3: Drag a field to the Values area.
• Step 4: Your chart appears!

5. Formatting Your Visuals

Definition: Formatting means changing the look of your visual. You can change colours, fonts, and more.

Why it is important: Formatting makes your visuals look professional and easy to read.

Simple explanation: Think of formatting like decorating your visual.

• Colours: Change the colours of bars or lines.
• Labels: Show or hide data labels.
• Titles: Add or change the title.
• "example-box">

  🏫 School example: A teacher changes the colours of a bar chart to match the school colours.

  🏠 Home example: Your parents change the font size to make it easier to read.

  🇳🇬 Nigerian example: A business uses green and white colours for a Nigerian theme.

    +-------------------+
    |  Bar Chart        |  ← Compare categories
    |  Line Chart       |  ← Show trends
    |  Pie Chart        |  ← Show proportions
    |  Matrix           |  ← Cross-tabulate
    |  Table            |  ← Show details
    |  Map              |  ← Geographic data
    +-------------------+
    

    +-------------------+
    |  Scatter Plot     |  ← Show relationships
    |  Waterfall        |  ← Cumulative effect
    |  Decomposition    |  ← Drill down into a measure
    +-------------------+
    

    +-------------------+
    |  Slicer           |  ← Filter data
    |  Filter           |  ← Control data
    |  Bookmark         |  ← Save state
    |  Button           |  ← Navigate
    |  Drill-through    |  ← Go to details
    +-------------------+