DEV Community: jayson kibet

SQL joins Explained

jayson kibet — Thu, 16 Apr 2026 15:17:45 +0000

Introduction

SQL JOINs are one of those concepts that seem simple in theory but confusing in practice.At first,SQL joins really confused me and didn't make any sense because i had the data i needed but in different tables.Putting them together actually felt harder than I thought.

1.Inner join

This join returns only the rows that match in both tables that you want to join.
The syntax is:select column
from t1
inner join t2
on t1.column=t2.column;

2.left join

LEFT JOIN returns all rows from the left table, plus any matching rows from the right.When there's no match,the result will be recorded as NULL.The order of the table in this join matters
the syntax is:select column
from t1
left join t2
on t1.column=t2.column

3.Right join

This is actually the opposite of the left join.It selects all rows from the right and matches them on the left.

4.full outer join

A Full outer join returns all rows from both tables.when it doesn't match, the missing side shows NULL.

5.self join

This is simply a table joining itself.Often when rows reference other rows in the same table.

Note:Always remember to put the ON function after the join

Window functions and Group by function

I once knew group by is a very powerful tool and could summarize my data in seconds and get results quickly until i came across the window functions and suddenly everything became a bit difficult.They almost did the same job but in a different way.

1.Group by

This command is used to combine rows into groups and return one result as per a group.You can group your results either by gender(female or male) or by age or any way you want your data to be presented.

Note:Do not include a where clause together with a group by function perhaps you can use a having clause to be more specific with your group by clause.

Window functions.

Window functions allow you to perform calculations across a two or more rows without removing any rows

Note:a.PARTITION BY divides the data into groups using PARTITION BY and ORDER BY specifies the order of rows within each group using ORDER BY.

1.sum()

In simple terms,the sum command creates a running total.Each row adds its value to the total so far.

2.avg()

Calculates the average value within a window.

3.Row_number()

This asigns a sequential number to each row.Every row gets a unique value.

4.Rank() and Dense rank()

when two rows have the same value RANK() and DENSE_RANK() both gives the same rank but they differ in what comes next
RANK() - It skips numbers after a tie , handles ties eg.1,2,3,3,5
DENSE_RANK() - It does not skip.However,the next rank after two tied at 2 is 3 eg.1,2,3,3,4,5.It doesnt leaves gaps in ranking

Functions every beginner should know

As a data analyst,you should go beyond just analyzing data and mastering basic commands like where function,group by and order by and so on.To become extraordinary,you need some powerful functions to help you analyze data with ease.

1.Lag()

This lets you access data from the previous row without writing complex queries.It looks at the previous row's value.

2.Lead()

This is the opposite of the lag function.It looks at the next row's value

NOTE:The Lag and Lead is perfect for making comparison over time either students performance in two exams or employees salary.

3.NTILE

This divides rows into equal groups(buckets)
It's useful for percentages,quartiles and even splitting students into perfomance bands according to their exams results.

It gives priority to the 1st groups.

4.substring

It selects the number of characters in a certain column.It helps in data cleaning and formating.

It wll select only the first 3 letters.

5.Date_part

This functions extracts either the year,month or day from a date

6.UNION

Despite UNION not being a function,i feel every beginner should master it.
It combines results from two queries and it also removes duplicates as a bonus.

common mistakes to avoid

1.running a delete or update statement

Running the delete or update statement can wipe your table at a glance.Always specify the row you want to update or delete by introducing the where clause.

2.Having and where clause

The where clause filters rows before the grouping happens.
Having filters after the aggregations.
Also,where clause does not go together with group by function.

3.Null values

Null is not the same as 0(zero).
Ignoring them leads to empty results.Always use IS NULL or IS NOT NULL.Don't use the =(equal) sign

4.Joins

When you run a join query,always include the word ON.This is where you want to join your table with common columns.

conclusion

We all learn by making mistakes.Every wrong query,every error message and every unexpected result is part of the process.Fixing them is what sharpens your thinking.The goal isn’t to write perfect queries from the start but understanding the structure of the queries that give the right answer to the question.

