Forem: RaggedGent-io

How to Publish a Power BI Report and Embed It in a Website

RaggedGent-io — Mon, 06 Apr 2026 15:00:48 +0000

This is a complete beginner-to-intermediate guide to sharing your Power BI dashboards with the world.

Introduction

You've built a beautiful Power BI report — data is clean, visuals are sharp, and the insights are clear. But right now it only lives on your laptop. Publishing is what takes your work from a local file to a live, shareable dashboard that anyone with a link (or a browser) can interact with.

In this article, you'll learn the full journey:

What Power BI publishing actually means
How to set up a Workspace
How to upload and publish your .pbix file
How to generate an embed code
How to drop that embed into any website

By the end, you'll have a live, interactive Power BI report running inside a webpage — just like the big data teams do it.

What Is Power BI Publishing?

Power BI Desktop is where you build reports. Power BI Service (the cloud platform at app.powerbi.com) is where you share and manage them.

Publishing is the bridge between the two. When you publish from Desktop, your .pbix file is uploaded to the Power BI Service, where it becomes a live dataset and report that others can view, interact with, and embed.

Power BI Desktop (.pbix)
        ↓ Publish
Power BI Service (cloud)
        ↓ Embed code
Your Website / Blog / App

Power BI Service also handles scheduled data refresh, row-level security, collaboration through Workspaces, and embedding into external platforms — all without writing backend code.

Prerequisites

Before you start, make sure you have:

Power BI Desktop installed (free download)
A Microsoft account (work, school, or personal with Power BI sign-up)
A completed .pbix report ready to publish
For public embed: A Power BI Pro or Premium Per User (PPU) license (required for workspace publishing)

Note on Licensing: The free Power BI license lets you build and publish to My Workspace. To publish to a shared workspace or embed publicly, you need Power BI Pro (≈$10/month).

Step 1: Create a Workspace

A Workspace in Power BI Service is like a shared project folder — it holds your reports, datasets, and dashboards, and can be shared with teammates.

How to Create One

Go to app.powerbi.com and sign in.
In the left sidebar, click "Workspaces".
Click "+ New workspace" at the bottom of the panel.
Fill in the workspace Name (e.g., Electronics Sales Dashboard) and optional Description.
Expand Advanced to set a contact list — people listed here receive notifications about workspace activity.

Fig 1: The Advanced settings panel during workspace creation — the Contact list field

Click Apply. Your workspace now appears in the left sidebar.

Tip: Pin frequently used workspaces to the top of the list by hovering over the workspace name and clicking the pin icon.

Fig 2: Pinning a workspace for faster daily access

Step 2: Upload and Publish Your Report

There are two ways to publish — directly from Power BI Desktop (recommended), or by uploading the .pbix via the browser.

Method A: Publish from Power BI Desktop (Recommended)

Open your .pbix file in Power BI Desktop.
Go to the Home tab in the ribbon.
Click the "Publish" button on the right side of the ribbon.

Fig 3: The Publish button in the Power BI Desktop Home ribbon

Sign in with your Microsoft account if prompted.
A "Select a destination" dialog appears. Choose the workspace you just created — you can search by name.

Fig 4: Choosing your target workspace in the Publish to Power BI dialog

Click Select. Power BI Desktop uploads your file to the cloud.
A success dialog appears with a direct link to your live report.

Fig 5: Publish success — click the link to verify your live report in the browser

Click the link to open and verify your report in the browser.

Method B: Upload via Browser

If you prefer not to open Desktop:

Navigate to your workspace in Power BI Service.
Click "+ New" → "Upload a file" → "Local file".
Select your .pbix file. Power BI automatically creates both a dataset and a report.

Step 3: Generate the Embed Code

With your report live in Power BI Service, you can now generate an embed code to place it on any website.

Important: "Publish to web" creates a public link — no login required to view it. Never use this for sensitive or confidential data.

Steps

Open your published report in Power BI Service.
Click "File" in the top menu → "Embed report" → "Publish to web (public)".

Fig 6: Accessing "Publish to web" from the File menu in Power BI Service

A dialog appears reviewing what "Publish to web" means. Click "Create embed code".

Fig 7: The embed code creation prompt

A warning appears confirming the report will be publicly accessible. Click "Publish" to confirm.

Fig 8: Public sharing confirmation — only proceed if your data is safe to share publicly

The Success dialog appears. You'll see:
- A shareable link to send directly to anyone
- An HTML embed snippet (<iframe> tag) to paste into your website
- Options to set the Size and Default page of the embedded view

Fig 9: The embed code dialog — copy either the link or the full iframe HTML

Copy the <iframe> code. It looks like this:

<iframe 
  title="Electronics Sales Dashboard" 
  width="1140" 
  height="541.25" 
  src="https://app.powerbi.com/reportEmbed?reportId=XXXX&autoAuth=true&ctid=XXXX" 
  frameborder="0" 
  allowFullScreen="true">
</iframe>

You can manually adjust the width and height values. Adding 56px to the height accounts for Power BI's bottom navigation bar and prevents cut-off.

Fig 10: The raw iframe embed code — width and height are fully customizable

Step 4: Embed the Report on a Website

You now have an <iframe> snippet. Here's how to use it across different platforms.

Option A: Plain HTML Website

Paste the iframe directly into your HTML file:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>My Sales Dashboard</title>
  <style>
    .report-container {
      width: 100%;
      max-width: 1140px;
      margin: 0 auto;
      padding: 20px;
    }
    iframe {
      width: 100%;
      height: 541px;
      border: none;
      border-radius: 8px;
      box-shadow: 0 4px 12px rgba(0,0,0,0.1);
    }
  </style>
