POWER BI – Special

POWER BI – Syllabus
About Power BI:
Power BI is a business analytics tool developed by Microsoft that
allows users to visualize data, share insights, and make data-driven
decisions through interactive reports and dashboards.

Power BI Components:
Power BI Desktop- Windows application to build and design reports.
Power BI Service- Online SaaS platform for publishing, sharing, and
collaboration.
Power BI Mobile- App for accessing reports on smartphones and
tablets.
 Key Features of Power BI

Feature Description
Connects to multiple sources like Excel, SQL Server, Oracle, MySQL,
Data Connectivity
SharePoint, and web APIs.

Cleans and reshapes data using Power Query (no-code interface for ETL:
Data Transformation
Extract, Transform, Load).

Establishes relationships between data tables and uses DAX (Data Analysis
Data Modeling
Expressions) for calculated fields/measures.

Offers charts, maps, KPIs, slicers, cards, and more to create dynamic
Interactive Visualizations
dashboards and reports.

Cloud-based platform to publish, share, and collaborate on reports

Power BI Service
([Link]

Mobile Access View and interact with dashboards via mobile apps on Android and iOS.

Includes features like Q&A (natural language query), predictive analytics,

AI Capabilities
and ML integrations.
Power BI Sample Dashboard
 HOW TO DOWNLOAD & INSTALL POWER BI
DESKTOP?
1. Go to the official site:
[Link]
2. Click “Download free” or scroll down and click “Advanced download
options”.
3. You'll be redirected to the Microsoft Download Center.
Select your preferred language and click Download.
4. Choose the correct file for your system:
•[Link] → for 64-bit Windows
•[Link] → for 32-bit Windows (rare)
5. Run the installer and follow the setup wizard.
6. Launch Power BI Desktop from your Start Menu.
 System Requirements

Component Minimum Requirement

OS Windows 10/11 (64-bit preferred)

Memory (RAM) 4 GB (8+ GB recommended)

Disk Space ~1 GB free

.NET Framework .NET 4.7.2 or later

 Power Query in Power BI
Power Query is a powerful data connection and transformation tool built
into Power BI, Excel, and other Microsoft tools. It allows you to connect to
data, clean, reshape, and transform it before loading it into your model.
Power Query uses a language called M Language in the background. You
don't need to code, but you can customize if needed.

Power Query Editor Interface

When you click “Transform Data”, the Power Query Editor opens. It
includes:
Left pane- List of loaded tables/queries.
Main pane- Data preview and editing interface.
Query Settings pane (right)- Applied Steps (history of transformations).
Ribbon- Buttons for operations like Merge, Append, Replace Values, Group, etc.
 What You Can Do with Power Query?
Task Description
Import data from sources like Excel, SQL, Web,
Connect
SharePoint, APIs, etc.
Remove nulls, fix column types, split/merge
Clean
columns, remove duplicates.
Filter rows, pivot/unpivot data, group rows, add
Transform
calculated columns.
Rename columns, reorder, promote headers, create
Shape
relationships.
All steps are recorded and can be refreshed
Automate
automatically with updated data.
 Hands-on exercise with Power Query
Invoice ID Customer Name Date Product Quantity Unit Price Currency Region

INV001 Ram & Co. 2024-01-05 Laptop 2 75000NPR Kathmandu

INV002 Sita Traders 2024-01-06 Printer 1 25000NPR Biratnagar

INV003 Gita Pvt. Ltd. 2024-01-07 Mouse 5 1500NPR Pokhara

INV004 Hari Stores 2024-01-07 Keyboard 3 2500NPR Lalitpur

INV005 Laxmi Suppliers 2024-01-08 Monitor 2 35000NPR Birgunj

INV006 Bikash & Sons 2024-01-09 USB Drive 10 800NPR Butwal

 Hands-on exercise with Power Query
1. Remove the 'Currency' column (always NPR).
2. Change data types:
- Date → Date
- Quantity & Unit Price → Numbers
3. Add new column 'Total Price': Quantity × Unit Price.
4. Group data by 'Region':
- Total Quantity
- Total Sales (Quantity × Unit Price)
5. Pivot the 'Product' column: show product-wise quantity sold.
6. Filter: Keep rows where Unit Price > 2000.
7. Merge with a new table containing:
- Product
- Category (Electronics/Accessories)
8. Extract 'Year-Month' from the Date column.
Goal: Clean, transform, and enrich your dataset for better reporting.
Outputs
 DAX Deep Dive
DAX (Data Analysis Expressions) is a formula language used in Power BI,
Excel Power Pivot, and Analysis Services to create custom calculations on
your data models. It’s similar to Excel formulas—but designed for use
with relational data and columns.

Key Purpose of DAX

DAX is used to:
•Build calculated columns and measures
•Create aggregations, filters, and time intelligence (like YTD, MTD)
•Enable dynamic and interactive dashboards in Power BI
 Examples of What You Can Do with DAX

Task DAX Example Description

Adds up sales from
Calculate Total Sales Total Sales = SUM(Sales[Amount])
the 'Amount' column

Online Sales =
Sum sales only where
Filtered Measure CALCULATE(SUM(Sales[Amount]),
channel is Online
Sales[Channel] = "Online")

YTD Sales = TOTALYTD([Total Sales], Calculates sales YTD

Year-to-Date
Dates[Date]) using the date table

Running Total = CALCULATE([Total Sales],

Cumulative sales to
Running Total FILTER(ALL(Dates), Dates[Date] <=
current date
MAX(Dates[Date])))
 Types of DAX Formulas

Type Use For Example

Calculated Row-level Profit = Sales[Revenue] -

Columns calculations Sales[Cost]

Aggregated, Avg Sales =

Measures
dynamic values AVERAGE(Sales[Amount])

Intermediate FilteredTable = FILTER(Sales,

Tables
data tables Sales[Region] = "East")
 Popular DAX Functions
•SUM, AVERAGE, COUNT, DISTINCTCOUNT
•CALCULATE – modifies filter context
•FILTER, ALL, ALLEXCEPT
•RELATED, LOOKUPVALUE
•DATESYTD, SAMEPERIODLASTYEAR
(Time intelligence)
 DAX Exercises: Aggregates
DAX Formula (as
Exercise Objective
Measure)

Calculate total Total Sales = SUM(Sales[Sales

Total Sales
revenue Amount])

Total Quantity =
Total Quantity Sold Total units sold
SUM(Sales[Quantity])

Avg Unit Price =

Average Unit Price Average of Unit Price
AVERAGE(Sales[Unit Price])
 DAX Exercises: Time Intelligence

Exercise Objective DAX Formula (as Measure)

YTD Sales = TOTALYTD([Total

YTD Sales Sales Year-to-Date
Sales], 'Date'[Date])

MTD Sales = TOTALMTD([Total

MTD Sales Month-to-Date Sales
Sales], 'Date'[Date])
Sales LY = CALCULATE([Total
Sales Last Year Sales],
Compare YoY
(Same Period) SAMEPERIODLASTYEAR('Date'[
Date]))
 DAX Exercises: Filtered Measures
DAX Formula (as
Exercise Objective
Measure)

East Sales =
Sales in East Region-specific
CALCULATE([Total Sales],
Region measure
Sales[Region] = "East")

Laptop Sales =
Product-specific
Laptop Sales CALCULATE([Total Sales],
measure
Sales[Product] = "Laptop")
 DAX Exercises: Running Totals
Running Sales =
CALCULATE(
[Total Sales],
FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date]))
)

