Module Two: Power Query and Data Transformation – Making Excel Clean Data Automatically

“Copilot in Microsoft Excel – Level Three” – Become a true Excel master

Module Introduction

Welcome back, young Excel champion! In Module One, you learned advanced formulas and dynamic arrays. You learned how to FILTER, SORT, UNIQUE, and use XLOOKUP. You learned how to create custom functions with LAMBDA. You are becoming very powerful!

Now it is time to learn something even more powerful: Power Query and data transformation. Power Query is a tool inside Excel that cleans and organises data automatically. Instead of cleaning your data by hand every week, you set up Power Query once, and it cleans the data for you every time.

Think of Power Query as a robot cleaner for your data. You tell it what to do once, and it does the same job again and again without mistakes. This saves hours of work and makes your data always ready for analysis.

By the end of this module, you will be able to use Power Query to connect to data, clean it, transform it, and refresh it automatically. Copilot will be right beside you, helping you every step of the way.

Let’s begin!

Learning Objectives

After finishing this module, you will be able to:

• Explain what Power Query is.
• Open Power Query in Excel.
• Connect to different data sources.
• Clean data automatically with Power Query.
• Remove duplicates, fix errors, and change data types.
• Split and merge columns.
• Merge and append queries.
• Refresh data automatically.
• Use Copilot to help build queries.
• Complete a mini project and practical assignment.

Warm-up Story: Ngozi’s Data Cleaning Robot

Ngozi runs a small restaurant in Enugu. Every week, she receives a report from her suppliers. The report comes as a CSV file (a type of text file). The file has names, phone numbers, products, quantities, and prices.

The problem was that the file was always messy. Some names had extra spaces. Some phone numbers were in different formats. Some rows had duplicates. Some prices were stored as text instead of numbers. Ngozi spent two hours every week cleaning the file by hand.

Her cousin Chidi said, “Ngozi, use Power Query! It is like a robot cleaner for your data. You set it up once, and it cleans every new file automatically.”

Ngozi opened Excel, went to the Data tab, and clicked “Get Data.” She connected to her CSV file. Then she used Power Query to remove duplicates, trim spaces, fix phone numbers, and change prices to numbers.

The best part was that Ngozi did not have to repeat the steps. She clicked “Close & Load,” and her cleaned data appeared in Excel. Now, every week, she just clicks “Refresh,” and the new file is cleaned automatically.

Ngozi was amazed. What used to take two hours now takes two minutes. Her father said, “You have become a true data expert!” Ngozi smiled. She learned that Power Query is like a robot that cleans data forever.

Moral of the story: Power Query cleans and transforms data automatically. Set it up once, refresh it forever. Copilot helps you build queries.

Main Lessons

Lesson 1: What is Power Query?

Definition: Power Query is a tool inside Excel that connects to data, cleans it, and transforms it automatically.

Why it is important: Power Query saves time and ensures your data is always clean and ready for analysis.

Simple explanation: Imagine a robot that cleans your room every morning without you asking. That is what Power Query does for your data.

Real-life example: A bank uses Power Query to clean daily transaction files from ATMs.

School example: A teacher uses Power Query to clean student records from different classes.

Home example: A family uses Power Query to organise shopping receipts.

Nigerian example: A market trader uses Power Query to clean customer lists from different markets.

Illustration:

  Messy Data
      |
      V
  Power Query (Robot Cleaner)
      |
      V
  Clean Data
      |
      V
  Ready for Analysis 🎉
  

Mini summary: Power Query is a robot cleaner for your data. It cleans and transforms automatically. Copilot helps you build queries.

Lesson 2: Opening Power Query in Excel

Definition: Opening Power Query means finding and using the Power Query tools in Excel.

Why it is important: You need to know where to find the tools to use them.

Simple explanation: Like finding the toolbox in a workshop.

Real-life example: A bank finds Power Query in the Data tab.

School example: A teacher uses Data > Get Data.

Home example: A family uses Data > Get Data to import a CSV file.

Nigerian example: A trader imports a supplier file into Excel.

Illustration:

  Excel Ribbon:
  +----------------------------------+
  | Home | Insert | Data | Review    |
  +----------------------------------+
                    |
                    V
              Get Data
                    |
                    V
              From File
                    |
                    V
              From Text/CSV
  

Step-by-step:

1. Open Excel.
2. Click the “Data” tab.
3. Click “Get Data.”
4. Choose your data source (e.g., From File).
5. Select your file and click “Import.”

Mini summary: Open Power Query from the Data tab. Choose your data source and import.

Lesson 3: Connecting to Different Data Sources

Definition: A data source is where your data comes from, like a CSV file, an Excel workbook, or a website.

Why it is important: Power Query can connect to many types of data.

Simple explanation: Like plugging into different sockets to charge your phone.

Real-life example: A bank connects to a database of transactions.

School example: A teacher connects to a CSV file of students.

Home example: A family connects to a web page with product prices.

Nigerian example: A trader connects to a WhatsApp message export.

Illustration:

  Data Sources:
  +------------------+
  | Excel Workbook   |
  +------------------+
  | CSV / Text File  |
  +------------------+
  | Web Page         |
  +------------------+
  | Database         |
  +------------------+
  | Folder           |
  +------------------+
  

Step-by-step:

1. Click “Get Data.”
2. Choose the source type.
3. Browse to your data.
4. Click “Import” or “Connect.”
5. The data appears in the Power Query Editor.

Mini summary: Power Query can connect to many data sources. Choose the one you need.

Lesson 4: The Power Query Editor

Definition: The Power Query Editor is the workspace where you clean and transform your data.

Why it is important: This is where all the magic happens.

Simple explanation: Like a workshop where you build and fix things.

Real-life example: A bank uses the editor to clean transactions.

School example: A teacher uses the editor to fix student records.

Home example: A family uses the editor to organise shopping receipts.

Nigerian example: A trader uses the editor to clean sales data.

Illustration:

  +------------------------------------------------+
  |  Power Query Editor                            |
  |  +------------------------------------------+  |
  |  |  [Home] [Transform] [Add Column] [View]  |  |
  |  +------------------------------------------+  |
  |  |  Data Preview (rows and columns)         |  |
  |  +------------------------------------------+  |
  |  |  Applied Steps (list of changes)         |  |
  |  +------------------------------------------+  |
  +------------------------------------------------+
  

Mini summary: The Power Query Editor is where you clean and transform data. It has a data preview and applied steps.

Lesson 5: Removing Duplicates with Power Query

Definition: Removing duplicates means keeping only one copy of repeated rows.

Why it is important: Duplicates waste space and cause wrong counts.

Simple explanation: Like removing repeated names from a list.

Real-life example: A bank removes duplicate customer records.

School example: A teacher removes duplicate student names.

Home example: A family removes duplicate items from a shopping list.

Nigerian example: A trader removes duplicate customer names.

Illustration:

  Before:                    After:
  Ada                        Ada
  Tunde                      Tunde
  Ada                        Ngozi
  Ngozi
  Tunde

  Right-click column → Remove Duplicates
  

Step-by-step:

1. Open the Power Query Editor.
2. Right-click the column header.
3. Click “Remove Duplicates.”
4. The duplicates are removed.
5. Click “Close & Load” to save the changes.

Mini summary: Remove duplicates with one click in Power Query. Copilot can show you how.

Lesson 6: Trimming and Cleaning Text

Definition: Trimming means removing extra spaces from text.

Why it is important: Extra spaces make data messy and cause errors.

Simple explanation: Like cutting the edges of a piece of paper to make it neat.

Real-life example: A bank trims customer names before saving.

School example: A teacher trims student names for a register.

Home example: A family trims shopping items for neatness.

Nigerian example: A trader trims product names for invoices.

Illustration:

  Before: "  Ada  "     After: "Ada"
  Before: "Tunde "     After: "Tunde"

  Transform → Format → Trim
  

Step-by-step:

1. Select the column with extra spaces.
2. Click “Transform.”
3. Click “Format.”
4. Click “Trim.”
5. The extra spaces are removed.

Mini summary: Trim removes extra spaces. Power Query does it with one click.

Lesson 7: Changing Data Types

Definition: Data types are the kind of data in a column, like text, number, or date.

Why it is important: If data type is wrong, formulas will not work.

Simple explanation: Like making sure you put food in the right container.

Real-life example: A bank makes sure prices are numbers, not text.

School example: A teacher makes sure scores are numbers.

Home example: A family makes sure dates are dates.

Nigerian example: A trader makes sure prices are numbers for calculations.

Illustration:

  Column with text numbers:   After change:
  "500"                      500
  "300"                      300
  "700"                      700

  Right-click column → Change Type → Number
  

Step-by-step:

1. Click the small icon to the left of the column header.
2. Choose the correct data type (e.g., Whole Number).
3. Click “Replace Current” if prompted.

Mini summary: Change data types so formulas work. Power Query does it with one click.

Lesson 8: Splitting Columns

Definition: Splitting means breaking one column into two or more columns.

Why it is important: Splitting makes data easier to use.

Simple explanation: Like cutting a long ribbon into pieces.

Real-life example: A bank splits full names into first and last names.

School example: A teacher splits a full name into first and last.

Home example: A family splits an address into street and city.

Nigerian example: A trader splits a product code into category and number.

Illustration:

  Before:                    After:
  +----------------+          +--------+--------+
  | Full Name      |          | First  | Last   |
  +----------------+          +--------+--------+
  | Ada Okeke      |          | Ada    | Okeke  |
  | Tunde Balogun  |          | Tunde  | Balogun|
  +----------------+          +--------+--------+

  Right-click column → Split Column → By Delimiter
  

Step-by-step:

1. Select the column to split.
2. Click “Transform” or right-click.
3. Click “Split Column.”
4. Choose “By Delimiter.”
5. Choose the delimiter (e.g., space).
6. Click OK.

Mini summary: Splitting breaks one column into many. Power Query does it with one click.

Lesson 9: Merging Columns

Definition: Merging means joining two or more columns into one.

Why it is important: Merging makes a single column from parts, like full names.

Simple explanation: Like gluing pieces of paper together.

Real-life example: A bank merges first and last names into a full name.

School example: A teacher merges subjects into a full title.

Home example: A family merges first and last names for an address list.

Nigerian example: A trader merges a product code with a description.

Illustration:

  Before:                    After:
  +--------+--------+        +----------------+
  | First  | Last   |        | Full Name      |
  +--------+--------+        +----------------+
  | Ada    | Okeke  |        | Ada Okeke      |
  | Tunde  | Balogun|        | Tunde Balogun  |
  +--------+--------+        +----------------+

  Select columns → Add Column → Merge Columns
  

Step-by-step:

1. Select the columns to merge.
2. Click “Add Column.”
3. Click “Merge Columns.”
4. Choose a separator (e.g., space).
5. Click OK.

Mini summary: Merging joins columns into one. Power Query does it with one click.

Lesson 10: Merging Queries (Joining Tables)

Definition: Merging queries means joining two tables into one, like a VLOOKUP but more powerful.

