Module 4

Lesson 1
Slide 1 – Lesson 1: Available Data Sources
“Alright everyone, welcome to Lesson 1: Available Data Sources. Before we can
analyze or report on any data, we need to know where that data actually lives.
In this lesson, we’ll look at three broad categories of data sources:
1. Databases – traditional structured systems that store data in tables.
2. Azure – Microsoft’s cloud platform that offers cloud-based storage and
computing.
3. Other Sources – a variety of systems like websites, SharePoint, and more,
that don’t fall into the first two categories but are still important in analytics.
By the end of today, you’ll understand what each of these categories is, see
examples of common systems in each category, and learn how we can connect to
them in real life.”
Mini-Discussion Question:
“Before I show you any examples, can anyone tell me a data source they’ve
personally used before?”
Talking Points:
 Students may mention Excel, websites, or SQL databases.
 Emphasize that all of these can fit into one of the three categories we’ll cover
today.

Slide 2 – Databases
“Let’s start with Databases, the most traditional and structured type of data
source.
A database is a structured system for storing, retrieving, and managing data. Most
of the databases we’ll deal with are relational, meaning they store data in tables
and allow those tables to be linked.
Here are the main database types on our list:
 Microsoft Access – a desktop-based database system. Great for small-scale
projects.
 Microsoft SQL Server – enterprise-grade, used by medium to large
organizations.
  Microsoft SQL Server Analysis Services (SSAS) – an analytical engine for
multidimensional data and cubes.
 Oracle RDBMS – robust and scalable, common in banking, government, and
large corporations.
 IBM DB2 – high-performance, enterprise-level database used with IBM
systems.
 MySQL – open-source, widely used for web applications and small-to-mid
projects.
When connecting to a database from a tool like Power BI, the process usually follows
a pattern: you choose the database connector, enter the server and database
name, authenticate, and then select the tables you need.”
Step-by-Step Demonstration:
1. Open Power BI → click Get Data.
2. Select SQL Server.
3. Enter server name and database name.
4. Choose authentication method.
5. Select desired tables (e.g., Orders, Customers, Products).
Mini-Discussion Question:
“If you were making a dashboard for a single small retail shop, which database
might you choose from this list and why?”
Talking Points:
 Access or MySQL might be enough for small-scale use.
 SQL Server or Oracle for larger, integrated systems.

Slide 3 – Azure
“Next, we have Azure, Microsoft’s cloud platform. Instead of storing data on local
physical servers, Azure stores it in the cloud, meaning you can access it from
anywhere.
The slide shows some of Azure’s main data components:
 VMs (Virtual Machines) – cloud-based computers that can host databases
or applications.
 SQL Database – cloud-hosted SQL Server with all the features but without
physical hardware.
 Marketplace – ready-made datasets, analytics tools, and solutions.
  Blobs – storage for large, unstructured data like images, videos, or logs.
 Tables – fast, NoSQL storage for structured data in key-value format.
Azure is great for scalability, remote access, and integration with other Microsoft
services.”
Step-by-Step Demonstration: (Connecting to Azure Blob Storage)
1. Open Power BI → click Get Data.
2. Search for Azure Blob Storage.
3. Enter storage account name or URL.
4. Authenticate with Azure credentials.
5. Select and load the required files (e.g., a CSV file with sales data).
Mini-Discussion Question:
“What do you think is the biggest advantage of storing your SQL database in Azure
instead of on a local server?”
Talking Points:
 Accessibility from anywhere.
 No physical server maintenance.
 Ability to quickly scale up or down.

Slide 4 – Other Sources

“Finally, let’s look at Other Sources. These are data sources that don’t fit neatly
into databases or Azure but are still widely used in analytics.
From the slide, we have:
 Websites and Web Services – APIs and online data feeds. Example: pulling
real-time weather data.
 SharePoint Lists – structured lists stored in a SharePoint site.
 OData Feeds – a protocol for getting data via the web; used by apps like
Dynamics 365.
 Active Directory – stores information about users, groups, and permissions
in an organization.
 Microsoft Exchange – email and calendar server, can be analyzed for
communication trends.
The connection process here varies depending on the type of source, but most
follow a similar pattern: choose the connector in Power BI, enter the URL or
connection details, authenticate, and load the data.”
Step-by-Step Demonstration: (Connecting to SharePoint List)
1. Open Power BI → click Get Data.
2. Select SharePoint Online List.
3. Enter SharePoint site URL.
4. Authenticate with Office 365 credentials.
5. Select the list and load the data.
Mini-Discussion Question:
“Can you think of an example where connecting to a website or API might be better
than using a database?”
Talking Points:
 Real-time data needs (e.g., live stock prices).
 Data that is not stored internally.

[Wrap-Up – Final 10 Minutes]

“To sum it up, we’ve covered:
 Databases – the structured backbone of most analytics projects.
 Azure – cloud-based data storage and services.
 Other Sources – APIs, SharePoint, OData, Active Directory, and Exchange.
