joseph mwangi

Posted on Apr 7

From Data Preparation to Web Deployment: A Complete Guide to Building, Publishing, and Embedding Power BI Reports

#analytics #beginners #microsoft #tutorial

Introduction to Power BI

In modern organisations, data plays a central role in decision-making. Businesses are known for generating massive volumes of transactions, operations, and customer data daily, but raw data alone has little value unless it is transformed into meaningful insights. This is where Microsoft Power BI becomes essential.

What is Power BI?

Power BI is a business intelligence and data visualization platform developed by Microsoft that enables users to:

Connect to multiple data sources
Clean and transform data.
Model relationships between datasets
Perform advanced analytics using DAX
Build interactive dashboards and reports
Share insights across organisations or publicly via the web

Power BI bridges the gap between technical data processing and business interpretation by enabling analysts to create visually rich, interactive reports without extensive programming.

Three Power BI categories:

1. Power BI Desktop – Used for data preparation, modeling, and report creation.
2. Power BI Service- Cloud platform for publishing and sharing reports.
3. Power BI Mobile –Access dashboards on mobile devices.

A step-by-step guide explaining how to prepare data, build a report, publish it to Power BI Service, and embed it into a website using IFrames.

Data Cleaning in Power BI

Before analysis begins, data must be cleaned. Poor data quality leads to misleading insights and inaccurate business decisions.

Data cleaning in Power BI is performed inside Power Query Editor.

Common Data Issues a typical dataset may have:

Missing values
Duplicate records
Incorrect data types
Inconsistent formats
Negative or unrealistic values

Key Cleaning Technique Steps.

Open Power BI Desktop ➡️ Click Transform Data ➡️ Power Query Editor opens.

Changing Data Types
Select the column(s) ➡️ click on the transform ribbon ➡️ Data type

Ensure columns have correct formats:

Date ➡️ Date type
Sales ➡️ Decimal Number
Category ➡️ Text

Incorrect data types prevent accurate calculations.

Removing Duplicates

Duplicate transactions distort totals.

Steps:

Select column(s)
Home ➡️ Remove Rows ➡️ Remove Duplicates

Handling Missing Data

Missing values are common in real datasets.

Options include:

Remove rows with null values
Replace with averages or defaults
Use business logic adjustments

Example:

If customer region is missing ➡️ replace with “Unknown”.

Handling Extreme or Incorrect Values

A key analytical question?

What happens when some rows have missing data or unrealistic values, such as negative profit?

Negative profit may indicate:

Discounts
Returns
Data entry errors
Loss-making transactions

Action Framework

Scenario	Recommended Action
Data entry error	Correct or remove
Legitimate loss	Keep for analysis
Missing critical fields	Exclude or estimate
Outliers	Investigate before removal

Data Analysis Using DAX Functions

After cleaning, analysis begins using DAX (Data Analysis Expressions).

DAX is a formula language used for:

Calculations
Aggregations
Time intelligence

Common DAX Measures

Total Sales
Total Sales = SUM(Sales[salesAmount])

Total Profit
Total Profit = SUM(Sales[Profit])

Average Profit
Average Profit = AVERAGE(Sales[Profit])

Conditional Analysis Example

Average profit for a specific region:

Average Profit Region =

CALCULATE(
AVERAGE(Sales[Profit]),
Sales[Region] = "East"
)

Handling Missing or Negative Values in DAX

You can filter unwanted values:

Valid Profit =

CALCULATE(
SUM(Sales[Profit]),
Sales[Profit] > 0
)

Click on the new measure ribbon ➡️ name the field(Example Total discount (usd))➡️ input the DAX function ➡️ click enter ➡️ a new measure appears on the right below the data section.

DAX ensures analysis excludes unrealistic data when necessary.

Data Modeling and Creating Relationships

Data modeling organizes tables into a structured system that improves performance and analytical accuracy.

Power BI commonly uses a Star Schema.

Star Schema Elements:

Fact Table ➡️ transactional data (Sales)
Dimension Tables ➡️ descriptive data (Date, Customer, Product)

Creating Relationships

Steps:
Go to Model View
Drag the foreign key from the fact table
Connect to the primary key in the dimension table
For more information on building relationships, refer to the YouTube link below.
Mastering data modelling in Power BI

Important Concept on date dimension

A Date Dimension Table is one of the most
important modelling practices.

Instead of relying only on raw dates inside the sales table, analysts create a separate calendar table.

Why Create a Date Dimension?

Without a date table:

Time intelligence functions fail.
Year-to-Date calculations break.
Filtering becomes inconsistent.

With a date table, you can analyse:

Sales by Year
Month trends
Quarterly performance
Year-over-Year growth