Why it is important: Merging lets you combine data from different sources.

Simple explanation: Like joining two puzzle pieces to make a bigger picture.

Real-life example: A bank merges customer names with account balances.

School example: A teacher merges student names with their scores.

Home example: A family merges shopping items with prices.

Nigerian example: A trader merges products with their prices.

Illustration:

  Table 1:          Table 2:
  Ada               Ada 85
  Tunde             Tunde 65
  Ngozi             Ngozi 90

  Merged:
  Ada 85
  Tunde 65
  Ngozi 90
  

Step-by-step:

1. Click “Home” in Power Query.
2. Click “Merge Queries.”
3. Choose the second table.
4. Select the matching columns.
5. Choose the join type (e.g., Left Outer).
6. Click OK.

Mini summary: Merging queries joins two tables. Power Query does it like a super VLOOKUP.

Lesson 11: Appending Queries (Stacking Tables)

Definition: Appending means stacking two tables on top of each other.

Why it is important: Appending lets you combine data from similar tables.

Simple explanation: Like stacking books on top of each other.

Real-life example: A bank stacks monthly transactions into one table.

School example: A teacher stacks class results into one sheet.

Home example: A family stacks shopping lists from several weeks.

Nigerian example: A trader stacks daily sales into one sheet.

Illustration:

  Table 1:           Table 2:
  Ada 85             Emeka 72
  Tunde 65           Chidi 88

  Appended:
  Ada 85
  Tunde 65
  Emeka 72
  Chidi 88
  

Step-by-step:

1. Click “Home” in Power Query.
2. Click “Append Queries.”
3. Choose two or more tables.
4. Click OK.

Mini summary: Appending stacks tables on top of each other. Power Query does it with one click.

Lesson 12: Refreshing Data Automatically

Definition: Refreshing means re-running the Power Query steps on new data.

Why it is important: Refreshing keeps your data up to date without redoing the cleaning.

Simple explanation: Like pressing “refresh” on a web page to see the latest news.

Real-life example: A bank refreshes transaction data every hour.

School example: A teacher refreshes student scores every week.

Home example: A family refreshes shopping prices every weekend.

Nigerian example: A trader refreshes sales data every day.

Illustration:

  Add new data to source
       |
       V
  Click "Refresh"
       |
       V
  Power Query runs all steps again
       |
       V
  Clean data appears automatically
  

Step-by-step:

1. Click “Close & Load” in Power Query.
2. Your clean data appears in a new Excel sheet.
3. When the source data changes, click “Data” > “Refresh All.”
4. The data updates automatically.

Mini summary: Refresh updates your data automatically. Set it up once, refresh forever.

Lesson 13: Using Copilot with Power Query

Copilot can help you build Power Query steps.

Step 1: Open Copilot in Excel.

Step 2: Describe what you want in plain English.

Step 3: Copilot explains the steps.

Step 4: Follow the steps to build your query.

Examples:

• “Remove duplicates from this table.”
• “Trim extra spaces from names.”
• “Change price column to number.”
• “Split full name into first and last.”
• “Merge these two tables by customer name.”

Illustration:

  You: "How do I remove duplicates in Power Query?"
       |
       V
  Copilot: 1. Right-click the column
           2. Click Remove Duplicates
           3. Click Close & Load
       |
       V
  You: Follow the steps and clean your data.
  

Mini summary: Copilot explains Power Query steps. Just ask in plain English.

Lesson 14: Common Mistakes with Power Query

Definition: Mistakes happen. Knowing them helps you avoid them.

Why it is important: A small mistake can ruin your data.

Simple explanation: Like making a mistake while cooking. You might waste the whole meal.

Real-life example: A bank removes wrong duplicates.

School example: A teacher changes the wrong column type.

Home example: A family merges the wrong columns.

Nigerian example: A trader splits a product code wrongly.

Table of common mistakes:

Mistake	What Happens	How to Fix
Wrong column selected	Wrong data cleaned	Check column header
Wrong data type	Formulas fail	Change to correct type
Wrong split delimiter	Wrong parts	Choose correct delimiter
Forgetting to save	Lose changes	Click Close & Load
Removing too many duplicates	Lose real data	Check before removing
Not testing	Wrong results	Test with sample

Mini summary: Common mistakes include wrong columns and types. Always check your work.

Lesson 15: Putting It All Together – A Complete Power Query Project

Let’s build a complete Power Query project.

Step 1: Open Excel and click “Data.”

Step 2: Click “Get Data” and choose “From File” > “From Text/CSV.”

Step 3: Select your messy CSV file.

Step 4: In the Power Query Editor, remove duplicates.

Step 5: Trim extra spaces from names.

Step 6: Change prices to number type.

Step 7: Split full name into first and last names.

Step 8: Click “Close & Load.”

Step 9: Your clean data appears in Excel.

Step 10: Save as “My_Clean_Data_PowerQuery.”

Illustration:

  Messy CSV
      |
      V
  Get Data
      |
      V
  Power Query Editor
      |
      V
  Remove Duplicates
      |
      V
  Trim Spaces
      |
      V
  Change Types
      |
      V
  Split Columns
      |
      V
  Close & Load
      |
      V
  Clean Data 🎉
  

Mini summary: A complete Power Query project cleans data from start to finish. Copilot helps you at every step.

Key Vocabulary

Word	Simple Definition
Power Query	A tool that cleans and transforms data automatically.
Data source	Where your data comes from.
Query	A set of steps to clean or transform data.
Editor	The workspace in Power Query.
Duplicate	A value that appears more than once.
Trim	Remove extra spaces.
Data type	Text, number, or date.
Split	Break one column into many.
Merge	Join columns or tables.
Append	Stack tables on top of each other.
Refresh	Re-run the query on new data.
Close & Load	Save your query and load the result.
Delimiter	A character used to split text (e.g., space, comma).
Join type	How two tables are merged.
Applied steps	The list of changes you made.

Important Concepts

• Power Query cleans data automatically: Set up once, refresh forever.
• Connect to many data sources: CSV, Excel, web, database.
• The Editor is where you work: Preview and applied steps.
• Remove duplicates: One click in Power Query.
• Trim removes spaces: Keeps data neat.
• Change data types: So formulas work.
• Split and merge columns: Break and combine text.
• Merge and append queries: Join and stack tables.
• Refresh updates data: New data, same cleaning.
• Copilot helps build queries: Just ask in plain English.

Step-by-step Explanations

How to import a CSV file step by step

1. Click “Data.”
2. Click “Get Data.”
3. Click “From File” > “From Text/CSV.”
4. Select your file.
5. Click “Import.”

How to remove duplicates step by step

1. Right-click the column header.
2. Click “Remove Duplicates.”
3. Click “Close & Load.”

How to trim spaces step by step

1. Select the column.
2. Click “Transform.”
3. Click “Format” > “Trim.”
4. Click “Close & Load.”

How to change data type step by step

1. Click the icon left of the column header.
2. Choose the correct type (e.g., Whole Number).
3. Click “Replace Current.”

How to merge queries step by step

1. Click “Home” in Power Query.
2. Click “Merge Queries.”
3. Choose the second table.
4. Select matching columns.
5. Click OK.

Real-life Examples

• Shops: Clean daily sales files from different branches.
• Schools: Clean student records from different classes.
• Homes: Clean monthly spending reports.
• Offices: Clean staff records from multiple sheets.
• Hospitals: Clean patient records from different systems.

Nigerian Examples

• Market traders: Clean supplier files before pricing.
• POS operators: Clean daily transaction exports.
• Schools in Lagos: Clean student lists from various sources.
• Transporters: Clean fuel and fare records.
• Church groups: Clean donation records from multiple weeks.

Fun Examples Children Can Relate To

• Video game scores: Clean duplicate scores.
• Football league: Stack weekly results.
• Pocket money: Clean spending categories.
• Chores: Remove duplicate chores.
• Snack list: Split snack names and prices.

Everyday Examples

• Shopping list: Clean items from different shops.
• School timetable: Merge subjects and teachers.
• Exercise log: Stack daily exercise data.
• Reading list: Clean book titles with extra spaces.
• Grocery budget: Merge shopping items with prices.

Parent Tips

• Encourage your child to clean one data file each week.
• Let them practice with real data like shopping receipts.
• Use everyday examples to explain Power Query.
• Set a small daily practice time.
• Celebrate small wins, like a working refresh.
• Be patient. Power Query takes practice.
• Ask them to teach you what they learned.
• Keep it fun. Use games and stories.
• Remind them to check data types.
• Encourage them to ask Copilot when unsure.

Interesting Facts

• Power Query was first released in 2013.
• It can connect to over 100 data sources.
• Power Query can handle millions of rows.
• It remembers every step you take.
• Refreshing takes only a few seconds.
• Power Query is used in every industry.
• Copilot can help write Power Query steps.
• It is one of the most powerful Excel tools.

Did You Know?

• Did you know that Power Query remembers every step you make?
• Did you know that you can undo a step by clicking the X?
• Did you know that Power Query can clean a new file in seconds?
• Did you know that you can merge more than two tables?
• Did you know that Power Query can split columns by any character?
• Did you know that refreshing does not repeat your manual steps?
• Did you know that Copilot can explain Power Query steps?
• Did you know that Power Query is used by data scientists?

Remember This

• Power Query cleans data automatically.
• Connect to many data sources.
• The Editor is where you work.
• Remove duplicates with one click.
• Trim removes extra spaces.
• Change data types so formulas work.
• Split and merge columns as needed.
• Merge and append queries.
• Refresh updates data automatically.
• Copilot helps build queries.

Common Mistakes

• Wrong column selected.
• Wrong data type.
• Wrong split delimiter.
• Forgetting to save.
• Removing too many duplicates.
• Not testing.
• Not refreshing.
• Trusting Copilot blindly.

Best Practices

• Always work on a copy of your data.
• Use clear column headers.
• Check data types after import.
• Test your query with sample data.
• Name your queries clearly.
• Save your work often.
• Refresh regularly.
• Ask Copilot if unsure.
• Document your steps.
• Keep learning new features.

Illustrations and Diagrams

Power Query Workflow

  Data Source
      |
      V
  Get Data
      |
      V
  Power Query Editor
      |
      V
  Clean (Remove Duplicates, Trim, Change Types)
      |
      V
  Transform (Split, Merge, Append)
      |
      V
  Close & Load
      |
      V
  Clean Data in Excel 🎉
  

Merge Queries Illustration

  Table 1:          Table 2:
  Ada               Ada 85
  Tunde             Tunde 65
  Ngozi             Ngozi 90

  Merge:
  Ada 85
  Tunde 65
  Ngozi 90
  

Append Queries Illustration

  Table 1:          Table 2:
  Ada 85             Emeka 72
  Tunde 65           Chidi 88

  Append:
  Ada 85
  Tunde 65
  Emeka 72
  Chidi 88
  

Refresh Illustration

  Source Data Change
      |
      V
  Click "Refresh All"
      |
      V
  Query Runs Again
      |
      V
  Clean Data Updates 🎉
  

