Module 3

Lesson 1
🟦 [Slide: Lesson 1 – Using Excel as a Data Source for Power BI]
"Good morning, everyone! Welcome back to our Power BI series. I hope you’re all
doing great and ready for another productive and hands-on session. Today, we're
diving deep into a very practical and commonly used aspect of Power BI—
connecting and using Excel files as a data source.
This is a crucial topic because most organizations still rely heavily on Excel to
manage and analyze data. Power BI’s ability to directly import, transform, and
visualize Excel data is one of the reasons it’s so popular. Throughout this session,
we’ll explore how to connect to files, import Excel workbooks, publish and update
those files within Power BI, and understand how data refresh works.
By the end of today’s session, you should feel confident working with Excel as your
source, keeping your reports dynamic and up-to-date, and understanding the
different ways of maintaining connections between Excel and Power BI. And yes,
we’ll wrap it up with a hands-on demonstration where I’ll walk you through
importing files from a local folder."

🟢 [Slide: Connecting to Files]

"Let’s begin by understanding the connection process. Power BI allows us to
connect to various file types—not just Excel, but also CSV, XML, and JSON files. You
can connect to files from either Power BI Desktop or directly through the Power BI
service on the web.
From the Power BI service, which you access at app.powerbi.com, you can connect
to files stored locally on your computer, OneDrive for Business or Personal, or
SharePoint Team Sites. However, do note that the Power BI service has a 250 MB file
size limit per upload.
From Power BI Desktop, the process is slightly different. You’ll go to the ‘Home’ tab,
click on ‘Get Data,’ and select your file type and location—whether from your PC,
OneDrive, or even a network drive. An important feature here is the ability to
connect to a folder instead of a single file. This is particularly useful when you have
multiple files with the same structure—like monthly sales reports—and want to
combine them into one dataset."
💬 Quick check-in question: “Has anyone here worked with data that’s split across
multiple files like that—say, a file for every region or department? Type 'yes' or 'no'
in the chat. If yes, can you share what kind of data it was?”
🟡 [Slide: Importing Excel Files]
"There are two main approaches to importing Excel files into Power BI, and it’s
important to know the difference.
The first method is connecting to the Excel file and using its contents as datasets.
This means that only the data formatted as tables in Excel will be available to Power
BI. So make sure you format your data ranges as a table before attempting to
import. Once connected, you can use Power Query to apply transformations like
filtering, column splitting, or calculated columns.
The second method is to import the entire Excel workbook, including any Power
Pivot or Power View content. In this case, it’s like using Excel Online within Power BI.
You can interact with pivot tables, charts, and even slicers directly. This method is
good if your Excel file already has a lot of built-in analysis.
Both methods support scheduled data refresh and allow you to work with .xlsx
or .xlsm files."
🔄 Student interaction prompt: “Why do you think Power BI requires data to be in a
table format before importing it as a dataset? Think about what Power BI needs in
terms of structure and relationships.”

🟠 [Slide: Publishing to Power BI from Excel]

"Now let’s look at how we can publish Excel files into Power BI. There are two
options here, and they each serve a slightly different purpose.
Option one is uploading your workbook to Power BI. In this case, the file will appear
in Power BI Service just like it does in Excel Online. However, you can’t edit it within
Power BI—only view it. This is ideal for workbooks that contain raw data and no
visuals.
Option two is exporting workbook data to Power BI. This is the preferred method
when your workbook has Power View visualizations, Power Pivot models, or Get &
Transform queries. Everything—tables, the data model, visuals—is brought into
Power BI and treated like a regular Power BI report."
💬 Ask the class: “Which publishing method do you think would be better for
dashboards meant for upper management—upload or export? Why?”
Answer: Exporting is better because it allows full report interactivity and control
over visuals within Power BI.

🔷 [Slide: Updating Files in Power BI]

"When we update data in Power BI, we need to understand how Power BI recognizes
and matches the updated file to the existing dataset.
If you’ve uploaded a file to Power BI and want to update it, the new file must match
the original one in name, type, and structure. Power BI ignores formatting changes
like font size or colors but will recognize added or deleted columns. If a new column
is added, it’s appended to the dataset automatically.
If you store your Excel files on OneDrive or SharePoint, Power BI can sync updates
automatically, which is a huge time-saver. However, if you’re using local files, you’ll
have to manually refresh or re-upload them."
💡 Instructor tip: "Always keep the same file structure when updating. That way, you
won’t break your reports or visuals!"

