Module 3

Lesson 1
Slide 1 – Lesson 1: Importing Data into Excel
Alright, everyone, welcome to today’s lesson — Importing Data into Excel. This is
one of those topics that’s going to change the way you work with information,
because it’s all about making Excel work smarter for you. Instead of typing
everything in manually or copying and pasting from various files, we’re going to
learn how to connect directly to the data where it lives, bring it into Excel, and even
keep it up to date automatically.
Today’s session is divided into a few key sections. First, we’ll look at Get &
Transform, which is the built-in tool for importing and shaping data. Then we’ll talk
about Supported File Types, so you know what sources Excel can connect to.
After that, we’ll cover Importing PDF Data, Importing CSV Files, and finally
Import From Folder.
By the end of this session, you’ll not only know where to click in Excel, but also why
you’re doing each step, how to clean up the imported data, and how to set things
up so you can refresh it later without re-doing all your work.

Slide 2 – Get & Transform

Let’s start with Get & Transform. This is actually the new name for what used to
be called Power Query. If you’ve been using Excel for a while, you might remember
Power Query as a separate add-in you had to download. Now, the good news is —
no add-in required. It’s already built right into Excel.
Think of Get & Transform as your “data concierge.” It can:
 Import data from many different formats and locations.
 Transform or modify that data so it’s exactly how you need it — you might
remove unnecessary columns, change text to proper case, split full names
into first and last names, and more.
 Load that data into your Excel workbook or into a Data Model, which is like
Excel’s internal database for more advanced analysis.
The way you do all of this is through something called the Query Editor. This is a
separate window in Excel where you can visually clean and reshape your data
without affecting the original file. One of the great things about Query Editor is that
it records each transformation you do, so you can just hit “Refresh” later and Excel
will repeat those steps on the new data.
You can even combine data from multiple sources in a single Data Model. Imagine
combining a CSV file of sales transactions with an Excel file containing product
details — Get & Transform makes that possible without you having to manually
match them up every time.
Demonstration 1 – Opening Get & Transform
Let’s open Excel, go to the Data tab, and look for the Get & Transform Data
group. If you click the “Get Data” dropdown, you’ll see a long list of sources: Excel
workbooks, text/CSV files, databases, web pages, and more. This is your launchpad
for all imports.
Group Discussion Prompt: “What types of external data do you deal with most
often — Excel files, CSVs, PDFs, or something else?”
Sample Answer: Many people work with CSV files because they’re a common export
format from systems. Others might mention PDFs for reports, or even direct
database connections in business environments.

Slide 3 – Supported File Types

Now, it’s important to know exactly what file types Excel supports for importing. The
short answer is: a lot. But there are some specifics worth knowing.
Through Get & Transform’s Query Editor, Excel supports more database types than
before, including Azure data sources. So if your company stores data in Microsoft’s
cloud, you can connect to it directly.
PDF files are a bit of a special case. In older Excel versions, there was no direct
support — you had to copy and paste from a PDF. In newer versions, we have a
built-in “From PDF” option, but it’s not available everywhere, so we’ll cover
workarounds shortly.
If you’ve used the older Get External Data commands, you can still use them for
some imports, but Microsoft is planning to migrate all of that functionality into Get &
Transform, so this is the tool to focus on going forward.
Demonstration 2 – Exploring Sources
Click “Get Data” and hover over “From File” and “From Database.” Notice the
options for SQL Server, Access, text/CSV, XML, JSON, and more. This is where the
variety of file types comes into play.

Slide 4 – Importing PDF Data

PDF stands for Portable Document Format, created by Adobe. It’s designed so a
document looks the same no matter where it’s opened. That’s great for sharing
reports, but it means the data is locked in a format that’s not easy for Excel to work
with.
Older Excel versions can’t directly import a PDF’s tables. You’d either open the PDF
in Word — which can extract the text and tables — and then copy/paste into Excel,
or you’d use third-party tools. But in newer Excel, if you go to “Get Data” > “From
File” > “From PDF,” you can actually connect to a PDF and pull tables directly.
Demonstration 3 – Importing from PDF
1. Go to Data tab > Get Data > From File > From PDF.
2. Browse and select a PDF file.
3. In the Navigator window, Excel will show a list of tables it found.
4. Select the ones you need and click “Load” to bring them into Excel, or
“Transform Data” to clean them first.
Tip: If the PDF’s tables are poorly formatted, you may still need to do some cleanup
in Query Editor.

Slide 5 – Importing CSV Files