Your Learning Journey

  Level One: Getting Ready with Copilot
        |
        V
  Level Two: Formulas, Cleaning, Analysis, Charts, Automation
        |
        V
  Level Three Module One: Advanced Formulas
        |
        V
  Level Three Module Two: Power Query
        |
        V
  Level Three Module Three: Power Pivot
        |
        V
  Level Three Module Four: AI Insights & Power BI
        |
        V
  Copilot in Excel Master 🎉
  

Comparison Tables

Manual Cleaning vs Power Query

Feature	Manual	Power Query
Time	Long	Short
Repeatable	No	Yes
Mistakes	More	Fewer
Effort	High	Low

Merge vs Append

Feature	Merge	Append
Direction	Side by side	Stacked
Purpose	Add columns	Add rows
Example	Names + scores	January + February

Split vs Merge Columns

Feature	Split	Merge
Purpose	Break one into many	Combine many into one
Example	"Ada Okeke" → Ada, Okeke	Ada + Okeke → "Ada Okeke"

Trim vs Change Type

Feature	Trim	Change Type
Purpose	Remove spaces	Fix data type
Example	" Ada " → "Ada"	"500" → 500

Lesson Summaries

Lesson 1: Power Query is a robot cleaner for your data.

Lesson 2: Open Power Query from the Data tab.

Lesson 3: Power Query connects to many data sources.

Lesson 4: The Editor is where you clean and transform.

Lesson 5: Remove duplicates with one click.

Lesson 6: Trim removes extra spaces.

Lesson 7: Change data types so formulas work.

Lesson 8: Split breaks one column into many.

Lesson 9: Merge joins columns into one.

Lesson 10: Merge queries joins two tables.

Lesson 11: Append queries stacks tables.

Lesson 12: Refresh updates data automatically.

Lesson 13: Copilot helps build Power Query steps.

Lesson 14: Common mistakes include wrong columns.

Lesson 15: Build a complete Power Query project.

End-of-Module Summary

Congratulations! You have finished Module Two of Copilot in Microsoft Excel – Level Three. You learned what Power Query is. You learned how to open it, connect to data sources, and use the Editor. You learned how to remove duplicates, trim spaces, change data types, split and merge columns, merge and append queries, and refresh data automatically. You learned how Copilot helps build queries. You learned common mistakes and best practices. Most importantly, you can now clean and transform data automatically. Keep practising, and you will become an Excel master!

Frequently Asked Questions

1. What is Power Query? A tool that cleans and transforms data automatically.
2. How do I open Power Query? Click Data > Get Data.
3. What data sources can I use? CSV, Excel, web, database, folder.
4. How do I remove duplicates? Right-click column > Remove Duplicates.
5. How do I trim spaces? Transform > Format > Trim.
6. How do I change data types? Click the icon left of the header.
7. How do I split columns? Transform > Split Column.
8. How do I merge queries? Home > Merge Queries.
9. How do I refresh data? Data > Refresh All.
10. How does Copilot help? It explains Power Query steps in plain English.

Matching Exercises

Match the tool to what it does.

Tool	What It Does
1. Get Data	A. Stacks tables
2. Remove Duplicates	B. Connects to data sources
3. Trim	C. Removes repeated rows
4. Merge Queries	D. Removes extra spaces
5. Append Queries	E. Joins two tables

Answers: 1-B, 2-C, 3-D, 4-E, 5-A

Scenario-based Exercises

1. Scenario: You have a list with duplicates. What do you do?
   Answer: Remove Duplicates.
2. Scenario: You have names with extra spaces. What do you do?
   Answer: Trim.
3. Scenario: Prices are stored as text. What do you do?
   Answer: Change data type.
4. Scenario: You want to combine first and last names. What do you do?
   Answer: Merge columns.
5. Scenario: You want to join two tables. What do you do?
   Answer: Merge queries.

Group Activity

Title: “Clean a Messy File Together”

Instructions: In groups of 3–4, create a messy CSV file with names, phone numbers, and prices. Use Power Query to remove duplicates, trim spaces, change data types, and split names. One person types, one person asks Copilot, one person checks, and one person presents. Share your cleaned file with the class.

Goal: Practice cleaning data with Power Query.

Individual Activity

Task: Create a small CSV file with five messy rows (duplicates, extra spaces, text numbers). Use Power Query to:

• Remove duplicates.
• Trim spaces.
• Change data types.
• Split names.

Hint: Start with a clean table with headings.

Mini Project

Project: “My Clean Supplier List”

Create a CSV file with at least 20 rows of supplier data. Include full name, phone, product, and price. Make the list messy on purpose (duplicates, extra spaces, text numbers). Use Power Query to:

• Remove duplicates.
• Trim spaces.
• Change prices to numbers.
• Split names into first and last.
• Merge product and price into one column.
• Refresh when new data arrives.

Example output:

  +--------+--------+--------+--------+
  | First  | Last   | Phone  | Product|
  +--------+--------+--------+--------+
  | Ada    | Okeke  | 0801   | Rice   |
  | Tunde  | Balogun| 0802   | Beans  |
  | Ngozi  | Eze    | 0803   | Oil    |
  +--------+--------+--------+--------+
  

Practical Assignment

Assignment: Create a new Excel workbook called PowerQuery_Project. In the workbook, do the following:

1. Create a CSV file with at least 20 rows of messy data.
2. Import the CSV file with Power Query.
3. Remove duplicates.
4. Trim extra spaces.
5. Change data types correctly.
6. Split a name column into first and last.
7. Merge two columns into one.
8. Close & Load into Excel.
9. Refresh the data.
10. Save the workbook and submit.

Submit: Your workbook file and screenshots of your Power Query steps.

Key Takeaways

• Power Query cleans data automatically.
• Connect to many data sources.
• The Editor is where you work.
• Remove duplicates with one click.
• Trim removes extra spaces.
• Change data types so formulas work.
• Split and merge columns.
• Merge and append queries.
• Refresh updates data automatically.
• Copilot helps build queries.

Classroom Discussion Questions

1. What is Power Query?
2. Why is Power Query useful?
3. What data sources can Power Query connect to?
4. How do you remove duplicates?
5. When would you use Trim?
6. Why change data types?
7. When would you split a column?
8. When would you merge columns?
9. What is the difference between Merge and Append?
10. What did Ngozi learn from her data cleaning robot?

Preparation for Module Three

In Module Three, we will learn about Power Pivot and advanced data modelling. We will cover:

• What Power Pivot is.
• Creating table relationships.
• Introduction to DAX measures.
• Time intelligence functions.
• Building KPIs with Copilot.

To prepare, make sure you have completed the practical assignment and have a clean data sheet ready. Review your notes on Power Query. Bring your curiosity!

See you in Module Three!

---

End of Module Two – Copilot in Microsoft Excel – Level Three

Module Three: Power Pivot and Advanced Data Modelling – Connecting Your Data Like a Pro

“Copilot in Microsoft Excel – Level Three” – Become a true Excel master

Module Introduction

Welcome back, young Excel champion! In Module One, you learned advanced formulas. In Module Two, you learned Power Query and how to clean data automatically. Now it is time to learn something even more powerful: Power Pivot and advanced data modelling.

Power Pivot is a tool inside Excel that lets you connect many tables together and create powerful calculations. Instead of putting all your data in one big table, you can keep separate tables and connect them with relationships. This is how professional data analysts work.

Think of Power Pivot as a bridge builder. You have different islands of data – customers, products, sales, and dates. Power Pivot builds bridges between them so you can see the whole picture. You can then create special calculations called DAX measures that give you powerful insights.

By the end of this module, you will be able to create relationships, write DAX measures, use time intelligence, and build KPIs. Copilot will be right beside you, helping you every step of the way.

Let’s begin!

Learning Objectives

After finishing this module, you will be able to:

• Explain what Power Pivot is.
• Enable Power Pivot in Excel.
• Create table relationships.
• Understand the data model.
• Write basic DAX measures.
• Use CALCULATE to change context.
• Use time intelligence functions like TOTALYTD.
• Build KPIs with Copilot.
• Create a complete data model.
• Complete a mini project and practical assignment.

Warm-up Story: Ada’s Data Bridge

Ada runs a small supermarket in Lagos. She sells many products: rice, beans, oil, soap, and drinks. She has three separate Excel sheets: one for products, one for customers, and one for sales.

Ada wanted to know which customers bought the most rice, and which month had the highest sales. But her data was in three separate sheets. She tried to copy everything into one big sheet, but it became messy and slow.

Her cousin Emeka said, “Ada, use Power Pivot! It can connect your three sheets together with relationships. Then you can ask any question you want.”

Ada enabled Power Pivot in Excel. She created a relationship between the ProductID in the Sales sheet and the ProductID in the Products sheet. She created another relationship between the CustomerID in the Sales sheet and the Customers sheet.

Then she wrote a DAX measure: Total Sales = SUM(Sales[Amount]). She used this measure in a PivotTable and instantly saw her total sales. She created another measure: Sales YTD = TOTALYTD(SUM(Sales[Amount]), Dates[Date]). Now she could see her year-to-date sales.

Ada was amazed. She had built a professional data model. Her father said, “You are a true data expert!” Ada smiled. She learned that Power Pivot connects data like building bridges.

Moral of the story: Power Pivot connects tables with relationships. DAX measures give powerful calculations. Copilot helps you build them.

Main Lessons

Lesson 1: What is Power Pivot?

Definition: Power Pivot is a tool in Excel that lets you connect many tables together and create powerful calculations.

Why it is important: Power Pivot lets you work with large amounts of data from different sources.

Simple explanation: Imagine building a bridge between two islands. Power Pivot builds bridges between your tables.

Real-life example: A bank connects customer data with transaction data.

School example: A teacher connects students with their scores.

Home example: A family connects shopping items with prices.

Nigerian example: A trader connects products with sales.

Illustration:

  Products Table      Sales Table      Customers Table
       |                  |                  |
       +------------------+------------------+
                          |
                          V
                    Power Pivot
                          |
                          V
                   Data Model 🎉
  

Mini summary: Power Pivot connects tables. It lets you create powerful calculations. Copilot helps you build it.

Lesson 2: Enabling Power Pivot in Excel

Definition: Enabling Power Pivot means turning it on in Excel so you can use it.

Why it is important: Power Pivot is not always visible by default. You need to enable it.

Simple explanation: Like turning on a light switch in a dark room.

Real-life example: A bank enables Power Pivot to work with large data.

School example: A teacher enables Power Pivot for student data.

Home example: A family enables Power Pivot for budget data.

Nigerian example: A trader enables Power Pivot for sales data.

Illustration:

  File → Options → Add-ins
       |
       V
  COM Add-ins → Go
       |
       V
  Check "Microsoft Power Pivot for Excel"
       |
       V
  Click OK
  

Step-by-step:

1. Click “File.”
2. Click “Options.”
3. Click “Add-ins.”
4. In the Manage box, choose “COM Add-ins.”
5. Click “Go.”
6. Check “Microsoft Power Pivot for Excel.”
7. Click “OK.”
8. The Power Pivot tab appears in Excel.