🔺 [Slide: Data Refresh]

"One of the most powerful features in Power BI is data refresh. Power BI allows
datasets to be refreshed in several ways, depending on your license.
Free accounts can schedule one refresh per day, while Power BI Pro users can
refresh up to eight times a day. Refreshing can be automatic, on-demand, or
through a live connection using DirectQuery.
Power BI also supports hybrid datasets. For example, you could combine an Excel
file with SQL Server data and configure them to refresh differently. However, keep in
mind that all this depends on your data gateway and whether the sources are on-
premises or in the cloud.
There are three main types of refreshes:
 Automatic refresh (scheduled in the Power BI Service)
 Manual refresh (done by clicking 'Refresh now')
 Live connection (with tools like DirectQuery, used mostly for databases)"
🧠 Discussion question: “What risks do you think are associated with not refreshing
your data frequently in a live dashboard environment?”

💻 [Slide: Demonstration – Importing Files from a Local Folder]

"Let’s now walk through a quick demo so you can see how to bring in an Excel file
and a CSV file from your local folder."
🧪 Demonstration steps:
1. Open Power BI Desktop and click 'Get Data.'
2. Choose 'Excel' and browse to a sample file.
3. Select the tables or ranges formatted as tables.
4. Preview, then load or transform using Power Query.
 5. Repeat the steps for a CSV file.
6. Combine datasets into a simple report by dragging in fields and visuals.
🔍 Instructor tip: “If you want to import multiple monthly CSV files, use the 'Folder'
connector to combine them automatically—just ensure the structure is identical
across all files.”

🧾 [Wrap-Up & Review]

"Let’s summarize what we’ve covered today. We started by looking at how Power BI
connects to Excel files, both through the desktop app and the web service. We then
explored two methods of importing data—connecting as datasets or uploading
entire workbooks. We understood the publishing process and looked at the right way
to update files without breaking reports. We also discussed the importance of data
refresh and how it differs based on source and license. Finally, we did a live
walkthrough to make it all real.

Lesson 2
🟦 Slide: Lesson 2 – The Power BI Data Model
Good morning everyone, and welcome to Lesson 2 of our Power BI course. In
today’s session, we’re going to explore one of the most powerful concepts behind
Power BI: the data model. Understanding how to structure, manage, and optimize
your data model is crucial to making your reports accurate, dynamic, and scalable.
Whether you’re working with small datasets in Excel or connecting to enterprise-
grade databases, mastering the data model will make your reporting more effective
and your visualizations more insightful.
We’ll begin by discussing what a data model is in the context of Power BI. Then we’ll
explore how to manage relationships between tables and optimize the model for
reporting purposes. We’ll also dive into the concept of hierarchies—why they’re
important and how they enhance drill-down capabilities. Finally, we’ll do a practical
demonstration on creating hierarchies inside Power BI Desktop, and I’ll give you
some best practices for using these hierarchies in visualizations.

🔷 Slide: What Is a Data Model?

So let’s begin with a foundational question—what is a data model? In simple terms,
a data model is a structured way of organizing and connecting data. In traditional
relational databases, this includes tables, columns, data types, and relationships
between tables. Power BI follows a similar model. When you bring data into Power
BI, especially from multiple sources, Power BI organizes this data into a semantic
model behind the scenes.
Power BI allows you to connect to different sources like Excel files, SQL databases,
and web data, and import them into a unified model. This model supports
relationships between tables, filtering logic, and transformations. One key
advantage is that you don’t have to flatten your data—Power BI works well with
normalized structures. So instead of one massive table, you can break your data
into fact tables (like sales or transactions) and dimension tables (like product,
customer, or date), creating what’s called a “star schema.”
While working with this model, we pay close attention to certain things. First is data
types—they must be accurate, or your visuals and calculations will break. Second is
cross-filtering—whether relationships are one-directional or bidirectional. Third is
performance—we often reduce the dataset size by removing columns we don’t
need or omitting rows that are too sensitive. These best practices make your model
faster, lighter, and easier to use in reports.
Discussion Question: "Why do you think having a clean, well-structured data
model is important before you even begin creating visualizations? Can anyone give
a real-world example where messy data caused reporting issues?"
(Wait for 2–3 student responses, encouraging examples from workplaces,
internships, or past school projects.)

