DEV Community: Derick Kimanthi

Connecting Power BI to a SQL Database:local & cloud option

Derick Kimanthi — Fri, 20 Mar 2026 12:34:03 +0000

INTRODUCTION:

Think of this you have raw data, for example let's say: customers data, products data, sales data and inventory data. Imagine, you needing to get meaningful insights from this manually. It can be hectic and overwhelming. That's why Microsoft produced a powerful tool known as Power BI. What is Power BI? its a detailed business intelligence(BI) and data visualization tool, that helps analysts transform raw data into meaningful insights and reports. In power BI, you can create interactive dashboards, clean data and perform detailed analysis, but that is just a drop in the ocean there's much more Power BI can do. Companies and organisations can easily connect Power Bi to their databases. By doing this, companies can be able to work on live and secure data in real-time, therefore enhacing their decision-making. In this article we will be discussing one of the most important uses of Power BI: how to connect Power BI to a SQL database both locally to your machine and through a cloud option.

Connecting Power BI to a Local PostgreSQL:

Connecting Power BI locally: means you are connecting from a database that's hosted inside your computer.

First step is launching or opening Power BI Desktop. Below is how the loading screen looks like:

Selecting a data source:

After Power BI has fully loaded, click blank report you will be redirected to the report view.
Inside the report view click get data. You will see multiple sources of data.

Below is the pop up you will get after clicking get data

Since you want to connect from a database, click the database option. In this case, select postgreSQL and click connect.

Entering Database Details:

You will get a pop up that will require you to enter the database details.

Server: This is the name of the server that your database runs on. By default, its usually: localhost:5432
name: This is the name for you database that you want to connect to Power BI. Reference Image:

Data connectivity mode Now, in the above Image you can see data connectivity mode.

The modes include:

Import -> Copies data into Power BI directly. Its best for faster querying.
Direct Query ->In this mode querying occurs in real-time and directly from your database. It's best when dealing with large datasets.

The type of mode entirely depends on your data set requirements. But the most recommended for beginners is the import mode.

Authentication:

After entering the database details, click ok.
A popup will appear requiring you to enter the database credentials these will be the:

username -> postgres
password -> ******

Click OK. Following these steps you will be able to connect your database to Power BI locally.

Confirming the connection:

To confirm this: you will see the tables from the database you connected.
You can now be able to select the tables that you want to work on.

Loading the tables into Power BI:

After connecting your database, you will see a navigator window, through this window you will select the tables that you wabt to load in Power BI.
Simply you do that by checking the boxes. And clicking the Load button.

Reference Image:

Common Failures when connecting:

When selecting the data source, you might fail to see the PostgreSQL database.
To solve this:

Download the Npgsql PostgreSQL driver and install it in your computer.
Then afterwards restart Power BI.

Connecting Power BI to a cloud database:

The second option, is connecting Power BI to a cloud based database.
In this case, we will be connecting through the aiven platform.

Step 1: Getting the connection details: Create an aiven account. After creating an aiven account. Click create service

Upon creating a service, you will need to select a database that you want to connect Power BI to.
In this case, we will be using PostgreSQL for the tutorial.

Below is a list of the services aiven provides:

Thereafter, choose the plan aiven offers multiple plans ranging from free to pro.
Choose the plan the best suits your needs. After that select the region that is near you.
Then click create service.

Getting the connection information After creating a service, you will acquire the connecting credentials. Below is a sample of the credential details:

Loading the credentials in Power BI

Open Power BI desktop
Navigate to the report view by clicking blank report.
And select the get data.
For the data source: select database and select postgreSQL.
Then click connect.

After that a prompt will appear requiring you to enter the credential details.
For example

server: Enter pg-12345.aivencloud.com:12345
name: defaultdb

Downloading SSL certificate
For cloud databases, a SSL certificate is required to ensure secure and realiable connection.
To download the SSL certificate, inside aiven, under the overview you will see the connection information
From this page you can also download the CA certificate. The CA certificate verifies the identity of your database.
Simply, it's for establishing a trust between Power BI + Postgres + Aiven
In most cases it usually looks like: ca.pem
Configuring SSL Mode
Now that you have acquired a CA certificate you will need to configure the settings in Power BI.
Therefore, when Power BI asks for an encryption setting choose
SSL MODE: required
The importance of selecting this mode is that all the transactions between Power BI and PostgreSQL will be encrypted using the SSL/TLS.
Authentication
After that, Power BI will prompt you for an authentication. Whereby, you will be required to enter your credentials.
Example:
1.username:avnadmin
2.password:********
Then Click Connect

Loading Data into Power BI:

Now that we have connected Power BI to our PostgreSQL database and also loaded tables. The next thing we will explore is how relationships are created between the tables.

Creating Relationships in Power BI:

The key to creating relationships, is understanding two concepts that is the primary key and the foreign key.

1.Primary key- This is a unique identifier in a table.

By unique -> it means it cannot be replicated. They include National IDs, CustomerIDs. There can never be a single person with two national IDs e.t.c
2.Foreign key -> a foreign key its a primary key inside another table.

For example: Your database can contain customers, products, sales and inventory tables.
Inside the customers you will have a customer_id now this is a primary key, but the customer_id we still appear at the sales table this is a foreign key.
In this case the customer_id links the customers and sales table.

Automatic relationship creation:

Power BI can automatically detect relationships after you have loaded your data into it.

But your data needs:

to be correctly structured,
to have well defined columns
to have correct data types

By clicking the model view in Power BI you will see the relationships

Illustration:

Manual creation of relationships in Power BI In a case where Power BI doesnot automatically detect relationships, you can create the manually.

Still at the model view.
Drag one common column and match it with another column from another table.
A relationship dialog box will appear.
To confirm column pairing, relationship type and the cross-filter direction.

Using the manage relationships method:

-> Go to the modelling tab.

-> Select New relationship

-> Select the first table and the common column
-> Then, select the second table.
-> Choose a relationship type and cross-filter direction.
-> Click OK.

Data Modelling in Power BI:

Data modelling is the process of structuring and connecting data from various sources.
Through data modelling, data analysis becomes accurate, efficient and easy.
There are three types of data modelling:
-> Conceptual data modelling - Just like its name conceptual its basically a concept defined by the business stakeholders, collaborating with the technical teams.
It defines the entities and relationships between them.
-> Logical data modelling - This level spices things from the conceptual model, actually it builds more from the conceptual.
In the logical model attributes and keys are added to the defined entities.
-> Physical data modelling - This is the most detailed level.
Its the actually implementation of the logical model.
In this you can actually visualize how data is stored in databases, also it includes the data types and Indexes.

Components of Data Modelling:

Relationships:

Relationships are the main feature of data modelling.

They define how tables connect based on a common column.

Types of relationships include:
-> One-to-One-One user can only have one profile
-> One-to-Many-One customer can have multiple orders.
-> Many_to-Many-Many-Many students can register for multiple classes
Basically, these relationships help Power BI connect data logically.

2.Star Schema Method:
The star schema, its the most recommended and widely adopted model in Power BI.
It usually takes the shape of a star with a:

Facts table -> which contains measurable data like sales, revenue e.t.c
Dimension table ->which contains descriptive data, this is data that describes more of what happened. For example: (products) -> product_details, Product_category

The advantages of star schema include:

Fast performance - due to optimized quering data retrieval becomes faster.
Ease of use - application of the star schema is straight forward.
Lower Maintenance - due to the less complexity of the dimension tables, maintaining the star schema is simple and easy.

Importance of relationships to Data analysis:
Relationships in Power BI are very important as they tell us how different tables connect and relate to each other.
Lets discuss how this helps Power BI analyze data correctly.

Improves the performance analysis -> Through linking tables efficiently, relationships help Power BI reduce the amount of data processed for a retrieval.
Data integrity and accuracy -> Relationships ensure there is consistency through the data.
Reduce redundancy -> Relationships ensures there are no duplicates, by making sure that data is only stored once and is referenced from other tables.
Table connections -> Power BI connects different tables based on the matching columns, through this data retrieval becomes easy. For example you can match a customerID in customers with a Sales or Orders. Without relationships this is impossible.

The Importance Of SQL Skills

Access to data sources -> Having SQL skills allows analysts to access and connect data from various sources.(e.g. MySQL, PostgreSQL)
Handling large datasets -> SQL concepts enable you as a data analyst to manipulate and work on large data easily and efficiently.
Data cleaning and preparation -> Oftenly, Raw data is messy but SQL has powerful functions for data cleaning, filtering and removing duplicates.
Perform complex analysis -> Through the use of operations like joins anaysts can connect different tables, this allows for comprehensive analysis e.g. you can link customerID with sales/orders table to view the amount of peroducts bought by a customer.

How SQL is used For Daily Tasks:

Data Retrieval: You can retrieve or get data from a specific path you want:

setting a specific search_path :

Data retrieval:

The above example shows data retrieval from specific tables.

2.Data Filtering:
SQL allows you to select a subset of data from a larger dataset by applying conditions.
Filtering in SQL, the where clause is used with the select, update and delete statements.

Example:

Performing Aggregations: SQL can summarize your data based on several functions like: SUM(), COUNT(), AVG()

Example:

4.Preparing data before building dashboard:

For you to build a dashboard, the data has to be structured properly for the readiness of analysis.

The following are methods of preparing data before building a dashboard:

Performing Joins: Joins combine multpile tables based on matching columns Example: Below you can see we have joined the customers and sales table base on the customer_id. We are finding top 5 customers who made the highest purchase

Adding new calculated columns: This adds new caluclated columns to your table In this case we use aliases AS to name the new columns' For example:

Handling Missing Values: Nulls in your data can result to incorrect or inaccurate results. Therefore, replacing nulls is essential for accurate results. We use the COALESCE() function. Example:

Sorting Data: This is organizing data for better analysis

In the above example you can see we have sorted the names of the customers in a descending order.

Conclusion:

Connecting databases to Power BI is an essential skill for any data analyst. This gives you access to real-time and realible data for analysis. Therefore, based on your data requirements there are multiple options to connect. At first, it seems complicated. However, with more practice it becomes easy to understand and apply. Remember to always practice, trial and error builds confidence and you will be able to acquire the expertise required to connect Power BI to local or cloud databases as well as work with the data.

Joins and Window Functions in SQL.

Derick Kimanthi — Mon, 02 Mar 2026 16:49:37 +0000

Introduction

In data analysis, SQL plays a vital role for querying and transforming data. Inbuilt features like joins and window functions enable analysts to combine datasets into important and meaningful views as well as perform basic and complex calculations. In this article, we will discuss how joins and window functions work and their importance in analytics.

Joins

Joins are used to combine rows from two or more tables based on a common column.
Usually, the common column its the primary key and a foreign key.

What is the Importance Of Joins?

Relational Data Connection: Joins allow easy connection of tables. For Example: You can match a customer_id in sales table with a customers table.
Normalization: Joins can help to normalize data i.e. eliminate duplicates therefore improving data storage, by storing data into different tables rather than repeating data which brings inconsistencies.
Performance Optimization: Using joins like the (inner join) improves data retrieval and enhances fast filtering also.

Types of Joins Include:

INNER JOIN
The INNER JOIN returns only matching records from both tables.
Excludes non-matching data from the result
For Example: Matching employees from employees table with departments table the common column being the department_id.

SELECT e.name, d.department_name
FROM Employees e
INNER JOIN Departments d
ON e.department_id = d.department_id;

Visual Representation:

LEFT JOIN
Also known as left outer join.
Returns all records from the left table, and matched records from the right.

SELECT e.name, d.department_name
FROM Employees e
LEFT JOIN Departments d
ON e.department_id = d.department_id;

Visual Representation:

RIGHT JOIN
Also known as right outer join
Returns all records from the right table, and matched records from the left

SELECT e.name, d.department_name
FROM Employees e
RIGHT JOIN Departments d
ON e.department_id = d.department_id;

Visual Representation:

FULL JOIN
Also known as full outer join.
Returns all records when a match exists in either table.

SELECT e.name, d.department_name
FROM Employees e
FULL JOIN Departments d
ON e.department_id = d.department_id;

Visual Representation:

WINDOW FUNCTIONS IN SQL

Window functions allow you to perform calculations across a defined set of rows (window), without collapsing the result into a single value.

Common uses for Window Functions:

Aggregates->calculates sum, averages, count across a partition of rows.
Rankings->They assign ranks to rows. Very useful when performing N-queries or ordered comparisons.
Running totals->they generate cumulative totals over a period of time.

To define a window for calculation, you use the OVER() clause.
Through this you can:

PARTITION BY->it divides data into groups but doesn't collapse the rows.
ORDER BY->order by specifies the order of the rows.

General syntax for a window function:
SELECT column_name1, window_function(column_name2) OVER ([PARTITION BY column_name3] [ORDER BY column_name4]) AS new_column FROM table_name;

Types of window functions in SQL

A. AGGREGATE FUNCTIONS
Aggregate functions perform aggregates over a window of rows.
They include:

SUM(): Sums values within a window.

Sample Query: Total salary by department.

SELECT name,department,salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

AVG(): Calculates the average value within a window.

Sample Query: Calculating the average salary within each department.

SELECT Name, Age, Department, Salary, 
AVG(Salary) OVER( PARTITION BY Department) AS Avg_Salary
FROM employee

COUNT(): Counts the rows within a window.

Sample Query: Counting rows within a specific group.

SELECT OrderID,CustomerID,OrderDate,
COUNT(OrderID) OVER (PARTITION BY CustomerID) AS OrdersPerCustomer
FROM Orders;

MAX(): Returns the maximum value in the window.

Sample Query: Finding the maximum salary within each department

SELECT  employee_id, department, salary,
MAX(salary) OVER(PARTITION BY department) AS dep_maximum_salary
FROM employee;

MIN(): Returns the minimum value in the window.

Sample Query: finding the minimum salary within each department and displaying each employee's individual details.

SELECT department, employee_name, salary,
MIN(salary) OVER (PARTITION BY department) AS min_department_salary
FROM employees;

B. RANKING FUNCTIONS