What really is DDL and DML and their comparision

jayson kibet — Sun, 12 Apr 2026 17:23:34 +0000

Introduction

About three weeks ago,I jumped into learning SQL.I thought it's all about writing queries and getting results by running them and thats all.Yes i had a point but when i went deeper in learning SQL,I realized there’s a clear split in how SQL actually works.DDL (Data Definition Language) and DML (Data Manipulation Language).

DDL(Data Definition Language)

This is what you use to create and define your database.It deals with the structure of your table.When you run them,the table either exists or it doesn't.You can think of it as an empty house(empty rooms just walls)

DML(Data Manipulation Language)

This actually works with the data inside your table.You can think of it as the staff inside your house.

NOTE:DDL deals with the structure while DML deals with the data.

How I used CREATE, INSERT, UPDATE, and DELETE

1.Create

This command creates the table of your title choice

2.Insert

This command allows you to fill your table by inserting data into the table you created following the data type strictly.

The data type should corespond with the data you put in the table you created.

3.Update

The update command allows you to make changes in your table for instance if you made a mistake and you need to make immediate changes.

4.Delete/drop

The 'delete' command allows you to eliminate a specific rows of your choice while alter messes with the columns.

Filtering and Where

The where clause is actually one of the most important commands.Its actually my 'best friend'since I interact with it a lot.Without this you cannot execute the delete and update command since it will affect every row.

1.=(equals to)

=(equals to) only filters the data corresponding to your where clause.

It will only highlight the form 4 students.

2.>=(greater than or equals to)

This only selects the data that is above or equal the highlighted command in the where clause.

3.Like

It is done by adding a percentage before of after a character to give a hint that there is some characters but it's just not given.

subjects like computer studies will be highlighted.

4.Between

This gives the data that is in the range that you highlighted

Marks like 70s will be included.

5.In

This matches the list.After the function'In',it is then followed by brackets and data separated by a comma in between them inside the bracket

Case when

This is a condition inside a query.It's almost similar to the 'if' statement in Excel and power BI.I didn't expect myself to like this as much as i do at the moment.

What i found challenging while attempting my assignment

In a spreadsheet, you click a cell and type. In SQL,you describe exactly what you want to change and under what conditions. The approach feels rigid at first, but it is actually more precise. You cannot accidentally drag a formula into the wrong column.
Also while doing the assignment,I ran an UPDATE without a WHERE clause.Every data in the table immediately became 'Unknown'.That mistake reinforced two habits: always try to always write the WHERE clause first before anything else and always work inside a transaction when experimenting.

Conclusion

The past few weeks taught me that SQL isn't about memorizing commands.First,you set up the structure with DDL,you then work inside it with DML.Think of it like building a house from scratch.At one point I ran an UPDATE without a WHERE clause and watched every single row change at once.That really got me frustrated.Now 'WHERE' and 'CASE WHEN' don't feel like random syntax anymore,they feel like tools I actually want to use.
If you're just starting out,always remember to write your conditions before your changes.

How to publish a Power BI report and embed it in a website.

jayson kibet — Sun, 05 Apr 2026 18:11:41 +0000

Introduction

Power BI is a powerful intelligence tool that enables users to transform raw data and generate insights and interactive dashboards and reports.One of its most useful features is the ability to publish reports to the Power BI Service and embed them into websites so that your work can reach your boss or client or add into your potfolio website.
This guide walks through the full process step by step: creating a workspace, publishing a report, generating embed code, and embedding the report into a website.

Step 1: Create a Workspace in Power BI Service

Before you can share anything,you need to put it somewhere.That space is called a workspace in power BI.In simple terms,a worksapce is like your report's new home.
It's designed mostly for power BI reports,dashboards and datasets.Each workspace can have its own set of people with different permission levels.
How to create:
1.open your web browser and go to app.powerbi.com. Sign in with your Microsoft account either work or school account.
2.Once you're logged in, look at the left-hand menu. You'll see icons for Home, Browse, Workspaces. Click Workspaces.At the bottom of the workspace list, you'll see a button that says + New workspace. Click it.