</head>
<body>
  <div class="report-container">
    <h2>Electronics Sales Report</h2>
    <iframe 
      title="Electronics Sales Dashboard" 
      src="https://app.powerbi.com/reportEmbed?reportId=YOUR_ID&autoAuth=true&ctid=YOUR_CTID" 
      allowFullScreen="true">
    </iframe>
  </div>
</body>
</html>

Option B: WordPress

Open the WordPress block editor.
Add a Custom HTML block.
Paste your <iframe> code and preview before publishing.

Option C: Dev.to

Dev.to supports raw HTML in its editor — paste the <iframe> code directly into the article body and it will render. Medium does not support iframes natively; use the shareable link instead, or link to your hosted HTML page.

Making the Embed Responsive

The default iframe has a fixed width. For mobile-friendly layouts, use this CSS wrapper:

.pbi-container {
  position: relative;
  width: 100%;
  padding-bottom: 56.25%; /* 16:9 aspect ratio */
  height: 0;
  overflow: hidden;
}

.pbi-container iframe {
  position: absolute;
  top: 0;
  left: 0;
  width: 100%;
  height: 100%;
  border: none;
}

<div class="pbi-container">
  <iframe 
    src="https://app.powerbi.com/reportEmbed?reportId=YOUR_ID&autoAuth=true"
    allowFullScreen="true">
  </iframe>
</div>

Recommended iframe sizes for common aspect ratios:

Ratio	Size	Dimensions (W × H)
16:9	Small	640 × 416 px
16:9	Medium	800 × 506 px
16:9	Large	960 × 596 px
4:3	Small	640 × 536 px
4:3	Medium	800 × 656 px

Managing Your Embed Codes

Power BI tracks all your active embed codes. To view or revoke them:

Click the Settings gear icon in Power BI Service → "Manage embed codes".

Fig 11: Accessing embed code management from the Settings menu

You'll see all active embed codes for reports in that workspace. You can retrieve (copy) or delete any code.
Deleting a code instantly breaks the embed on any website using it — useful if you need to revoke public access quickly.

Key Insights and Takeaways

1. Power BI Desktop ≠ Power BI Service
Desktop is your local editor; Service is your cloud publisher. Publishing syncs your work to the cloud — it doesn't auto-update. Re-publish from Desktop whenever you make changes.

2. Workspaces are your project folders
Use separate workspaces for different projects or clients. The default "My Workspace" is personal and cannot be shared — use a new workspace for any collaboration or embedding.

3. "Publish to web" = fully public
There's no login wall on public embeds. Never publish sensitive financials, personal data, or confidential information this way. For private sharing, use Power BI's App feature or Secure Embed (requires Pro license).

4. Dataset and report are separate objects
When you publish a .pbix, Power BI creates two items: a dataset (your data model) and a report (your visuals). This matters when refreshing data or building multiple reports from the same dataset.

5. Data refresh requires a gateway for local sources
If your data lives in local Excel files or databases, you'll need a Power BI Gateway for scheduled refresh. Cloud sources (SharePoint, Azure, web APIs) refresh directly without a gateway.

6. The embed URL is customizable
Append parameters to control the embed experience:

?filter=Table/Column eq 'Value'     → pre-filter to a specific value
&pageName=ReportSection123          → open a specific report page
&navContentPaneEnabled=false        → hide the page navigation pane

Quick Reference Checklist

[ ] Report built and saved as .pbix in Power BI Desktop
[ ] Signed in to Power BI Service (app.powerbi.com)
[ ] Workspace created for the project
[ ] Report published from Desktop (Home → Publish)
[ ] Report visible and verified in workspace
[ ] Embed code generated (File → Embed → Publish to web)
[ ] iframe copied and pasted into website/blog
[ ] Embed tested and rendering correctly
[ ] .pbix uploaded to GitHub repository
[ ] Article published on Dev.to / Medium / personal blog
[ ] GitHub link sent to datascience@luxdevhq.com
[ ] Article link sent to datascience@luxdevhq.com

Conclusion

Publishing a Power BI report and embedding it into a website is one of the most impactful skills you can add to your data toolkit. It transforms a static analysis into a live, interactive experience that non-technical stakeholders can explore on their own — no Power BI license, no downloads, no fuss.

The workflow is simple once you've done it once:
Build in Desktop → Publish to Service → Generate embed code → Paste into your site.

As you grow in Power BI, explore row-level security, scheduled refresh, and the Power BI REST API for programmatic embedding — but the fundamentals you've just learned will carry you through most real-world scenarios.

If you found this helpful, follow me for more data science content covering Power BI, Excel, SQL, and hands-on projects from the African data science community.

Understanding Data Modeling in Power BI

RaggedGent-io — Sun, 29 Mar 2026 18:11:07 +0000

Joins, Relationships & Schemas

A comprehensive technical guide for data professionals covering SQL Joins, Power BI Relationships, Star & Snowflake Schemas, Fact & Dimension Tables

What is Data Modeling?
SQL Joins The Foundation
Power BI Relationships
Joins vs Relationships Key Differences
Fact Tables vs Dimension Tables
Schema Designs Star, Snowflake & Flat Table
Role-Playing Dimensions
Common Modeling Issues & How to Fix Them
Where to Do Everything in Power BI (Quick Reference)
Summary & Key Takeaways

1. What is Data Modeling?

Data modeling is the process of organizing and structuring data so that it can be stored, retrieved, and analyzed efficiently. In the context of Power BI, a data model defines how tables relate to one another, what calculations exist, and how data flows from source to visual.

