DEV Community: joseph mwangi

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

joseph mwangi — Tue, 07 Apr 2026 13:44:46 +0000

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!

Understanding Data Modelling in Power BI: Joins, Relationships, and Schemas Explained

joseph mwangi — Mon, 30 Mar 2026 17:15:50 +0000

Introduction

When beginners start using Power BI, most focus on visuals like charts and reports. However, a data professional knows that great dashboards are built on great data models.

You should note that Data modelling is the foundation that determines whether your reports are fast, accurate, and scalable, or are they confusing and unreliable.

In this article, we will learn about data modelling from the beginning in a way that is easy to understand. We will be covering:

What SQL is and why joins exist.
All SQL joins with real-life examples and syntax.
Power BI relationships and how they work.
The difference between joins and relationships.
Fact versus dimension tables.
Data modelling schemas.
Role-playing dimensions and common modelling problems.
Create models inside Power BI step by step.

What is Data Modelling?

Data modelling is the process of organising data tables so they can work together efficiently for analysis and reporting.

Instead of storing everything in one massive table, we structure data into connected tables using keys and relationships.

Think of data modelling as if organising a library. You have books which are like facts and categories which are like dimensions, and the library system is like the relationships connecting them.

From this analogy, we can conclude that the model reduces duplication, improves performance, and makes analysis easier

What is SQL?

SQL (Structured Query Language) is a language used to communicate with databases.

It allows us to:

Retrieve data.
Combine tables.
Filter records.
Analyse datasets.

One of SQL’s powerful features is JOINS.

What are Joins?

A JOIN combines rows from two tables based on a related column, an ID. For example, we have a customers table and a sales table. We join them to know which customer made a purchase, or maybe which sale was made in what store.

SQL Joins Explained

1. INNER JOIN

What it does

Returns only matching records in both tables.

INNER JOIN Venn Diagram

Real-life Example

Show customers who actually placed orders.

SQL Syntax applied.

SELECT *
c.id,
c.first_name,
o.order_id,
o.sales
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;

Results of merged tables after executing INNER JOIN.

2. LEFT JOIN (LEFT OUTER JOIN)

What it does

Returns ALL records from the left table and matching ones from the right.

Example

Show all customers, even those without orders.
(LEFT JOIN Venn Diagram)

Syntax

SELECT *
c.id,
c.first_name,
o.order_id,
o.sales
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID;

Final Results after executing (LEFT JOINT)

3. RIGHT JOIN

What it does

Returns all records from the right table and matches from the left.

Example

Show all orders, including those missing customer details.

RIGHT JOIN Venn Diagram

Syntax

SELECT *
c.id,
c.first_name,
o.orde_id,
order.sales
FROM Customers c
RIGHT JOIN Orders o
ON c.CustomerID = o.CustomerID;

4. FULL OUTER JOIN

What it does

Returns all records from both tables, whether matching or not.

Example

Audit missing data between systems.

FULL OUTER JOIN Venn Diagram

Syntax

SELECT *
c.id,
c.first_name,
o.order_id,
o.sales
FROM Customers c
FULL OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID;

Merged tables after executing FULL OUTER JOIN

5. LEFT ANTI JOIN

What it does

Returns records from the left table that have no match in the right table.

Example

Customers who have never placed orders.

LEFT ANTI JOIN Venn Diagram

Syntax

SELECT *
c.id,
c.first_name,
o.id,
o.sales
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

Results after executing LEFT ANTI JOIN

6. RIGHT ANTI JOIN

What it does

Returns records from the right table without matches in the left.

Example

Orders with missing customers.
(RIGHT ANTI JOIN Venn Diagram)

Syntax

SELECT*
c.id,
c.first_name,
o.id,
o.sales
FROM Customers c
RIGHT JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE c.CustomerID IS NULL;

(Results after executing RIGHT ANTI JOIN)

Creating Joins in Power BI (Power Query)

Unlike SQL, Power BI creates joins visually.

Step-by-Step

Open Power BI Desktop
Click Transform Data ➡️ Power Query Editor
Select a table
Click Home ➡️ Merge Queries
Select the second table
Choose matching columns
Select Join Type:

Left Outer
Right Outer
Full Outer
Left Anti
Right Anti

Then,

Click OK
Expand columns

Power BI Relationships

After loading data, Power BI connects tables using Relationships instead of joins.

Types of Relationships

1. One-to-Many

Most common relationship.

Example:

One customer-to-many orders

2. Many-to-Many

Occurs when both tables contain duplicates.

For example:
Students and Courses.

3. One-to-One (1:1)

Rare; both tables contain unique keys.

Cardinality

Cardinality defines how tables relate either :

One-to-Many
Many-to-One
Many-to-Many
One-to-One

This you Set inside Manage Relationships

Active vs Inactive Relationships

Active relationships are used automatically in visuals.
Inactive are used via DAX (USERELATIONSHIP()).

Example:
Order Date vs Ship Date.

Cross Filter Direction

Controls filtering flow we have Single direction which is recommended and Both directions which you should use carefully.

Creating Relationships in Power BI

Method 1: Model View

Open Model View
Drag the key column between tables

Method 2: Manage Relationships

Home ➡️ Manage Relationships
New
Select tables and columns
Set cardinality and filter direction

Joins vs Relationships (Key Difference)

Joins	Relationships
Combine tables physically	Keep tables separate
Done in Power Query	Done in Model View
Creates new table	Logical connection
Happens during load	Happens during analysis

Note that Joins prepare data and Relationships enable analysis.

Fact and Dimension Tables

Fact Tables

Contain measurable events.

Examples:

Sales Amount
Quantity Sold
Transactions

Characteristics:

Large
Contains numeric values
Contains foreign keys

Dimension Tables

Provide descriptive context.(Atrributes)

Examples:

Customer
Product
Date
Location

Characteristics:

Text attributes
Smaller tables

Data Modeling Schemas

1. Star Schema ⭐️

A fact table in the center is connected to dimensions. This is recommended for business reporting dashboards. Benefits include performance and easy understanding

(Star schema diagram)

2. Snowflake Schema

Dimensions are split into related tables. This is used in normalized enterprise systems. It has its Pros which include redundancy but can include more complex queries.

(snowflake diagram)

3. Flat Table (DLAT – Denormalized Large Analytical Table)

All data is stored in one table. This is used for small datasets or quick prototypes. It's a simple setup and simple to understand, but it can result in poor scalability and slow performance.

Role-Playing Dimensions

A single dimension is used multiple times. For example, a date table can be used as the order date, ship date, and delivery date. The solution is to create multiple relationships (one active).

Common Data Modeling Issues

Circular relationships
Many-to-many misuse
Bidirectional filtering confusion
Duplicate keys
Missing dimension tables

Conclusion

Data modelling is not just a technical step but the architecture behind every reliable Power BI report.

Understanding joins teaches how data is combined.
Understanding relationships teaches how data communicates during analysis.
Understanding schemas teaches how data scales.

Master these concepts, and Power BI stops being just a visualisation tool and becomes a powerful analytical engine.
Try opening one of your old Power BI files today, switch to Model View, and redesign it using a Star Schema. You might be surprised how much faster and clearer your reports can become.
If this article helped you understand Power BI data modeling better, share it with someone who is learning, and start talking about good ways to work with data

Happy modelling!

How to Connect Slicers with scattered plot on an Excel Dashboard (Beginner-Friendly Guide That Actually Works)

joseph mwangi — Fri, 27 Mar 2026 07:50:37 +0000

Introduction

When building dashboards in Excel, one of the frustrating experiences is realizing that your slicers work perfectly with PivotTables, but your scattered charts refuse to respond correctly.
You try clicking filters expecting your dashboard to behave interactively, but your scattered plots remain unchanged. One might even conclude that Excel is malfunctioning. But no, the Excel is functioning fine.

I recently faced this challenge while building a dashboard for my Excel project. What looked like an issue turned out to reveal one of the most misunderstood concepts in Excel dashboards.

Here is the good news. Once you understand how slicers, PivotTables, and Scattered charts work together, troubleshooting becomes very easy.

In this guide, I’ll walk you step-by-step through connecting slicers to chart plots so your Excel dashboard becomes fully interactive.

⚠️The Problem