Your main takeaway should be that no matter what tool you’re using, you first need
to identify the right data source for your needs. The best choice depends on factors
like scale, location, update frequency, and the type of data you need.”

Lesson 2
Slide 1 – Lesson 2: Previewing, Shaping, and Transforming Data
“Hi everyone, welcome back! In the last session, we learned about different data
sources – where our data lives and how we can connect to it. Now that we know
where to find our data, the next big question is: What do we do with it once we
have access?
The truth is, data almost never comes in a perfect, ready-to-use form. Think about
the last spreadsheet someone sent you – maybe there were blank rows, merged
cells, inconsistent names, or extra columns you didn’t need. If you’ve ever spent
time fixing a spreadsheet before analyzing it, you’ve already done part of what we’ll
cover today – you’ve shaped and transformed data.
Our goal today is to learn how to:
1. Preview database data – so we know what we’re working with before we
import it.
2. Edit queries – fine-tune what data we bring in and how it’s shaped before
loading it.
3. Transform data – clean and reformat data so it’s useful and reliable.
4. Apply it all in a demonstration where we connect to a SQL Express
database, shape the data, and load it into Excel.
By the end of this lesson, you’ll be able to confidently say, ‘I know how to take raw
data and get it into the shape I need for analysis.’”

Slide 2 – Previewing Database Data

“Let’s start with previewing database data.
Imagine you’ve just been given access to a company’s database – maybe it’s a
retail sales database. You wouldn’t want to just hit ‘Import All’ and bring in
everything. Why? Because databases can contain millions of rows and hundreds of
tables. Pulling everything would not only slow down your computer, it would also
give you a mountain of irrelevant data.
So here’s what we do instead:
Step 1 – Connect to the database
This usually means entering the database server’s name or address, the database
name, and your login credentials. For example, if we’re connecting to SQL Server,
the server name might be something like CompanyServer01 and the database
might be SalesDB.
Step 2 – Browse through databases, tables, and views
Once connected, you’ll see a list of items. Tables are raw datasets, like Orders or
Customers. Views are pre-defined queries stored in the database, often already
filtered or aggregated. For example, MonthlySalesSummary might be a view that
already groups sales by month.
Step 3 – Select one or more tables or views
We only choose the ones that are directly relevant to our task. If our project is
analyzing sales trends, maybe we pick the Orders table and a Products table to
combine later.
Example Scenario:
Let’s say we want to analyze product sales for the last year. I connect to SalesDB,
see a list of tables, and notice there’s an Orders table with all transactions and a
Products table with product details. I select both because I’ll need to join them to
match product names to sales numbers.
Mini-Discussion Question:
Why might it be dangerous to import the entire database without previewing?
Sample Talking Points:
 Wastes time and computer memory.
 Increases file size unnecessarily.
 Makes it harder to focus on the relevant data.

Slide 3 – Editing Queries

“After previewing, we edit the query to fine-tune the data we’re pulling in.
Think of the query as a set of instructions you give to the database: ‘Bring me this
data, but only the rows that meet my conditions, and only the columns I care
about.’
In tools like Excel Power Query or Power BI, we use the Query Settings Pane to
manage these instructions. Every step we take – like removing a column, filtering
rows, or changing a data type – is recorded in order. That means if the data updates
tomorrow, those same steps will be reapplied automatically.
Column Operations might include:
 Renaming a column from Prod_ID to Product ID so it’s clearer.
 Removing a column like InternalNotes that we don’t need.
 Changing a column type from ‘Text’ to ‘Date’ or ‘Number’.
 Splitting one column into two – for example, splitting FullName into FirstName
and LastName.
Row Operations might include:
 Filtering to include only rows where OrderStatus = ‘Shipped’.
 Sorting sales by amount in descending order.
 Removing duplicates to avoid double counting.
Why is this important?
Because every time you refresh your data, you don’t have to redo the cleaning – it’s
done for you automatically. That’s a huge time-saver and ensures consistency.”
Mini-Discussion Question:
Why is it better to apply filters and cleaning steps at the query level instead of after
the data is already loaded?
Talking Points:
 It reduces the volume of data imported.
 It speeds up processing.
 It keeps transformations consistent.

Slide 4 – Transforming Data

“Transformation is where we reshape and clean data into the exact form we need.
Common Operations:
 Transpose – swap rows and columns.
 Reverse order – reorder rows, maybe for chronological sorting.
 Count rows – useful for quick summaries.
 Move columns – for better readability.
 Pivot – turn values into columns. For example, pivot Month so each month
becomes its own column.
 Fill – fill down missing values so all rows have data.
Text Operations:
 Split columns – split a full address into street, city, and postal code.
 Merge columns – combine first and last name into one.
 Format – change to uppercase, lowercase, or proper case.
 Parse – extract a certain part of text, like area code from a phone number.
Numeric Operations:
 Average – calculate average sales per product.
 Standard deviation – useful for identifying variability in sales data.
 Trigonometric functions – rarely used in basic business, but useful in
