Power BI for Analysis - Lesson Notes (Beginner to Intermediate)

Course Outline
Module 1: Introduction to Power BI
 What is Power BI?
 Installing & Setting Up Power BI Desktop
 Understanding the Power BI Interface
Module 2: Data Import & Transformation
 Connecting to Data Sources
 Power Query Editor Basics
 Advanced Transformations
Module 3: Data Modeling & Relationships
 Star Schema vs. Flat Tables
 Creating & Managing Relationships
 Role-Playing Dimensions & Date Tables
Module 4: DAX Fundamentals
 Calculated Columns vs. Measures
 Basic DAX Functions
 Intermediate DAX
Module 5: Visualization & Reporting
 Best Practices for Dashboard Design
 Key Visuals
 Interactive Features
Module 6: Publishing & Sharing Insights
 Publishing to Power BI Service
 Creating Dashboards & Sharing Reports
 Scheduled Refreshes & Security

Module 1: Introduction to Power BI

Topic Title: What is Power BI?
Subtopics:
 Power BI Components
  Power BI Desktop vs. Power BI Service
 Power BI Mobile & Gateway
Explanation:
Power BI is Microsoft’s business analytics tool designed for data visualization
and business intelligence. Key components include:
 Power BI Desktop: Authoring and modeling tool
 Power BI Service: Cloud-based publishing and sharing platform
 Power BI Gateway: For data refresh from on-premise sources
Examples:
 A retail analyst uses Power BI Desktop to create a report, publishes it
to Power BI Service, and sets up a gateway to refresh SQL Server data
daily.
Key Takeaways:
 Power BI is a suite of tools that work together to deliver end-to-end
business intelligence.
 Desktop is for creation; Service is for sharing.

Topic Title: Installing & Setting Up Power BI Desktop

Subtopics:
 System Requirements
 Download & Installation
 Initial Configuration
Explanation:
Power BI Desktop can be downloaded from Microsoft Store or the Power BI
website. Once installed, set up the regional settings and preview features
based on project needs.
Examples:
 Enable preview features like small multiples and field parameters.
Key Takeaways:
 Always use the latest version.
 Customize settings to suit business requirements.

Topic Title: Understanding the Power BI Interface

Subtopics:
  Report View
 Data View
 Model View
Explanation:
 Report View: Design visual reports.
 Data View: View underlying data tables.
 Model View: Manage relationships and data structure.
Examples:
 Use Model View to ensure correct cardinality and cross-filter directions.
Key Takeaways:
 Mastering the interface improves productivity and accuracy.

Module 2: Data Import & Transformation

Topic Title: Connecting to Data Sources
Subtopics:
 Excel & CSV
 SQL Server & Databases
 Web APIs
Explanation:
Use “Get Data” to connect to sources. Power BI supports a wide range of
connectors.
Examples:
 Connect to Excel sales data, import tables, and load to Power BI.
Key Takeaways:
 Choose between import and DirectQuery based on data volume and
performance needs.

Topic Title: Power Query Editor Basics

Subtopics:
 Removing Columns & Rows
 Changing Data Types
 Sorting & Filtering
Explanation:
Power Query uses a step-based transformation model. Clean data before
loading.
Examples:
= [Link]([Link](...))

Key Takeaways:
 Clean data ensures accurate reports.
 Power Query transformations are non-destructive.

Topic Title: Advanced Transformations

Subtopics:
 Merging Queries
 Appending Tables
 Custom Columns (M Code)
Explanation:
Use Merge for joining datasets and Append to stack datasets. Use M code for
advanced logic.
Examples:
 Merge customer and order tables by Customer ID.
Key Takeaways:
 Understand joins (Left, Inner, Full) in merging.
 M code enhances transformation flexibility.

Module 3: Data Modeling & Relationships

Topic Title: Star Schema vs. Flat Tables
Subtopics:
 Fact and Dimension Tables
 Benefits of Star Schema
 Avoiding Snowflakes
Explanation:
Star schemas improve model performance and enable reusable dimensions.
Examples:
  Sales Fact table connected to Date, Customer, and Product
Dimensions.
Key Takeaways:
 Use star schema for scalability and clarity.
 Avoid flattening tables unnecessarily.

Topic Title: Creating & Managing Relationships

Subtopics:
 Relationship Cardinality
 Cross-filter Direction
 Auto-detect vs. Manual
Explanation:
Relationships define how tables interact. Use Model View for relationship
management.
Examples:
 Create one-to-many relationship between Customers and Orders.
Key Takeaways:
 Always validate relationships visually.
 Avoid circular dependencies.

Topic Title: Role-Playing Dimensions & Date Tables

Subtopics:
 Multiple Relationships
 USERELATIONSHIP Function
 Calendar Table Creation
Explanation:
Role-playing dimensions like multiple date fields (order date, ship date)
require inactive relationships.
Examples:
SalesByShipDate = CALCULATE([Total Sales], USERELATIONSHIP(Orders[Ship
Date], Calendar[Date]))