Think of a data model as the backbone of your Power BI report. Without a well-designed model, even the cleanest data will produce slow, confusing, or incorrect reports.

Why Data Modeling Matters

A good data model makes DAX formulas simpler, reports faster, and slicers more predictable. A bad model leads to many-to-many ambiguities, circular dependencies, and performance nightmares. Modeling is the single most important skill for any Power BI developer.

Core Goals of Data Modeling

Eliminate data redundancy store each fact once
Enable fast query performance via column-store compression
Support correct aggregations and filter propagation
Make DAX measures simple and reusable
Keep the model easy to understand and maintain

2. SQL Joins The Foundation

Before understanding Power BI relationships, you need to understand SQL joins. Joins are how you combine rows from two or more tables based on a related column. In Power BI, joins are mainly applied in Power Query (M language) when loading and transforming data.

How to Read the Diagrams Below

Each diagram below shows two circles representing two tables. Shaded regions indicate which rows are returned by that join type.

[ Table A (Left) ]  ∩  [ Table B (Right) ]
      ↑                        ↑
  Left-only            Right-only
  rows                 rows
          ↑
      Matching
      rows (intersection)

2.1 INNER JOIN Matching Rows Only

[ Table A ]  ██████  [ Table B ]
              ↑
         Only matching rows returned

An INNER JOIN returns only the rows where the join key exists in both tables. Rows that do not have a match on the other side are dropped entirely.

Real-life example:
You have an Orders table and a Customers table. An INNER JOIN on CustomerID will only return orders that belong to customers who exist in the Customers table any orphaned orders are excluded.

-- SQL
SELECT o.OrderID, c.CustomerName, o.Amount
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

// Power Query (M)
= Table.NestedJoin(Orders, "CustomerID", Customers, "CustomerID", "Joined", JoinKind.Inner)

2.2 LEFT JOIN (LEFT OUTER) All Left, Matched Right

[ ████ Table A ████ ]  ███  [ Table B ]
        ↑                       ↑
   All left rows        NULLs where no match

A LEFT JOIN returns all rows from the left table plus matching rows from the right. Where there is no match on the right side, the result is NULL for right-side columns.

Real-life example:
You want a list of all products, including those that have never been ordered. A LEFT JOIN from Products to OrderItems returns every product; those with no orders show NULL in the order columns.

-- SQL
SELECT p.ProductName, oi.Quantity
FROM Products p
LEFT JOIN OrderItems oi ON p.ProductID = oi.ProductID;

// Power Query (M)
= Table.NestedJoin(Products, "ProductID", OrderItems, "ProductID", "Joined", JoinKind.LeftOuter)

2.3 RIGHT JOIN (RIGHT OUTER) All Right, Matched Left

[ Table A ]  ███  [ ████ Table B ████ ]
                          ↑
                   All right rows returned
                   NULLs where no match on left

A RIGHT JOIN is the mirror of a LEFT JOIN. All rows from the right table are returned, and NULLs fill left-side columns where no match exists.

Real-life example:
You are auditing a Suppliers table against a PurchaseOrders table. A RIGHT JOIN ensures all suppliers appear, even those with no purchase orders yet.

-- SQL
SELECT po.POID, s.SupplierName
FROM PurchaseOrders po
RIGHT JOIN Suppliers s ON po.SupplierID = s.SupplierID;

// Power Query (M)
= Table.NestedJoin(PurchaseOrders, "SupplierID", Suppliers, "SupplierID", "Joined", JoinKind.RightOuter)

2.4 FULL OUTER JOIN All Rows from Both Tables

[ ████ Table A ████ ]  ███  [ ████ Table B ████ ]
         ↑                           ↑
  All rows from both sides; NULLs where no match on either side

A FULL OUTER JOIN returns all rows from both tables. Rows that match appear once with data from both sides. Unmatched rows appear with NULLs on the side where there is no match.

Real-life example:
Comparing two budget versions (Budget v1 and Budget v2). Some line items only exist in one version. A FULL OUTER JOIN returns every line from both, with NULLs indicating which version is missing the item.

-- SQL
SELECT COALESCE(v1.LineItem, v2.LineItem) AS LineItem,
       v1.Amount AS Budget_v1,
       v2.Amount AS Budget_v2
FROM BudgetV1 v1
FULL OUTER JOIN BudgetV2 v2 ON v1.LineItem = v2.LineItem;

// Power Query (M)
= Table.NestedJoin(BudgetV1, "LineItem", BudgetV2, "LineItem", "Joined", JoinKind.FullOuter)

2.5 LEFT ANTI JOIN Left Rows with NO Match in Right

[ ████ Table A ████ ]  ░░░  [ Table B ]
         ↑
  Only left rows that have NO match in right

A LEFT ANTI JOIN returns only those rows from the left table that have no corresponding match in the right table. This is used to find orphaned or unmatched records.

Real-life example:
Find all customers who have never placed an order. LEFT ANTI JOIN from Customers to Orders returns only customers absent from the Orders table.

-- SQL
SELECT c.CustomerID, c.CustomerName
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

// Power Query (M)
= Table.NestedJoin(Customers, "CustomerID", Orders, "CustomerID", "Joined", JoinKind.LeftAnti)

2.6 RIGHT ANTI JOIN Right Rows with NO Match in Left

[ Table A ]  ░░░  [ ████ Table B ████ ]
                           ↑
          Only right rows that have NO match in left

A RIGHT ANTI JOIN returns only rows from the right table that have no match in the left. It is the reverse of LEFT ANTI.