Mini summary: Enable Power Pivot from File > Options > Add-ins. Then the Power Pivot tab appears.

Lesson 3: Adding Tables to the Data Model

Definition: The data model is where Power Pivot stores all your connected tables.

Why it is important: Tables must be in the data model before you can create relationships.

Simple explanation: Like putting all your ingredients on the kitchen counter before cooking.

Real-life example: A bank adds customers, accounts, and transactions to the data model.

School example: A teacher adds students, subjects, and scores.

Home example: A family adds items, prices, and shops.

Nigerian example: A trader adds products, customers, and sales.

Illustration:

  Data Model:
  +----------------+
  | Products       |
  +----------------+
  | Customers      |
  +----------------+
  | Sales          |
  +----------------+
  | Dates          |
  +----------------+
  

Step-by-step:

1. Click the “Power Pivot” tab.
2. Click “Manage.”
3. Click “Add to Data Model” for each table.
4. Or use “Get Data” and check “Add to Data Model.”

Mini summary: Add your tables to the data model so Power Pivot can use them.

Lesson 4: Creating Table Relationships

Definition: A relationship is a connection between two tables based on a shared column.

Why it is important: Relationships let you combine data from different tables.

Simple explanation: Like a friendship between two people based on a shared interest.

Real-life example: A bank connects customers to their accounts with CustomerID.

School example: A teacher connects students to scores with StudentID.

Home example: A family connects items to prices with ItemID.

Nigerian example: A trader connects products to sales with ProductID.

Illustration:

  Products Table:          Sales Table:
  ProductID | Name         SaleID | ProductID | Amount
  1         | Rice         1      | 1         | 500
  2         | Beans        2      | 2         | 300
                           3      | 1         | 400

  Relationship: Products[ProductID] → Sales[ProductID]
  

Step-by-step:

1. Click “Power Pivot” tab.
2. Click “Manage.”
3. Click “Diagram View.”
4. Drag the shared column from one table to the other.
5. A relationship is created.

Mini summary: Relationships connect tables by a shared column. Copilot can show you how.

Lesson 5: Understanding the Data Model

Definition: The data model is the collection of all your connected tables.

Why it is important: The data model is the foundation of all your calculations.

Simple explanation: Like a map of all your connected towns.

Real-life example: A bank’s data model connects customers, accounts, and transactions.

School example: A teacher’s data model connects students, subjects, and scores.

Home example: A family’s data model connects items, prices, and shops.

Nigerian example: A trader’s data model connects products, customers, and sales.

Illustration:

  +-----------+     +-----------+
  | Products  |-----| Sales     |
  +-----------+     +-----------+
                         |
                         |
  +-----------+     +-----------+
  | Customers |-----| Dates     |
  +-----------+     +-----------+
  

Mini summary: The data model is your connected tables. It powers all your analysis.

Lesson 6: Introduction to DAX Measures

Definition: DAX stands for Data Analysis Expressions. A DAX measure is a special calculation that works on your data model.

Why it is important: DAX measures give you powerful ways to calculate and analyse data.

Simple explanation: Like a magic formula that knows how to work across many tables.

Real-life example: A bank creates a measure for total deposits.

School example: A teacher creates a measure for average score.

Home example: A family creates a measure for total spending.

Nigerian example: A trader creates a measure for total sales.

Illustration:

  DAX Measure:
  Total Sales = SUM(Sales[Amount])

  Total Sales:
  500 + 300 + 400 = 1200
  

Step-by-step:

1. Click “Power Pivot” tab.
2. Click “Measures” > “New Measure.”
3. Type a name (e.g., Total Sales).
4. Type the DAX formula.
5. Click “OK.”

Mini summary: DAX measures are powerful calculations. Copilot writes them for you.

Lesson 7: Common DAX Functions

Here are some common DAX functions you will use.

SUM: Adds numbers.

Total Sales = SUM(Sales[Amount])

AVERAGE: Finds the average.

Average Sale = AVERAGE(Sales[Amount])

COUNT: Counts rows.

Number of Sales = COUNT(Sales[SaleID])

DISTINCTCOUNT: Counts unique values.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Illustration:

  Common DAX:
  +-----------------+---------------------------+
  | Function        | Example                   |
  +-----------------+---------------------------+
  | SUM             | SUM(Sales[Amount])        |
  | AVERAGE         | AVERAGE(Sales[Amount])    |
  | COUNT           | COUNT(Sales[SaleID])      |
  | DISTINCTCOUNT   | DISTINCTCOUNT(CustomerID) |
  +-----------------+---------------------------+
  

Mini summary: Common DAX functions include SUM, AVERAGE, COUNT, and DISTINCTCOUNT. Copilot writes them.

Lesson 8: Using CALCULATE to Change Context

Definition: CALCULATE changes the filter context of a measure.

Why it is important: CALCULATE lets you create measures for specific situations.

Simple explanation: Like asking a question in a specific way: “Sales for just this month” instead of “Sales overall.”

Real-life example: A bank calculates total deposits for a specific branch.

School example: A teacher calculates the average score for a specific class.

Home example: A family calculates spending for a specific month.

Nigerian example: A trader calculates sales for a specific product.

Illustration:

  Total Sales = SUM(Sales[Amount])

  Sales for Lagos = CALCULATE(
      [Total Sales],
      Customers[City] = "Lagos"
  )

  Result: Only sales where city is Lagos.
  

Step-by-step:

1. Create a base measure like Total Sales.
2. Create a new measure using CALCULATE.
3. Add a filter condition.
4. Test the result.

Mini summary: CALCULATE changes the context of a measure. Copilot writes it for you.

Lesson 9: Time Intelligence Functions

Definition: Time intelligence functions let you calculate things like year-to-date, month-to-date, or previous year.

Why it is important: Time intelligence helps you compare periods.

Simple explanation: Like asking, “How much have I earned so far this year?”

Real-life example: A bank calculates year-to-date profits.

School example: A teacher calculates term-to-date average scores.

Home example: A family calculates month-to-date spending.

Nigerian example: A trader calculates year-to-date sales.

Illustration:

  Total Sales = SUM(Sales[Amount])

  Sales YTD = TOTALYTD(
      [Total Sales],
      Dates[Date]
  )

  Sales MTD = TOTALMTD(
      [Total Sales],
      Dates[Date]
  )
  

Step-by-step:

1. Create a Dates table with all dates.
2. Link the Dates table to your Sales table.
3. Create a measure using TOTALYTD or TOTALMTD.
4. Use the measure in a PivotTable.

Mini summary: Time intelligence shows year-to-date, month-to-date, and more. Copilot writes them.

Lesson 10: Building KPIs

Definition: A KPI is a Key Performance Indicator. It is a number that shows how well you are doing.

Why it is important: KPIs help you track progress and make decisions.

Simple explanation: Like a score in a game. It tells you if you are winning or losing.

Real-life example: A bank tracks profit margin as a KPI.

School example: A teacher tracks average score as a KPI.

Home example: A family tracks monthly spending as a KPI.

Nigerian example: A trader tracks daily sales as a KPI.

Illustration:

  KPI: Total Sales
  Target: ₦500,000
  Actual: ₦450,000
  Status: Below Target (Red)
  

Step-by-step:

1. Create your DAX measure.
2. Ask Copilot: “Create a KPI for this measure.”
3. Set a target value.
4. Choose status colours (green, yellow, red).
5. Add the KPI to your dashboard.

Mini summary: KPIs track your progress. Copilot helps you build them.

Lesson 11: Using Copilot with Power Pivot

Copilot can help you build Power Pivot models and DAX measures.

Step 1: Open Copilot in Excel.

Step 2: Describe what you want in plain English.

Step 3: Copilot explains the steps.

Step 4: Follow the steps to build your model.

Examples:

• “How do I create a relationship between two tables?”
• “Write a DAX measure for total sales.”
• “How do I calculate year-to-date sales?”
• “Create a KPI for profit margin.”
• “How do I use CALCULATE?”

Illustration:

  You: "Write a DAX measure for total sales"
       |
       V
  Copilot: Total Sales = SUM(Sales[Amount])
       |
       V
  You: Copy the measure into Power Pivot.
  

Mini summary: Copilot explains Power Pivot and writes DAX measures. Just ask in plain English.

Lesson 12: Common Mistakes with Power Pivot

Definition: Mistakes happen. Knowing them helps you avoid them.

Why it is important: A small mistake can break your data model.

Simple explanation: Like building a bridge with weak parts. It might collapse.

Real-life example: A bank creates the wrong relationship and gets wrong totals.

School example: A teacher creates the wrong relationship and gets wrong averages.

Home example: A family creates the wrong relationship and gets wrong totals.

Nigerian example: A trader creates the wrong relationship and gets wrong sales.

Table of common mistakes:

Mistake	What Happens	How to Fix
Wrong column for relationship	Wrong results	Check column names
Duplicate values in relationship column	Error	Remove duplicates
Wrong DAX formula	Wrong results	Check formula
Missing Dates table	Time intelligence fails	Add Dates table
Forgetting to save	Lose work	Save often
Not testing measures	Wrong answers	Test with sample data

Mini summary: Common mistakes include wrong relationships and formulas. Always check your work.

Lesson 13: Best Practices for Power Pivot

Definition: Best practices are good habits that make Power Pivot work well.

Why it is important: Good habits save time and prevent mistakes.

Simple explanation: Like keeping your room tidy. It is easier to find things.

Real-life example: A bank keeps a clean data model.

School example: A teacher uses clear table names.

Home example: A family uses clear column names.

Nigerian example: A trader uses consistent product codes.

List of best practices:

• Use clear table names.
• Use consistent column names.
• Create a Dates table for time intelligence.
• Test relationships with sample data.
• Write DAX measures with clear names.
• Use CALCULATE for specific filters.
• Build KPIs for key numbers.
• Save your work often.
• Ask Copilot if unsure.
• Document your model.

Mini summary: Best practices: clear names, Dates table, test, save often.

Lesson 14: Building a Complete Data Model

Let’s build a complete data model.

Step 1: Create tables: Products, Customers, Sales, Dates.

Step 2: Add them to the data model.

Step 3: Create relationships: Products→Sales, Customers→Sales, Dates→Sales.

Step 4: Create DAX measures: Total Sales, Average Sale, Unique Customers.

Step 5: Create time intelligence: Sales YTD, Sales MTD.

Step 6: Build KPIs: Total Sales vs Target.

Step 7: Create a PivotTable using your measures.

Step 8: Save as “My_Data_Model”.

Illustration:

  +-----------+     +-----------+
  | Products  |-----| Sales     |
  +-----------+     +-----------+
                         |
                         |
  +-----------+     +-----------+
  | Customers |-----| Dates     |
  +-----------+     +-----------+

  DAX: Total Sales, Sales YTD, KPIs
  

Mini summary: A complete data model connects tables and creates powerful measures. Copilot helps.

Lesson 15: Putting It All Together – Your Data Model Toolkit