Key Takeaways:
 Use single calendar table for consistency.
  Manage inactive relationships with DAX.

Module 4: DAX Fundamentals

Topic Title: Calculated Columns vs. Measures
Subtopics:
 Row Context vs. Filter Context
 Storage Considerations
Explanation:
 Calculated Columns: Used for row-by-row calculations.
 Measures: Used for aggregated results.
Examples:
FullName = Customers[FirstName] & " " & Customers[LastName]
Total Sales = SUM(Sales[Amount])

Key Takeaways:
 Prefer measures to optimize model size and performance.

Topic Title: Basic DAX Functions

Subtopics:
 SUM, AVERAGE, COUNT
 FILTER & ALL
Explanation:
These functions are the foundation of Power BI calculations.
Examples:
Average Sales = AVERAGE(Sales[Amount])
Filtered Sales = CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

Key Takeaways:
 Combine functions to achieve complex logic.
 Use ALL to ignore filters.

Topic Title: Intermediate DAX

Subtopics:
 CALCULATE & Context Transition
  RELATED & RELATEDTABLE
 Time Intelligence (YTD, MTD)
Explanation:
CALCULATE modifies filter context. Time intelligence functions require a
proper date table.
Examples:
YTD Sales = TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

Key Takeaways:
 CALCULATE is the most powerful function in DAX.
 Time Intelligence needs a marked date table.

Module 5: Visualization & Reporting

Topic Title: Best Practices for Dashboard Design
Subtopics:
 Simplicity & Focus
 Consistent Colors & Fonts
 Avoiding Chart Overload
Explanation:
Use clear titles, legends, and tooltips. Design for your audience.
Examples:
 KPI cards for performance metrics, slicers for interactivity.
Key Takeaways:
 Less is more. Focus on insights, not decoration.

Topic Title: Key Visuals

Subtopics:
 Bar & Column Charts
 Line Charts & Combo Charts
 Maps & Tables
Explanation:
Each visual type serves different purposes. Tables = detail, charts = trends.
Examples:
  Use line charts for trends over time, stacked bars for comparison.
Key Takeaways:
 Choose visuals that best represent your data story.

Topic Title: Interactive Features

Subtopics:
 Slicers & Filters
 Drill-Through Pages
 Tooltips & Bookmarks
Explanation:
Interactivity improves user experience and allows data exploration.
Examples:
 Add a drill-through to see sales by product when clicking on region.
Key Takeaways:
 Empower users with interactive exploration.

Module 6: Publishing & Sharing Insights

Topic Title: Publishing to Power BI Service
Subtopics:
 Workspace Structure
 Uploading Reports
 Versioning & Replacing Reports
Explanation:
Use workspaces for organizing content. Publish from Power BI Desktop
directly.
Examples:
 Publish Sales_Report.pbix to “Finance” workspace.
Key Takeaways:
 Name reports consistently and follow version control.
Topic Title: Creating Dashboards & Sharing Reports
Subtopics:
 Pinning Visuals to Dashboards
 Sharing with Teams or External Users
 Embed Options
Explanation:
Dashboards aggregate visuals from multiple reports. Share via links or
Microsoft Teams.
Examples:
 Pin monthly KPI to shared dashboard for execs.
Key Takeaways:
 Dashboards provide a snapshot view; reports offer drill-down.

Topic Title: Scheduled Refreshes & Security (RLS)

Subtopics:
 Setting Up Data Gateway
 Scheduled Refresh Rules
 Role-Level Security
Explanation:
Ensure fresh data via scheduled refreshes. Use RLS to restrict access by user
roles.
Examples:
 Manager in East sees only East data using RLS filter.
Key Takeaways:
 Security and automation are critical in enterprise BI solutions.

End of Lesson Notes

Module 1: Introduction to Power BI

What is Power BI?
Power BI Components
 Power BI Desktop: The primary tool for data modeling and report
creation.
 Power BI Service: Cloud-based platform for sharing and
collaboration.
 Power BI Mobile: Allows users to view reports on mobile devices.
 Power BI Gateway: Acts as a bridge for secure data refresh between
on-premises data and the Power BI service.
Power BI Desktop vs. Power BI Service
 Desktop: Offline tool, used for data loading, transformation, modeling,
and report building.
 Service: Online platform to publish, share, and collaborate on reports
and dashboards.
Power BI Mobile & Gateway
 Mobile: View and interact with reports on smartphones and tablets.
 Gateway: Required for scheduling refreshes from local files or
databases like SQL Server.

Installing & Setting Up Power BI Desktop

System Requirements
 Minimum Windows 10 OS
 At least 2 GB RAM (4 GB recommended)
 .NET Framework 4.7.2 or later
Download & Installation
 Download from Microsoft Store or official website.
 Installation is straightforward with default settings.
Initial Configuration
 Configure regional settings, default file locations, and preview
features (like field parameters) after first launch.
Understanding the Power BI Interface
Report View
 Design visualizations, add slicers, and format the layout.