Real-life example:
You receive a new employee roster (right table) and want to find new joiners not yet in the HR system (left table). A RIGHT ANTI JOIN reveals those new employees.

-- SQL
SELECT r.EmployeeID, r.Name
FROM HRSystem h
RIGHT JOIN NewRoster r ON h.EmployeeID = r.EmployeeID
WHERE h.EmployeeID IS NULL;

// Power Query (M)
= Table.NestedJoin(HRSystem, "EmployeeID", NewRoster, "EmployeeID", "Joined", JoinKind.RightAnti)

2.7 Join Types at a Glance

Join Type	Returns	Power Query `JoinKind`	Common Use Case
INNER	Matched rows only	`JoinKind.Inner`	Clean transactional reporting
LEFT OUTER	All left + matched right	`JoinKind.LeftOuter`	Include all products/customers
RIGHT OUTER	All right + matched left	`JoinKind.RightOuter`	Supplier/reference auditing
FULL OUTER	All rows from both	`JoinKind.FullOuter`	Reconciliation & comparison
LEFT ANTI	Left with NO right match	`JoinKind.LeftAnti`	Find inactive customers
RIGHT ANTI	Right with NO left match	`JoinKind.RightAnti`	Find new unregistered items

2.8 Step-by-Step: Applying Joins in Power Query

Where to find this in Power BI:
Home tab → Transform Data opens the Power Query Editor.
Inside: Home ribbon → Merge Queries.

Open Power BI Desktop and load your data sources (e.g., CSV files, SQL tables).
Click Home → Transform Data to open Power Query Editor.
Select the left (primary) query in the left panel.
Click Home → Merge Queries → Merge Queries as New.
In the Merge dialog, select the right table from the dropdown.
Click the join key column in the top table preview, then click the matching column in the bottom preview. Both should highlight in blue.
Choose your Join Kind from the dropdown (Inner, Left Outer, Right Outer, Full Outer, Left Anti, Right Anti).
Click OK. A new column appears containing the nested right table.
Click the expand icon (double arrow ↔) on the new column to select which columns to bring in.
Click Close & Apply to load the merged table into your model.

3. Power BI Relationships

Relationships in Power BI are logical connections between tables defined in the Model View. Unlike SQL joins which physically combine rows during data load relationships are applied at query time when a visual or DAX measure requests filtered or aggregated data.

Key Distinction: The data is not merged at storage time. Power BI uses the relationship to propagate filters across tables dynamically when a user interacts with a report.

3.1 Cardinality How Many Rows Match?

Cardinality describes the numeric relationship between the rows of two tables. Power BI supports four cardinality types:

Type	Definition	Example	Best Practice
One-to-Many (1:M)	One row in Table A matches many rows in Table B. Most common type.	1 Customer → many Orders	Always preferred; use for Dimension → Fact
Many-to-One (M:1)	Identical to 1:M, viewed from the other table's perspective.	Many Orders → 1 Customer	Same as 1:M, just direction flipped
One-to-One (1:1)	Each row in Table A matches exactly one row in Table B.	Employee ↔ Employee Details	Merge into one table when possible
Many-to-Many (M:M)	Multiple rows on both sides can match.	Students ↔ Courses	Use a bridge table; avoid direct M:M

Many-to-Many Warning

Direct Many-to-Many relationships can cause double-counting and unexpected filter results. Always try to introduce a bridge (junction) table to convert M:M into two 1:M relationships.

3.2 Active vs Inactive Relationships

Between any two tables, Power BI allows only ONE active relationship at a time.

Active relationship shown as a solid line in Model View. Automatically propagates filters when you reference either table.
Inactive relationship shown as a dashed line. Does not automatically propagate filters. You must explicitly activate it in DAX using USERELATIONSHIP().

Real-life example Date table with multiple date fields:

A Sales table has three date columns: OrderDate, ShipDate, and DeliveryDate. You create a Date dimension table and set up three relationships but only one can be active (usually OrderDate). The other two remain inactive.

-- Use inactive ShipDate relationship in a measure
Sales by Ship Date =
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], DateTable[Date])
)

3.3 Step-by-Step: Creating Relationships

Method 1 Drag & Drop in Model View

Where: Click the Model View icon on the left sidebar (three connected rectangles).

Click the Model View icon on the left sidebar.
Locate the two tables you want to relate (e.g., Orders and Customers).
Click and drag from CustomerID in Orders to CustomerID in Customers. A line appears.
Double-click the relationship line to open its properties.
Verify: Cardinality, Cross Filter Direction, and whether the relationship is Active.
Click OK to save.

Method 2 Manage Relationships Dialog

Where: Home tab → Manage Relationships (in the Relationships group).

Go to Home → Manage Relationships.
Click New to manually create a relationship.
Select Table 1 and its column, then Table 2 and its column.
Set Cardinality and Cross Filter Direction.
Check or uncheck Make This Relationship Active.
Click OK → Close.

3.4 Cross-Filter Direction

Cross-filter direction controls which way filters flow across a relationship.

Direction	Behaviour	When to Use
Single (→)	Filters flow from the '1' side to the 'Many' side only. Standard and safe.	Default for 1:M relationships. Always try this first.
Both (↔)	Filters flow in both directions. Can cause ambiguous filter paths.	Use only when M:M is unavoidable, or for role-playing dimensions. Use sparingly.

Best Practice: Prefer Single Direction

Bidirectional filtering can cause performance issues and unexpected results in complex models. Use CROSSFILTER() in DAX instead of enabling Both on the relationship when possible.