A CSV file — Comma Separated Values — is basically a text file with each value
separated by a comma. It has no formatting — no bold text, no colors — just plain
data. Sometimes, instead of commas, you’ll see semicolons or tabs as separators.
CSV files are extremely common in business because almost every system can
export data in this format. For example, an online store might export orders as a
CSV, or your accounting software might export transactions this way.
Demonstration 4 – Importing a CSV
1. Go to Data tab > Get Data > From File > From Text/CSV.
2. Select your CSV file.
3. Excel will open a preview and automatically detect the delimiter.
4. You can either load the data directly into Excel or click “Transform Data” to
make changes — for example, removing empty columns, renaming headers,
or changing data types.

Slide 6 – Import From Folder

This is one of the most powerful features in Get & Transform. Imagine you have a
folder where each month, someone drops a new sales report. Instead of importing
each file manually, you can connect to the folder itself. That way, Excel will combine
all the files inside and refresh them whenever new files are added.
Demonstration 5 – Importing from Folder
1. Go to Data tab > Get Data > From Folder.
2. Browse to your folder path and click OK.
 3. Excel lists all files in that folder.
4. Click “Combine” to merge them into one dataset.
5. In the Query Editor, you can clean up the combined data just once — and it
applies to all files.
Group Discussion Prompt: “Where could this save you the most time in your
work or studies?”
Sample Answer: Combining weekly or monthly reports automatically; processing
survey results stored in separate files; aggregating multiple branch or department
files without manual copy-paste.

Lesson 2
Slide 1 – Lesson 2: Shaping and Transforming Data
Alright everyone, welcome back. Today we’re moving into Lesson 2, which is all
about shaping and transforming data. This is where we take the raw information
we’ve imported—maybe from a CSV, a folder of files, or a PDF—and make it usable,
clean, and ready for analysis.
Here’s what we’ll cover:
We’ll start with Formatting Data, then move to Transforming Data, then
Filtering Data, followed by Query Settings, and we’ll end with a hands-on
Demonstration: Import and Transform Data.
By the time we’re done, you’ll know how to take messy, inconsistent data and turn
it into something structured, accurate, and ready to use—without having to redo
your work every time the source changes.

Slide 2 – Formatting Data

When we talk about formatting in Get & Transform, we’re not just talking about
making things look nice visually—this is about ensuring the data types and text
formats are correct so everything works properly in calculations and reports.
In Power Query, every column has a data type. These could be text, whole
numbers, decimal numbers, dates, true/false, and so on. Getting this wrong can
cause major issues later—if dates are stored as text, they won’t sort or filter
properly, and if numbers are stored as text, they won’t sum.
Demonstration – Setting Data Types
Let’s open a sample dataset in Power Query.
 1. In Excel, go to Data → Get Data → From File → From Text/CSV, pick a
sample CSV, and click Transform Data.
2. Look at the small icons next to each column name. If you see “ABC123” that
means the type is set to “Any” and isn’t fixed.
3. Click the icon and change it to the correct type—for example, “Whole
Number” for ID columns, “Date” for date columns, and “Text” for names.
Once the data types are set, we can clean and format text columns. Power Query’s
Transform → Format menu has options like:
 Trim – Removes leading and trailing spaces.
 Clean – Removes hidden, non-printable characters.
 Capitalize Each Word, Uppercase, Lowercase – Standardizes
capitalization.
 Add Prefix/Suffix – Adds labels or codes to values.
Group Prompt: “If you were cleaning a ‘Customer Name’ column with inconsistent
capitalization and extra spaces, which formatting steps would you take?”
Sample Answer: Trim to remove spaces, Clean to remove odd characters, and
Capitalize Each Word to standardize names.

Slide 3 – Transforming Data

Transforming data is about reshaping it so it’s actually useful. There are several
reasons we do this:
 Data entry can be inconsistent.
 Data from multiple systems might use different codes or formats.
 Mistakes or missing values creep in.
Common transformations include:
 Removing unnecessary columns to make the dataset leaner.
 Renaming columns so they’re clear and descriptive.
 Splitting combined data (e.g., full name into first and last names).
 Merging separate columns into one.
 Filling down values in hierarchical data.
 Replacing codes with readable labels.
 Unpivoting cross-tab data into a “tall” table for analysis.
Demonstration – Removing and Renaming Columns
 1. In Power Query, hold Ctrl and select only the columns you need.
2. Right-click a column header → Remove Other Columns.
3. Double-click a column header to rename it clearly.
Mini Tip: Always remove “other columns” rather than deleting unwanted ones—this
makes your query flexible when new columns are added to the source file.

Slide 4 – Filtering Data

Filtering lets us remove rows we don’t need, so our dataset contains only the
information that matters.
We can filter in several ways in Power Query:
 Column Value Filter: Use the filter dropdown in the column header, similar
