DEV Community

Cover image for Power BI Data Modeling and Schema Design
Ibrahim Khalif
Ibrahim Khalif

Posted on

Power BI Data Modeling and Schema Design

#luxdev #powerfuldevs #webdev

Overview of Power BI

Power BI is a business analytics tool by Microsoft used to:

  • Analyze data
  • Create interactive reports
  • Build dashboards
  • Share insights visually

components of Power BI

  1. Power BI Desktop
    Used to import data, clean data, and build reports
    Installed on your computer
    Main tool for beginners

  2. Power BI Service (Online)
    Used to publish and share reports
    Runs in a web browser

  3. Power BI Mobile
    View dashboards on mobile devices

Installing Power BI Desktop

Go to Microsoft Store or official Power BI website
Search Power BI Desktop
Download and install
Open the application

Data Sources in Power BI

Power BI can connect to many data sources such as:
Excel
CSV files
SQL Server
Web data
SharePoint
Google Sheets

Power BI Workflow

The Power BI workflow answers one big question:
How do I turn raw data into insights people can understand and trust?
The workflow has 6 main stages:

Data → Cleaning → Modeling → Calculations → Visualization → Sharing
Enter fullscreen mode Exit fullscreen mode
Get Data – “Where is my data coming from?”

What this step means
This is where you connect Power BI to your data source.
Power BI does NOT store data like Excel.
It connects, loads, and refreshes data.
Common data sources

Excel files
CSV files
Databases
Web data
SharePoint

NOTE: What beginners often miss
Thinking Power BI is only for Excel
Loading messy data without checking it
Loading everything instead of only what is needed

Transform Data (Power Query) – “Is my data clean?”

This is THE MOST IMPORTANT STEP for beginners.
What Power Query does
Power Query is where you:

  • Clean data
  • Fix errors
  • Prepare data for analysis
  • Typical cleaning tasks
  • Remove blank rows
  • Remove duplicates

Fix data types (text vs number vs date)

  • Rename columns
  • Handle missing values
  • Trim extra spaces

Example
If you have:

Discount = "20%" (text)
`

Power BI cannot calculate with it.
You must convert it to:


0.2 (number)

Beginner mistake

  • Skipping Power Query
  • Doing cleaning in visuals instead of Power Query
  • Leaving columns as text

Golden rule
If your data is wrong, your visuals will lie.

Load Data – “Freeze the clean version”
When you click Close & Apply:

  • Power BI loads the cleaned data
  • This becomes the official dataset

Data Modeling – “How do tables talk to each other?”
This is where Power BI becomes powerful.
What modeling means

It defines:

  • Relationships between tables
  • How filters flow
  • How calculations behave

Example
You may have:
1.Sales table
2.Products table
3.Customers table
You connect them using keys like:

ProductID
CustomerID

Relationship types

  • One-to-Many (MOST COMMON)
  • Many-to-Many (advanced)
  • One-to-One (rare)

Beginner mistake
No relationships
Wrong direction of relationship
Using one big flat table when a model is better

DAX Calculations – “What questions am I asking?”
DAX is NOT Excel formulas.

It is about context.
Two types of DAX
1.Calculated Columns
- Calculated row by row
- Stored in the table
Example:

Profit = Sales[Revenue] - Sales[Cost]

2.Measures (VERY IMPORTANT)
- Calculated on demand
- Change with filters
Example:

Total Sales = SUM(Sales[Revenue])

Why measures are preferred
- Faster
- Dynamic
- Used in visuals

Beginner mistake

  • Using calculated columns for everything
  • Not understanding filter context
  • Hardcoding numbers

Build Visuals – “How do I tell the story?”
This is where insights become visible.
What visuals do

  • Show patterns
  • Compare values
  • Highlight trends
  • Support decisions

Common visuals and their purpose
Visual When to use

Bar/Column- Compare categories
Line- Trends over time
Pie- Share/percentage
Table- Details number
Scatter- Relationships

Beginner mistake
Too many visuals
No titles
Wrong visual type
Decorative instead of informative

Report Design – “Can someone understand this in 5 seconds?”

  • Design matters.
  • Good report design
  • Clear titles
  • Logical layout
  • Consistent colors
  • Important KPIs at the top

Publish & Share – “Who needs to see this?”
Publishing does

  • Uploads report to Power BI Service
  • Enables sharing
  • Allows refresh

Beginner mistake
Publishing without checking data
No explanation for users

The FULL Workflow Summary (Mental Map)

Raw Data
↓
Power Query (Clean & Fix)
↓
Data Model (Relationships)
↓
DAX (Answer Questions)
↓
Visuals (Tell Story)
↓
Publish (Share Insights)

Top comments (0)