They assign ranks to rows within a specified area.
They include:

ROW_NUNMBER()->Assigns a unique number to each row in the result set. It automatically increments by 1 for every row.

Sample Query:

SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

RANK()->They assign ranks rows, when they find a duplicate they skip. Simply, they organize data and analyze data.

Sample Query: Ranking employees by salary, as well as skipping where salary is equal.

SELECT Name, Department, Salary,
RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_rank
FROM employee;

DENSE_RANK()->Assigns ranks to rows without skipping rank numbers for duplicates. Its usually the same as Rank() but doesn't skip.

Sample Query: Ranking employees by salary without skipping ranks

SELECT Name, Department, Salary,
DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_dense_rank
FROM employee;

PERCENT_RANK()->shows where a row stands compared to others in the same group. Sample Query: finding the relative salary position of each employee within a department.

SELECT Name, Department, Salary,
PERCENT_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS emp_percent_rank
FROM employee;

Conclusion

Joins and window functions are very important in SQL. As they make data analysis easier and effective. Therefore, learning and practicing these topics is essential for any data analyst.

Data Cleaning, DAX And Dashboards in PowerBI.

Derick Kimanthi — Mon, 09 Feb 2026 14:23:31 +0000

INTRODUCTION:

Having messy data is completely normal, but working with messy data can really be overwhelming. Luckily, PowerBI provides a platform that makes data cleaning and analysis fast and easy. In this article I will explain how analysts translate messy data using key tools in PowerBI such as: PowerQuery, Data Analysis Expression (DAX) and Dashboards to visualize key insights.

What is Data cleaning?

Data cleaning is the process of analysing, detecting and correcting: inaccurate, incomplete and poorly formatted data within a given dataset.

Loading Data in PowerBI.

After Opening PowerBI, The first page will be about you connecting data through a specific data source.

You can select through various data sources including Excel and Databases including SQL.
After loading your data you will need to spot the errors and inconsistencies in your data before cleaning.
To do this you can select the table mode. For easy analysis and identification.
The red circle is where you select to have the table view:

"How to Identify Messy Data."

Identifying errors and inconsistencies is key in the data cleaning process. Below are the signs of a messy data.

Signs of Messy Data:

Dates Labelled as Texts.
Numbers stored as Texts.
Single columns holding multiple values.
Nulls and blanks.
Duplicate rows.

Implementing PowerQuery In PowerBI

In powerBI data is transformed in the powerQuery Editor. To open the PowerQuery editor:

Go to the home tab.
Click the transform data button.

PowerBI provides a feature under powerQuery that helps you assess the errors in your data set quickly, therefore making the data cleaning process efficient and quicker.
To activate this:

Go the view tab.
Check the column quality checkbox.

As illustrated in the image above: If there were any errors from the data the quality would clearly show.

Step1:Fixing Data types.

Go through your data and ensure that each column falls in to their correct data type.
For example:

Date should be in Date format.
Quantity should be in Whole number.
Revenue should be in Decimal number.

To do this:

Right click on a column.
Then select change type.
Select the appropriate data type from the list.

Step2:Handling missing values.

When replacing missing values, don't just guess the values this is because the data needs to maintain its originality. So, some business logic is applied in order to maintain consistency without altering the original data.
For Example:

For a blank CITY field you can replace with "Unknown"or "NotGiven".
For a blank DISCOUNT field you can replace the null with 0. This ensures that aggregation calculations can be performed on the column.

To Do This:

Go to the transform tab.
Select the replace values.
Find the value you want to replace then input the value to replace with.
Click Ok.

Step3:Cleaning Textual Data.

To clean text data in PowerBI:
-Click transform tab.
-Select format.
Inside the list you will find various text formatting tools, they include:

Trim- this tool removes extra spaces into text.
Lowercase-It converts all text to lowercase.
Uppercase-It converts all text to uppercase.
Capitalize each word-It converts text into Proper case.
Clean-It removes non-printable characters.

Step4:Removing Duplicates.

Duplicate values return inaccurate results when doing calculations.
You can remove duplicate values on a single column or multiple columns.
To do this in a single column:

Right click on the column.
Then select remove duplicates.

To do this in multiple columns:

Hold ctrl then right click the columns you want.
Then got to home tab.
Select Remove rows.
Select Remove duplicates.

Step5:Spliting Columns.

Spliting data in powerBI depends with how your data is mixed up.

1.First Scenario:Spliting by a delimiter.
A delimeter is a character that's used to separate values within text. Delimeters include: comma(,); vertical bar (|),semicolon(;). For example: Laptop|Electronics|LT001 The delimiter is the Vertical bar(|).
To split this:

Go to the Home tab.
Select split columns.
Choose the "by delimiter" option.
Select the delimiter, In this case its the vertical bar(|).

2.Second Scenario:Split by Letter Case.
If you have dataset with joined values e.g "FirstNameLastName" you can implement the split by lettercase.
To split this:

Lowercase to UpperCase: It splits text at the point where a lowercase letter is followed by an uppercase letter.
UpperCase to LowerCase: It splits text at the point where an uppercase letter is followed by a lowercase letter.

3.Third scenario:Split by Digit/Non-digit.
This method works best to split text from numbers. Especially when there is no consistency delimeter.
For example: "Product123"

Digit To Non-Digit-splits the text when a number starts. e.g"123product" becomes "123" and "Product".
Non-digit to Digit-splits the text wherever a letter transitions to a number. e.g "Product123" becomes "Product" and "123".

Key takeaway:

Data cleaning is a dynamic process, each dataset will always have different requirements. Therefore, by practicing you will be able to build the skills to handle this challenges easily.

DATA ANALYSIS EXPRESSION (DAX)

DAX(Data Analysis Expression)- refers to a formula language in powerBI that's used to create powerful calculations and data models.
DAX can build:

Measures-Its a dynamic calculation that is calculated only when a visual is required.
Calculated columns-New columns added to a table and calculation performed row by row.
Calculated tables-Its derived from existing tables using DAX formula.

Common DAX functions.

Dax has a variety of functions, they are organized into different categorizes for different uses.

Aggregation function- these functions perform calculations on values.

They Include:

Sum-adds all numeric values in a column.
Average-Returns the average of values in a column.
Count-Counts the number of rows or values in a column.
Min-Returns the minimum numeric value in a column.
Max-Returns the largest numeric value in a column.
Product-Returns the product of numbers in a column.

2.Logical Functions-Logical functions in DAX compare values, test conditions and return a TRUE or FALSE.

They Include:

IF-Checks a condition and returns one value if TRUE, else it returns a another value.
AND- Returns a TRUE if all conditions are TRUE.
OR-Checks if one condition is TRUE and returns a TRUE.
NOT- The NOT function reverses a logical expression. e.g Reverses TRUE to FALSE or FALSE to TRUE.

3.Filter Functions- Filter functions in DAX are used to control context.
They Include:

ALL-It returns all rows or values within a column ignoring any filters applied.
CALCULATE-Evaluates an expression in a modified filter context.
FILTER-The filter function creates a newtable based on a specific data.
ORDERBY-Defines the columns that determine the sort order.