geospatial or engineering datasets.
Date Operations:
 Extract year, month, or day from a date column.
 Calculate duration – e.g., days between order date and delivery date.
 Find day of the week – useful for spotting patterns in sales.
Example:
If we have an OrderDate column, we might create a Year column, a Month column,
and a DayOfWeek column so we can see trends like ‘Most sales happen on
Saturdays in December.’”
Mini-Discussion Question:
What transformation would you apply if you had a column with values like Mr. John
Smith and you only wanted John Smith?
Talking Points:
 Use Text → Replace Values to remove Mr.
 Or use Split Column to remove the title.

Slide 5 – Demonstration: Formulating a Query

“Now let’s see this in action with a demonstration.
Step 1 – Connect to SQL Express
In Excel, go to Data > Get Data > From Database > From SQL Server Database.
Enter localhost\SQLEXPRESS and select the SalesDB database.
Step 2 – Preview Data
In the Navigator window, browse and select the Orders table.
Step 3 – Format and Transform
 Remove unnecessary columns like InternalNotes.
 Filter rows to only include OrderStatus = ‘Shipped’.
 Change OrderDate to Date type.
 Add new columns: Year and Month extracted from OrderDate.
 Merge FirstName and LastName into FullName.
Step 4 – Load into Excel
Click Close & Load to bring the cleaned data into an Excel table. Now every time we
refresh, Excel will pull the latest shipped orders, with our transformations
automatically applied.
This is the beauty of shaping and transforming – we set it up once, and it works
forever, even as the underlying data changes.

Lesson 3
Slide: Lesson 3 – Loading Data
“Welcome back, everyone. Today we’re going to take everything we’ve learned
about connecting to data and transforming it, and we’re going to complete the
journey by actually loading that prepared data into a model we can analyze. I’ll
guide you through what happens when we load from the Query Editor, how to work
with more than one data source in the same model, how to take an existing Excel
table and push it into the Data Model, and why a dedicated Date table is essential
for any serious analysis. We’ll close with a hands-on demonstration where we add
an Excel table and a Date table to the Data Model and wire everything together. I’ll
keep the language simple and I’ll talk to you as if we’re doing this together on the
same screen, so if you’re completely new to this, you’ll be fine.”

Slide: Loading Data from the Query Editor

“Let’s begin by demystifying what actually happens when you click the buttons that
say things like Close & Load in Excel or Close & Apply in Power BI. Think of the
Query Editor as a kitchen where you prep ingredients. You bring in raw vegetables,
you wash, peel, chop, season, and only then do you send the prepared ingredients
to the dining table. In our world, the ‘ingredients’ are your tables, the ‘prep steps’
are your transformation steps, and the ‘dining table’ is the Data Model.
When you finish your steps and you click the load button, four things happen in a
very specific order. First, your tool sends the query instructions down to the data
source. Those instructions are simply the list of steps you built in the Query Settings
pane: filter these rows, remove those columns, change this data type, split this text,
and so on. Second, the source executes those instructions and returns a result set—
think of this as the clean batch of rows that match your rules. Third, the Query
Editor applies any remaining transformations that couldn’t be pushed down to the
source. This is important because not every source understands every step; for
example, a web CSV won’t understand a complex date extraction, so the Query
Editor applies it after the rows arrive. Finally, the transformed results are committed
to the destination you chose: either loaded as a worksheet table, loaded only to the
Data Model (which keeps your workbook light), or kept as a connection for later
merge or append operations.
If you’re in Excel’s Power Query, you’ll often choose between ‘Close & Load’ which
creates a visible sheet table, and ‘Close & Load To…’ which lets you hide the sheet
and load directly to the Data Model. In Power BI Desktop there’s just ‘Close & Apply’
which loads into the model used by your report. In both tools, be intentional: if you
plan to build pivot tables or DAX measures, load to the model; if you only need a
quick table on a sheet, load to a worksheet. I also want you to notice performance
cues. The more rows and the more steps, the longer the load. If refresh feels slow,
reduce columns at the source, filter early, and prefer steps that the source can
execute for you, a concept called ‘query folding.’ If students ask, your simple
answer is: folding means the heavy lifting happens on the database server instead
of on your laptop, which is faster.”
Instructor check-in prompt: “Give me one example of a step you would try to do
as early as possible in your query to improve load performance.”
Talking points you can offer if cameras go quiet: encourage ‘remove columns
you don’t need’ and ‘filter to the time window you actually analyze’ because both
drastically reduce the size of the result set.

Slide: Using Multiple Data Sources