to Excel’s worksheet filters.
 Keep/Remove Rows: Remove top rows (extra headers), bottom rows
(footers), or alternate rows.
 Text Filters: Equals, Does Not Equal, Begins With, Ends With, Contains, Does
Not Contain.
 Number/Date Filters: Greater Than, Less Than, In This Year, Between, etc.
 Advanced Filters: Combine multiple conditions using AND/OR.
Demonstration – Filtering Example
1. Click the filter arrow on the “Region” column and select only “East” and
“West.”
2. To remove unwanted headers, go to Home → Remove Rows → Remove
Top Rows and enter how many to remove.
3. For complex logic—like keeping only orders from Canada and over $1,000—
use Advanced Filter under Home → Keep Rows → Keep Rows Where.
Group Prompt: “If you wanted to see only online sales over $500, how would you
filter that?”
Sample Answer: Filter the “Channel” column to “Online,” then apply a number filter
on the “Sales” column to keep only values greater than 500.

Slide 5 – Query Settings

The Query Settings pane in Power Query is your control panel for everything
you’ve done.
On the right-hand side, you’ll see:
  Name – Change this to something descriptive (e.g., “Cleaned Orders Data”).
 Applied Steps – A step-by-step list of every change you’ve made. You can
edit, move, or delete these steps.
 Properties – Lets you add a description or change how the query loads.
Query Settings are saved with your workbook, so you can refresh the query later
without re-doing the work.
Demonstration – Managing Applied Steps
1. Click a step in the list to see the preview of what the data looked like at that
stage.
2. Use the gear icon (when available) to edit that step’s settings.
3. Right-click a step to rename, move, or delete it.
You can also view saved queries from:
 Data tab → Queries & Connections
 Or in Power Query’s left-hand panel.

Slide 6 – Demonstration: Import and Transform Data

Now let’s bring it all together. In this demo, we’ll import a CSV file and transform it
by replacing numeric codes with actual data.
Step 1 – Import the CSV
1. Go to Data → Get Data → From File → From Text/CSV.
2. Pick your file and click Transform Data instead of Load.
Step 2 – Clean and Format
 Set correct data types for each column.
 Use Trim and Clean on text fields.
 Rename columns for clarity.
Step 3 – Replace Codes with Labels (Quick Method)
 Select the “StatusCode” column.
 Home → Replace Values: Replace 0 with “Pending,” 1 with “Shipped,” and 2
with “Cancelled.”
Step 4 – Replace Codes via Lookup Table (Best Practice)
 Create a small two-column lookup table in Excel (“Code” and “Status”).
  Load it into Power Query as a connection.
 In your main query, use Home → Merge Queries to join on the code
column.
 Expand the “Status” column from the lookup and remove the numeric code
column if not needed.
Step 5 – Apply Filters
 Filter dates to “This Year” if needed.
 Filter out cancelled orders, or any values not needed for the report.
Step 6 – Load Data
 Home → Close & Load to a table or to the Data Model for use in PivotTables.
Group Prompt: “Why is the lookup table method better than manual Replace
Values?”
Sample Answer: It’s easier to maintain—if new codes are added, you just update the
lookup table and refresh the query without editing the steps.

Lesson 2
Slide 1 – Lesson 3: Loading Data
Alright everyone, welcome to Lesson 3 — Loading Data.
In our previous lessons, we focused on getting data into Excel and transforming it so
that it’s clean and structured. But cleaning is not the end of the story — after all of
that work, the question is: Where and how do we place the finished data so it
can actually be used?
In Power Query, “loading” is the step where we decide how the cleaned data will
appear in Excel — or whether it appears in Excel at all. It’s a critical decision
because it affects performance, usability, and even how you share the file.
When you’re in the Query Editor, the Home tab has a “Close” group with two main
commands:
 Close & Load
 Close & Load To…
Close & Load sends the data to the default location — usually a new worksheet
as an Excel Table. It’s the quick, one-click option.
Close & Load To… opens a dialog where you choose exactly how and where to
place the data — a table, PivotTable, PivotChart, or Data Model.
Demonstration – Loading into the Default Location
Let’s walk through a quick example together.
1. Open a workbook where you have a prepared query in Power Query (or create
one quickly using Data → Get Data → From File → From Text/CSV and click
Transform Data).
2. In the Query Editor, go to the Home tab.
3. Click Close & Load (the button, not the drop-down).
4. Watch what happens — Excel creates a new sheet and loads the table there.
That’s it — you now have a live table. If the source data changes, you don’t re-
import — you just right-click anywhere in the table and choose Refresh.