You now have a powerful toolkit. Let’s review.

Power Pivot: Connect tables with relationships.

Data Model: The collection of connected tables.

DAX Measures: Powerful calculations.

CALCULATE: Change the context of a measure.

Time Intelligence: Year-to-date, month-to-date, and more.

KPIs: Track performance against targets.

Copilot: Your helper for every step.

Illustration:

  Your Toolkit:
  +----------+  +----------+  +----------+
  | Power    |  | DAX      |  | CALCULATE|
  | Pivot    |  | Measures |  |          |
  +----------+  +----------+  +----------+
  +----------+  +----------+  +----------+
  | Time     |  | KPIs     |  | Copilot  |
  | Intel    |  |          |  |          |
  +----------+  +----------+  +----------+
  

Mini summary: You now have a toolkit of Power Pivot skills. Keep practising and combining them.

Key Vocabulary

Word	Simple Definition
Power Pivot	A tool that connects tables and creates powerful calculations.
Data model	The collection of all your connected tables.
Relationship	A connection between two tables based on a shared column.
DAX	Data Analysis Expressions – a formula language for Power Pivot.
Measure	A calculation in Power Pivot.
CALCULATE	A DAX function that changes the filter context.
Time intelligence	Functions for calculating periods like YTD.
YTD	Year-to-date.
MTD	Month-to-date.
KPI	Key Performance Indicator – a number showing performance.
Context	The filters applied to a measure.
Dates table	A table with all dates for time intelligence.
Distinct count	Counting unique values.
Target	A goal for a KPI.
Diagram view	A view in Power Pivot showing relationships.

Important Concepts

• Power Pivot connects tables: Relationships build bridges between data.
• Enable Power Pivot: Add-in must be turned on.
• The data model is the foundation: All tables live there.
• Relationships connect tables: Shared columns link them.
• DAX measures are powerful: They calculate across tables.
• CALCULATE changes context: Filter measures for specific situations.
• Time intelligence shows periods: YTD, MTD, and more.
• KPIs track performance: Compare actual vs target.
• Copilot writes DAX: Just ask in plain English.
• Test everything: Always verify your results.

Step-by-step Explanations

How to enable Power Pivot step by step

1. Click File > Options.
2. Click Add-ins.
3. Choose COM Add-ins.
4. Click Go.
5. Check Microsoft Power Pivot for Excel.
6. Click OK.

How to create a relationship step by step

1. Open Power Pivot.
2. Click Diagram View.
3. Drag the shared column from one table to the other.
4. The relationship is created.

How to create a DAX measure step by step

1. Click Power Pivot > Measures > New Measure.
2. Type a name.
3. Type the DAX formula.
4. Click OK.

How to use CALCULATE step by step

1. Create a base measure.
2. Create a new measure using CALCULATE.
3. Add a filter condition.
4. Test the result.

How to create a KPI step by step

1. Create a DAX measure.
2. Ask Copilot: “Create a KPI.”
3. Set a target.
4. Choose status colours.
5. Add the KPI to your dashboard.

Real-life Examples

• Shops: Connect products, customers, and sales.
• Schools: Connect students, subjects, and scores.
• Homes: Connect items, prices, and shops.
• Offices: Connect employees, departments, and salaries.
• Hospitals: Connect patients, doctors, and treatments.

Nigerian Examples

• Market traders: Connect products, customers, and sales.
• POS operators: Connect transactions, agents, and locations.
• Schools in Lagos: Connect students, classes, and scores.
• Transporters: Connect routes, vehicles, and fares.
• Church groups: Connect members, donations, and events.

Fun Examples Children Can Relate To

• Video game scores: Connect players, games, and scores.
• Football league: Connect teams, matches, and points.
• Pocket money: Connect days, items, and amounts.
• Chores: Connect people, chores, and time.
• Snack list: Connect snacks, prices, and shops.

Everyday Examples

• Shopping list: Connect items, prices, and shops.
• School timetable: Connect subjects, teachers, and times.
• Exercise log: Connect days, exercises, and minutes.
• Reading list: Connect books, authors, and pages.
• Grocery budget: Connect months, items, and spending.

Parent Tips

• Encourage your child to build simple data models.
• Let them practice with real data like shopping receipts.
• Use everyday examples to explain relationships.
• Set a small daily practice time.
• Celebrate small wins, like a working measure.
• Be patient. Power Pivot takes practice.
• Ask them to teach you what they learned.
• Keep it fun. Use games and stories.
• Remind them to test their measures.
• Encourage them to ask Copilot when unsure.

Interesting Facts

• Power Pivot was first released in 2010.
• It can handle millions of rows.
• DAX stands for Data Analysis Expressions.
• CALCULATE is one of the most powerful DAX functions.
• Time intelligence is used in every industry.
• KPIs help businesses track performance.
• Copilot can write DAX measures for you.
• Power Pivot is used by data analysts worldwide.

Did You Know?

• Did you know that Power Pivot can connect many tables at once?
• Did you know that relationships work like VLOOKUP but faster?
• Did you know that DAX measures can reference other measures?
• Did you know that CALCULATE can add multiple filters?
• Did you know that time intelligence needs a Dates table?
• Did you know that KPIs can change colour based on performance?
• Did you know that Copilot can explain any DAX function?
• Did you know that Power Pivot is used in Power BI too?

Remember This

• Power Pivot connects tables with relationships.
• Enable Power Pivot from Add-ins.
• Add tables to the data model.
• Relationships connect tables by shared columns.
• DAX measures are powerful calculations.
• CALCULATE changes the context.
• Time intelligence shows YTD, MTD, and more.
• KPIs track performance.
• Copilot writes DAX for you.
• Always test your measures.

Common Mistakes

• Wrong column for relationship.
• Duplicate values in relationship column.
• Wrong DAX formula.
• Missing Dates table.
• Forgetting to save.
• Not testing measures.
• Not using clear names.
• Trusting Copilot blindly.

Best Practices

• Use clear table names.
• Use consistent column names.
• Create a Dates table for time intelligence.
• Test relationships with sample data.
• Write DAX measures with clear names.
• Use CALCULATE for specific filters.
• Build KPIs for key numbers.
• Save your work often.
• Ask Copilot if unsure.
• Document your model.

Illustrations and Diagrams

Power Pivot Data Model

  +-----------+     +-----------+
  | Products  |-----| Sales     |
  +-----------+     +-----------+
                         |
                         |
  +-----------+     +-----------+
  | Customers |-----| Dates     |
  +-----------+     +-----------+
  

DAX Measure Illustration

  Total Sales = SUM(Sales[Amount])
       |
       V
  500 + 300 + 400 = 1200
  

CALCULATE Illustration

  Total Sales = SUM(Sales[Amount])
       |
       V
  Sales for Lagos = CALCULATE(
      [Total Sales],
      Customers[City] = "Lagos"
  )
       |
       V
  Only Lagos sales
  

Time Intelligence Illustration

  Sales YTD = TOTALYTD(
      [Total Sales],
      Dates[Date]
  )
       |
       V
  Sales from Jan 1 to today
  

Your Learning Journey

  Level One: Getting Ready with Copilot
        |
        V
  Level Two: Formulas, Cleaning, Analysis, Charts, Automation
        |
        V
  Level Three Module One: Advanced Formulas
        |
        V
  Level Three Module Two: Power Query
        |
        V
  Level Three Module Three: Power Pivot
        |
        V
  Level Three Module Four: AI Insights & Power BI
        |
        V
  Copilot in Excel Master 🎉
  

Comparison Tables

Power Query vs Power Pivot

Feature	Power Query	Power Pivot
Purpose	Clean and transform data	Connect and analyse data
Data storage	Temporary	Data model
Calculations	Basic	DAX measures
Best for	Data prep	Data modelling

SUM vs CALCULATE

Feature	SUM	CALCULATE
Purpose	Adds numbers	Changes filter context
Example	SUM(Sales[Amount])	CALCULATE([Total], City="Lagos")
Flexibility	Basic	Powerful

YTD vs MTD

Function	Meaning	Example
TOTALYTD	Year-to-date	Jan 1 to today
TOTALMTD	Month-to-date	1st of month to today

Power Pivot vs Normal PivotTable

Feature	Power Pivot	Normal PivotTable
Data size	Millions of rows	Thousands of rows
Relationships	Yes	No
DAX measures	Yes	No
Best for	Complex models	Simple summaries

Lesson Summaries

Lesson 1: Power Pivot connects tables and creates powerful calculations.

Lesson 2: Enable Power Pivot from File > Options > Add-ins.

Lesson 3: Add tables to the data model.

Lesson 4: Relationships connect tables by shared columns.

Lesson 5: The data model is your collection of connected tables.

Lesson 6: DAX measures are powerful calculations.

Lesson 7: Common DAX functions include SUM, AVERAGE, COUNT, DISTINCTCOUNT.

Lesson 8: CALCULATE changes the context of a measure.

Lesson 9: Time intelligence shows YTD, MTD, and more.

Lesson 10: KPIs track performance against targets.

Lesson 11: Copilot explains Power Pivot and writes DAX.

Lesson 12: Common mistakes include wrong relationships.

Lesson 13: Best practices: clear names, Dates table, test.

Lesson 14: Build a complete data model with Copilot.

Lesson 15: You now have a toolkit of Power Pivot skills.

End-of-Module Summary

Congratulations! You have finished Module Three of Copilot in Microsoft Excel – Level Three. You learned what Power Pivot is. You learned how to enable it, add tables to the data model, create relationships, and understand the data model. You learned about DAX measures, common DAX functions, CALCULATE, and time intelligence. You learned how to build KPIs and how Copilot helps. You learned common mistakes and best practices. Most importantly, you can now connect tables and create powerful calculations. Keep practising, and you will become an Excel master!

Frequently Asked Questions

1. What is Power Pivot? A tool that connects tables and creates powerful calculations.
2. How do I enable Power Pivot? File > Options > Add-ins > COM Add-ins.
3. What is a relationship? A connection between two tables by a shared column.
4. What is a data model? The collection of all your connected tables.
5. What is DAX? Data Analysis Expressions – a formula language.
6. How do I create a measure? Power Pivot > Measures > New Measure.
7. What is CALCULATE? A DAX function that changes the filter context.
8. What is time intelligence? Functions for periods like YTD and MTD.
9. What is a KPI? Key Performance Indicator – a number showing performance.
10. How does Copilot help? It explains Power Pivot and writes DAX.

Matching Exercises

Match the term to its meaning.

Term	Meaning
1. Power Pivot	A. Changes filter context
2. Relationship	B. Connects tables and creates calculations
3. DAX	C. Connection between tables
4. CALCULATE	D. Formula language for Power Pivot
5. KPI	E. Key Performance Indicator

Answers: 1-B, 2-C, 3-D, 4-A, 5-E

Scenario-based Exercises

1. Scenario: You have products and sales in separate tables. What do you do?
   Answer: Create a relationship.
2. Scenario: You want total sales. What do you create?
   Answer: A DAX measure.