🟡 Slide: Managing Data Relationships

Now that we understand what a data model is, let’s talk about relationships
between tables. When you import multiple tables into Power BI, it tries to identify
relationships automatically using matching column names and data types. These
relationships can also be created manually in the Model view.
There are two types of keys involved: Primary keys, which uniquely identify rows
in a table—for example, CustomerID in a customer table—and Foreign keys, which
are used in another table to refer back to the primary key—for example, CustomerID
in a sales table. These keys define how the tables relate to each other.
If Power BI does not recognize a relationship automatically, you can create one
yourself by dragging a line between columns or using the "Manage Relationships"
dialog box. This is also where you decide if you want the filtering to be one-way or
two-way, depending on how your visuals should behave.
Let’s say you have a Product table and a Sales table. If you link them using
ProductID, and select bi-directional filtering, filters applied on product category will
impact your sales visuals and vice versa. This level of control helps you build
precise and dynamic reports.

🟠 Slide: Optimizing the Model for Reporting

Now, once your model is built, it’s time to polish it up—this is what we call
optimization. When you import data from external databases, it often comes in raw
form: unformatted columns, inconsistent types, and unnecessary fields.
Optimization ensures better performance and a smoother experience when building
visuals.
First, you should hide any fields that won’t be used in the report. These fields still
exist in the background, but they won’t clutter your Fields pane. Next, sort your
data correctly. For instance, days of the week may need to be sorted from Monday
to Sunday instead of alphabetically. Finally, format the data—especially fields like
dates and currency—to be user-friendly.
Think of this as tidying up your kitchen before cooking. If everything is named
properly, organized, and clean, you’ll cook faster and with fewer errors.
Ask Students: "When working in Excel, have you ever had to clean or reformat
data before using it in a PivotTable or chart? What kind of issues did you face?"

🔺 Slide: What are Hierarchies?

Let’s move into one of the coolest and most powerful features in Power BI:
hierarchies. A hierarchy is a way of grouping related fields in a natural, logical
order. For example, a geographic hierarchy might include Country → State → City.
Another common one is Date → Quarter → Month → Day.
Hierarchies allow users to drill down from summary data into more detailed views.
When you place a hierarchy into a visual like a bar chart, Power BI lets you click to
drill down into different levels. This improves user interaction and makes it easier to
explore trends at both macro and micro levels.
What’s even better is that Power BI automatically creates hierarchies for date fields.
If you import a column called "OrderDate," it will automatically generate a date
hierarchy with year, quarter, month, and day levels. You can also create custom
hierarchies based on your own business needs—like Region → Store → Department.

🟢 Slide: Creating Hierarchies

Creating a hierarchy in Power BI is simple but very powerful. Inside the Fields pane,
you select a column—say, "Country"—and right-click to choose New Hierarchy.
Then you rename it as needed, perhaps “Geography Hierarchy.” You can then right-
click another column like “State” and choose “Add to Hierarchy,” selecting the one
you just created. Repeat for "City" or any other related field.
Once your hierarchy is created, you can drag it into the Axis bucket of a visual like
a bar or column chart. You’ll see a drill-down arrow appear at the top of the visual.
Clicking that allows you to dive from country level down to state, then to city.
This creates an intuitive navigation system within your visual. It's great for users
who aren't familiar with the raw data but want to explore trends. You can also apply
filters at any level in the hierarchy to narrow down results without losing
interactivity.

🧪 Slide: Demonstration – Creating a Hierarchy