When working on a dashboard project, most beginners naturally follow these steps:

Cleaning the dataset and formatting it as an Excel Table.
Creating PivotTables for summaries.
Adding slicers for filtering.
Building a Scatter Chart for trend analysis in a sheet.
Copying PivotTables and charts into a dashboard layout.

Everything looks perfect at the moment, but until you start interacting with the dashboard, and then suddenly:

Slicers control PivotTables but not charts
Charts stop updating
Values look incorrect and may be inflated.

Frustration now begins to build, and many beginners assume Excel is malfunctioning, but Excel is working exactly as designed. The real issue is basically how Excel treats data sources.

🔑 A Key Concept to Understand.

Excel charts can use two different data sources:

PivotTable which Aggregates data (Sum, Count, Average)
Excel Table which uses original row-level data.

Please note: pivot tables summarise information, while Scatter charts require individual records.

🚫Why You Cannot Properly Plot a Scatter Chart from a PivotTable

A scatter chart plots relationships between observations, and each data point represents one row of data. However, PivotTables do not store rows but instead they:

group records,
values,
calculate summaries like sum or Average.

What this means is (X-Y) pairs needed to plot a scatter chart no longer exist; therefore, scatter plots must read table data, not PivotTable summaries.

☑️☑️Correct Architecture for Interactive Dashboards

Raw Data ➡️ Excel Table ➡️ PivotTable ➡️ Slicer
             ⬇️
       Scattered plot(source data from table)

Why does this Architecture Work?

PivotTables act as the filtering engine.
Slicers control PivotTables.
Charts visualise row‑level data.

Filtering flows through the system logically, forcing charts to perform calculations they were never designed to handle. Think of PivotTables as the control engine and charts as the display layer.

🪜🪜 Step‑by‑Step Solution(That actually works)☑️☑️

Step 1. Convert Your Data into an Excel Table

Select your dataset ➡ Go to Insert ➡ Table

Then, rename the table to something like Excel_projectTable. You can give it your preferred name; it does not really matter. Excel Tables are most preferred because they:

expand automatically when new data is added,
maintain references,
synchronize filtering behavior,
allow dashboards to scale without breaking.

Step 2. Build a PivotTable from the Table



Go to Insert ➡️ PivotTable ➡️From Table/Range

The PivotTable is not your chart’s data source, but use it for:

KPIs
summaries
aggregations
slicer control

Step 3. Add Slicers and pivot charts to your Dashboard Control Panel
Inside pivotTable sheets, you can build pivotCharts using either (Bars, column, or pie charts) for summaries

go to pivotTable ➡️ insert ➡️ PivotCharts

Select your preferred chart

For slicers:


Go to PivotTable Analyse ➡️ Insert Slicer

This may include:

Category. Electronics, Fashion, Home Appliances
Brand: Samsung, Nike, Apple
Rating: 3⭐️, 4⭐️,5⭐️Products
Region: Nairobi, Mombasa, Kisumu
Date: Monthly or Quarterly periods

Think of slicers as the control of your dashboard.

Step 4. Let's Build a Scatter Chart That Actually Works with Slicers

Step 4a. Create the Scatter Chart from the Table

Open a worksheet.
Select data from your table Excel_practiceproject.
Copy data for (X-Y) to the opened worksheet.
Highlight the two columns.
Go to Insert ➡️ Scatter Chart.
Set:

. X‑axis ➡️ influencing variable (example fuel prices )

. Y‑axis ➡️ outcome variable (example: goods cost of production)

Then copy the chart into your dashboard sheet.

Step 4b. Create a Helper PivotTable (The Secret Sauce)

Create another PivotTable from the table.

Product ➡️ Rows

X variable ➡️ Values

Y variable ➡️Values

Change aggregation from Sum to Max or Min, this helps avoid values inflation and rename it Scatter_Helper

Step 4C. Convert Pivot Layout

Go to Design → Report Layout → Show in Tabular Form

Each field now appears in its column, and this way, you can copy each column.

Step 4D. Link Helper Data to Dashboard

Go to dashboard ➡️ Right click on an empty cell(to the far right)➡️ Name the two cells e.g(FUEL PRICE-X, Cost of production-Y)