Demonstration – Loading into a Specific Location

Now let’s see Close & Load To… in action.
1. Re-open the same query in the Query Editor (right-click the table → Edit).
2. This time, click the arrow on Close & Load and choose Close & Load To….
3. You’ll see the “Import Data” dialog.
4. Select “Table” and then choose “Existing Worksheet.” Click in the cell where
you want the top-left corner of the table to be.
5. Click OK.
This is useful when you already have a dashboard or report layout and you want the
cleaned data to drop into a specific area without breaking your existing design.

Group Discussion Prompt:

"If you had a dashboard with charts and formulas referencing specific cells, would
you load to a new sheet or a specific location? Why?"
Sample Talking Points:
 Specific location keeps the dashboard intact.
 Loading to a new sheet might be safer during initial testing, but not ideal for
a final product.

Slide 2 – Creating Data Models

Now, let’s step into Data Models.
This is where Excel behaves like a mini-database. Instead of loading every query as
a visible table in a worksheet, we can store them in the Data Model — an in-
memory storage space inside the workbook.
Why use a Data Model?
 It’s perfect when you have multiple related tables — like Customers, Orders,
Products — and want to analyze them together without merging into one flat
file.
 It improves performance, especially with large datasets, because Excel
doesn’t have to render the data in a sheet.
 It allows relationships between tables, just like in Access or SQL databases.

Close & Load To gives two key choices:

1. Table – Loads into the sheet as a normal Excel table.
2. Connection Only – Keeps it hidden from the sheet, but available for queries
and analysis.
If you check the box Add this data to the Data Model, you can then create
relationships between tables in the Data tab’s “Manage Data Model” window.

Demonstration – Loading to the Data Model

1. Open your query in the Query Editor.
2. Click Close & Load To….
3. Select “Only Create Connection” and tick Add this data to the Data
Model.
4. Load another related table in the same way.
5. Go to Data → Manage Data Model.
6. In the Diagram View, drag and drop fields to create relationships — for
example, link CustomerID in Customers to CustomerID in Orders.
Now, when you insert a PivotTable and choose “Use this workbook’s Data Model,”
you can pull fields from both tables, and Excel will automatically link them based on
the relationships you defined.

Group Discussion Prompt:

"Why might you choose ‘Connection Only’ instead of loading the data into a sheet?"
Sample Talking Points:
 To reduce file size and avoid clutter.
  When the table is just a staging step feeding into other queries.
 To improve refresh speed on large datasets.

Slide 3 – Importing and Linking Data

Now for something slightly different — working with Microsoft Access.
Access is a relational database that’s often used in small to medium-sized
businesses for managing structured data.
We can use Excel and Access together in two ways:
1. Importing – Copying Excel data into Access so that Access stores its own
version.
2. Linking – Creating a live connection where the data remains in Excel, but
Access can view and use it.

When you import:

 The data is copied into Access.
 Changes in Excel will NOT update Access — you’d have to re-import.
When you link:
 Access shows the data but doesn’t store it permanently.
 The data can’t be edited from within Access.
 If the Excel source changes, Access automatically reflects those changes the
next time the table is opened.

Demonstration – Linking Excel to Access

1. Open Access.
2. Go to External Data → New Data Source → From File → Excel.
3. Select “Link to the data source by creating a linked table.”
4. Choose your Excel workbook.
5. Pick the sheet or named range.
6. Access creates a table with a small arrow in the icon — that means it’s linked.
Now, if you change something in Excel and reopen the table in Access, you’ll see
the updated values instantly.
Group Discussion Prompt:
"If your Excel sales tracker is updated daily, and your Access database is used for
monthly sales reports, would you import or link the data? Why?"
Sample Talking Points:
 Link it, so Access always has the latest data without manual re-import.
 This reduces duplication and keeps one single source of truth.

Summary
 Loading Data
 The final step after importing and transforming data in Power Query.
 From the Query Editor, use Close & Load to send data to the default location
(new worksheet as a table).
 Use Close & Load To… to choose a specific destination: existing worksheet,
PivotTable, PivotChart, or Data Model.
 Creating Data Models
 Data Model stores tables inside the workbook for analysis without showing
them in worksheets.
 Loading options: Table (visible in a sheet) or Connection Only (hidden, used
for analysis).
 Check “Add to Data Model” to relate multiple tables, enabling advanced
PivotTable analysis.
 Importing and Linking Data
 Importing into Access copies Excel data into Access — changes in Excel
don’t update Access.
 Linking keeps the data in Excel but makes it visible in Access; changes in
Excel are reflected in Access automatically.