4. Joins vs Relationships Key Differences

Aspect	SQL Join / Power Query Join	Power BI Relationship
When applied	At data load / query time in Power Query	At visual query time (lazy evaluation)
Result	Physically merges rows into one table	Virtually links tables; no new table created
Storage	Stored as a combined dataset in the model	Tables stay separate; relationship is metadata
Flexibility	One result per join query	Filters propagate dynamically on interaction
Performance	Larger tables if many columns joined	Smaller tables; engine handles filtering efficiently
DAX support	Not directly accessible in DAX	Fully accessible via `RELATED()`, `USERELATIONSHIP()`
Best for	Data cleansing, flattening lookups, anti-joins	Star schema models, slicers, and dynamic measures

Rule of Thumb

Use Power Query joins to clean and shape data before loading. Use Power BI relationships to enable cross-table filtering and DAX calculations after loading. The ideal model has thin, clean tables connected by relationships not one giant flat table.

5. Fact Tables vs Dimension Tables

A well-designed Power BI model separates data into two categories: Fact tables (what happened) and Dimension tables (the context of what happened).

Feature	Fact Table	Dimension Table
Contains	Measurable events: sales, transactions, clicks, logins	Descriptive context: product names, customer info, dates
Grain	One row per event (e.g., one row per order line)	One row per unique entity (e.g., one row per product)
Key type	Foreign keys linking to dimension tables	Primary key (surrogate or natural)
Row count	Millions to billions of rows	Thousands to hundreds of thousands of rows
Column count	Few columns (keys + measures)	Many columns (attributes for filtering/grouping)
Aggregation	SUM, COUNT, AVERAGE on numeric columns	Not aggregated; used for slicing and grouping
Example	Sales, Inventory Movements, Web Sessions	Date, Customer, Product, Store, Geography

Example E-Commerce Model:

Fact_Sales contains:

DateKey (FK) | CustomerKey (FK) | ProductKey (FK) | StoreKey (FK) | Quantity | Revenue | DiscountAmount

Dim_Customer contains:

CustomerKey (PK) | CustomerName | Email | City | Country | Segment

6. Schema Designs Star, Snowflake & Flat Table

6.1 Star Schema

The Star Schema is the recommended structure for Power BI models. It places a single Fact table at the center, surrounded by denormalized Dimension tables that connect to it directly via 1:M relationships.

                    ┌─────────────────┐
                    │   Dim_Customer  │
                    │─────────────────│
                    │  CustomerKey  │
                    │  CustomerName   │
                    │  City           │
                    │  Segment        │
                    └────────┬────────┘
                             │
          ┌──────────────────┼───────────────────┐
          │                  │                   │
┌─────────┴──────┐  ┌────────▼────────┐  ┌──────┴──────────┐
│   Dim_Store    │  │   Fact_Sales  │  │   Dim_Product   │
│────────────────│  │─────────────────│  │─────────────────│
│  StoreKey    │  │  DateKey      │  │  ProductKey   │
│  StoreName     │  │  CustomerKey  │  │  ProductName    │
│  Region        │  │  ProductKey   │  │  Category       │
│  Country       │  │  StoreKey     │  │  Brand          │
└────────────────┘  │  Quantity       │  └─────────────────┘
                    │  Revenue        │
                    │  Discount       │
                    └────────┬────────┘
                             │
                    ┌────────┴────────┐
                    │    Dim_Date     │
                    │─────────────────│
                    │  DateKey      │
                    │  Date           │
                    │  Month          │
                    │  Quarter        │
                    └─────────────────┘

Key characteristics:

One central Fact table surrounded by denormalized Dimensions
All dimension attributes stored in a single dimension table (no sub-dimensions)
Simpler DAX fewer RELATED() hops needed
Faster query performance due to fewer joins
Best choice for most Power BI projects

Star Schema Use Cases

Retail sales analysis, financial reporting, HR headcount dashboards, e-commerce performance tracking any scenario where the model needs to be fast, readable, and easy to maintain.

6.2 Snowflake Schema

A Snowflake Schema normalizes Dimension tables by splitting them into sub-dimensions. Instead of storing City, Region, and Country all in Dim_Customer, you extract them into a separate Dim_Geography table.

┌──────────────────┐        ┌─────────────────┐        ┌─────────────────────┐
│   Dim_Category   │        │   Dim_Product   │        │     Fact_Sales     │
│──────────────────│        │─────────────────│        │─────────────────────│
│  CategoryKey   │───────▶│  ProductKey   │───────▶│  DateKey          │
│  CategoryName    │        │  ProductName    │        │  ProductKey        │
└──────────────────┘        │  CategoryKey  │        │  GeoKey            │
                            └─────────────────┘        │  Revenue             │
                                                        └──────────┬──────────┘
┌──────────────────┐        ┌─────────────────┐                   │
│   Dim_Country    │        │  Dim_Geography  │        ┌──────────▼──────────┐
│──────────────────│        │─────────────────│        │      Dim_Date       │
│  CountryKey    │───────▶│  GeoKey       │───────▶│─────────────────────│
│  CountryName     │        │  City           │        │  DateKey          │
└──────────────────┘        │  CountryKey   │        │  Date               │
                            └─────────────────┘        │  Month / Quarter    │
                                                        └─────────────────────┘

Star Schema vs Snowflake Schema:

	Star Schema	Snowflake Schema
Redundancy	Some (country name repeated)	Minimal (each name stored once)
Relationships	Fewer	More (chained lookups)
DAX complexity	Low	Higher (more `RELATED()` hops)
Query speed	Faster	Slightly slower
Power BI fit	Preferred	Use with care