Copy columns from Scatter_Helper.


 Go to Paste Special ➡️ Paste Link.

If you see:

='Scatter_Helper'!A5 on your formula section, then you are correctly linked.

Step 4E. Change the data source of the scattered plot

Change the data source of the chart plot created from the Excel_practiceTable

Right-click on the scattered plot, then:

Go to Select data ➡️ Delete current X Values, and Y values ➡️ replace with values on the dashboard.

Step 5. Connect Slicers to the Helper PivotTable

slicer ➡️ Report Connections.

Select Scatter_Helper.

Now the magic happens.

PivotTables update
Helper PivotTable updates
Linked cells refresh
Scatter chart updates

Why This Works (The Hidden Mechanism)

The architecture:

Slicer ➡️ Helper PivotTable ➡️ Linked Cells ➡️ Scatter Chart

Slicers control PivotTables. The helper PivotTable feeds updated values into linked dashboard cells, allowing the scatter chart to refresh dynamically.

Final Result

An example of a finished dashboard(Before applying filters)

After applying a filter on the slicers

Conclusion

At the beginning, the slicers were working. The scatter chart was not responding, and it seemed like there was a problem with Excel. In reality, nothing was actually broken. The data structure just needed to be organised in a way that made sense to Excel.

Sometimes the best way to improve your analytics is not to get a new tool but to really understand how the current one works. I hope this Guide was helpful. Now, you can avoid static reports but instead create an interactive dashboard.

Happy dashboard building.

From Data collecting to Data Understanding: My First week Of Learning Excel

joseph mwangi — Thu, 26 Mar 2026 14:27:44 +0000

Familiar Tools Become New Again.

Most professionals have, in one way or another, interacted with Excel at some point in their career, but often at the surface level, and honestly i was no exception.

Having worked in the field of research,Drug discovery from natural products, Excel was always present in my workflow, primarily for collecting experimental data, storing results, sorting observations, and performing simple calculations. Then, this felt sufficient; the tool was actually doing what I needed! Or so I thought.

However, my interaction with Excel at times reached a dead end. I often found myself wondering what some of these functions actually did, and I would resort to repeated referencing and online searches. Simple operations or tasks that should have been straightforward become tedious, time-consuming, and sometimes frustrating. My one week of learning Excel at LuxDev Academy has changed my perspective entirely. I've realized it is not just a spreadsheet tool; it's also a Data analysis tool.

What is Excel?

Microsoft Excel is a software tool designed for organizing,analyzing, and visualizing data using structured rows and columns.
After the data entry process, Excel enables users in:

Managing large datasets efficiently
Cleaning and transforming datasets.
Performing mathematical and statistical calculations.
Creating charts, dashboards, and automating workflows.

Excel in Real-World Scenarios.

Almost every data-driven field relies on Excel, but I will be focusing on Excel in Drug Discovery Research. In drug discovery, researchers often begin their work by referencing already published datasets either to identify research gaps, avoid duplication of previous work, or compare experimental outcomes.
Let's explore one modern approach,Network Pharmacology. Researchers gather chemical and biological data from multiple databases, such as compound libraries, protein interaction databases, and pathway repositories. These datasets originate from different sources, and they have different formats. Excel, therefore, becomes the integrating tool where researchers:

Removes duplicates
Merges datasets
Standardises variables
Filter relevant targets
Prepares data for downstream analysis

Different Formulas in Excel and how they apply in Data Work.

During my one week of learning Excel, I began by learning Excel formulas not just as abstract functions but as a solving tool, and here are some of the few examples and how they apply in a real analytics workflow, in our case, drug discovery research.

Logical Functions-IF()

This function would apply in categorizing experimental outcomes. A good example application is classifying compounds as Active or Inactive based on inhibition values. This would allow automatic classification instead of manual labelling.

=IF(B2>50,"Active","Inactive")

Text Cleaning Fuctions-LEFT(),RIGHT(),SUBSTITUTE()

These functions are useful when dealing with datasets with inconsistent formatting. The best example is extracting numerical values from ranges or percentage strings during data cleaning. These functions will transform messy data into analysis-ready datasets.