Let’s now do a quick demonstration together. I’ll show you how to create a simple
geographic hierarchy using sample data.
Step 1: Open Power BI Desktop and load a dataset with columns like Country,
State, and City. The “AdventureWorks” dataset is a good example, but you can use
any table that has multiple geographic levels.
Step 2: In the Fields pane, right-click on the "Country" column and choose New
Hierarchy. Rename the hierarchy “Geography.”
Step 3: Next, right-click on “State” and select Add to Hierarchy → Geography.
Do the same with “City.”
Step 4: Now, insert a column chart. Drag the “Geography” hierarchy into the Axis
bucket and drag a measure like “SalesAmount” into the Values bucket.
Step 5: At the top of the visual, click the "drill-down" arrow. You’ll be able to click a
bar for "Canada," for example, and then drill into its provinces, and then into cities.
This simple setup allows anyone looking at the report to interact with it more deeply
and extract layered insights.

🧾 Final Wrap-Up and Group Activity

Before we close today’s lesson, I want to emphasize that a good data model is the
backbone of every successful Power BI report. Whether it’s relationships,
optimization, or hierarchies, these elements ensure that your visuals are fast,
reliable, and meaningful.

Lesson 3
Today we are diving into an important and practical topic in Power BI: Using
Databases as a Data Source. This is Lesson 3 in our journey, and we’re going to
cover how to connect Power BI with various databases—most importantly SQL
Server, but also other data sources like SaaS apps and text files, and we’ll even
touch on using R scripts as a connector. We'll also walk through a live demonstration
where we connect Power BI to SQL Server and pull data into the Query Editor.
Let me start by reminding you why this is so important: as data professionals, you’ll
rarely work with data that’s stored only in Excel. Most enterprise-level organizations
store their data in databases. Think of a database like a giant filing cabinet,
structured and optimized for storage, retrieval, and management. And just like you
wouldn't dump all your documents on the floor to find one page, we need efficient,
structured ways to extract what we need. That’s where Power BI shines.

🧩 Part 1: What Is SQL Server and Why Connect It to Power BI?

Let’s begin with SQL Server. SQL Server is a relational database management
system—also called an RDBMS—developed by Microsoft. It stores data in structured
formats using tables, and these tables can be linked together using relationships,
typically through primary and foreign keys. Unlike something like Access, SQL
Server can handle multiple users simultaneously, process complex transactions, and
scale from small applications to massive enterprise systems.
Now, why would we want to connect Power BI to SQL Server? Think about a hospital
with thousands of patient records updated in real-time. You can't manage or analyze
that in Excel. But with SQL Server connected to Power BI, you can query, clean, and
visualize those insights efficiently.
👉 Group discussion prompt: “Can you think of any businesses or industries you
know where SQL Server might be used as a backend? And what kind of reports
would they generate in Power BI?”
(Wait for students to respond. Sample answer: Retail stores using SQL Server to
track inventory and sales, and Power BI to monitor top-selling items.)

Part 2: Connecting to SQL Server from Power BI

Now, let’s talk about how the connection happens. In Power BI Desktop, we go to
Home > Get Data > SQL Server. You'll be asked to enter the server name and,
optionally, the database name. You can then use a SQL query to pull specific data,
or select tables and views manually. After that, you can load the data into either the
Query Editor for cleaning or directly into the data model.
👉 Demonstration: Step-by-step walkthrough
Let me walk you through a typical connection workflow now:
1. Open Power BI Desktop.
2. Click on Home, then Get Data, and choose SQL Server.
3. In the SQL Server database window, input the server name. If you're
using a local server, it might be something like localhost\SQLEXPRESS.
4. Input the database name if you know it (optional).
 5. Choose how you want to connect:
o Import (data is copied into Power BI)

o DirectQuery (queries the database live)

6. Click OK. You’ll then be shown a Navigator pane with a list of tables.
7. Select the tables or views you want, or enter a custom SQL query if you
prefer more control.
8. Choose whether to load directly into the model or transform in Power Query
Editor.
Once done, you’ll have your database table available as a dataset. From here, you
can clean, transform, and visualize.

🧮 Part 3: Other Data Sources in Power BI