A window will pop up asking for some details.
.NAME:Put something clear and simple like"Electronics Sales Dashboard"
DESCRIPTION:Write something like "Monthly sales data for the electronics team"though its optional.
ADVANCED OPTION: This is where you set who can access and what license type to use
3.Click Apply.

The Four Workspace Roles
Power BI gives you four different levels of access.
Admin-He does Everything. Delete the workspace, add or remove people, change settings, upload reports — total control
Member-Edit and share content, but cannot add other members or delete the workspace
Contributor-Upload and edit reports, but cannot manage permissions or share with new people
Viewer Can only look at reports. No editing, no uploading, no sharing

step2. Uploading and Publishing Your Report

Once your workspace is ready, it's time to get your report from Power BI Desktop into the cloud.
Publishing from Power BI Desktop

Open your finished report in Power BI Desktop. 2.Save your file (Ctrl+S) 3.Click the Publish button on the Home ribbon at the top. 4.If asked, sign in with your Microsoft account. 5.A small window will pop up showing your available workspaces. Pick the one you just created. 6.Click Select.

7.Wait a moment while Power BI uploads and checks your file. This usually takes afew seconds close to a minutes, depending on how big and complex your report is.
8.When it's done, you'll see a success message. Click the link to open your report in Power BI Service (the cloud version).

Double-Check That Everything Worked

After publishing, go back to Power BI Service and click on your workspace. You should see three things:
The Report (your actual visuals and pages)
The Dataset (your data model)
Maybe a Dashboard if you made one

Click on the report name to open it. Click through every page. Test your slicers, your drill-throughs, and any custom visuals. Sometimes things look perfect in Desktop but act weird in the cloud.

step3.Creating the embed code

Power BI gives you three different ways to embed a report. Picking the wrong one can either break things or expose private data.

Publish to Web-Anyone with the link and it's Best for Personal blogs, public portfolios and marketing demos
Secure Embed-Only people with Power BI accounts you approve Pro for each viewer and it's best for Internal company portals, private team sites
Power BI Embedded-Any app user (no Power BI account needed) Azure subscription (paid) best for Customer-facing products, SaaS apps

Option A: Publish to Web (Public)

This is the easiest method and perfect for portfolio pieces or blog posts. But be aware that anyone on the internet can access and view your report. Never use this for customer data, salaries, or anything private.
Here's how to do it:

1.Open your published report in Power BI Service.
2.In the top menu, click File → Embed report → Publish to web (public).
3.Read the warning. Seriously. It's telling you that this report will be public and search engines can index it. Don't click past this without thinking.
4.Click Create embed code.
5.Click Publish to confirm.
6.You'll see two things: a Link (direct URL) and an HTML

That weird long string in the src part is your report's unique ID.

Option B: Secure Embed (For Internal Use)

This is best and highly recomended for private and sensitive data like employee salary and sales figures.
This is how you do it:
1.Open your report in Power BI Service.
2.Click File → Embed report → Website or portal.
3.Copy the URL and embed code provided.
4.Anyone who tries to view the embedded report will have to sign in with their Microsoft account. And they only see it if you've given them permission in the workspace.

Option C:Power BI Embedded (For Commercial Products)

This one is for developers building apps that show reports to customers. Think of a SaaS product that includes analytics for thousands of none users who have Power BI licenses. This requires Azure setup, API knowledge, and a paid subscription. If you're just trying to put a report on your blog or team site, ignore this option.

Step 3:Embedding the Report on a Website

You've got your embed code. Now let's put it somewhere people can actually see it.

Method 1: Simple HTML Page

2.Replace YOUR_REPORT_ID_HERE with the actual URL from your embed code
3.save the file as dashboard.html
4.Double-click the file to open it in your browser. You should see your live report on the page.