4.Date and time functions-Extracts insights and data from date and time.
They Include:

DATE Function-Returns the specified date in datetime format.
DAY Function-Extracts the days from a specified date.
DATEDIFF-Returns the difference between two dates.
YEAR Function-Returns the year of a date in a four digit format.
QUARTER FUNCTION-Returns the quarter as a number from 1 to 4.
TIME-Converts hours, minutes, and seconds given as a number from 0 to 59.
WEEKNUM-Returns the week number for the given date and year according to the return type value.

5.Iterator Functions-They evaluate row by row, to perform calculations.
Common Iterator Functions:

SUMX-Iterates through a table then evaluates an expression for each row and then sums the result.
AVERAGEX- Calculates the average for an expression.
MAXX/MINX-Finds the maximum or minimum value.
RANKX-used to rank items based on a specific expression.

DASHBOARDS IN POWERBI.

A dashboard its a single-page document used to visualize key insights drawn from the data. They are used to monitor key performance indicators (KPIs) at a glance.

Characteristics of a Good Dashboard
A good dashboard should be:

Relevant-Make sure to focus on key KPIs that align to the business requirements and goals of the analysis.
Clear-Make sure to use clear visuals and appropriate charts for easier understanding.
Consistent-Make sure to maintain a common color and fonts through the entire page that is relevant to your audience.
Interactive-A good dashboard should be responsive, add slicers and filters to enable easy analysis through the data.

Components of a PowerBI dashboard:

Title-Should be in a large font, placed in the top of the page.

2.Key Performance Indicators (KPIs)- These are high-level critical numbers that gauge the performance.
They are structured at the top, mostly after the title. They should be in big and bold characters.

An Example of KPIs:

Total Sales.
Total Profits.
Total Revenue.

3.Charts(Visual Insights)-These are graphical representation of data.

They Include:

Card Visual-Displays a single value of a calcualation.

Multi-row card-Displays multiple values of KPIs together.

Column and Bar Charts-A column chart displays data using vertical and horizontal bars respectively to compare values across categories.

Pie Chart-Shows distribution of data in different sectors.

Line Chart-A line chart shows trends over time.

Area Chart-An area chart is a line chart, with the area under filled.

Funnel Chart-Shows values across sequential stages.

Donught Chart-Similar to a pie chart but it has a hole in the middle.

Table-A table shows data in rows and columns.

Matrix-Its a pivot table with rows and columns.

Q&A visual-It self-analyzes your data. Allowing users to ask natural questions.

Stacked Column Chart-It compares multiple values side by side.

Stacked Bar Chart-Its similar to a Column chart but displays horizontally.
Scatter Chart-It shows a relationship between two variables.
WaterFall Chart-It shows how values drop or rise over a period of time.
Combo Chart-Its a combination of column and line chart.
Map-It displays data distribution across different geographic regions.

Python Chart-Implements python to create custom charts.
R Visual-Implement R to create custom charts.

Creating Dashboards In PowerBI:

Creating a Dashboard involves organizing multiple visuals like (charts and KPIs) together. This allows you to communicate key insights therefore supporting decision making.

A dashboard should:

Consist of one unscrollable page.
Shows critical metrics.
Be interactive.
Update automatically.

Layout And Structure:

Title - placed at the top.
KPIs - positioned at the top row.
Charts -Displayed at the middle.
Filters and Slicers- should be aligned at the side (left or right).

Illustration Of A PowerBI Dashboard:

What To Avoid.

Avoid the following when creating dashboards:

Avoid cluttering many charts together (3-6 are enough).
Repeating charts with the same data.
Adding to many decorative colors (Stick to a consistent color that aligns with your audience).
Raw data or tables in to your dashboard.
Calculated columns-Do not include calculated columns in your dashboard.

Data Modelling And Schemas In PowerBI.

Derick Kimanthi — Mon, 02 Feb 2026 14:51:53 +0000

What is data modelling?

Data modelling refers to the process of structuring and organizing data into a logical models (tables), therefore creating relationships that enable;

Easy understanding of data by aggregating data accurately and correctly.
Providing realible insights, through proper structuring.
Maintaining data integrity.

Facts And Dimension Tables:

There are two types of tables in data modelling: i.e Facts and Dimension tables.
Facts Tables
They contain measurable data that is numeric data. Includes: Sales, Amounts, Quantity, Cost etc. Data that contains numbers.
Dimension Tables
They help in describing facts. Instead of having numerical values, they are more of textual content. They contain describe attributes. For example: We can have a product; then have a ProductName, ProductCategory, ProductKey, all these you see are attributes that describe a Product.

Schemas In PowerBI.

A data schema is a logical structure of your data model. Its a blue print that organizes facts and dimension tables together with their relations.

Types Of Schemas

The following are the common types of schemas used in PowerBI:

Star Schema. As the name suggests it has a shape of a star. It has a central fact with surrounding dimension tables.

Illustration of Star Schema:

Above is a description of a star schema in PowerBI with:

The Facts table(Numeric data) in the middle.
The dimension tables connected from the Fact table.

2.Snowflake Schema.
For the snowflake schema dimensions are split into multiple related tables.
Meaning: In star schema the dimensions are kept in a single table. But in snowflake the dimesions are normalized i.e They are organized to reduce redundacy (repetition of data). The dimensions can relate to other dimensions including the fact table.

For Example:

In the above relationship you can be able see the further sub-division of dimensions.

Relationships In PowerBI:

Relationships are used to link facts and dimensions. They define how tables connect to each other therefore enhancing accurate reporting.
In PowerBI there are several types of relationships they include:

One to One. The rarest relationship. One table relates to only one table. For Example (1) dimension relates to (1) Fact:

One to Many.
One record relates to multiple records in other tables.
For Example: We can have one customer with multiple transactions.
Many to Many
Its a type of relationships where multiple records are related to other multiple records in other tables.
For Example: One student can take many courses and one course can have multiple students.

Importance of a good modelling:

Performance-By minimizing the number of joints, a clean schema ensures optimized queries and less memory usage therefore ensuring a good responsive dashboard.
Accuracy-An accurate model ensures the correctness of aggregates. This ensures that there is a consistency across the report.
Maintability-A good model with a clean schema is easy to maintain by spoting errors and easy troubleshooting. Also its easy to explain to others.
Scalability- Its easy to add new features, you can easily add more dimensions and supports large growth of data.

INTRODUCTION TO MS EXCEL FOR DATA ANALYTICS

Derick Kimanthi — Sun, 25 Jan 2026 19:17:12 +0000

INTRODUCTION

If you are a starting out in data analytics, I am sure you have heard of excel. You see excel is a tool that helps you analyze data, create dashboards, perform data entries, automate tasks and so much more. In this guide i will explain how MS excel can be used for basic data analytics, in a simple and beginner-friendly language.

MS Excel Layout

In this article, i will assume you have already downloaded and installed Microsoft excel. When you open excel the first page you will see is the start up screen. After which you will be redirected to a page where you will select a blank workbook or templates of your choice depending on the type of tasks you are performing. For a beginner i recommend you starting with a blank book. This will enable you to practice entering data, applying formulas as well as exploring Microsoft Excel's features.
Once you select the blank work book, excel takes you to a new worksheet:
In the worksheet there is a:

Title Bar- Displays the name of your workbook and the application (Excel).
Quick Access Toolbar-Provides access to frequently used commands.
Formula Bar- It shows the contents of a selected cell. You can also type and enter formulas and data.
Ribbon-This is the main menu at the top ofthe work sheet, it's organized into tabs; whereby each contains a group of commands and tools.
Cell-This is where you enter data. Each cell is identified by its column letter and row number (e.g., A1).
Row-A horizontal line of cells, numbered along the left side of the worksheet.
Column-A vertical line of cells, labeled with letters across the top of the worksheet.
Scroll Bars-Allow you to move horizontally or vertically through your worksheet.
Below is an Image that clearly illustrates the above:

Basic Formatting

This basically involves improving the data readability. It's simply done by adjusting the font and size, alingment and adding other formatting styles like bold, italics etc. These tools are located at the home tab.

Important Formatting techniques:

Font Styles-Simply it consists of font types,sizes,color or making the text either bold, italic. You can also use the paint bucket to fill the entire text with a color of your choice.
Number Formatting-This involves choosing the correct data type that custom-fits a column and a cell e.g. you can choose currency, dates, text number etc.
Alingment-Adjusting the alingments enhaces data readability, you can align data center, top, right, bottom , vertical alingment and text wrap.
Row & column sizing-You can adjust the height of rows, when your text or data doesnot fit you can raise the height of the row ensuring that data or text fits nicely.
Freeze panes- The headers can be freezed allowing you to see the headers when scrolling in large data sets

Working with a large data set

When working with large data sets; you will need to apply some formatting and formulas that will make your work easier.
For Example:

Data Validation

This is a tool that helps you control the type of data entered in a cell. It's essential as it helps maintain accuracy by restricting what is entered. For example you create a dropdown that limits a cell's selection to either Male or Female e.t.c. for gender selection.
To do this:

Select a column
Click the data tab
Then click data validation
You will see a popup menu
Select the list in the dropdown Menu
Type the source as seen and click ok

Data Sorting

This involves arranging data in a specific order:
It includes:

Alphabetical sortin- from A to Z.
Number sorting-smallest to largest and viceversa.
Date and time sorting-oldest to new and viceversa.
To do this:

Select all the data by CTRL+A
Click sorting, then navigate to custom sorting.
Then select the column you want to sort.

Data Filtering

its used to show data that meets the specific conditions while hiding the rest.
E.g Show sales above 10,000
How to do it:

Select your data
Click the data tab then filter
A small dropdown arrow will appear on each column header

Types of filtering include:

Text Filters
Number filters
Date filters
Illustration:

Functions In Excel

Text Functions
Majorly these functions manipulate texts, they join and transform texts from one form to another e.g uppercase, lowercase, propercase. They include:

Upper case-converts text from lower case to uppercase syntax=UPPER(Cell_reference)
Lower case-Converts text to lowercase Syntax: =LOWER(cell_reference)
Trim Function-Removes extra spaces from text except single spaces between words Syntax: =TRIM(text)
Length Function-Counts the number of characters in a text string Syntax: =LEN(text)
Left Function-Extracts a specified number of characters from the left side of a text Syntax: =LEFT(text, num_chars)
Right Function-Extracts a specified number of characters from the right side of a text Syntax: =RIGHT(text, num_chars)
Mid Function-Extracts text from the middle of a text string Syntax: =MID(text, start_num, num_chars)
Concate-Joins two or more text strings together Syntax:=CONCAT(text1, text2, …)
Proper case-Converts text to proper case (first letter of each word capitalized) Syntax: =PROPER(cell_reference)

Aggregate Functions

These functions are used to perform calculations on a group of values. They are performed on numerical values. They include:

Sum-Performs a summation for a range. Syntax:=SUM(range)
Average-Calculates the mean of a range. Syntax:=AVERAGE(range)
Count-Counts cells that contain numbers.Syntax:=COUNT(range)
Counta-Counts cells that are not empty. Syntax:=COUNTA(range)
Max-It returns the maximum value in a range. Syntax:=MAX(range)
Min-It returns the minimum value in a range. Syntax:=MIN(range) ILLUSTRATION: The highlighted part is the selected range, Once you press enter excel will calculate and display the result of the sum of the above range.

Conditional Aggregation

These functions calculate totals, counts or averages based on one or more conditions.
E.g Total sales based on region = Nairobi where count of sales<10
They include:

1.SUMIF-Adds values with one condition. Syntax:=SUMIF(range,criteria,sum_range)

SUMIFS-Adds values with multiple conditions. Syntax:=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...).
COUNTIF-Counts the number of cells that satisfy a certain condition. Syntax:=COUNTIF(Range,criteria)
COUNTIFS-Counts the number of cells that satisfy multiple conditions. Syntax:=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...).
AVERAGEIF-Calculates the mean of values based on one condition. Syntax:=AVERAGEIF(range, criteria, [average_range]),
AVERAGEIFS-Calculates the mean of values based on multiple conditions. Syntax:=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

ILLUSTRATION:

Once you press enter, Excel will calculate the Sum based on the condition highlighted in blue which is the sales departement.

Logical Functions

Logical functions automate decision-making. What they do is: compare ideas, Test conditions, return true or false based on specific results.
They Include

IF Functions-Perform Logical test and returns false based on the decision.
syntax: =IF(Logical_test, Value_if_true,value_if_false)
E.g =IF(E2>80000, "High","Low"
Nested IF- Used when you have more than two conditions
it's an if inside an if( therefore nested if)
Syntax:=IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false_all))
E.g A scenario whereby you are required to categorise employees by age as follows: highly experienced (>30), moderately experienced(20-30), low experinced(10-20),very low experience(less than 10).
Approach:

Insert another column to your data by right-clicking on the header, then select insert. You can then name it Experience level
At the formula bar you can use the following formula:
=IF(P>30, "Highly Experience",IF (P2>=20 "Moderately Experienced", IF (P2>=10 "Low Experienced", IF(P2<10 "Very Low Experience")))) once you press enter, excel will categorise the employees based on the following criteria.
ILLUSTRATION:

After autofilling:

AND FUNCTION-Returns true if all conditions are true. Syntax:=IF(AND(Conditional1,Conditional2) Example:=IF(AND(C2>50,D2>5),"Eligible",Not Eligible") In this case all conditions have to be met.

4.OR Function-Returns true if any condition is true
E.g=IF(OR(C2>50,D2>5),"Eligible",Not Eligible")
In this case only one condition needs to be met.

VLOOKUP VS HLOOKUP

VLOOKUP-it searches for a value in the first column of a range and returns a value from another column.
It functions by searching for a value vertically (down a column).
It looks in the first column of the table.
Then returns the value from another column in the same row.

Syntax:=VLOOKUP(lookup_value, Table_Array, Col_Index_Num, [Range_LookUP]
Where: lookup_value = Value
Table_Array = Entire Table
Col_Index_Num = Column number
[Range_LookUP] = setup to False

LOOKUP-Stands for horizontal lookup. It searches for a value in the first row of a table and returns a value from the same column in a row you specify.

Syntax:=HLOOKUP(lookup_value, Table_Array, Col_Index_Num, [Range_LookUP]
SCENARIO:
How much is the bonus for employee 108759 =HLOOKUP(108759,A1:A1867,13,False)
What is the marital status for employee 10622=HLOOKUP(10622,A1:A1867,14,False)
NB To use both of these tools, you need to have an idea of what you are searching for. This enables you to find other related information easily.

INDEX AND MATCH

INDEX-Refers to organizing data so that excel can quickly locate and retrieve specific values.
Syntax:=INDEX(Array,row_num,[Col_num]
INDEX WITH MATCH-This Combination replaces VLOOKUP & HLOOKUP, by enabling you to search anywhere.
Syntax:`=INDEX(Return_range,match*(Lookup_value,Lookup_range,0))You simply index what you are trying to find: E.g.=INDEX(F2:F877,*match(10871,A2:A877,0))`
ILLUSTRATION:Find the department for employee 10871

The result will be:

As you can see the result is marketing department.

Conclusion:

In the above guide, we have only focused on the basic functionalities of Excel. As a beginner understanding the full potential of Excel is essential towards the growth of your data analytics career. Therefore by starting small through practicing, you will be able to master these and more concepts easily.

INTRODUCTION TO MS EXCEL FOR DATA ANALYTICS

Derick Kimanthi — Sun, 25 Jan 2026 16:17:10 +0000

INTRODUCTION

MS Excel Layout

Title Bar- Displays the name of your workbook and the application (Excel).
Quick Access Toolbar-Provides access to frequently used commands.
Formula Bar- It shows the contents of a selected cell. You can also type and enter formulas and data.
Ribbon-This is the main menu at the top ofthe work sheet, it's organized into tabs; whereby each contains a group of commands and tools.
Cell-This is where you enter data. Each cell is identified by its column letter and row number (e.g., A1).
Row-A horizontal line of cells, numbered along the left side of the worksheet.
Column-A vertical line of cells, labeled with letters across the top of the worksheet.
Scroll Bars-Allow you to move horizontally or vertically through your worksheet.
Below is an Image that clearly illustrates the above:

Basic Formatting

Important Formatting techniques:

Font Styles-Simply it consists of font types,sizes,color or making the text either bold, italic. You can also use the paint bucket to fill the entire text with a color of your choice.
Number Formatting-This involves choosing the correct data type that custom-fits a column and a cell e.g. you can choose currency, dates, text number etc.
Alingment-Adjusting the alingments enhaces data readability, you can align data center, top, right, bottom , vertical alingment and text wrap.
Row & column sizing-You can adjust the height of rows, when your text or data doesnot fit you can raise the height of the row ensuring that data or text fits nicely.
Freeze panes- The headers can be freezed allowing you to see the headers when scrolling in large data sets

Working with a large data set

When working with large data sets; you will need to apply some formatting and formulas that will make your work easier.
For Example:

Data Validation

Select a column
Click the data tab
Then click data validation
You will see a popup menu
Select the list in the dropdown Menu
Type the source as seen and click ok

Data Sorting

This involves arranging data in a specific order:
It includes:

Alphabetical sortin- from A to Z.
Number sorting-smallest to largest and viceversa.
Date and time sorting-oldest to new and viceversa.
To do this:

Select all the data by CTRL+A
Click sorting, then navigate to custom sorting.
Then select the column you want to sort.

Data Filtering

its used to show data that meets the specific conditions while hiding the rest.
E.g Show sales above 10,000
How to do it:

Select your data
Click the data tab then filter
A small dropdown arrow will appear on each column header

Types of filtering include:

Text Filters
Number filters
Date filters
Illustration:

Functions In Excel

Text Functions
Majorly these functions manipulate texts, they join and transform texts from one form to another e.g uppercase, lowercase, propercase. They include:

Upper case-converts text from lower case to uppercase syntax=UPPER(Cell_reference)
Lower case-Converts text to lowercase Syntax: =LOWER(cell_reference)
Trim Function-Removes extra spaces from text except single spaces between words Syntax: =TRIM(text)
Length Function-Counts the number of characters in a text string Syntax: =LEN(text)
Left Function-Extracts a specified number of characters from the left side of a text Syntax: =LEFT(text, num_chars)
Right Function-Extracts a specified number of characters from the right side of a text Syntax: =RIGHT(text, num_chars)
Mid Function-Extracts text from the middle of a text string Syntax: =MID(text, start_num, num_chars)
Concate-Joins two or more text strings together Syntax:=CONCAT(text1, text2, …)
Proper case-Converts text to proper case (first letter of each word capitalized) Syntax: =PROPER(cell_reference)

Aggregate Functions

These functions are used to perform calculations on a group of values. They are performed on numerical values. They include:

Sum-Performs a summation for a range. Syntax:=SUM(range)
Average-Calculates the mean of a range. Syntax:=AVERAGE(range)
Count-Counts cells that contain numbers.Syntax:=COUNT(range)
Counta-Counts cells that are not empty. Syntax:=COUNTA(range)
Max-It returns the maximum value in a range. Syntax:=MAX(range)
Min-It returns the minimum value in a range. Syntax:=MIN(range) ILLUSTRATION: The highlighted part is the selected range, Once you press enter excel will calculate and display the result of the sum of the above range.

Conditional Aggregation

These functions calculate totals, counts or averages based on one or more conditions.
E.g Total sales based on region = Nairobi where count of sales<10
They include:

1.SUMIF-Adds values with one condition. Syntax:=SUMIF(range,criteria,sum_range)

SUMIFS-Adds values with multiple conditions. Syntax:=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...).
COUNTIF-Counts the number of cells that satisfy a certain condition. Syntax:=COUNTIF(Range,criteria)
COUNTIFS-Counts the number of cells that satisfy multiple conditions. Syntax:=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...).
AVERAGEIF-Calculates the mean of values based on one condition. Syntax:=AVERAGEIF(range, criteria, [average_range]),
AVERAGEIFS-Calculates the mean of values based on multiple conditions. Syntax:=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

ILLUSTRATION:

Once you press enter, Excel will calculate the Sum based on the condition highlighted in blue which is the sales departement.

Logical Functions

Logical functions automate decision-making. What they do is: compare ideas, Test conditions, return true or false based on specific results.
They Include

IF Functions-Perform Logical test and returns false based on the decision.
syntax: =IF(Logical_test, Value_if_true,value_if_false)
E.g =IF(E2>80000, "High","Low"
Nested IF- Used when you have more than two conditions
it's an if inside an if( therefore nested if)
Syntax:=IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false_all))
E.g A scenario whereby you are required to categorise employees by age as follows: highly experienced (>30), moderately experienced(20-30), low experinced(10-20),very low experience(less than 10).
Approach:

Insert another column to your data by right-clicking on the header, then select insert. You can then name it Experience level
At the formula bar you can use the following formula:
=IF(P>30, "Highly Experience",IF (P2>=20 "Moderately Experienced", IF (P2>=10 "Low Experienced", IF(P2<10 "Very Low Experience")))) once you press enter, excel will categorise the employees based on the following criteria.
ILLUSTRATION:

After autofilling:

AND FUNCTION-Returns true if all conditions are true. Syntax:=IF(AND(Conditional1,Conditional2) Example:=IF(AND(C2>50,D2>5),"Eligible",Not Eligible") In this case all conditions have to be met.

4.OR Function-Returns true if any condition is true
E.g=IF(OR(C2>50,D2>5),"Eligible",Not Eligible")
In this case only one condition needs to be met.

VLOOKUP VS HLOOKUP

Syntax:=VLOOKUP(lookup_value, Table_Array, Col_Index_Num, [Range_LookUP]
Where: lookup_value = Value
Table_Array = Entire Table
Col_Index_Num = Column number
[Range_LookUP] = setup to False

LOOKUP-Stands for horizontal lookup. It searches for a value in the first row of a table and returns a value from the same column in a row you specify.

INDEX AND MATCH

INDEX-Refers to organizing data so that excel can quickly locate and retrieve specific values.
Syntax:=INDEX(Array,row_num,[Col_num]
INDEX WITH MATCH-
This Combination replaces VLOOKUP & HLOOKUP, by enabling you to search anywhere.
Syntax:=INDEX(Return_range,**match**(Lookup_value,Lookup_range,0))
You simply index what you are trying to find:
E.g.=INDEX(F2:F877,**match(10871,A2:A877,0))
ILLUSTRATION:Find the department for employee 10871

The result will be:

As you can see the result is marketing department.

Conclusion:

INTRODUCTION TO MS EXCEL FOR DATA ANALYTICS

Derick Kimanthi — Sun, 25 Jan 2026 16:17:10 +0000

INTRODUCTION

MS Excel Layout

Title Bar- Displays the name of your workbook and the application (Excel).
Quick Access Toolbar-Provides access to frequently used commands.
Formula Bar- It shows the contents of a selected cell. You can also type and enter formulas and data.
Ribbon-This is the main menu at the top ofthe work sheet, it's organized into tabs; whereby each contains a group of commands and tools.
Cell-This is where you enter data. Each cell is identified by its column letter and row number (e.g., A1).
Row-A horizontal line of cells, numbered along the left side of the worksheet.
Column-A vertical line of cells, labeled with letters across the top of the worksheet.
Scroll Bars-Allow you to move horizontally or vertically through your worksheet.
Below is an Image that clearly illustrates the above:

Basic Formatting

Important Formatting techniques:

Font Styles-Simply it consists of font types,sizes,color or making the text either bold, italic. You can also use the paint bucket to fill the entire text with a color of your choice.
Number Formatting-This involves choosing the correct data type that custom-fits a column and a cell e.g. you can choose currency, dates, text number etc.
Alingment-Adjusting the alingments enhaces data readability, you can align data center, top, right, bottom , vertical alingment and text wrap.
Row & column sizing-You can adjust the height of rows, when your text or data doesnot fit you can raise the height of the row ensuring that data or text fits nicely.
Freeze panes- The headers can be freezed allowing you to see the headers when scrolling in large data sets

Working with a large data set

When working with large data sets; you will need to apply some formatting and formulas that will make your work easier.
For Example:

Data Validation

Select a column
Click the data tab
Then click data validation
You will see a popup menu
Select the list in the dropdown Menu
Type the source as seen and click ok

Data Sorting

This involves arranging data in a specific order:
It includes:

Alphabetical sortin- from A to Z.
Number sorting-smallest to largest and viceversa.
Date and time sorting-oldest to new and viceversa.
To do this:

Select all the data by CTRL+A
Click sorting, then navigate to custom sorting.
Then select the column you want to sort.

Data Filtering

its used to show data that meets the specific conditions while hiding the rest.
E.g Show sales above 10,000
How to do it:

Select your data
Click the data tab then filter
A small dropdown arrow will appear on each column header

Types of filtering include:

Text Filters
Number filters
Date filters
Illustration:

Functions In Excel

Text Functions
Majorly these functions manipulate texts, they join and transform texts from one form to another e.g uppercase, lowercase, propercase. They include:

Upper case-converts text from lower case to uppercase syntax=UPPER(Cell_reference)
Lower case-Converts text to lowercase Syntax: =LOWER(cell_reference)
Trim Function-Removes extra spaces from text except single spaces between words Syntax: =TRIM(text)
Length Function-Counts the number of characters in a text string Syntax: =LEN(text)
Left Function-Extracts a specified number of characters from the left side of a text Syntax: =LEFT(text, num_chars)
Right Function-Extracts a specified number of characters from the right side of a text Syntax: =RIGHT(text, num_chars)
Mid Function-Extracts text from the middle of a text string Syntax: =MID(text, start_num, num_chars)
Concate-Joins two or more text strings together Syntax:=CONCAT(text1, text2, …)
Proper case-Converts text to proper case (first letter of each word capitalized) Syntax: =PROPER(cell_reference)

Aggregate Functions

These functions are used to perform calculations on a group of values. They are performed on numerical values. They include:

Sum-Performs a summation for a range. Syntax:=SUM(range)
Average-Calculates the mean of a range. Syntax:=AVERAGE(range)
Count-Counts cells that contain numbers.Syntax:=COUNT(range)
Counta-Counts cells that are not empty. Syntax:=COUNTA(range)
Max-It returns the maximum value in a range. Syntax:=MAX(range)
Min-It returns the minimum value in a range. Syntax:=MIN(range) ILLUSTRATION: The highlighted part is the selected range, Once you press enter excel will calculate and display the result of the sum of the above range.

Conditional Aggregation

These functions calculate totals, counts or averages based on one or more conditions.
E.g Total sales based on region = Nairobi where count of sales<10
They include:

1.SUMIF-Adds values with one condition. Syntax:=SUMIF(range,criteria,sum_range)

SUMIFS-Adds values with multiple conditions. Syntax:=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...).
COUNTIF-Counts the number of cells that satisfy a certain condition. Syntax:=COUNTIF(Range,criteria)
COUNTIFS-Counts the number of cells that satisfy multiple conditions. Syntax:=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...).
AVERAGEIF-Calculates the mean of values based on one condition. Syntax:=AVERAGEIF(range, criteria, [average_range]),
AVERAGEIFS-Calculates the mean of values based on multiple conditions. Syntax:=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

ILLUSTRATION:

Once you press enter, Excel will calculate the Sum based on the condition highlighted in blue which is the sales departement.

Logical Functions

Logical functions automate decision-making. What they do is: compare ideas, Test conditions, return true or false based on specific results.
They Include

IF Functions-Perform Logical test and returns false based on the decision.
syntax: =IF(Logical_test, Value_if_true,value_if_false)
E.g =IF(E2>80000, "High","Low"
Nested IF- Used when you have more than two conditions
it's an if inside an if( therefore nested if)
Syntax:=IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false_all))
E.g A scenario whereby you are required to categorise employees by age as follows: highly experienced (>30), moderately experienced(20-30), low experinced(10-20),very low experience(less than 10).
Approach:

Insert another column to your data by right-clicking on the header, then select insert. You can then name it Experience level
At the formula bar you can use the following formula:
=IF(P>30, "Highly Experience",IF (P2>=20 "Moderately Experienced", IF (P2>=10 "Low Experienced", IF(P2<10 "Very Low Experience")))) once you press enter, excel will categorise the employees based on the following criteria.
ILLUSTRATION:

After autofilling:

AND FUNCTION-Returns true if all conditions are true. Syntax:=IF(AND(Conditional1,Conditional2) Example:=IF(AND(C2>50,D2>5),"Eligible",Not Eligible") In this case all conditions have to be met.

4.OR Function-Returns true if any condition is true
E.g=IF(OR(C2>50,D2>5),"Eligible",Not Eligible")
In this case only one condition needs to be met.

VLOOKUP VS HLOOKUP

Syntax:=VLOOKUP(lookup_value, Table_Array, Col_Index_Num, [Range_LookUP]
Where: lookup_value = Value
Table_Array = Entire Table
Col_Index_Num = Column number
[Range_LookUP] = setup to False

LOOKUP-Stands for horizontal lookup. It searches for a value in the first row of a table and returns a value from the same column in a row you specify.

INDEX AND MATCH

The result will be:

As you can see the result is marketing department.

Conclusion:

IMPLEMENTING GIT BASH, BEGINNER-FRIENDLY GUIDE

Derick Kimanthi — Sat, 17 Jan 2026 19:31:21 +0000

INTRODUCTION

In this guide, I will explain the basic uses of git bash. In a simple, beginner-friendly manner.

Git bash installation

Open your browser, e.g chrome, microsoft edge e.t.c
Search for Git Bash download on the browser to download the setup file.
Run the .exe file to install.
Make sure you check the launch gitbash icon before installing.

Connecting Git Bash with GitHub

To connect git bash to git hub, we use a set of command lines. I will explain the steps in a beginner-friendly manner for easy understanding.
After installation, open Git Bash

To check the installation and version, we use the following command: git --version and then press enter. You should be able to see the version number. ## Configuring Git
We need to update your name and email. This connects git bash to your GitHub Profile.
You will run the following commands separately
First, it's the name; git config --global user.name "YOUR NAME" and press enter. Second, run the second command for configuring the email: git config --global user.email "Your Email@gmail.com"
Confirmation of the configured details;
Run the following command; git config --list
You should be able to see your user name and email as seen below:

Establishing a connection between git bash and git hub

To connect GitBash to GitHub securely, we need to generate an SSH key.

An SSH key is an access credential used by the Secure Shell (SSH) protocol to provide secure, passwordless authentication between two systems. In this case, between Git Bash and Git Hub.

Generating an SSH key
Type the following command: ssh -keygen -t ed25519 -C "your Email" Then press Enter.
Starting the SSH agent
What this agent does is securely hold your private SSH key in memory to avoid you entering the passphrase each time you make an SSH connection. In short, it connects automatically when GitBash needs to make a connection with GitHub.
Run the following command eval "$(ssh-agent -s)" and press enter
Adding the SSH key.
Type the following Command: ssh-add ~./ssh/id_ed25519, press enter. In Windows, the key is mostly saved in the local disc C inside the users folder, where you will find the SSH sub-folder. There are two keys: the private and public keys. Whereby the public key goes to your GitHub.
Adding the SSH key to GitHub

Open the private key location.
Open the key and copy it using a text editor of your choice e.g visual code studio.
Open gitHub click the profile, and scroll down to find settings.
Click the SSH and GPG Keys. Paste the copied key and save.

Testing the connection between GitBash and GitHub. To test the connection, run the following command: ssh -T git@github.com You should be able to see the following:Hi username! You've successfully authenticated.

What is Version Control?

This is a system that tracks changes made to files overtime.
The functions of version control are:

Backup- prevents loss of work
Enhances collaboration if you are working with a team.
Lets you undo mistakes.
Tracks progress of your projects.

Basic git workflow

Most projects have the following workflow:

Modify files in your repository.
Stage changes adding snapshots to your project using git add
Commit the changes by adding descriptions that describe: who made the change? What time was the change made? Why was it made?
Pushing your local commits to your GitHub repo git push
Pulling changes made by other members to update your repository git pull

Understanding Push and Pull

The term push and pull refers to git commands that are simply used to upload and download changes made to your code (projects) remotely. This is crucial as it backs up your project as well as makes collaboration easy.

The git push command is used to send the committed changes to your GitHub repository.
The git pull command is used to download content from a remote repository.

Conclusion

Understanding Git Bash and Git Hub is very important, as both of these combined provide a realible and effective method to manage projects, enhanced collaboration and easy tracking of changes. Therefore for a beginner developer understanding these tools is crucial.

DEV Community: Derick Kimanthi

Connecting Power BI to a SQL Database:local & cloud option

INTRODUCTION:

Connecting Power BI to a Local PostgreSQL:

Connecting Power BI to a cloud database:

Loading Data into Power BI:

Data Modelling in Power BI:

The Importance Of SQL Skills

Conclusion:

Joins and Window Functions in SQL.

Introduction

Joins

Types of Joins Include:

WINDOW FUNCTIONS IN SQL

Types of window functions in SQL

Conclusion

Data Cleaning, DAX And Dashboards in PowerBI.

INTRODUCTION:

What is Data cleaning?

Loading Data in PowerBI.

"How to Identify Messy Data."

Implementing PowerQuery In PowerBI

Step1:Fixing Data types.

Step2:Handling missing values.

Step3:Cleaning Textual Data.

Step4:Removing Duplicates.

Step5:Spliting Columns.

Key takeaway:

DATA ANALYSIS EXPRESSION (DAX)

Common DAX functions.

DASHBOARDS IN POWERBI.

Components of a PowerBI dashboard:

Creating Dashboards In PowerBI:

Layout And Structure:

What To Avoid.

Data Modelling And Schemas In PowerBI.

What is data modelling?

Facts And Dimension Tables:

Schemas In PowerBI.

Types Of Schemas

Relationships In PowerBI:

Importance of a good modelling:

INTRODUCTION TO MS EXCEL FOR DATA ANALYTICS

INTRODUCTION

MS Excel Layout

Basic Formatting

Important Formatting techniques:

Working with a large data set

Data Validation

Data Sorting

Data Filtering

Functions In Excel

Aggregate Functions

Conditional Aggregation

Logical Functions

VLOOKUP VS HLOOKUP

INDEX AND MATCH

Conclusion:

INTRODUCTION TO MS EXCEL FOR DATA ANALYTICS

INTRODUCTION

MS Excel Layout

Basic Formatting

Important Formatting techniques:

Working with a large data set

Data Validation

Data Sorting

Data Filtering

Functions In Excel

Aggregate Functions

Conditional Aggregation

Logical Functions

VLOOKUP VS HLOOKUP

INDEX AND MATCH

Conclusion:

INTRODUCTION TO MS EXCEL FOR DATA ANALYTICS

INTRODUCTION

MS Excel Layout

Basic Formatting

Important Formatting techniques:

Working with a large data set