Besides SQL Server, Power BI can connect to a huge list of other data sources. This
includes SaaS applications like Salesforce, Google Analytics, Facebook, Marketo,
GitHub, and even Microsoft Exchange. You can also connect to other RDBMS
platforms like MySQL, Oracle, IBM DB2, or even non-relational sources like JSON and
XML.
Why is this important? Because in many organizations, data is distributed across
platforms. For example, sales data might be in Salesforce, customer feedback in
Google Sheets, and inventory in SQL Server. Power BI can bring all of that together
in one dashboard.
👉 Mini discussion: “Can anyone share a situation where combining data from
different sources would give better insights?”
(Sample response: Combining product performance data from SAP with marketing
campaign data from Google Analytics to understand campaign ROI.)

💻 Part 4: Using the R Script Data Connector

Let’s now switch gears and talk briefly about a more technical but powerful feature:
the R Script Data Connector. This lets you run R scripts in Power BI Desktop. R is a
programming language used for statistical computing, and if you know it, this
connector can be incredibly helpful.
When you run an R script, the result (typically a dataframe) can be imported into
Power BI for analysis and visualization. However, this feature does have some
limitations. For example, you must install Microsoft R Open before running scripts.
You can only import data frames, not lists or vectors, and scripts must be fully
tested outside Power BI first. Timeout is also limited to 30 minutes.
You’ll find this option under Get Data > More > Other > R Script.
I won’t dive into R scripting deeply today, but I encourage those of you with a
background in data science to try integrating some scripts into Power BI dashboards
—especially for predictive modeling or machine learning outputs.

🧪 Demonstration: Importing Data from SQL Server

Let’s now do the demonstration that was listed in your module. I want everyone to
follow along if possible.
1. Launch Power BI Desktop.
2. Go to Home > Get Data > SQL Server.
3. Enter the server name. If you're doing this on a school server or lab machine,
it might be pre-configured.
4. Click on DirectQuery or Import depending on your use case.
5. In the Navigator, select a couple of tables such as SalesData and ProductInfo.
6. Click on Transform Data to open the Power Query Editor.
7. Here, we can remove unnecessary columns, apply filters, or merge datasets.
8. Once your changes are applied, click Close & Apply.
Your data is now in Power BI, ready for use in visuals!

📚 Summary and Wrap-Up

So let’s wrap things up. Today we learned how Power BI connects to SQL Server—
one of the most powerful and scalable relational database systems out there. We
walked through how to extract, transform, and load data from SQL Server using
Power BI Desktop. We also explored how to connect to a range of other sources,
including SaaS platforms and even R scripts.
Remember that data connection is the very first step in any BI process. If your
connection is flawed, everything downstream—from visuals to decisions—will suffer.
Always make sure your data sources are correct, refreshed, and secure.

Lesson 4
Slide 1 – Introduction to Lesson 4: The Power BI Service
“Hi everyone! Welcome back to our Power BI module. Today we’re diving into Lesson
4, which is all about The Power BI Service. This is where the magic happens after
you’ve created your reports—this is where collaboration, sharing, and natural
querying take place. Specifically, we’ll be covering four major areas: Configuring
Your Data for Q&A, Creating Content Packs, Creating a Group, and a live
Demonstration where we’ll learn to query data using the Q&A feature. By the end of
this session, you’ll understand how to set up your Power BI content so that others
can not only access and use it, but also interact with it in very intelligent ways.”

Slide 2 – Configuring Your Data for Q&A

“Let’s start by exploring Power BI’s Q&A feature. This is one of the most intuitive
tools in Power BI—it allows users to ask questions using natural language and
receive instant answers in the form of charts, tables, or other visuals. When you
open any dashboard, you’ll notice a little text box at the top. That’s the Q&A box.
You can literally type in something like, ‘What were the sales in Q1 2023?’ and
Power BI will generate the appropriate visual based on the dataset linked to that
dashboard.
But there’s an important caveat: for Q&A to work effectively, it depends heavily on
how your dataset is named and structured. Q&A understands data best when the
entity names—like tables, columns, and measures—are clean, descriptive, and
resemble natural words or phrases someone would use while searching.
For example, instead of naming a column ‘empID’, name it ‘Employee ID’. Instead of
‘Rev_Q1’, name it ‘Quarter 1 Revenue’. These naming conventions help Q&A
understand what the user is asking.
You can use structured data from various sources, including uploaded Excel
workbooks. But regardless of the source, doing some upfront data cleaning is key.
Optimizing your data model by removing junk, creating calculated fields, and using
appropriate relationships will significantly improve Q&A results.
Let me ask you all this: Have any of you ever used Google or ChatGPT to ask a
question like 'Top restaurants near me'? Think of Power BI Q&A in the same way—it
just needs good, searchable data to answer effectively.”