Method 2: WordPress

1.Add a Custom HTML block to your page or post.
2.Paste your iframe code inside.
3.Publish or update the page.

Method 3: Medium or Dev.to

These platforms don't allow raw iframe codes for security reasons. Your best bet is to host the HTML page and then share the link in your story.

Troubleshooting

When the Publish button is grayed out and won't let you click it,it means you're probably not signed in. Go to File → Sign in and log into your Microsoft account.
When you get an error,You probablly don't have permission to publish to this workspace.This is either the workspace admin hasn't given you access and you should reach out to them to add you as a Member or Contributor.
When the embedded report shows a blank screen or says "Access denied."For public embeds, your Power BI admin might have disabled "Publish to web" in the tenant settings.You should ask them to turn it on or for secure embeds, make sure the viewer has been added to the workspace.
When your report shows old data,it means your dataset probably isn't refreshing automatically. Check that scheduled refresh if it is turned on and that your data source credentials haven't expired. Expired passwords is the main reason reports stops updating.

Note:

Security should be the first priority always.
Do NOT use "Publish to web" for anything with personal information, customer data, financial details, or trade secrets. Once it's public, it's public forever. Google can find it. Your competitors can find it.

Conclusion

Publishing a Power BI report takes your work from a file stuck on your computer to a live tool that anyone can access. The whole process — workspace, upload, embed code, website takes few minutes once you understand the steps.
The real skill is knowing which embedding method fits your situation and keeping your data safe. Publish your report out and let your insights do the work.

Understanding Data modeling in power bi,Joins,relationships and schemas explained.

jayson kibet — Sun, 29 Mar 2026 18:38:47 +0000

Introduction

When I first heard about Power BI, I thought it's all about making charts and dashboards. I came to realize even the most beautiful visuals can produce wrong numbers when it's not well organised and most beginners start questioning themselves why their totals are not adding up correctly.

Data Modeling

Data modeling is organising and structuring your data so that it can be efficient and accurate for Power BI to easily analyse it.
In simple terms, modeling is what separates a report that looks good from what is actually correct. You can think of it like a building plan. You cannot skip the plan and expect the building to go well. If you skip data modeling, you will get inaccurate report.

Joins

Joins in Power BI combines data from multiple tables based on related columns. It uses relationships and DAX for joining data unlike SQL where you write join statements.

1. Inner Join

An inner join returns only the rows where both tables have a matching value and leaves out the ones that don't have a match. This join gives you a clean and trimmed result with no missing pieces on either side.

2. Outer Join

A full outer join returns all rows from both tables. Where there is a match, the columns are filled in from both sides. Where there is no match on either side, you get nulls.
You can only use it when you need a complete picture of both datasets.

3. Left Join

A left join returns all rows from the first table and the matching rows from the second table. If a row in the left table has no match in the right table, the right-side columns come back as null. It acts as 'don't leave anyone behind.'

4. Right Join

A right join is the exact opposite of the left join. It returns all rows from the right table and the matching rows from the left table. Unmatched rows on the left side come back as null.

5. Right Anti Join

This join keeps only the rows from the right table that don't match anything on the left. Commonly used to find new data on the second table that don't match with the main dataset.

6. Left Anti Join

A left anti join returns only the rows from the left table that have no matching rows in the right table. Used to find the 'what's missing.'

Power BI Relationships

Power BI understands how tables get connected.When you connect two tables in Power BI, that connection is called a relationship.

1. Cardinality

Cardinality describes how many rows on each table can merge. It helps you understand the structure of your data and how to join the tables perfectly.

a. 1:1

This matches one-to-one whereby each row in table A matches exactly one row in table B.

b. 1:M

In this case, one row in table A matches many rows in table B. They are most efficient and predictable in Power BI and I recommend to always aim for 1:M.

c. M:M

Multiple rows in table A can match multiple rows in table B.
This relationship can cause unexpected filter behaviour, double counting, and you can resolve it by introducing a bridge table.