Step-by-Step: Creating a Date Dimension

Go to the Power Query tab.

Select From New Sources ➡️ BlankQuery.(This will launch the Power Query Editor) .
Select Advanced Editor.
Remove any code that the editor is currently storing and replace it with the following(Copy as it is)

//Create Date Dimension
(StartDate as date, EndDate as date)=>

let
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate)),
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate)),

    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),

    //Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 
    #duration(1,0,0,0)),

    //Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    //Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),

    //Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

    //Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
    each Date.WeekOfYear([Date])),

    //Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),

    //Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM")),

    //Add Day of Week Column
    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
    each Date.ToText([Date],"dddd"))

in
    DayOfWeek

After pasting, click OK/Done

Click Invoke and provide the range of dates that you would like the date table to return ➡️ click OK ➡️ Rename Date Table

Building Dashboards and Reports

After modelling, visualisation begins.

Cards ➡️ KPIs
Bar charts ➡️ category comparisons
Line charts ➡️ trends
Maps ➡️ geographic performance
Scatter charts ➡️ correlation analysis

Example of a Dashboard and Report

Publishing Power BI Reports

Once the report is complete, it must be published to the Power BI Service.

Step 1: Sign In

Open Power BI Desktop:

File ➡️ Sign In

Use an organizational account.

Step 2. Publish Report

Click Publish
Select Workspace ➡️ Confirm upload

Publish dialog box

Step3. Creating a Workspace in Power BI Service

Workspace allows collaboration among users.

Steps

Go to PowerBI.com ➡️ Click Workspaces ➡️ Select New Workspace

Enter: Name Description Click Save
New workspace contact details

Step4. Uploading and Accessing the Published Report

Inside workspace:

Navigate to Reports ➡️ Open uploaded report ➡️Verify visuals load correctly

Report opened in Power BI Service

Step 5. Generating Embed Code

Power BI allows reports to be embedded into websites using an iframe.

Steps:
Open the report in Power BI Service
File ➡️ Embed report
Select Publish to Website or portal

Note:

There is publishing to the website portal
Publishing to web (Public) options

The main difference between publishing to a website/portal and publishing to the web (public) is security and access control. Publishing to a website or portal requires user authentication to ensure only authorized individuals can view the content, whereas publishing to the web makes the content accessible to anyone with the URL.

Power BI generates HTML iframe code. So, copy the HTML iframe code.

Embed code generation window

Step6. Embedding Report on a Website

6a. On your computer, create a website folder
PowerBI_Website

6b. Open the folder in the text editor
In this case i will use VSCode

6c. Create HTML File
Inside VS Code ➡️ Right Click Explorer Panel ➡️ select New file
Can name it (index.html)

6d. Paste this full HTML Template

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Power BI Report</title>

    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 0;
            background-color: #f4f6f9;
            text-align: center;
        }

        header {
            background-color: #1f4e79;
            color: white;
            padding: 20px;
        }

        .report-container {
            margin: 30px auto;
            width: 90%;
            max-width: 1200px;
            height: 700px;
            box-shadow: 0px 4px 10px rgba(0,0,0,0.2);
        }

        iframe {
            width: 100%;
            height: 100%;
            border: none;
        }
    </style>
</head>

<body>

<header>
    <h1>Sales Analytics Dashboard</h1>
    <p>Embedded Power BI Report</p>
</header>

<div class="report-container">

    <!-- PASTE YOUR POWER BI IFRAME BELOW -->

    <!-- Example -->
    <!-- Replace this iframe -->
    <iframe 
        title="Power BI Report"
        src="PASTE_YOUR_IFRAME_SRC_HERE"
        allowfullscreen>
    </iframe>

</div>

</body>
</html>

Note that you can ask chatgbt to generate for you the html template.

6e. Paste PowerBI iframe

Note in this case:

I currently have this place holder

<iframe
    title="Power BI Report"
    src="PASTE_YOUR_IFRAME_SRC_HERE"
    allowfullscreen
>
</iframe>

I will delete it.

Then paste the Iframe copied from powerbi.com in the same location

Save work by clicking(cmd+s/ctrl+s)

Step7. Run website
Right-click on the file(index.html) ➡️ Open with live server
you should see a browser open ➡️ Microsoft Login ➡️ use the organisation signings➡️ browser opens.
PowerBI Dashboard and Reports will be loaded in the website page.

Embedded Power BI Report

Conclusion

This guide started from raw data to finally embedding Power BI report into a website using an iframe.

With these steps complete, your Power BI reports should no longer be limited to Power BI Desktop but be shared, accessed, and interacted with directly from the web.

And just like that, your analytics solution moves from development to real-world use!

DEV Community