3. Scenario: You want sales for Lagos only. What do you use?
   Answer: CALCULATE.
4. Scenario: You want year-to-date sales. What do you use?
   Answer: TOTALYTD.
5. Scenario: You want to track performance against a target. What do you build?
   Answer: A KPI.

Group Activity

Title: “Build a Data Model Together”

Instructions: In groups of 3–4, create three tables (Products, Customers, Sales). Use Power Pivot to create relationships and write DAX measures. One person types, one person asks Copilot, one person checks, and one person presents. Share your model with the class.

Goal: Practice building a data model with Power Pivot.

Individual Activity

Task: Create a small Excel workbook with two tables: Products and Sales. Use Power Pivot to:

• Add tables to the data model.
• Create a relationship.
• Create a DAX measure for Total Sales.
• Use CALCULATE to filter by product.

Hint: Start with clean tables with clear headers.

Mini Project

Project: “My Sales Data Model”

Create an Excel workbook with three tables: Products, Customers, and Sales. Use Power Pivot to:

• Add all tables to the data model.
• Create relationships.
• Create DAX measures: Total Sales, Average Sale, Unique Customers.
• Create time intelligence: Sales YTD.
• Build a KPI for Total Sales vs Target.

Example output:

  +----------------------------------------+
  |         SALES DATA MODEL               |
  |  Total Sales: ₦250,000                 |
  |  Average Sale: ₦2,500                  |
  |  Unique Customers: 150                 |
  |  Sales YTD: ₦250,000                   |
  |  KPI: On Target (Green)                |
  +----------------------------------------+
  

Practical Assignment

Assignment: Create a new Excel workbook called PowerPivot_Project. In the workbook, do the following:

1. Create three tables: Products, Customers, Sales.
2. Enable Power Pivot.
3. Add all tables to the data model.
4. Create relationships between the tables.
5. Create DAX measures: Total Sales, Average Sale, Unique Customers.
6. Create a time intelligence measure: Sales YTD.
7. Build a KPI for Total Sales.
8. Create a PivotTable using your measures.
9. Save the workbook.
10. Write a short explanation of your model.

Submit: Your workbook file and screenshots of your data model and measures.

Key Takeaways

• Power Pivot connects tables with relationships.
• Enable Power Pivot from Add-ins.
• Add tables to the data model.
• Relationships connect tables by shared columns.
• DAX measures are powerful calculations.
• CALCULATE changes the context.
• Time intelligence shows YTD, MTD, and more.
• KPIs track performance.
• Copilot writes DAX for you.
• Always test your measures.

Classroom Discussion Questions

1. What is Power Pivot?
2. Why is Power Pivot useful?
3. What is a relationship?
4. What is a data model?
5. What is a DAX measure?
6. What does CALCULATE do?
7. What is time intelligence?
8. What is a KPI?
9. How does Copilot help with Power Pivot?
10. What did Ada learn from her data bridge?

Preparation for Module Four

In Module Four, we will learn about AI insights and Power BI. We will cover:

• Using Copilot for advanced analysis and forecasting.
• Anomaly detection and smart narratives.
• Connecting Excel to Power BI.
• Building interactive dashboards.
• Completing the final certification project.

To prepare, make sure you have completed the practical assignment and have a clean data model ready. Review your notes on Power Pivot and DAX. Bring your creativity!

See you in Module Four!

---

End of Module Three – Copilot in Microsoft Excel – Level Three

Module Four: AI Insights, Power BI and Certification Project – Becoming a True Excel Master

“Copilot in Microsoft Excel – Level Three” – Become a true Excel master

Module Introduction

Welcome to the final module, young Excel master! You have come a very long way. In Module One, you learned advanced formulas and dynamic arrays. In Module Two, you learned Power Query and how to clean data automatically. In Module Three, you learned Power Pivot and how to connect tables with relationships and DAX measures.

Now, in Module Four, we will learn how to use AI insights and Power BI to take your skills to the highest level. You will learn how Copilot can find hidden patterns, detect unusual values, forecast the future, and tell the story of your data in plain words. You will also learn how to connect Excel with Power BI to build interactive dashboards.

Finally, you will complete your certification project. This project will bring together everything you have learned in all three levels. By the end of this module, you will be a true Copilot in Excel master.

Let’s begin!

Learning Objectives

After finishing this module, you will be able to:

• Use Copilot for advanced analysis and forecasting.
• Detect anomalies and unusual values with Copilot.
• Create smart narratives that explain your data.
• Understand what Power BI is.
• Connect Excel data to Power BI.
• Build interactive dashboards with Power BI and Copilot.
• Publish and share your dashboards.
• Prepare and complete your certification project.
• Review everything you learned in Level Three.
• Plan your next steps as an Excel expert.

Warm-up Story: Tunde’s Magic Crystal Ball

Tunde runs a small phone accessories shop in Ibadan. He sells chargers, earphones, phone cases, and screen protectors. Every month, he records his sales in Excel. He has been using Power Query and Power Pivot, so his data is very clean and well organised.

One day, Tunde’s father asked him, “Tunde, what will sales be like next month? Which product will sell the most?” Tunde did not know. He could see past data, but he could not see the future.

His friend Ada said, “Tunde, use Copilot for AI insights! It can analyse your data, find trends, and even forecast future sales. It is like a magic crystal ball.”

Tunde opened Copilot in Excel. He asked: “What is the trend in my sales?” Copilot replied: “Sales have been growing by 10% each month.” He asked: “Forecast my sales for next month.” Copilot replied: “Next month’s sales are forecast at ₦280,000.” He asked: “Are there any unusual values?” Copilot replied: “Yes, there was one sale on 15th March that was 5 times higher than usual.”

Tunde was amazed. He also connected his Excel data to Power BI. He built a dashboard with charts and KPIs. He published it online and shared the link with his father. His father could see the live dashboard on his phone.

His father said, “Tunde, you have become a true data scientist!” Tunde smiled. He learned that AI insights and Power BI make data come alive.

Moral of the story: AI insights find patterns and predict the future. Power BI turns data into interactive dashboards. Copilot helps at every step.

Main Lessons

Lesson 1: What Are AI Insights?

Definition: AI insights are useful discoveries that artificial intelligence finds in your data.

Why it is important: AI insights help you understand your data faster and see things you might miss.

Simple explanation: Imagine a detective who looks at all your clues and tells you what happened.

Real-life example: A bank uses AI insights to find unusual transactions.

School example: A teacher uses AI insights to find which students need help.

Home example: A family uses AI insights to find where their money goes.

Nigerian example: A trader uses AI insights to find which product sells best.

Illustration:

  Raw Data
      |
      V
  AI Insights (Copilot)
      |
      V
  Useful Discoveries
      |
      V
  Better Decisions 🎉
  

Mini summary: AI insights are useful discoveries in your data. Copilot finds them for you.

Lesson 2: Forecasting with Copilot

Definition: Forecasting means predicting what will happen in the future based on past data.

Why it is important: Forecasting helps you plan ahead.

Simple explanation: Imagine looking at a weather forecast to decide if you need an umbrella.

Real-life example: A bank forecasts profits for next year.

School example: A teacher forecasts student performance.

Home example: A family forecasts monthly spending.

Nigerian example: A trader forecasts sales for the next month.

Illustration:

  Past Sales:
  Jan: 100
  Feb: 150
  Mar: 200
  Apr: 250

  Forecast:
  May: 300 (predicted)

  Ask Copilot: "Forecast sales for next month"
  

Step-by-step:

1. Select your data table with dates and values.
2. Ask Copilot: “Forecast [column] for the next [period].”
3. Copilot shows the forecast.
4. Copilot can even create a forecast chart for you.

Mini summary: Forecasting predicts the future from past data. Copilot does it in seconds.

Lesson 3: Detecting Anomalies with Copilot

Definition: An anomaly is a value that is very different from the others – an unusual value.

Why it is important: Anomalies can be mistakes or important discoveries.

Simple explanation: Imagine a class where everyone scored 70-80, but one student scored 10. The 10 is an anomaly.

Real-life example: A bank finds an unusual transaction that might be fraud.

School example: A teacher finds a very low score in a class of high scores.

Home example: A family finds a very high electricity bill in a month when they were away.

Nigerian example: A trader finds a huge sale on a day when the shop was closed.

Illustration:

  Scores: 75, 78, 80, 82, 10, 79
                         ^
                     Anomaly

  Ask Copilot: "Find anomalies in this column"
  

Step-by-step:

1. Select your data column.
2. Ask Copilot: “Find anomalies in this column.”
3. Copilot highlights the unusual values.
4. Check if they are mistakes or important.

Mini summary: Anomalies are unusual values. Copilot finds them for you.

Lesson 4: Smart Narratives – Telling the Story of Your Data

Definition: A smart narrative is a short, plain-language summary of your data created by AI.

Why it is important: Smart narratives make your data easy to understand for everyone.

Simple explanation: Imagine asking a friend to explain a complicated chart in one paragraph. That is a smart narrative.

Real-life example: A bank uses smart narratives in its monthly reports.

School example: A teacher uses smart narratives for student progress reports.

Home example: A family uses smart narratives for budget summaries.

Nigerian example: A trader uses smart narratives for weekly sales reports.

Illustration:

  Data + Charts
       |
       V
  Copilot Smart Narrative
       |
       V
  "Sales grew 15% this month.
   Phone cases were the top product.
   Average sale was ₦2,500."
  

Step-by-step:

1. Create a PivotTable or chart.
2. Click “Insert” > “Smart Narrative.”
3. A text box appears with an AI-written summary.
4. You can edit the text if needed.

Mini summary: Smart narratives explain your data in words. Copilot writes them for you.

Lesson 5: What is Power BI?

Definition: Power BI is a Microsoft tool that turns your data into interactive dashboards and reports.

Why it is important: Power BI helps you share your insights with others in a beautiful way.

Simple explanation: Imagine turning your Excel charts into a live dashboard that anyone can explore.

Real-life example: A bank uses Power BI to monitor branch performance.

School example: A school uses Power BI to track student progress.

Home example: A family uses Power BI for a family budget dashboard.

Nigerian example: A trader uses Power BI to track sales across multiple shops.

Illustration:

  Excel Data
      |
      V
  Power BI
      |
      V
  Interactive Dashboard
      |
      V
  Share with others 🎉
  

Mini summary: Power BI turns data into interactive dashboards. It works well with Excel.

Lesson 6: Connecting Excel to Power BI

Definition: Connecting means linking your Excel data to Power BI so it can be used there.

Why it is important: This lets you reuse your Excel work in Power BI.

Simple explanation: Like carrying your ingredients from the kitchen to the dining table.

Real-life example: A bank connects Excel data to Power BI.

School example: A teacher connects student data to Power BI.

Home example: A family connects budget data to Power BI.

Nigerian example: A trader connects sales data to Power BI.

Illustration:

  Excel Workbook
      |
      V
  Power BI Desktop
      |
      V
  Get Data → Excel
      |
      V
  Choose your file
      |
      V
  Load data into Power BI
  