Power BI Best Practice on Snowflake

If you receive data in snowflake form (e.g., from a SQL data warehouse), consider flattening the sub-dimensions in Power Query before loading to create a Star Schema. This gives you the best of both worlds: a normalized source with a denormalized model.

6.3 Flat Table / DLAT Schema

A Flat Table (sometimes called a DLAT Denormalized Large Aggregation Table, or simply a wide table) collapses all Fact and Dimension data into a single massive table. Every row contains every attribute.

Flat_Sales_All (Single Wide Table)
─────────────────────────────────────────────────────────────────────────────────
OrderID | OrderDate | CustomerName | CustomerCity | Country | ProductName |
Category | Brand | StoreRegion | Quantity | Revenue | Discount | ...
─────────────────────────────────────────────────────────────────────────────────
1001    | 2024-01-05| Alice Nguyen | Nairobi      | Kenya   | Vacuum Cleaner |
         Home & Kitchen | Philips | East Africa | 1 | 3750 | 0.12 | ...

Flat Table use cases:

Simple one-table imports for quick exploration or prototyping
Data coming from a flat CSV export with no normalization
Very small datasets where model complexity is not justified

Flat Table problems:

Massive data redundancy (every row repeats CustomerName, ProductCategory, etc.)
Large file sizes reduce performance in Power BI's in-memory engine
Difficult to maintain changing a product category means updating thousands of rows
DAX becomes complex and error-prone with no clean dimension context

Verdict on Flat Tables

Avoid flat tables in production Power BI models. They are acceptable for quick prototypes or tiny datasets. For anything beyond a few thousand rows or a few tables, invest in a Star Schema.

6.4 Schema Comparison Summary

Feature	Star Schema	Snowflake Schema	Flat Table
Normalization	Partially denormalized	Fully normalized	Not normalized
Table count	Few (1 Fact + N Dims)	Many (chained dims)	One giant table
Query speed	Fast	Moderate	Slow (large)
DAX complexity	Low simple `RELATED()`	Higher more hops	Very high no clean dims
Storage size	Moderate	Smallest	Largest
Power BI fit	Best choice	Use with care	Avoid in production
Best used for	Dashboards, KPIs, analytics	Large DWH with many categories	Quick prototypes only

7. Role-Playing Dimensions

A Role-Playing Dimension is a single dimension table that is used multiple times in the same model each time in a different 'role' or context.

The classic example is a Date dimension. A single Dim_Date table might relate to a Fact_Sales table three times:

Once as OrderDate
Once as ShipDate
Once as DeliveryDate

Because Power BI allows only one active relationship between any two tables, you have two options:

Option 1: Duplicate the Dimension Table

Create three separate copies of Dim_Date:

Dim_OrderDate  ──(active)──▶  Fact_Sales[OrderDate]
Dim_ShipDate   ──(active)──▶  Fact_Sales[ShipDate]
Dim_DeliveryDate ─(active)──▶  Fact_Sales[DeliveryDate]

Simple to understand
Increases model size three copies of the same date table

Option 2: One Dim + Inactive Relationships + USERELATIONSHIP()

Dim_Date ──(active)────────▶  Fact_Sales[OrderDate]
         ──(inactive - - - )──▶  Fact_Sales[ShipDate]
         ──(inactive - - - )──▶  Fact_Sales[DeliveryDate]

Lean model one date table
Activated per measure using DAX

-- Active relationship handles OrderDate automatically:
Total Revenue by Order Date = SUM(Fact_Sales[Revenue])

-- Measure using inactive ShipDate relationship:
Revenue by Ship Date =
CALCULATE(
    SUM(Fact_Sales[Revenue]),
    USERELATIONSHIP(Fact_Sales[ShipDate], Dim_Date[Date])
)

-- Measure using inactive DeliveryDate relationship:
Revenue by Delivery Date =
CALCULATE(
    SUM(Fact_Sales[Revenue]),
    USERELATIONSHIP(Fact_Sales[DeliveryDate], Dim_Date[Date])
)

Step-by-Step: Setting Up Role-Playing Dimensions

Where: Model View in Power BI Desktop.

In Model View, drag Sales[ShipDate] → Dim_Date[Date] to create the relationship.
Double-click the new relationship line.
Uncheck "Make this relationship active" → Click OK. The line becomes dashed.
Repeat for Sales[DeliveryDate] → Dim_Date[Date].
Write your DAX measures using USERELATIONSHIP() for each inactive relationship.
Add a Date slicer based on Dim_Date[Date] the active (OrderDate) relationship drives it by default. Use each measure to override per visual.

8. Common Modeling Issues & How to Fix Them

Issue	Cause	Fix
Many-to-Many ambiguity	Two tables with non-unique keys on both sides create a direct M:M relationship	Introduce a bridge table with unique combinations of both keys
Circular dependencies	Table A → Table B → Table C → Table A forms a loop	Restructure the model; remove one of the relationships causing the loop
Bidirectional filter confusion	Both-direction cross-filtering causes unexpected results in slicers	Switch to Single direction; use `CROSSFILTER()` in DAX when needed
Double-counting in measures	Incorrect M:M or missing relationship causes the engine to repeat rows	Fix the relationship cardinality; verify the grain of the Fact table
Inactive relationship ignored	Developer forgets the relationship is inactive; measure uses wrong dates	Use `USERELATIONSHIP()` explicitly in every affected DAX measure
Slow report performance	Flat table model; too many calculated columns; bidirectional filters	Restructure to Star Schema; move logic to DAX measures; use single-direction filters
Blanks in relationships	NULL foreign keys in the Fact table have no matching row in the Dimension	Add an 'Unknown' row (key = -1 or 0) to dimension tables to capture orphaned facts
Date table not marked as Date	Time intelligence functions (YTD, MTD) fail or behave incorrectly	Right-click `Dim_Date` → Mark as Date Table → select Date column