“Real projects rarely live in a single place. A common pattern is sales transactions in
SQL Server, product details in an Excel file marketing maintains, and exchange
rates or weather data from a web API. Loading data from multiple sources works
perfectly fine as long as you treat each source as its own query, shape each one
until it’s clean, and then bring them together with either a relationship or with a
merge or append, depending on what you’re trying to do.
Let’s make this concrete. Imagine we have an Orders table from SQL, a Products
table from an Excel workbook on SharePoint, and a Regions table from a small CSV
file. We would connect to each source separately and stop to preview. For Orders,
we’d filter to shipped orders only and keep the columns we care about, such as
OrderID, OrderDate, ProductID, Quantity, and NetAmount. For Products, we might
clean up the ProductID, fix text casing on Category, and make sure data types are
correct. For Regions, maybe we trim extra spaces and ensure RegionKey is numeric.
Once each query looks healthy, we decide how to combine them. If we want to
enrich Orders with the product name, we perform a merge between Orders and
Products on ProductID and expand the name and category columns. If we want
them to remain separate but related, we load both to the model and create a
relationship between Orders[ProductID] and Products[ProductID]. The choice is
simple: merge when you want to physically add columns from one table into
another; relate when you want to keep separate tables and let the model connect
them at query time. For multiple sources, you’ll also encounter a privacy setting
prompt. If your tool asks about privacy levels—organizational, public, private—set
them realistically. Private means the data cannot be combined with other sources in
a way that could leak its contents; Organizational is usually correct for company
data; Public is for truly public data like open datasets.”
Instructor micro-discussion: “If you’re combining a confidential HR table with a
public exchange-rate feed, what privacy levels would you assign and why?”
Talking points: mark HR as Private and exchange rates as Public; this prevents the
tool from sending sensitive values to the public source during mashups and keeps
credential isolation clean.

Slide: Loading Data from an Excel Table

“Many of you already have spreadsheets with nicely formatted tables. The good
news is that you do not need to rebuild them from scratch; you can lift those tables
directly into the Data Model. I’ll describe the path for Excel specifically, because this
slide is Excel-focused. Start in the workbook that contains the table. Click anywhere
inside the table to make sure Excel recognizes it as a formal ‘ListObject’—you’ll
know because you see the Table Design tab. If it isn’t a table yet, press Ctrl+T and
confirm that your headers are correct. With the table active, go to the Power Pivot
tab, and click ‘Add to Data Model.’ That one action pushes the table into the model
that lives behind your workbook. Think of the Data Model as a compact database
inside Excel where you can build relationships and write measures with DAX. After
adding the table to the model, you can use Power Pivot’s Diagram View to relate it
to other tables you’ve already loaded, such as your Orders table from SQL. If you
prefer to go through Power Query, you can also use Data > Get Data > From
Table/Range; that route opens the Query Editor, lets you clean the table, and then
you choose to load it into the Data Model only, keeping your worksheet tidy.
A couple of tips make this smooth. Keep column headers clean—no trailing spaces,
no duplicate names. Set data types before loading: numbers as whole or decimal,
dates as dates, text as text. If you see mixed data types in the same column, fix
them in the sheet or in Power Query to avoid errors. Finally, remember that if
someone edits the original worksheet table later, a simple refresh updates the
model as long as you haven’t broken the query steps.”
Instructor check-for-understanding prompt: “Why might you choose ‘Load to
Data Model only’ instead of placing a copy of the table on a worksheet?”
Talking points: it keeps the workbook smaller, avoids duplicate copies of the data,
and is required if you intend to build relationships and DAX measures for PivotTables
or Power BI-style analysis inside Excel.

Slide: Date Tables

“Now we need to talk about dates, because almost every analysis has a time
dimension, and this is where many beginners struggle. Using raw date columns
straight from your fact table seems convenient, but it creates a pile of headaches.
You’ll find that months don’t sort correctly because March might appear before
January if the month is stored as text. You’ll see gaps when a day has no sales,
because a fact table only contains dates where something happened. You’ll want to
compare year-to-date versus prior year-to-date, but without a proper Date table
your formulas become messy or impossible. A dedicated Date table, sometimes
called a Calendar table, solves all of this cleanly.
A Date table is nothing more than a single column containing every calendar date in
the period you care about, with companion columns pre-calculated for Year, Month
Number, Month Name, Year-Month labels, Quarter, Week Number, and Day of Week.
The Date column has one unique record per day with no blanks and no duplicates,
which makes it perfect for relationships. You relate your fact table’s date field, such
as Orders[OrderDate], to Date[Date]. After that, slicers and groupings work like
magic. You can drop Month Name on rows and the sort order behaves because you
also have Month Number to instruct the model how to sort. You can create
measures like Year-to-Date sales using DAX more reliably because the Date table
contains a continuous sequence of dates including those days when there were no
orders.
There are two simple ways to create a Date table for this class. The first is to build it
with Power Query. You start a blank query, generate a list of dates beginning at the
minimum OrderDate and ending at the maximum OrderDate, convert that list to a
table, and then add your columns for Year, Month, Quarter, and so on. The second
way is to create it right in Excel on a new sheet by typing the first date you need
and using the fill handle to extend the series down to your end date, then
converting that range to a table and adding it to the Data Model. Power BI Desktop
also offers a quick DAX function called CALENDAR; in Excel Power Pivot you can do
something similar with linked tables or with Power Query. I’ll demonstrate a
straightforward Power Query method in a moment so you have a repeatable recipe.”