Slide 3 – Creating Content Packs

“Alright, moving on to Content Packs. This is where we take a report, its dashboard,
and all its associated datasets, and bundle them together like a little package.
These are called content packs and they can be shared with other Power BI users
inside your organization.
Once someone imports a content pack, all its components—dashboards, reports,
and datasets—appear in their My Workspace. What’s even more helpful is that
these packs can be customized for different audiences. So if you’re building a pack
for sales managers, you might include revenue and conversion data. For HR, you
might focus on employee metrics.
You can also control who gets access—whether it’s specific teams, departments, or
the entire organization. And there’s room for branding too: you can add a title, a
short description, and even your company’s logo.
One key detail: the datasets that power the reports inside the content pack cannot
be removed. That’s intentional—because if someone opens your pack and the data
is missing, the whole thing breaks.
You can also import content packs from popular SaaS services like Bing, MailChimp,
Insightly, Marketo, and Twilio. These providers often have ready-made content packs
for you to plug into and start analyzing without having to build from scratch.”
Group Discussion Prompt: “Why might it be important to lock the dataset when
sharing content packs? What are the pros and cons of this?”
Sample Talking Points:
 Pros: Ensures data consistency, avoids broken visuals, keeps shared insights
reliable.
 Cons: Less flexibility for recipients to modify or experiment.

Slide 4 – Creating a Group

“Next, let’s talk about collaboration through Groups. In Power BI Pro or if you’re
using Microsoft Office 365, you can create groups that act like virtual workspaces.
These groups allow members to share dashboards, reports, and datasets
seamlessly.
Imagine your marketing team wants to work on a shared campaign dashboard. By
creating a group, you can all co-author the content, track updates, and manage
access—all in one place.
What’s even more powerful is that Power BI groups integrate directly with other
Office 365 services. So you can share files through OneDrive for Business, manage
events with Exchange calendars, or assign tasks in Teams.
You can create a group from your My Workspace area, and any report you build in
Power BI Desktop can be published directly to that group’s workspace.
This fosters real-time collaboration, and makes it easier for everyone in the group to
work from the same source of truth.”
Live Prompt: “Take a minute to think about a real team you’re part of—at school,
at work, or even in your personal life. What kind of dashboard would your team
benefit from sharing in a group workspace?”

Slide 5 – Demonstration: Querying Data by Using Q&A

“Now it’s time to bring everything together with a demonstration. Here’s what we’re
going to do step by step:
First, we’ll ask a question using the Q&A feature. I’ll start with something simple,
like ‘Total sales by category’. Power BI will instantly generate a bar chart or table
depending on how our data is structured.
Next, I’ll show you how to pin that visual to an existing dashboard. This is useful
because once you ask a great question and get a visual answer, you might want to
keep it for future tracking.
Finally, we’ll modify our question by adding context—like specifying a time period or
a region—and we’ll also manually select the type of visual. For example, instead of
letting Power BI choose, I’ll request a pie chart.
We’ll do this together. I’ll share my screen and walk you through it. Feel free to ask
your own questions using the Q&A box, and we’ll see how well Power BI
understands them. This is your chance to test it out and get comfortable using
natural language with your data.”

Lesson Summary – Wrap-up

“Today’s session focused on using the Power BI Service to make our reports and
dashboards more dynamic, shareable, and interactive. We learned how to configure
data so users can ask questions in natural language and get accurate, visually
appealing answers. We explored how to create and distribute content packs, which
allow us to package reports and dashboards into reusable bundles. We also learned
about creating groups in Power BI and Office 365 to encourage collaboration, and
finally, we saw a live demonstration on using the Q&A feature to ask smart
questions and display them on a dashboard. With these tools, you now have the
ability to not only build data stories but also empower others in your organization to
interact with and explore those stories themselves.”