Step-by-step:

1. Open Power BI Desktop.
2. Click “Get Data.”
3. Choose “Excel Workbook.”
4. Select your Excel file.
5. Choose the tables you want.
6. Click “Load.”

Mini summary: Connecting Excel to Power BI is easy. Just use Get Data in Power BI.

Lesson 7: Building Interactive Dashboards

Definition: An interactive dashboard lets viewers click, filter, and explore your data.

Why it is important: Interactive dashboards are more useful than static reports.

Simple explanation: Imagine a dashboard where you can click a button to see only Lagos sales. That is interactive.

Real-life example: A bank builds an interactive dashboard for branch managers.

School example: A school builds an interactive dashboard for student performance.

Home example: A family builds an interactive dashboard for spending.

Nigerian example: A trader builds an interactive dashboard for daily sales.

Illustration:

  +----------------------------------------+
  |        INTERACTIVE DASHBOARD           |
  |  [Filter: Lagos ▼]                     |
  |  +--------+  +--------+  +--------+    |
  |  | Sales  |  | Profit |  |Customers|   |
  |  | ₦250k  |  | ₦80k   |  | 150     |   |
  |  +--------+  +--------+  +--------+    |
  |  +------------------+  +------------+  |
  |  | Bar Chart        |  | Pie Chart  |  |
  |  +------------------+  +------------+  |
  +----------------------------------------+
  

Step-by-step:

1. Open Power BI Desktop.
2. Load your Excel data.
3. Drag fields onto the canvas to create charts.
4. Add slicers (filters) for interactivity.
5. Ask Copilot: “Suggest improvements to my dashboard.”
6. Format and arrange the visuals.

Mini summary: Interactive dashboards let viewers explore data. Power BI makes them easily.

Lesson 8: Using Copilot in Power BI

Definition: Copilot in Power BI helps you create dashboards and insights with plain English.

Why it is important: Copilot makes Power BI easier and faster.

Simple explanation: Like having a helpful assistant who builds your dashboard for you.

Real-life example: A bank uses Copilot to create branch reports.

School example: A teacher uses Copilot to create student dashboards.

Home example: A family uses Copilot to create spending dashboards.

Nigerian example: A trader uses Copilot to create sales dashboards.

Illustration:

  You: "Create a dashboard showing sales by product"
       |
       V
  Copilot: Builds the dashboard for you
       |
       V
  You: Refine and share 🎉
  

Step-by-step:

1. Open Copilot in Power BI.
2. Type your request in plain English.
3. Copilot creates the visual or dashboard.
4. Review and adjust as needed.

Mini summary: Copilot helps you build Power BI dashboards from plain English.

Lesson 9: Publishing and Sharing Dashboards

Definition: Publishing means putting your dashboard online so others can see it.

Why it is important: Sharing lets others benefit from your analysis.

Simple explanation: Like posting a photo online so friends can see it.

Real-life example: A bank publishes dashboards for managers.

School example: A school publishes dashboards for parents.

Home example: A family publishes a budget dashboard for members.

Nigerian example: A trader publishes a sales dashboard for staff.

Illustration:

  Dashboard in Power BI Desktop
       |
       V
  Click "Publish"
       |
       V
  Choose workspace
       |
       V
  Share link with others 🎉
  

Step-by-step:

1. Finish your dashboard in Power BI Desktop.
2. Click “Publish.”
3. Choose a workspace (like My Workspace).
4. Click “Select.”
5. Open the Power BI service online and share the link.

Mini summary: Publish and share dashboards so others can see your insights.

Lesson 10: AI Insights for Forecasting and What-If Analysis

Definition: What-if analysis means asking, “What if this changed?” and seeing the effect.

Why it is important: It helps you plan for different situations.

Simple explanation: Imagine asking, “What if I sell 20% more? How much profit will I make?”

Real-life example: A bank asks, “What if interest rates go up?”

School example: A teacher asks, “What if every student studies one more hour?”

Home example: A family asks, “What if we save ₦5,000 more each month?”

Nigerian example: A trader asks, “What if I increase prices by 10%?”

Illustration:

  Current Sales: ₦100,000
       |
       V
  What if: Price increases by 10%?
       |
       V
  New Sales: ₦110,000 (predicted)
  

Step-by-step:

1. Create your base measure.
2. Ask Copilot: “What if I change [something]?”
3. Copilot shows the effect.
4. Use the answer to make decisions.

Mini summary: What-if analysis explores different situations. Copilot helps you try them.

Lesson 11: Common Mistakes with AI Insights and Power BI

Definition: Mistakes happen. Knowing them helps you avoid them.

Why it is important: Wrong insights can lead to wrong decisions.

Simple explanation: Like following a map with the wrong road.

Real-life example: A bank forecasts wrong sales and loses money.

School example: A teacher uses wrong insights to help students.

Home example: A family uses wrong insights for budgeting.

Nigerian example: A trader uses wrong forecasts for buying stock.

Table of common mistakes:

Mistake	What Happens	How to Fix
Using messy data	Wrong insights	Clean data first
Too little data	Weak forecast	Use more data
Ignoring context	Wrong decisions	Consider the situation
Trusting AI blindly	Wrong conclusions	Check with experts
Sharing without checking	Others get wrong info	Review before sharing
Not refreshing data	Outdated insights	Refresh regularly

Mini summary: Common mistakes include messy data and trusting AI blindly. Always check.

Lesson 12: Best Practices for AI Insights and Power BI

Definition: Best practices are good habits that make AI insights reliable.

Why it is important: Good habits ensure correct decisions.

Simple explanation: Like washing your hands before cooking.

Real-life example: A bank checks AI insights carefully.

School example: A teacher verifies Copilot results.

Home example: A family double-checks forecasts.

Nigerian example: A trader verifies sales forecasts.

List of best practices:

• Clean your data before analysis.
• Use enough data for forecasting.
• Consider the business context.
• Always verify Copilot’s insights.
• Review dashboards before sharing.
• Refresh data regularly.
• Use clear titles and labels.
• Save your work often.
• Share findings clearly.
• Keep learning new features.

Mini summary: Best practices: clean data, verify insights, review before sharing.

Lesson 13: Preparing for the Certification Project

Your certification project brings together everything you have learned.

Project idea: Build a complete Business Intelligence solution.

Steps:

1. Create or collect a real dataset.
2. Clean the data with Power Query.
3. Build a data model with Power Pivot.
4. Write DAX measures.
5. Create charts and KPIs.
6. Use Copilot for insights and forecasting.
7. Build a Power BI dashboard.
8. Publish and share it.

Illustration:

  Raw Data
      |
      V
  Power Query (Clean)
      |
      V
  Power Pivot (Model + DAX)
      |
      V
  Charts + KPIs
      |
      V
  AI Insights (Forecast, Anomalies)
      |
      V
  Power BI Dashboard
      |
      V
  Publish + Share 🎉
  

Mini summary: The certification project combines all skills. Plan carefully and start early.

Lesson 14: Reviewing Everything You Learned in Level Three

You have learned so much in Level Three. Let’s review.

Module One: Advanced formulas (FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, LAMBDA).

Module Two: Power Query (clean, transform, refresh).

Module Three: Power Pivot (relationships, DAX, time intelligence, KPIs).

Module Four: AI insights (forecasting, anomalies, smart narratives) and Power BI.

Illustration:

  Level Three Skills:
  +-----------------+
  | Advanced        |
  | Formulas        |
  +-----------------+
  +-----------------+
  | Power Query     |
  +-----------------+
  +-----------------+
  | Power Pivot     |
  +-----------------+
  +-----------------+
  | AI Insights     |
  +-----------------+
  +-----------------+
  | Power BI        |
  +-----------------+
  

Mini summary: You have mastered advanced formulas, Power Query, Power Pivot, AI insights, and Power BI.

Lesson 15: Your Certification and Beyond

Your certification is proof that you are a Copilot in Excel master.

Next steps:

• Complete your certification project.
• Share your portfolio online.
• Help others learn Excel and Copilot.
• Apply your skills in school, home, or business.
• Keep learning – Excel keeps improving.

Illustration:

  Certification
       |
       V
  Portfolio
       |
       V
  Share with others
       |
       V
  Help others learn
       |
       V
  Apply skills
       |
       V
  Excel Master 🎉
  

Mini summary: Your certification opens doors. Keep growing and helping others.

Key Vocabulary

Word	Simple Definition
AI insight	A useful discovery found by AI.
Forecast	A prediction of the future.
Anomaly	An unusual value.
Smart narrative	A plain-language summary of data.
Power BI	A tool for interactive dashboards.
Dashboard	A page with charts and key numbers.
Slicer	A filter button on a dashboard.
Publish	To put a dashboard online.
What-if analysis	Asking “what if” questions.
Workspace	An online place for your dashboards.
Refresh	Updating data to the latest.
Context	The situation around your data.
Portfolio	A collection of your best work.
Certification	Proof of your skills.
Insight	A useful discovery.

Important Concepts

• AI insights find hidden patterns: Copilot discovers them for you.
• Forecasting predicts the future: Based on past data.
• Anomalies are unusual values: They may be mistakes or discoveries.
• Smart narratives explain data: In plain language.
• Power BI creates interactive dashboards: Share with anyone.
• Excel connects to Power BI: Reuse your work easily.
• Copilot works in Power BI too: Plain English requests.
• Publishing shares your work: Anyone can see it online.
• What-if analysis explores options: Ask “what if” questions.
• Always verify AI insights: Trust but check.

Step-by-step Explanations

How to forecast with Copilot step by step

1. Select data with dates and values.
2. Ask Copilot: “Forecast [column] for next [period].”
3. Copilot shows the forecast.
4. Review and use it for planning.

How to find anomalies step by step

1. Select your data column.
2. Ask Copilot: “Find anomalies in this column.”
3. Copilot highlights the unusual values.
4. Investigate each anomaly.

How to create a smart narrative step by step

1. Create a PivotTable or chart.
2. Click “Insert” > “Smart Narrative.”
3. An AI-written summary appears.
4. Edit if needed.

How to connect Excel to Power BI step by step

1. Open Power BI Desktop.
2. Click “Get Data” > “Excel Workbook.”
3. Select your Excel file.
4. Choose tables and click “Load.”

How to publish a dashboard step by step

1. Finish your dashboard.
2. Click “Publish.”
3. Choose a workspace.
4. Click “Select.”
5. Share the link online.

Real-life Examples

• Shops: Forecast sales and find top products.
• Schools: Predict student performance and find anomalies.
• Homes: Forecast spending and detect unusual bills.
• Offices: Build dashboards for teams.
• Hospitals: Track patient trends with Power BI.

Nigerian Examples

• Market traders: Forecast sales for market days.
• POS operators: Detect unusual transactions.
• Schools in Lagos: Track student progress with dashboards.
• Transporters: Forecast fuel costs.
• Church groups: Track donations with dashboards.

Fun Examples Children Can Relate To