5. Customer & Invoice Analysis

Unique Customers = DISTINCTCOUNT(Sales[Customer])

Invoice_Count = DISTINCTCOUNT(Sales[Invoice ID])
 DAX Exercises: Calculated Columns
Exercise Column Name DAX Formula

=IF(Sales[Sales
Sales Category High / Low sales Amount] > 100000,
"High", "Low")

Extract month from =FORMAT(Sales[Date],

Sales Month
Date "MMM")

=Sales[Customer] & " -

Customer-Product Key Composite key
" & Sales[Product]
 Bonus Challenges
•Identify top 3 products by total sales.
•Show % contribution of each region to total sales.
•Create a Slicer-based report with dynamic sales by region or product.
 Data Modeling
Star Schema vs Snowflake Schema: One central Fact Table linked
to multiple Dimension Tables in Star Schema while Fact Table connected
to normalized dimension tables (with sub-tables) in Snowflake.
Use Star Schema for better performance and easier DAX modeling in
Power BI.

Relationships and Cardinality

A relationship connects tables using keys (e.g., ProductID in Sales
and Product tables).
Cardinality defines the type of relationship: One-to-Many (1:* ), Many-to-
One (*:1), Many-to-Many (:), One-to-One (1:1).
Always use One-to-Many wherever possible for best performance
and clarity.
 Practice Tasks:

1. Create relationships between FactSales and dimension tables using

keys (CustomerID, ProductID, Date).
2. Build a star schema.
3. Use slicers for Product Category, Region, Month.
4. Create measures: Total Sales, Avg Sales per Region, Monthly
Trends, etc.
 Power BI Dashboard Design &
Storytelling
Executive Dashboards
Focus Description
Show metrics like Revenue, Profit, ROI, Cost Trends, YoY
KPI-Driven
Growth.
High-Level Include top-down views that drill into details only when
Summaries needed.
Real-Time or Should auto-refresh or provide snapshot for board
Periodical meetings.
Cards for KPIs, bar charts for performance, line charts for
Layout
trends, tables for quick summaries.
 Power BI Dashboard Design &
Storytelling
Drill-Throughs, Bookmarks, and What-If Analysis
Feature Purpose
Drill- Click on a data point (e.g., Product A) to see its detailed
throughs page (sales, region, trends).
Save specific visual states or filters—great for storytelling
Bookmarks
presentations or toggles.
What-If Add sliders or parameters (e.g., Discount %, Growth Rate)
Analysis to see forecast effects in real time.
 Power BI Dashboard Design &
Storytelling
From the data set available, create visuals such as:

• Revenue trends by Date

• Sales by Region
• Top Products or Categories
• Performance by Sales Rep
• KPI Cards for Revenue, Units Sold,
Avg. Unit Price
 Import Data from Varied Sources
Source Type Example How to Use
Excel Financial [Link] Get Data → Excel → Browse → Select File

CSV/Text [Link] Get Data → Text/CSV → Select File

SQL Server ERP Data Warehouse Get Data → SQL Server → Enter server name and DB

Web Exchange rates from website Get Data → Web → Paste URL (e.g., HTML table or API)

SharePoint Folder HR files stored in cloud Get Data → SharePoint Folder → Paste site URL

MySQL /
Company backend data Get Data → MySQL / PostgreSQL → Enter credentials
PostgreSQL
Azure Blob / Data
Big data or logs Get Data → Azure → Choose service
Lake

Power BI Dataset Use existing models Get Data → Power BI datasets

Folder Load multiple CSV files together Get Data → Folder → Select folder path
Thank You!