9. Where to Do Everything in Power BI (Quick Reference)

Task	Location in Power BI	Steps
Apply SQL-style join	Power Query Editor	`Home → Transform Data → Home → Merge Queries`
Create relationship	Model View	Drag column A to column B in Model View
Edit relationship	Model View or Home tab	Double-click line in Model View OR `Home → Manage Relationships`
Set inactive relationship	Model View	Double-click line → uncheck "Make this relationship active"
Use inactive relationship	DAX Measure Editor	`CALCULATE([Measure], USERELATIONSHIP(Table[Col1], Dim[Col2]))`
Mark Date Table	Model View	Right-click `Dim_Date` table → Mark as date table → pick date column
Set cross-filter direction	Model View	Double-click relationship line → change Cross Filter Direction dropdown
Add bridge table	Power Query Editor	`Transform Data → New Query` → build bridge table or reference existing queries
View relationship errors	Model View	Dotted/yellow lines = ambiguous or inactive; hover for tooltip details
Check cardinality	Model View	Double-click a relationship line → inspect the Cardinality field

10. Summary & Key Takeaways

Data modeling is the difference between a Power BI report that impresses and one that confuses. Here is a concise summary of everything covered in this article:

Topic	Key Takeaway
SQL Joins	Use INNER for matched-only, LEFT/RIGHT OUTER for all rows on one side, FULL OUTER for reconciliation, ANTI joins for finding unmatched records
Relationships	Build in Model View. Always prefer 1:M, Single cross-filter direction, and active relationships
Joins vs Relationships	Joins combine data at load time in Power Query. Relationships filter data at query time in the model
Fact vs Dimension	Fact tables hold numeric events; Dimension tables hold descriptive context. Keep them separate
Star Schema	Best choice for Power BI. Central Fact + denormalized Dimensions = fast, clean, simple
Snowflake Schema	Good for storage efficiency; avoid in Power BI unless the source requires it flatten it in Power Query
Flat Table	Fine for prototypes; avoid in production causes redundancy, slow performance, and complex DAX
Role-Playing Dims	Reuse one Dimension in multiple roles via inactive relationships + `USERELATIONSHIP()` in DAX
Common Issues	Fix M:M with bridge tables, mark your Date table, avoid bidirectional filters unless necessary

Happy Modeling!

Build your Star Schema, define clean relationships, and let DAX do the rest.

Understanding Data Modeling in Power BI Joins, Relationships & Schemas Explained

How Excel is Used in Real-World Data Analysis

RaggedGent-io — Sat, 28 Mar 2026 11:39:33 +0000

How Excel Is Used in Real-World Scenarios

1. Financial Analysis and Budgeting

Finance teams rely heavily on Excel to build budget models, track expenditures, and project future performance. A typical financial model might pull monthly revenue figures and use formulas to calculate variance against targets, flag overspending, and produce a summary dashboard for management.

Example: A sales manager might set up a workbook where each sheet represents a month, with formulas linking back to a master "Year to Date" summary sheet that automatically updates as new data is entered.

2. Data Cleaning and Transformation

Before any analysis can happen, raw data usually needs to be cleaned — duplicate entries removed, inconsistent formats standardised, missing values handled. Excel provides tools for all of this.

The TRIM() function strips extra spaces from text (a surprisingly common problem when data is imported from other systems). PROPER() standardises capitalisation. SUBSTITUTE() lets you replace specific characters in bulk — useful when, say, phone numbers come in as +254-700-123456 but you need them as 0700123456.

Real-world application: A data analyst receiving a CSV export from a CRM system will often spend the first 20–30 minutes using these functions to get the data into a usable shape before any actual analysis begins.

3. Lookup and Reference — The Power of VLOOKUP and XLOOKUP

One of the most transformative moments for a new Excel user is discovering lookup functions. Instead of manually searching for matching records across two tables, you can let Excel do it automatically.

VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) searches for a value in the first column of a range and returns the corresponding value from another column.

Example:

=VLOOKUP(A2, ProductList!$A:$C, 3, FALSE)

This formula looks up the product ID in cell A2 against a product list on another sheet and returns the price (column 3). Imagine doing this manually for 5,000 rows — VLOOKUP makes it instant.

The newer XLOOKUP() function improves on VLOOKUP significantly: it searches in any direction, handles missing values more gracefully, and is generally easier to write and debug.

4. Conditional Logic with IF, AND, and OR

Real data is rarely clean or binary. You often need to classify, flag, or categorise records based on multiple conditions. This is where IF(), AND(), and OR() become indispensable.

Example — grading system:

=IF(B2>=70, "Pass", IF(B2>=50, "Borderline", "Fail"))

Example — multi-condition flag:

=IF(AND(C2="Active", D2>90), "Overdue & Active", "OK")

In a real HR dataset, you might use a combination like this to flag employees who are both past their contract renewal date and still marked as active — surfacing records that need urgent attention.

5. Aggregation with SUMIF, COUNTIF, and AVERAGEIF

Standard SUM() and COUNT() are useful, but the conditional versions unlock far more analytical power.

SUMIF(range, criteria, sum_range) adds up values that meet a condition.