d. M:1

This relationship, many rows in table A matches to 1 row in table B.

Active vs Inactive

Tables can be connected in different ways when building data models.
An active relationship is always the default and always on between two tables and appears as a solid line in model view. Every DAX and every visual in your report uses the active relationship automatically.
Inactive relationship is a stand-by connection ignored by default. You can create it only when you need a second or third path between two tables.

Schemas

Schemas simply is the structure of your data model. It shows how tables are arranged and connected to each other. You can think of it like a map to your data.

1. Star Schemas

This is the recommended standard for Power BI.
It takes one flat table and splits it into separate, clean tables, one fact table in the middle surrounded by dimension tables hence resembling a star.

2. Snowflake Schema

This is basically like a star schema where some of the dimension tables are broken down further into smaller tables.

Conclusion

I used to create dashboards that had weird numbers and calculations that I couldn't even explain. Data modeling made Power BI make sense to me and once the model was right, everything started to fall in place. Once you understand joins, relationships and schemas, your reports start telling accurate story and not just creating beautiful visuals.

How Excel is Used in Real-World Data Analysis

jayson kibet — Wed, 25 Mar 2026 14:02:05 +0000

INTRODUCTION

I used to think Excel is a boring spreadsheet full of rows and columns only used by accountants and nobody cared about it. Everything changed the moment I jumped into data analytics. I came to realize that Excel really is a spreadsheet software developed by Microsoft that allows you to make calculations and analyse data. It is also the most powerful tool in data analytics. Excel is used in almost every industry you can think of.

Real World Use Cases of Excel

1. Education Institutions

Excel is used to monitor the trend in performance of students in schools by calculating their score, the mean mark and highlighting the top performers.

2. Business

Excel is used by most companies and firms to track down their sales, profits and losses. It can also show their product's performance in various tables and charts.

3. Healthcare

Health institutions use Excel to monitor their patients and keep their track records. It can also calculate the number of patients and medicines distributed within the health facility.

4. E-Commerce

A major example is Jumia. It uses Excel to calculate discounts distributed among different products and identify the most sold products.

Key Formulas in Excel

Excel is run by different formulas.

1. IF Statement

One of the most effective formulas is the IF statement. This gives a specific command. For example:
=IF(A2<30,"young","old")
This statement tells us that in cell A2, if its value is less than 30, then it should be classified as young and the rest should be old.

2. SUM Statement

The SUM statement adds up the highlighted column. For example:
=SUM(A2:A20)
This command sums up the values from the second row up to the twentieth row.

3. VLOOKUP

VLOOKUP searches for a value in one column and returns a matching value from another column: =VLOOKUP(A2,product_table,2,FALSE)
This formula searches for the value in cell A2 in the first column of the table and returns the corresponding value from the second column.

Data Cleaning

Before analysing data, you ought to clean it for easier analysis. One of the major ways of cleaning is by removing duplicates. You can also replace some values by simply pressing Ctrl+H. This shortcut allows you to find a value and replace it.
Another common formula is TRIM.
This special command eliminates unnecessary spaces before, after and between words inside the cells. Example:
=TRIM(A2)
It eliminates unnecessary spaces in cell A2.

Sorting and Conditional Formatting

Sorting allows you to arrange the selected column in either ascending or descending order, whereas conditional formatting allows you to change the colour of the highlighted cell depending on what you want to highlight. It makes it easier for you to scan and spot patterns in large data instead of going through every row.

Data Visualization

Excel goes beyond calculations and writing formulas.

1. Pivot Tables

Pivot tables in Excel summarise data in seconds without writing formulas and making calculations, simply by dragging and dropping the columns you want to analyse.

2. Charts and Graphs

Excel allows you to present your data in the form of graphs and charts for easy understanding. You can also use a pie chart to show how various parts make a whole.

Conclusion

At first, I underestimated Excel, but learning it has completely changed how I see data. It is now one of the first tools I would recommend to anyone starting in data analytics.