Lookup Functions-XLOOKUP()/VLOOKUP()

How do you apply this function? A good example is when matching compound names with biological targets imported from separate databases. This is a very useful function because rarely do datasets originate from the same source.

>XLOOKUP(A2,Sheet2!A::Sheet2!B:B)

Aggregate Functions-AVERAGE(),SUM(),COUNT()

Most of the experiments are done in triplicate, so these aggregate functions are applied when summarising experiment replicates.

Pivot Tables.

Making a pivot table is one of the most transformative skill i learnt. Now, instead of manually summarising results, the pivot table allows me to compare activities across compounds, summarise experimental batches, and analyse trends instantly, interactively, and all in one Excel workbook.

Example of an Excel workflow

Interactive Dashboards

Drug research does not end with lab work and analysis of the data; researchers need to disseminate their months/year of research work to the rest of the world. This session is always allocated very limited time,15 to 20 mins max, and presenting raw spreadsheets is idealistic. The interactive dashboards skill becomes invaluable in this section. With this skill as a researcher, I can :

Summarise my findings visually
Use slicers to explore my datasets interactively
Use interactive charts to explain trends
Highlight key findings instantly (KPI section)

Example of an Excel Dashboard

Rediscovering Excel

Earlier, Excel was simply a storage tool or a digital notebook for recording values. The view has changed to a system for thinking analytically. Data collection alone does not translate to insights. You may gather very important data, but without the ability to clean, analyse, and visualize it effectively, its true value remains hidden. Looking back, I only wish I had learnt this skill earlier. Nevertheless, beginning this learning path has changed how I approach data, which is not just a value to be recorded, but a coded message waiting to be decoded!

Installing Anaconda on Intel MacBook- Via Terminal

joseph mwangi — Tue, 17 Mar 2026 11:34:43 +0000

Step-by-step installation of Anaconda on an Intel Macbook Via terminal.

When you want to install Anaconda on your intel Macbook Pc, it is recommended to do it through the terminal. This way, it is more reliable, and you have control over the installation process. It avoids the common problems caused by macOS security restrictions that sometimes block the graphical installer. I will be demonstrating a step-by-step process on how to get Anaconda on your Intel MacBook via terminal.

Step 1

Check your Mac type first. This is very crucial.

 Apple Silcon ( M1/M2/M3)

         OR
 Intel

To check this, Click On the Apple Menu > About the Mac and look for

Chip - M1/M2/M3

Processor -Intel -intel Mac

How to Check MacBook Processor

Step 2

Downloading the correct installer

The Intel Mac Version is no longer shown on the main download page Main download page. Anaconda officially stopped publishing new Intel-Mac Installers in 2025, but it is still kept on the archive page. For intel Mac book users, you should download the Legacy Intel Installer manually.

Open the archive Download page

Hit click on this official archive.

Find the Intel Installer

Scroll down and look for the latest file, preferably (2025) with a name ending with.

MacOSX-x86_64.sh

Note you shoould select -x86- (which is intel)

Not

arm64- which is Apple Silicon
Linux
Windows

Download the sh. not the .pkg

Step 3

Installing via Terminal

Open your terminal. Click command+ Space and type terminal.

Open the terminal and run:

Bash

cd ~/Downloads

Then:

Bash

bash Anaconda3-MacOOOX-x86_64.sh

Follow the prompt -type

yes or press enter

conda will now work automatically in termininal.

After hitting enter it will say

modified /User/mac/ .Zshrc

Installation is complete

Final step

Restart terminal

Close the Terminal and the reopen.

In the new terminal window type

Bash

conda --version

If you see that, you have successfully installed Anaconda in your Intel Mac- via Terminal. To open the installed Anaconda now, still on the terminal, type

Anaconda-Navigator

An ancadonda Navigator icon will pop up at the taskbar.

Click the icon, and you will see Applications on the Anaconda Navigator dashboard.

Conclusion

I hope this article was able to show you how to install Anaconda on your Intel MacBook.In the upcoming series i will be showing you how to navigate through different applications on the Anaconda Navigator to get you started in your journey as a data scientist/analyst/Engineer or a software engineer.If you have any questions, feel free to leave them in the comments area.