Data View
 Inspect the raw data tables post-transformation.
Model View
 Define and visualize relationships between tables. Use cardinality and
cross-filter settings here.

Module 2: Data Import & Transformation

Connecting to Data Sources
Excel & CSV
 Common file formats for business data. Supports loading structured
tables or named ranges.
SQL Server & Databases
 Connects directly to databases for large-scale data. Choose between
Import and DirectQuery.
Web APIs
 Access public or private APIs using URLs to pull JSON/XML data into
Power BI.

Power Query Editor Basics

Removing Columns & Rows
 Eliminate unnecessary data to streamline reports.
Changing Data Types
 Ensure columns are in correct format (e.g., Date, Currency, Whole
Number).
Sorting & Filtering
 Rearrange or narrow down data before loading into the model.
Advanced Transformations
Merging Queries
 Combines data from two tables based on a common key (like a SQL
JOIN).
Appending Tables
 Stacks similar data tables vertically (e.g., monthly sales files).
Custom Columns (M Code)
 Write custom logic using M language to derive new fields.

Module 3: Data Modeling & Relationships

Star Schema vs. Flat Tables
Fact and Dimension Tables
 Fact: Contains numeric data like sales or revenue.
 Dimension: Descriptive attributes (e.g., Customer, Product).
Benefits of Star Schema
 Faster query performance
 Clearer relationships
 Easier DAX calculations
Avoiding Snowflakes
 Keep dimension tables denormalized to reduce complexity.

Creating & Managing Relationships

Relationship Cardinality
 One-to-many (1:), Many-to-one (:1), or Many-to-many (:)
Cross-filter Direction
 Determines how filters flow between related tables (Single or Both).
Auto-detect vs. Manual
 Power BI tries to auto-create relationships but manual setup ensures
correctness.
Role-Playing Dimensions & Date Tables
Multiple Relationships
 Tables like Orders can relate to a Date table through different date
fields (OrderDate, ShipDate).
USERELATIONSHIP Function
 Temporarily activates an inactive relationship in a DAX expression.
Calendar Table Creation
 Use DAX or Power Query to create a calendar; mark it as a Date Table
for Time Intelligence.

Module 4: DAX Fundamentals

Calculated Columns vs. Measures
Row Context vs. Filter Context
 Calculated Columns operate row-by-row.
 Measures aggregate across filters in visuals.
Storage Considerations
 Calculated columns consume more memory; measures are optimized.

Basic DAX Functions

SUM, AVERAGE, COUNT
 Perform basic aggregations on numeric columns.
FILTER & ALL
 FILTER: Apply custom filter logic in measures.
 ALL: Remove filters to show totals or compare against them.

Intermediate DAX
CALCULATE & Context Transition
 CALCULATE modifies the filter context.
  Context transition converts row context to filter context inside
CALCULATE.
RELATED & RELATEDTABLE
 RELATED pulls columns from a related table.
 RELATEDTABLE returns a table for use in aggregations.
Time Intelligence (YTD, MTD)
 Built-in DAX functions to calculate metrics over time using a proper
Date table.

Module 5: Visualization & Reporting

Best Practices for Dashboard Design
Simplicity & Focus
 Avoid clutter; emphasize key insights.
Consistent Colors & Fonts
 Maintain brand alignment and user readability.
Avoiding Chart Overload
 Only use visuals that add value.

Key Visuals
Bar & Column Charts
 Compare categories.
Line Charts & Combo Charts
 Show trends over time. Combo charts combine bars and lines for dual-
axis insight.
Maps & Tables
 Maps: Geographical analysis
 Tables: Detailed records or drill-down data

Interactive Features
Slicers & Filters
 Enable users to refine the data in real-time.
Drill-Through Pages
 Create detail pages accessible by right-clicking a data point.
Tooltips & Bookmarks
 Tooltips: Show extra context when hovering.
 Bookmarks: Save report states or views.

Module 6: Publishing & Sharing Insights

Publishing to Power BI Service
Workspace Structure
 Organize reports, datasets, and dashboards into themed workspaces
(e.g., Sales, HR).
Uploading Reports
 From Desktop, use the Publish button to push reports to the selected
workspace.
Versioning & Replacing Reports
 Re-publish the same file to overwrite; maintain naming convention for
clarity.

Creating Dashboards & Sharing Reports

Pinning Visuals to Dashboards
 Dashboards can contain pinned visuals from multiple reports.
Sharing with Teams or External Users
 Share via email, Teams, or external links (requires proper permissions).
Embed Options
 Reports can be embedded into websites, SharePoint, or apps with
secure access.

Scheduled Refreshes & Security (RLS)

Setting Up Data Gateway
 Required for refreshing on-premises data sources.
Scheduled Refresh Rules
 Define refresh times (e.g., daily at 8AM). Use fail notifications.
Role-Level Security
 Create DAX filters for roles; users see only what they’re allowed to.