• Video game scores: Forecast your next high score.
• Football league: Predict league champions.
• Pocket money: Forecast savings.
• Chores: Detect unusually slow chores.
• Snack list: Find which snacks you buy most.

Everyday Examples

• Shopping list: Forecast next week’s spending.
• School timetable: Track study time trends.
• Exercise log: Predict weekly exercise minutes.
• Reading list: Forecast books read per month.
• Grocery budget: Detect unusual spending.

Parent Tips

• Encourage your child to use AI insights on real data.
• Let them practice with family budget or shopping data.
• Use everyday examples to explain forecasts.
• Set a small daily practice time.
• Celebrate small wins, like a correct forecast.
• Be patient. AI insights take practice.
• Ask them to teach you what they learned.
• Keep it fun. Use games and stories.
• Remind them to verify AI insights.
• Encourage them to ask Copilot when unsure.

Interesting Facts

• AI can forecast future values with high accuracy.
• Anomaly detection is used in banks to catch fraud.
• Smart narratives save hours of report writing.
• Power BI is used by over 250,000 companies.
• You can view Power BI dashboards on your phone.
• Copilot in Power BI works in many languages.
• What-if analysis is used by presidents and CEOs.
• Excel and Power BI are a powerful team.

Did You Know?

• Did you know that Copilot can forecast up to 6 months ahead?
• Did you know that anomalies can be mistakes or important discoveries?
• Did you know that smart narratives update when data changes?
• Did you know that Power BI has a free version?
• Did you know that you can share Power BI dashboards with anyone?
• Did you know that Copilot can suggest the best chart for your data?
• Did you know that what-if analysis can use sliders?
• Did you know that Excel and Power BI use the same data model?

Remember This

• AI insights find hidden patterns.
• Forecasting predicts the future.
• Anomalies are unusual values.
• Smart narratives explain data in words.
• Power BI creates interactive dashboards.
• Excel connects easily to Power BI.
• Copilot works in Power BI too.
• Publishing shares your work.
• What-if analysis explores options.
• Always verify AI insights.

Common Mistakes

• Using messy data.
• Too little data for forecasting.
• Ignoring business context.
• Trusting AI blindly.
• Sharing without checking.
• Not refreshing data.
• Not testing what-if analysis.
• Not saving work.

Best Practices

• Clean your data before analysis.
• Use enough data for forecasting.
• Consider the business context.
• Always verify Copilot’s insights.
• Review dashboards before sharing.
• Refresh data regularly.
• Use clear titles and labels.
• Save your work often.
• Share findings clearly.
• Keep learning new features.

Illustrations and Diagrams

AI Insights Workflow

  Clean Data
      |
      V
  Copilot Analysis
      |
      V
  Insights (Forecast, Anomalies, Narratives)
      |
      V
  Better Decisions 🎉
  

Forecast Illustration

  300 |           * (forecast)
  250 |        *
  200 |     *
  150 |  *
  100 |
      +----------------
       Jan Feb Mar Apr May
  

Anomaly Detection Illustration

  Sales: 100, 110, 105, 500, 115
                        ^
                    Anomaly (500)
  

Power BI Dashboard Illustration

  +----------------------------------------+
  |        INTERACTIVE DASHBOARD           |
  |  [Filter: Lagos ▼]                     |
  |  +--------+  +--------+  +--------+    |
  |  | Sales  |  | Profit |  |Customers|   |
  |  +--------+  +--------+  +--------+    |
  |  +------------------+  +------------+  |
  |  | Bar Chart        |  | Pie Chart  |  |
  |  +------------------+  +------------+  |
  +----------------------------------------+
  

Your Learning Journey

  Level One: Getting Ready with Copilot
        |
        V
  Level Two: Formulas, Cleaning, Analysis, Charts, Automation
        |
        V
  Level Three Module One: Advanced Formulas
        |
        V
  Level Three Module Two: Power Query
        |
        V
  Level Three Module Three: Power Pivot
        |
        V
  Level Three Module Four: AI Insights & Power BI
        |
        V
  Copilot in Excel Master 🎉
  

Comparison Tables

Excel Dashboard vs Power BI Dashboard

Feature	Excel Dashboard	Power BI Dashboard
Interactivity	Limited	High
Sharing	Email files	Online link
Data size	Millions of rows	Billions of rows
Best for	Personal use	Team use

Forecast vs What-If

Feature	Forecast	What-If
Purpose	Predict the future	Explore changes
Example	Next month sales	If price rises 10%
Based on	Past data	Assumptions

Anomaly vs Outlier

Term	Meaning	Example
Anomaly	Unusual value in context	Very large transaction
Outlier	Extreme value in statistics	Score of 10 in 70-80 class

Copilot in Excel vs Copilot in Power BI

Feature	Excel	Power BI
Formulas	Yes	No
DAX measures	Yes	Yes
Dashboards	Basic	Advanced
Sharing	Files	Online

Lesson Summaries

Lesson 1: AI insights are useful discoveries found by AI.

Lesson 2: Forecasting predicts the future from past data.

Lesson 3: Anomalies are unusual values.

Lesson 4: Smart narratives explain data in words.

Lesson 5: Power BI creates interactive dashboards.

Lesson 6: Excel connects easily to Power BI.

Lesson 7: Interactive dashboards let viewers explore data.

Lesson 8: Copilot helps build Power BI dashboards.

Lesson 9: Publish and share dashboards online.

Lesson 10: What-if analysis explores different situations.

Lesson 11: Common mistakes include messy data and trusting AI blindly.

Lesson 12: Best practices: clean data, verify insights, review before sharing.

Lesson 13: The certification project combines all skills.

Lesson 14: Review everything you learned in Level Three.

Lesson 15: Your certification opens doors. Keep growing.

End-of-Module Summary

Congratulations! You have finished Module Four of Copilot in Microsoft Excel – Level Three. You learned what AI insights are, how to forecast with Copilot, how to detect anomalies, and how to create smart narratives. You learned what Power BI is, how to connect Excel to Power BI, how to build interactive dashboards, how to use Copilot in Power BI, and how to publish and share dashboards. You learned common mistakes and best practices. You reviewed everything you learned in Level Three. Most importantly, you completed the course and are now a Copilot in Excel master. Keep practising, and keep learning!

Frequently Asked Questions

1. What are AI insights? Useful discoveries found by AI.
2. How do I forecast with Copilot? Ask Copilot: “Forecast [column].”
3. What is an anomaly? An unusual value.
4. What is a smart narrative? A plain-language summary of data.
5. What is Power BI? A tool for interactive dashboards.
6. How do I connect Excel to Power BI? Use Get Data > Excel Workbook.
7. How do I build a dashboard? Drag fields onto the Power BI canvas.
8. How do I publish a dashboard? Click Publish and choose a workspace.
9. What is what-if analysis? Asking “what if” questions.
10. How does Copilot help? It finds insights and builds dashboards from plain English.

Matching Exercises

Match the term to its meaning.

Term	Meaning
1. Forecast	A. Unusual value
2. Anomaly	B. Predicts the future
3. Smart narrative	C. Interactive dashboards
4. Power BI	D. Plain-language summary
5. What-if	E. Exploring changes

Answers: 1-B, 2-A, 3-D, 4-C, 5-E

Scenario-based Exercises

1. Scenario: You want to predict next month’s sales. What do you use?
   Answer: Forecast with Copilot.
2. Scenario: You want to find unusual transactions. What do you use?
   Answer: Anomaly detection.
3. Scenario: You want a written summary of your report. What do you use?
   Answer: Smart narrative.
4. Scenario: You want to share an interactive dashboard online. What do you use?
   Answer: Power BI.
5. Scenario: You want to see what happens if prices increase. What do you use?
   Answer: What-if analysis.

Group Activity

Title: “Build an AI Insights Dashboard Together”

Instructions: In groups of 3–4, create a sales sheet in Excel. Use Copilot to forecast future sales, find anomalies, and write a smart narrative. Then connect the data to Power BI and build a dashboard. One person types, one person asks Copilot, one person builds the dashboard, and one person presents. Share your dashboard with the class.

Goal: Practice AI insights and Power BI together.

Individual Activity

Task: Create a small Excel sheet with ten rows of data (e.g., monthly sales). Use Copilot to:

• Forecast the next month’s value.
• Find any anomalies.
• Write a smart narrative.
• Create a simple what-if analysis.

Hint: Start with a clean table with dates and values.

Mini Project

Project: “My AI Insights Report”

Create an Excel sheet with at least 20 rows of sales data. Use Copilot to:

• Forecast next month’s sales.
• Find anomalies.
• Write a smart narrative.
• Create a what-if analysis.
• Connect to Power BI.
• Build a dashboard.
• Publish the dashboard.

Example output:

  +----------------------------------------+
  |         AI INSIGHTS REPORT             |
  |  Forecast Next Month: ₦280,000         |
  |  Anomalies Found: 2                    |
  |  Smart Narrative: "Sales grew 15%..."  |
  |  Power BI Dashboard: [Link]            |
  +----------------------------------------+
  

Practical Assignment

Assignment: Complete your certification project. Build a complete Business Intelligence solution using everything you learned in Level Three:

1. Create or collect a real dataset (at least 50 rows).
2. Clean the data with Power Query.
3. Build a data model with Power Pivot.
4. Write DAX measures.
5. Create charts and KPIs.
6. Use Copilot for insights, forecasting, and anomalies.
7. Build a Power BI dashboard.
8. Publish and share your dashboard.
9. Write a report explaining your project.
10. Submit your workbook, dashboard link, and report.

Submit: Your workbook file, Power BI dashboard link, and report.

Key Takeaways

• AI insights find hidden patterns.
• Forecasting predicts the future.
• Anomalies are unusual values.
• Smart narratives explain data in words.
• Power BI creates interactive dashboards.
• Excel connects easily to Power BI.
• Copilot works in Power BI too.
• Publishing shares your work.
• What-if analysis explores options.
• Always verify AI insights.

Classroom Discussion Questions

1. What are AI insights and why are they useful?
2. How can forecasting help a business?
3. Why is anomaly detection important?
4. What is a smart narrative?
5. What is Power BI and how does it help?
6. How do you connect Excel to Power BI?
7. Why are interactive dashboards useful?
8. What is what-if analysis?
9. Why should you verify AI insights?
10. What did Tunde learn from his magic crystal ball?

Next Steps After the Course

Congratulations! You have completed the entire Copilot in Microsoft Excel – Level Three course. Here are some next steps you can take:

• Practice: Keep using Excel, Copilot, Power Query, Power Pivot, and Power BI.
• Build: Create more real-world projects and dashboards.
• Share: Publish your dashboards and teach others.
• Explore: Learn more advanced Power BI and DAX features.
• Connect: Join Excel and Power BI communities online.
• Apply: Use your skills in school, home, or business.
• Keep learning: Microsoft keeps adding new Copilot features.

Remember, this is just the beginning. You are now a Copilot in Excel master. Keep practising and keep learning!

---

End of Module Four – Copilot in Microsoft Excel – Level Three

🎉 Congratulations! You have completed the entire Level Three course! 🎉