Example:

=SUMIF(B:B, "Nairobi", C:C)

This sums all sales figures in column C where the region in column B is "Nairobi". No manual filtering needed — the formula does the aggregation on the fly.

COUNTIF() is equally valuable for audits: how many records are duplicated? How many invoices are still unpaid? How many entries fall within a date range?

6. PivotTables — Instant Summaries at Scale

If there is one Excel feature that separates casual users from analytical thinkers, it is the PivotTable. A PivotTable takes a flat dataset and lets you summarise, group, and cross-tabulate it in seconds — with no formulas required.

Real-world example: A retail company with 50,000 transaction rows can use a PivotTable to instantly see:

Total sales by region
Top 10 products by revenue
Month-over-month performance broken down by salesperson

What would take hours of manual work (or require SQL knowledge) becomes a matter of dragging and dropping fields.

7. Conditional Formatting for Visual Analysis

Numbers alone rarely tell the story as clearly as a well-formatted spreadsheet. Conditional formatting lets you apply colours, icons, and data bars to cells based on their values — turning a wall of numbers into something visually interpretable at a glance.

Practical use: In a project tracker, you might use a red-to-green colour scale on a "Days Remaining" column so that overdue tasks are immediately visible in red without anyone having to read each cell individually.

8. Data Validation for Controlled Input

In collaborative workbooks, ensuring that others enter data correctly is crucial. Excel's Data Validation feature lets you restrict a cell to accept only specific values — a dropdown list of regions, a number within a valid range, or a date after a certain point.

This prevents the kind of messy data entry errors (like "Nairobi" vs "nairobi" vs "NAIROBI") that create headaches downstream.

A Personal Reflection

Before I started learning Excel properly, I looked at data the way most people look at a long receipt — I could see the numbers, but I was not really reading them. Learning Excel changed that.

What shifted was not just learning the formulas. It was developing a new instinct: when I see a table of raw information now, I automatically start asking questions. What patterns are in here? What would a PivotTable reveal? Where are the outliers hiding? Which records need a COUNTIF to expose inconsistencies?

Excel taught me that data analysis is not about having the right software. It is about asking the right questions and knowing which tools to reach for. The spreadsheet is just the canvas — the thinking is the real work.

Every VLOOKUP I wrote, every nested IF I debugged, every PivotTable I dragged together brought me a step closer to seeing data not as noise, but as a story waiting to be read.

If you are just starting out with Excel, my advice is simple: find a messy real-world dataset, pick one question you want to answer about it, and start there. The formulas will follow.

Thanks for reading! If you found this useful, drop a comment below — I would love to hear how you use Excel in your own work.

Tags: #excel #dataanalysis #beginners #productivity #spreadsheets

Forem: RaggedGent-io

How to Publish a Power BI Report and Embed It in a Website

Introduction

What Is Power BI Publishing?

Prerequisites

Step 1: Create a Workspace

How to Create One

Step 2: Upload and Publish Your Report

Method A: Publish from Power BI Desktop (Recommended)

Method B: Upload via Browser

Step 3: Generate the Embed Code

Steps

Step 4: Embed the Report on a Website

Option A: Plain HTML Website

Option B: WordPress

Option C: Dev.to

Making the Embed Responsive

Managing Your Embed Codes

Key Insights and Takeaways

Quick Reference Checklist

Conclusion

Understanding Data Modeling in Power BI

Joins, Relationships & Schemas

Table of Contents

1. What is Data Modeling?

Core Goals of Data Modeling

2. SQL Joins The Foundation

How to Read the Diagrams Below

2.1 INNER JOIN Matching Rows Only

2.2 LEFT JOIN (LEFT OUTER) All Left, Matched Right

2.3 RIGHT JOIN (RIGHT OUTER) All Right, Matched Left

2.4 FULL OUTER JOIN All Rows from Both Tables

2.5 LEFT ANTI JOIN Left Rows with NO Match in Right

2.6 RIGHT ANTI JOIN Right Rows with NO Match in Left

2.7 Join Types at a Glance

2.8 Step-by-Step: Applying Joins in Power Query

3. Power BI Relationships

3.1 Cardinality How Many Rows Match?

3.2 Active vs Inactive Relationships

3.3 Step-by-Step: Creating Relationships

Method 1 Drag & Drop in Model View

Method 2 Manage Relationships Dialog

3.4 Cross-Filter Direction

4. Joins vs Relationships Key Differences

5. Fact Tables vs Dimension Tables

6. Schema Designs Star, Snowflake & Flat Table

6.1 Star Schema

6.2 Snowflake Schema

6.3 Flat Table / DLAT Schema

6.4 Schema Comparison Summary

7. Role-Playing Dimensions

Option 1: Duplicate the Dimension Table

Option 2: One Dim + Inactive Relationships + USERELATIONSHIP()

Step-by-Step: Setting Up Role-Playing Dimensions

8. Common Modeling Issues & How to Fix Them

9. Where to Do Everything in Power BI (Quick Reference)

10. Summary & Key Takeaways

How Excel is Used in Real-World Data Analysis

How Excel Is Used in Real-World Scenarios

1. Financial Analysis and Budgeting

2. Data Cleaning and Transformation

3. Lookup and Reference — The Power of VLOOKUP and XLOOKUP

4. Conditional Logic with IF, AND, and OR

5. Aggregation with SUMIF, COUNTIF, and AVERAGEIF

6. PivotTables — Instant Summaries at Scale

7. Conditional Formatting for Visual Analysis

8. Data Validation for Controlled Input

A Personal Reflection