Slide: Demonstration – Adding a Date Table

“Let’s do a guided demonstration that ties the last two slides together. I’ll narrate it
so you can follow along later at your own pace. Our goal is twofold: first, we’ll take
an existing Excel table—let’s say a ‘Products’ table or an already-cleaned ‘Orders’
table—and add it to the Data Model. Second, we’ll build a Date table with Power
Query and add that as well, then relate it to Orders so time analysis works correctly.
I’ll start in the workbook that already contains a clean Orders sheet. I click a cell
inside the Orders table to activate the Table Design tab so I know Excel recognizes it
as a proper table. I glance at the headers to ensure I have OrderID, OrderDate,
ProductID, Quantity, and NetAmount with sensible names. With the table active, I go
to the Power Pivot tab and choose ‘Add to Data Model.’ A small window appears
showing the table inside Power Pivot; that confirms it is now part of the model. If I
prefer the Power Query route, I could instead choose Data, then Get Data, then
From Table/Range; the Query Editor would open, I’d verify data types—for example,
set OrderDate to Date and Quantity to Whole Number—and then I’d choose Close &
Load To, ticking the option that says ‘Only Create Connection’ and ‘Add this data to
the Data Model.’ Either path is fine; use the one that feels more comfortable.
Next I’ll create the Date table with Power Query because it is flexible and quick. I go
to Data, then Get Data, then Launch Power Query Editor, and in the Home ribbon I
click New Source and choose Blank Query. In the formula bar I type a simple
expression to define my date range. If I already have an Orders query, I can
reference it to discover the minimum and maximum dates. I’ll type something like:
let Start = [Link]([Link](Orders[OrderDate])), End =
[Link]([Link](Orders[OrderDate])) in [Link](Start, [Link](End -
Start) + 1, #duration(1, 0, 0, 0)). When I press Enter, Power Query shows me a
single column list of dates. I immediately convert this list to a table by clicking ‘To
Table,’ accept the default, and then rename the single column to ‘Date.’ I set its
data type explicitly to Date by clicking the little type icon. Now I enrich it with
helpful columns. From the ‘Add Column’ ribbon I choose Year and Month and Month
Name. I also add Month Number so sorting works, a Quarter column, and Day of
Week and Day Name. If you want a professional touch, add a Year-Month label
column: select Year and Month Number, then create a new custom column with the
formula [Link]([Year]) & "-" & [Link]([Link]([MonthNumber]), 2, "0").
This creates labels like 2025-07 that sort perfectly. I finish by checking for duplicates
in the Date column—there should be none—and I confirm there are no blanks. I
rename the query to ‘Date’ so it’s easy to recognize, and then I click Close & Load
To and choose ‘Only Create Connection’ and ‘Add to Data Model.’
With both Orders and Date in the model, I open the Power Pivot window and switch
to Diagram View. I see two boxes: Orders and Date. I drag Orders[OrderDate] onto
Date[Date] to create a relationship. If you’re asked about cardinality, it should be
Many-to-One from Orders to Date, with Date as the ‘one’ side. Direction can remain
single, which is the default. I click Create, and now the model understands time. If
you’re teaching in Power BI instead of Excel, you’d do the same in Model view and
then mark the Date table as a Date table by selecting the Date column under
Column tools; in Excel there isn’t a ‘mark as date’ button, but the behavior is the
same as long as your table is clean.
Let’s prove that it works by building a quick PivotTable. I go back to Excel, Insert a
PivotTable, and make sure ‘Use this workbook’s Data Model’ is selected. In the
PivotTable Fields, I drag Date[Year-Month] to Rows and Orders[NetAmount] to
Values. Immediately I see totals by month. I right-click Values, choose Value Field
Settings, and change the format to currency for readability. If I then drop Date[Year]
above Year-Month, I can expand and collapse years. Because our Date table is
continuous, months with zero sales still appear if you choose the ‘Show items with
no data’ option on the field settings, which is extremely useful for trend lines. If I
wanted a year-to-date measure, I could add a measure in Power Pivot using a DAX
expression like YTD Sales := TOTALYTD(SUM(Orders[NetAmount]), 'Date'[Date]). The
key point for students is that these time-intelligence functions work correctly
because we built a proper Date table and related it.”
Instructor pause for questions: “What part of that process felt the most new or
intimidating, and what would help make it easier next time?”
Talking points you can offer: reassure them that the Date table recipe can be
saved and reused; remind them they don’t have to memorize the M expression
because they can create a Date table by hand in Excel and add it to the model;
reinforce that the relationship is the crucial step.

Putting It Together: A Short Multi-Source Load Walkthrough

“Before we wrap, let me narrate one more realistic end-to-end flow so you can feel
the rhythm of a day-to-day build. I open Power Query and connect to SQL Server for
Orders, filtering to shipped orders and keeping only the columns I need. I connect to
an Excel workbook on SharePoint for Products, trimming and standardizing
categories. I merge Products into Orders to carry the product name and category
into my fact table or, if I plan to use the Products table independently for slicers, I
skip the merge and simply relate them in the model. I create the Date table with
Power Query as we just practiced. I load Orders, Products, and Date to the Data
Model. In Diagram View, I create two relationships: Orders to Products on ProductID,
and Orders to Date on Date. I save, refresh, and then build a PivotTable with
Date[Year-Month] on rows, Products[Category] on columns, and a measure called
Total Sales := SUM(Orders[NetAmount]) in values. If I decide later that including the
product name inside Orders via a merge was a mistake because the model got too
wide, I can go back, delete that merge step, reload, and rely on the relationship
instead. This is the beauty of working through the Query Editor: every choice is a
step you can revisit.”

Guided Group Discussion (5–8 minutes)

“Let’s close with a quick scenario discussion. Suppose you’re asked to deliver a
monthly revenue dashboard for leadership. Transactions live in SQL Server, product
metadata lives in Excel managed by marketing, and fiscal calendars are slightly
different from calendar months. What are the essential tables you’ll load, how will
you connect them, and how will you handle the fiscal calendar requirement?”
Sample answer you can model aloud: “I would load Orders from SQL and filter
to the last twenty-four months to keep it light. I would load Products from the Excel
file, standardize category names, and either merge that into Orders or, better, keep
it as a separate dimension and relate it on ProductID. I would create a dedicated
Date table that includes all dates for the same twenty-four-month window and add a
FiscalYear and FiscalPeriod set of columns, either by offsetting the month numbers
or by merging in a small mapping table provided by finance. I would then relate
Orders to Date and Orders to Products, and build measures such as Total Sales and
Year-to-Date Sales that reference the Date table for time intelligence. This design
lets leadership slice revenue by fiscal period and by product category with reliable
sorting and consistent totals.”

Final Instructor Notes and Common Pitfalls

“Two final habits will save you time. First, name everything clearly—queries, tables,
and columns. Use names like ‘Date’ not ‘Table2,’ and ‘NetAmount’ not ‘Amount1.’
Clear names make DAX easier and help your future self. Second, decide early
whether a piece of information belongs as a separate dimension table or as merged
columns inside your fact table. If the same attribute will be used as a slicer or
appears in multiple facts, keep it separate and relate it. If it’s purely descriptive and
only used with one fact, merging is fine.
Typical mistakes include loading the same data to both a worksheet and the model
and then forgetting which copy the PivotTable is connected to, relying on a raw date
column without a Date table and then wondering why months don’t sort, and
forgetting to set data types which causes numbers to behave like text. If refresh
ever fails, check credentials for each source under Data Source Settings and review
any steps that reference a local file path that changed.”

Wrap-up line you can read verbatim:

“Today you learned what happens under the hood when we load from the Query
Editor, how to combine multiple sources safely, how to promote an Excel table into
the Data Model, and how to build and connect a rock-solid Date table so time
analysis just works. In our next session we’ll start building calculations on top of this
model, but for now, take a breath and notice that you can now get clean, connected
data into a model on your own. That’s a huge milestone.”
If you’d like, I can package this into a printable facilitator guide with a one-page
checklist for each demo so you can keep it beside you while teaching.

Lesson 4
Slide 1 — Lesson 4: Table Relationships and Hierarchies
“Welcome back, team. Today we’re moving from ‘clean data’ to ‘connected data.’
Clean data is great, but most analyses need information from more than one table.
For example, sales rows don’t carry friendly category names, and dates don’t
magically know what year or quarter they live in. Relationships let our tables talk to
each other safely and correctly. We’ll start by understanding what a relationship is
and how queries feed into it, then we’ll practice creating relationships in the model.
After that, we’ll build hierarchies so our users can drill from a big picture down to
details in one smooth gesture. We’ll finish with a guided demonstration where we
create relationships and a hierarchy, then use them in a PivotTable you can navigate
like a pro.”

Slide 2 — Relationships and Queries

“Look at the Products table on the left and the ProductCategories table on the right.
Products has a ‘Category ID’ column with numbers such as 1 and 4. On its own, that
number is not helpful. The meaning lives in the ProductCategories table, where
CategoryID 1 equals Bikes and 4 equals Panniers. A relationship is the rule that
says ‘Products[Category ID] matches ProductCategories[Category ID].’ Once that
rule is in the model, any visual, PivotTable, or DAX calculation can combine fields
from both tables without duplicating data. Under the hood, relationships are built on
keys. The ‘one’ side (here, ProductCategories) must have unique key values with no
blanks and a consistent data type. The ‘many’ side (Products) can repeat those key
values as often as needed. Before you ever create a relationship, your queries
should make those keys trustworthy: trim extra spaces, fix mismatched data types
(Text vs. Whole Number), handle nulls, and remove duplicates from the ‘one’ side. If
you discover that the modeling would be easier with a single, flattened table, you
can merge the category name into Products during query time. If you want
reusability and cleaner slicers, you keep ProductCategories separate and relate the
tables in the model. There isn’t a single right answer; pick merge when you need a
quick descriptive column baked into one table, and pick a relationship when the
lookup table will be reused or sliced in many places.”
Quick class question you can ask now: “Given these two tables, would you
merge the category name into Products or keep a separate ProductCategories table
and relate them?”
Talking points you can offer if no one jumps in: “If categories will be used as
slicers across many reports, keep a separate table and relate it. If this is a one-off
analysis and I only need the name alongside each product, merging is acceptable.”

Slide 3 — Creating Relationships

“Let me show you two practical ways to create relationships in the Excel Data
Model. The first way is visual: open the Power Pivot window, switch to Diagram
View, and simply drag Products[Category ID] onto ProductCategories[Category ID].
Excel recognizes that ProductCategories contains unique values and marks it as the
‘one’ side automatically. The second way is form-based: in Excel, go to Data, click
Relationships, then New. In the dialog, choose the ‘many’ table and column on the
left (Products → Category ID) and the ‘one’ table and column on the right
(ProductCategories → Category ID), confirm, and save. If Excel refuses to create the
relationship, it’s almost always one of three issues: the data types don’t match
between the two columns, there are blanks or duplicates on the ‘one’ side, or the
values look the same but aren’t (such as text ‘001’ vs. number 1, or hidden trailing
spaces). Fix those in Power Query—change types, trim, and de-duplicate—then try
again.
Sometimes you need a relationship on more than one column, for example
StoreID and ProductID together form the key. The Excel and Power BI engines don’t
support multi-column keys directly, so we create a composite key column on both
sides using the same format, like [Link]([StoreID]) & "-" &
[Link]([ProductID]). We then relate the two composite columns. That’s the
simplest, most reliable pattern. Another situation you’ll see is multiple possible
paths between two tables, such as Orders having both OrderDate and ShipDate
linking to the Date table. The model can keep only one active relationship between
a pair of tables; pick the most common one (usually OrderDate). If later you need
calculations by ShipDate, you can create a second, inactive relationship and
activate it within a measure using a DAX function (that’s a topic we’ll touch when
we start measures). For now, just remember: one active path at a time keeps your
results predictable.”
Mini-discussion prompt: “Why do you think the ‘one’ side must be unique and
free of blanks?”
Talking points: “Uniqueness prevents ambiguous matches; blanks can’t join to
anything, which creates missing or doubled totals. Clean keys are the backbone of
reliable results.”

Slide 4 — Hierarchies
“Once tables are related, users still need a friendly way to navigate. That’s where
hierarchies shine. A hierarchy bundles multiple levels—for example Country, then
State or Province, then City—into a single field that behaves like a drill-down path.
Instead of dragging three separate fields into a Pivot, you drag one hierarchy. In a
sales model, common hierarchies are Geography (Country → State → City), Product
(Category → Subcategory → Product Name), Organization (Division → Department →
Team), and Time (Year → Quarter → Month → Day) coming from your Date table.
Hierarchies make exploration simple: you start with totals by Category, expand a
Category to see Subcategories, expand again to see individual Products, and so on,
without rebuilding the layout.
To create a hierarchy in Excel’s Data Model, open the Power Pivot window, go to
Diagram View, and in the lookup table—say ProductCategories or a combined
Products dimension—select the first field, then Ctrl-click the other fields that define
the path, right-click, and choose Create Hierarchy. Excel creates a new object under
that table with a default name like ‘Hierarchy1.’ Rename it to something your users
will recognize, such as ‘Product Path,’ then drag to reorder levels if needed. If you’re
in Power BI Desktop, the idea is identical: in Model view, right-click a field and
choose Create hierarchy, then add additional levels. A small polish tip that students
love: after you create a hierarchy, consider hiding the individual columns that are
now inside it (right-click column → Hide from Client Tools). This keeps field lists short
and nudges users to pick the clean hierarchy instead of a grab-bag of single
columns. Also set sort behavior where it matters. For example, Month Name should
be sorted by Month Number, not alphabetically; in Excel Power Pivot you can set
‘Sort by Column’ on Month Name and point it to Month Number in the Date table so
drill-downs look correct.”
Check-in question: “What benefit does a hierarchy give you that separate
columns do not?”
Talking points: “It provides a consistent drill path in one object, it reduces field-list
clutter, and it makes reports easier for non-experts to explore without breaking
layouts.”

Slide 5 — Demonstration: Creating Relationships and Hierarchies

“Let’s build this together step by step. Imagine we already loaded three tables into
our workbook’s Data Model: Products with columns ProductNumber, Name, Price,
and CategoryID; ProductCategories with CategoryID and CategoryName; and Sales
with OrderDate, ProductNumber, Quantity, and NetAmount. First, I’ll create the
relationships. I open the Power Pivot window and switch to Diagram View. I click the
ProductCategories table to confirm that CategoryID has unique values—if I’m
unsure, I can quickly check in Power Query that we removed duplicates there. Now I
drag Products[CategoryID] onto ProductCategories[CategoryID]. Excel draws a line
with a ‘1’ near the categories side and an asterisk near Products, telling me it’s a
one-to-many relationship. Next, I drag Sales[ProductNumber] onto
Products[ProductNumber] to link transactions to product details. If the drag refuses
to snap, I know I must fix data types or duplicates back in Power Query, then retry
the drag—this is normal when you’re learning.
With relationships in place, I’ll create a hierarchy for faster navigation. In the
Products table I select CategoryID (or better, the readable CategoryName if I
merged it in), then Subcategory if I have one, and finally Name. I right-click and
choose Create Hierarchy. I rename it ‘Product Hierarchy’ and make sure the order is
Category → Subcategory → Name. If I don’t have a Subcategory level in this dataset,
that’s fine—I build a two-level hierarchy of Category → Name. For a geography
example, I could do Country → State/Province → City in whichever table holds those
columns. For time, I would create Year → Quarter → Month → Day in the Date table
we built last lesson.
Now let’s test it in a PivotTable so you can feel the payoff. Back in Excel, I insert a
PivotTable and choose ‘Use this workbook’s Data Model.’ In the field list, under the
Date table I place Year on Columns, and under Products I drag ‘Product Hierarchy’
onto Rows. In Values I drop NetAmount from the Sales table. Immediately I have
totals by Category and Name across years. I can expand a Category to see each
Product without dragging any extra fields. If I right-click a Year and choose
Expand/Collapse → Expand Entire Field, I can step down the time axis if I used a
time hierarchy. The navigation is smooth because the relationships are correct and
the hierarchy organizes the path. Finally, I show one more small but powerful trick:
if Month Name is appearing out of order, I open the Power Pivot window, go to the
Date table, click Month Name, choose ‘Sort by Column,’ and set it to Month Number.
I refresh the Pivot and the months now flow Jan through Dec. This is the polish that
makes your models feel professional.”
Guided scenario you can ask the class to discuss for three minutes:
“Your company tracks sales by Store and Region, but some analysts keep building
reports with inconsistent drill paths and mismatched totals. You have a Stores table
with StoreID, StoreName, City, State, and Region; a Regions table with Region and
RegionalManager; and a Sales table with StoreID and amounts. What would you
relate, and what hierarchy would you create to make exploration consistent?”
Sample answer you can offer after discussion: “Relate Sales[StoreID] to
Stores[StoreID], and optionally relate Stores[Region] to Regions[Region] if you want
manager attributes. Build a ‘Store Geography’ hierarchy inside Stores: Region →
State → City → StoreName. Hide the individual columns so everyone uses the
hierarchy. This ensures every report drills the same way and totals match because
relationships route the filters properly.”

Practical tips and gentle pitfalls to call out as you wrap

“Keep your ‘one’ tables small and clean—distinct keys, correct data types, and
readable labels—because slicers almost always come from these tables. If you can’t
create a relationship, don’t fight the dialog; go clean the data first. If a relationship
exists but your totals look too high or too low, check for duplicate keys on the ‘one’
side or unexpected blanks on the ‘many’ side. If users get lost in giant field lists,
hide columns after you put them into a hierarchy. And remember the
merge-versus-relate decision: merge when you need a descriptive field inside one
table, relate when the lookup will be reused across multiple facts or used for
slicing.”

Closing line you can read verbatim

“Today we explored three interconnected skills that will form the backbone of your
future reports and dashboards. First, we looked at how queries prepare keys —
understanding that a relationship is only as strong as the columns it’s built on. We
talked about cleaning and shaping those keys in Power Query so they’re consistent,
free from duplicates, and in the right data type. This preparation ensures that when
you join tables, your matches are accurate and your results are trustworthy.
Second, we examined why and how to create relationships between tables in the
Data Model. We saw that relationships allow us to store data in a normalized,
efficient way, while still letting us combine information from multiple sources in a
single report. We discussed one-to-many relationships, active versus inactive
relationships, and when you might merge data instead of relating it. You now know
how to troubleshoot broken relationships and handle situations like composite keys.
Finally, we explored hierarchies — a way to turn a collection of separate fields into a
smooth, logical drill-down path. Instead of your report users dragging fields one by
one, hierarchies let them start at a high-level summary and move deeper into the
details with just a click, making reports far easier to navigate. We also covered best
practices like naming hierarchies clearly, ordering their levels properly, and hiding
individual columns to encourage consistent use.
In our next lesson, we’ll take these skills a step further by writing calculations —
such as year-to-date sales by product and region — that rely on the structure we
built today. These calculations will only work correctly if your relationships and
hierarchies are set up properly, so before then, I encourage you to practice on your
own dataset: create at least one clean, well-structured relationship and one
meaningful hierarchy. This hands-on repetition will help cement the process in your
mind, and you’ll walk into the next lesson ready to unlock the full analytical power
of your model.”