Forem: maggy njuguna

Introduction to python for data analytics.

maggy njuguna — Sun, 15 Mar 2026 13:49:12 +0000

Introduction

Python is a programming language created by Guido van Rossum and released in 1991.The most recent version of python is python 3 which is widely used.Python was invented to simplify programming by enabling writing of simple and clear code.
Programming sounds complex but its not ,in simple terms it is the process of giving instructions to a computer on how to perform specif tasks.Python is one of the languages used to give directions to computers on how to perform tasks.

Uses of Python.

Python can be used on a server to create web applications -this involves designing, creating and testing software programs that can be accessed over the internet using a browser.
Data analysis -Python is used in data analysis to perform analysis on large data sets and create visualizations.
Software development -Python is used to create programs that allow users to perform tasks on a mobile phone or a computer.It has been used to create tools that automate tasks example sending automated emails or analysis of large data sets.
Automation -Ever gotten tired of doing the same task over and over again?, Python comes in handy and helps automate repetitive tasks example collecting customer information from a company's website.
Artificial intelligence and machine learning. Machine learning in simple words is teaching a computer how to perform tasks.Example teaching a computer to identify many balls and how it can be able to identify a blue ball from many balls. Artificial intelligence aims at giving computers human intelligence that is enabling them to think and make decisions like human beings.Imagine that !

Why Python is preferred over other programming languages.

Simple beginner friendly syntax -syntax is simply the set of rules that enable a computer to perform tasks .Python syntax is simple like the English language which makes it easy to learn.
Python can be used in different platforms -It runs across many operating systems without requiring major modifications.
Flexibility- Python can be used in many fields including automation, data analysis ,web development and artificial intelligence.
Global community of developers -Python has good community support which ensures availability of learning resources and quick responses to solving problems.
Python allows for different programming styles.
- Procedural -the program to be followed by a computer is written step by step .
- Object-oriented -this programming style instructions to be followed by a computer around real life objects.
- Functional programming - it uses functions to perform calculations and analysis on data.

Python installation.
Many systems have python already installed however if your computer does not have python installed it can be downloaded from the official website.
To check on your python version run this code in your command line(cmd)
c:/users/ --python version

Python can run in various IDE(Integrated Development Environment) including; Sublime ,Visual studio code,PyCharm and jupyter notebook.

Python syntax
Python is executed using simple syntax.
print("Hello world")
Python indentation
Indentation in python is used to indicate a block of code.

Comments in python
Single line comments begin with the harsh symbol.
# perform analysis
Using docstrings for multiple lines of comments .""" or '''
Identifiers in python
An identifier is a name used to identify a variable or function.
name = "Angel"
score = 90
Name and score are identifiers used to name variables.

Variables in python
A variable in python is a name used to store a value in a program and will later be used in the program.
Name = "Maggy"
Town = "Nakuru"
Age = 15
Name, age, and town are the variables for storing Maggy, town and age.
You don't have to specify the data type in python unlike other programming languages.Python understands the data type based on the assigned value.

Differences between a variable and an identifier

An identifier is a name used to identify elements in a program while a variable is a name used to store a value inside a program.
An identifier is used to label variables and functions while a variable is used to store values that can be used later in a program.
An identifier does not hold data while a variable hold data.

Examples of identifiers name,age,city,score,calculate_sum

Examples of variables name = "Maggy" ,city = "kisumu"

Rules for naming variables.

A variable name should start with a letter or an undescore.

City = "Mombasa"

-school = "Daystar"
_number = 23

A variable cannot start with a number.
Variable names are case sensitive.
You cannot name a variable using python keywords.

The = (assignment operator) is used to assign value in python.
Python allows the use of multiple variables in a single line.

y, x, z = 2,3,5
print(y)
print(x)
print(z)

In python uppercase letters are used on a variable that should not change.
PI = 3.142

The type() function is used to check the type of your variable .
y = 10
print(type(x))

Data types in python
Data type is the classification of data that enables a computer identify the data its working with and the operations that be performed on that data.

Numeric types
- int -these are whole numbers .

Floats- these are numbers with decimal points.

Complex -these are used in scientific computing.

Changing data types.

Text types
- str - string is a sequence of characters.

Boolean type This data type has two possible values:True or false.

Sequence types

List -it is ordered ,changable and allows duplicates.

Tuple - it is ordered, unchangable and allows duplicates.

Range - represents a sequence of numbers.

Mapping types

Dictionaries this is a collection of key value pairs and is used to quickly look up values and organize data.

Set types

set- unordered and does not allow duplicates.
Frozen -similar to sets but unchangable.

Arithmetic operators

Comparison and logical operators in python

== Equals to.
!= Not equal to .
> Greater than.
< Less than.
>= Greater than or equal to.
<= Less than or equal to.

Logical operators
The common logical operators in python include:and, or and not
and their use case is shown below.

Conclusion
It is important for every data analyst to understand python since it makes data cleaning, calculations and analysis effective.

Connecting power bi to SQL databases locally and through cloud service.

maggy njuguna — Fri, 13 Mar 2026 10:38:47 +0000

Power BI is a powerful tool designed for cleaning data ,creating reports and dashboards.It is preferred over other tools due to its ability to handle large data sets,create real-time dashboards and its beginner friendly.Power BI desktop is a free version provided by Microsoft.

Main uses of Power BI

Connecting data from different sources- Power BI has the ability to connect data from many sources including ;web,excel,SQL.
Cleaning and transforming data-power bi is used to clean messy data making it ready to use for analysis.
Data modelling-Through power bi it is possible to combine different tables with data for effective analysis.
Data analysis using DAX(Data Analysis Expressions)-DAX is used to perform calculations in power bi.
Data visualisation -Power BI converts data into charts,filters,slicers,graphs and reports. Reports and dashboard creation.
Data automation, sharing and collaboration using Power BI service.

Why companies connect Power BI to databases.

Power bi's ability to handle large data sets -databases store large sets of data and power bi is very effective for handling large data.
Real time data -using power bi service companies can their data processed and accessed easily.Any changes made to data reflect instantly.
Data security -databases ensure data is protected and control its access.
Data centralisation -companies store their data in central databases and by connecting the database to power bi enables the staff to work with similar data at a given time.

Importance of SQL databases for storing and managing data

SQL databases have structured data storage - Data is stored in tables which have rows and columns and this makes it easy to retrieve and use the data.
Data retrieval efficiency -It is easy to retrieve specific data from SQL databases using queries.
Data integrity -SQL databases have rules and constraints including primary and foreign keys which ensure data remains accurate and consistent.
Large data sets- Most organisations deal with large volumes of data and SQL databases are effective for storage and management of this data.
SQL supports data relationship -Through SQL databases it is possible to connect data from different sources.

Connecting power bi to a local postgresql

Open the power bi desktop .

Click on Get datain the home ribbon ,this enables power bi to connect to many data sources.
Many options are available including excel,csv,web services click on postgresql .
Click connect-once you select postgresql click the button to connect.
Enter the server name local host:5432-this means that Power bi will be hosted locally in your computer.

Enter the database name postgres-this is the database that contains the tables we will be using.
Enter the authentication detail ;username and password.
Click connect.
After connection is complete a navigator window will open which shows what your database contains.

Select the tables you want to load from your database (Customers_2,sales,inventory and products).
- You can load your data directly into power bi.
- You can use power query to clean and transform your data before loading.
Once your tables are loaded they will appear on the data tab as shown below.

Connecting Power bi to Aiven PostgreSQL
The process of connecting power bi to a cloud database involves the following steps.Each step is illustrated with an image for clear understanding .

Log into your aiven account and open your Aiven PostgreSQL to get your connection details.

You will find the following details:Host ,Database name,Port,username and password.
Go the connection information and find the SSL certificate section.

Download the SSL certificate and save it in your computer.
Open power bi desktop and once you are in the home tab click Get data then select PostgreSQL database
Enter the connection details provided by Aiven and click OK.
Configure the SSL details -enter the certificate downloaded in your computer.
The navigator window appears and click load or transform data.

The SSL licence provides secure connection between Power bi and the cloud service(Aiven).
It protects the data from any malicious acts.
It also ensures you are connected to the right server and your credentials are protected.

Loading data into power bi
Open power bi desktop and once you are in the home tab click Get data then select PostgreSQL database
Select the tables you want to load from your database (Customers_2,sales,inventory and products).

You can load your data directly into power bi.
You can use power query to clean and transform your data.

Data Modelling and relationships
This process involves structuring,organizing and connecting data tables for effective performance.Designing a good data model is important since it makes reports accurate and easy to understand.To set up a data model in Power BI is done by opening the report view and go to Data Model.It involves bringing many data tables together and connecting the relationship between them.
A schema is a structure that shows how data is organized and related in a data model

Star Schema

This data model contains one central fact table and many dimension tables.The dimension tables are connected directly to the Facts table.
The fact table is at the center surrounded by the dimension tables forming the star shape.

Relationships in Data models

This explains how tables are connected in a data model.

Types of relationships

1 to many(1:*)-One record in a dimension data relates to many records in a fact table.
Many to 1(*:1)-Same as 1 to many but viewed in the opposite direction.
Many to many(:)-multiple records in one table match multiple records in another table.

Facts table

This table stores the key values for your analysis.It stores measurable and quantitative values and the values are mostly numeric.
In our case ,the sales table is the fact table.

Dimension tables.

This tables act like a look up table for the values in the facts table.

In our case the inventory,products and customers table are the dimensional tables.

Why is good data modelling critical?

Good data modelling ensures data accuracy by reducing data ambiguity and duplication.
A good data model improves performance by reducing the number of joins and relationships.Like in the case of star schema which has one fact table and a few dimension tables.
Simplified analysis -a well designed data model makes it easy to perform accurate DAX calculations.
A good data model ensures data integrity by enhancing accurate storage of data hence maintaining its integrity over time.

CONCLUSION.
It is important for power bi analysts to have SQL skills since they enable them to retrieve the stored data,interpret it and come up with reports that will impact decision making.Most organizations store their data in SQL databases therefore the power bi analysts have to retrieve the data from the databases for analysis.
Using the WHERE clause analysts are able to work on specific data.
SQL performs aggregation using functions such as COUNT,SUM,AVERAGE which enable analysts to summarise large sets of data and draw conclusions.
Analysts clean and transform data using query before building visualisations to feature in the dashboard.
They also use joins,subqueries and common table expressions to combine data from multiple tables for analysis.

ADVANCED SQL FOR DATA ANALYTICS:ADVANCED TECHNIQUES EVERY DATA ANALYST SHOULD KNOW.

maggy njuguna — Mon, 09 Mar 2026 16:24:13 +0000

Introduction
SQL(Structured Query Language) is an important tool in data analysis and is used to retrieve data ,manipulate and analyse stored data.Basic SQL techniques are important in data analysis however advanced techniques enhance analysis of complex data sets.The advanced techniques in SQL include;window functions,subqueries,ctes,stored procedures and indexes.

REAL WORLD PROBLEMS IN DATA

These are the products encountered by data analysts while trying to retrieve, manage and make decisions using data.Tools like SQL come in handy to solve these problems. Main data problems include;

Data quality and cleaning -data analysis involves using large data sets which are often messy,inconsistent and missing values and require cleaning and standardisation.Poor quality data can give inconsistent and unreliable results.
Data integration -Most data is stored in different tables and requires merging which if not done correctly may lead to inconsistencies.
Large volumes of data -organizations deal with large data sets which sometimes slow down query execution.
Data security - organizations deal with sensitive information which if not handled properly may lead to data violation and legal issues.

WINDOW FUNCTIONS IN SQL

Window functions perform calculations on related rows and keeps each row in the result unlike GROUP BY which groups the rows together.
A window function allows viewing of a group of rows.
The OVER() Clause is used in window functions.
Examples of window functions

ROW_NUMBER()
RANK()
DENSE_RANK
LEAD AND LAG
NTILE
RUNNING TOTAL
ROW_NUMBER.
It assigns a unique number to each row based on a condition.
In the case of 2 rows having similar values, this window function issues different numbers to the given rows.

RANK. This window function issues a ranking number to each row based on the given conditions. If two rows share the same value, they are assigned with the same value and the next number is skipped.

From this query rows with ties are issued with the same numbers and the next value is skipped.

DENSE_RANK Dense_rank is almost similar to rank and is used to issue ranks to rows but in relation to ties dense-rank does not leave gaps.The next number is issued after assigning a number to ties.

Example

Dense_rank does not skip the next number after handling ties.

LEAD AND LAG

LEAD -is used to compare the current rows with the next row.Like comparing the total quantity of products purchased by a customer with the customer's next order.If there is no next value it returns NULL.

Example

select order_id,order_date ,lead(order_date)OVER(order by order_date )as next_date, order_date-lead(order_date)OVER(order by order_date ) from orders;

LAG - is used to compare the current rows with the previous rows.Can be used to compare a customer's current order with the previous order.If there is no previous order it returns NULL.

select title,price,lag(price)over(order by price asc)as previous_price, price - lag(price)over(order by price asc)as price_diff from books;

NTILE It is used to divide the results set into a specified number of roughly equal parts.Example in dividing orders into 4 parts based on quantity.

Example

RUNNING TOTAL. This is a cumulative sum that adds the current value to previous values based on a specified order.

POINT TO REMEMBER

PARTITION BY. It is a part of the window function that divides data into groups before calculations. It divides the results in groups just like GROUP BY but does not remove rows.

HOW WINDOW FUNCTIONS PLAY A ROLE IN REAL WORLD DATA PROBLEMS

Improved query performance- data analysis involves using large data sets and window functions come in handy to ensure fast execution instead of using joins or subqueries.
Preserve data integrity -Window functions allow data analysts to view individual results and aggregated results.

Real word scenarios on the use of window functions.

Ranking products or customers When performing sales analysis for businesses a data analyst can use the windows function(Rank,Dense_rank) to give ranks to products or customers based on their performance.This helps the business identify the performance of products and decide on which products or customers need more marketing.
Calculating revenue growth
Using the running total a business is able to calculate cumulative revenue over time.
Identifying trends and performance.
Using the window functions businesses can compare the performance of their products, customers over time and guide them in making strategic decisions.
Compare current values with previous or next values.
Using lead and lag a company can perform trends in sales by doing comparisons on either the previous or next sales.

Subqueries
A subquery is a query written inside another query (Nested).Subqueries can appearin the following instances;

In the select clause.
In the Where clause.
In the From clause.

In the select clause
Subqueries in the select clause add an extra computed column to the results.
Example
select name,(select count(*) from projects p where p.employee_id = e.employee_id )as total_projects from employees_2 e;
In the where clause
Subqueries in the where clause allow the main query to filter results based on another's query output.
Example
select d.department_name ,e.salary from employees_2 e join departments d on e.department_id = d.department_id where salary =(select max(salary)from employees_2 where e.department_id =d.department_id);
In the From clause
A subquery in the from clause creates a temporary table from which data can be selected or filtered.
Example
select department_name,total_employees from(select department_id ,count(*)as total_employees from employees_2 group by department_id) as department_summary join departments d on d.department_id = department_summary.department_id;

Reasons for using subqueries

They allow filtering data in the main query based on results of an inner query.
Can be used in from clause for aggregation before proceeding with further analysis.

How subqueries solve real world data problems

Aggregate data comparison Using subqueries a data analyst can calculate the average performance of products and customers while the main query can identify products that are below or above that average.
Filtering data from complex conditions Subqueries can be used to perform calculations that can be filtered in other SQL queries.

Common Table Expressions(CTES)
CTES involves creating a temporary results set that only exists during the execution of the query.
CTES breakdown complex queries into simpler more manageable and readable parts.
CTES act like a virtual table which only exists during the execution of the main query.
Example
Listing employees earning more than 45000 using a cte

with high_earners as (select name,salary
from employees_2
where salary
>45000)
select * from high_earners;

CTES can be used to simplify joins.

Example
with total_orders as (select customer_id,sum(quantity) as total_quantity from orders group by customer_id) select first_name,last_name ,t.total_quantity from total_orders t join clients c on t.customer_id = c.customer_id;
Multiple CTES -More than one CTE can be used and they are separated by commas.

Example
with department_max as (select department_id,max(salary)as max_salary from employees_2 group by department_id), top_earners as (select name,employee_id,e.department_id,salary from employees_2 e join department_max m on e.department_id =m.department_id and e.salary = m.max_salary) select * from top_earners;

How CTES solve real world data problems

Temporary nature-CTES results are not stored permanently in database, they disappear after a query is executed hence they don't need maintenance.
Improving readability -CTES allow breaking down of queries into simple named steps instead of using nested unreadable queries.
Reusability -CTES can be referenced several times hence no need of writing the same subquery multiple times.
Performance optimization -complex nested queries can be turned into simple steps using CTES hence fast execution.

Key differences between subqueries and CTES.

CTES come before the main query while subqueries are inside the main query.
CTES are easy to read however subqueries can be long and messy.
CTES can be referenced several times while subqueries run once and cannot be reused.
CTES are used when the query is complex and repititive while subqueries are used when you need quick nested results.

Stored procedures
Stored procedure is a block of SQL logic stored inside the database.
Actions that can be executed using CTES include

Inserting data into tables.
Updating data in tables.
Delete data from tables.

Example
This is a procedure that inserts data into the clients table.

create or replace procedure add_clients( p_first_name varchar, p_last_name varchar, p_email varchar, p_contact varchar, p_city varchar )

language plpgsql
as $$
begin
insert into
clients(first_name,last_name,email,contact,city)
values (p_first_name, p_last_name,p_email,p_contact,p_city );
end;
$$;

How stored procedures solve real world data problems

Performance optimization -Using a call statement is faster than executing many lines of code.Because stored procedures are precompiled the database skips the compling steps hence faster execution.
Stored procedures enhance data security-Data base administrators can allow users to execute the stored procedures instead of allowing them to directly use the SELECT or UPDATE procedures hence protecting the data from any malicious acts.
Code reusability-Complex data procedures are written once and can be used across multiple executions.
Real world use case scenario.
- Banking -Transactions such as updating account balances,checking loan limits .
- Auditing - using stored procedures it is possible to track changes in your data. -Human resource department can use stored procedures to update staff records.

Indexes
Data analysts encounter complex and large data sets in their day to day jobs and querying this data sets can be slow and cumbersome. Indexes act as a road map for databases and allow the data base management system to quickly locate specific data rows without scanning the entire database .This ensures fast execution of queries.
create index idx_employees_2_name on employees_2(name);
Partial indexes indexes rows that meet a particular condition.
Indexes can also be used on joins.
create index idx_emp_dept on employees_2(department_id);
select e.name,d.department_name from employees_2 e inner join departments d on e.department_id = d.department_id where d.department_name ='Engineering';
How indexes solve data problems

Increases the speed of query execution- A search taking minutes can be reduced to seconds with indexes since the database only scans the specified rows instead of scanning the entire database.
Efficient sorting and grouping. Indexes store data in an sorted order which makes the ORDER BY,GROUP BY execution faster.
Speed up join operations -Indexes speed up the process of merging rows.

CONCLUSION
Data analysts face many data problems that hinder they ability to effectively analyse data .Some common data problems include poor quality data,data security and bias ,dealing with large data sets.Complex SQL techniques enable data analysts to overcome this data problems and clearly analyse data.Indexing increases query optimization and reduces query execution time.Advanced SQL techniques enable data analysts to clean and transform data for effective analysis.It is important for every data analyst to master the advanced SQL techniques.

JOINS AND WINDOW FUNCTIONS IN SQL.

maggy njuguna — Fri, 27 Feb 2026 19:21:32 +0000

SQL stands for structured Query Language and is the language used to communicate with a database.
What is a database ?
An organized collection of data stored electronically.
Data is stored in tables inside databases.
SQL is used to retrieve data,update and analyse data.

JOINS IN SQL

Joins are used in SQL to combine rows from two or more tables based on a common relationship(primary key)
TYPES OF JOINS

INNER JOIN It returns rows that are same from both tables.

EXAMPLE

From the records this are the matching rows from both tables.

LEFT JOIN A Left join returns all rows from the left table and the matching rows from the right(second) table. If there are no matching values,NULL values are returned from the right table.

EXAMPLE

RIGHT JOIN A right join returns all rows from the right table and the matching rows from the left table. If there is no match ,NULL values are returned for the columns from first(left) table.

EXAMPLE

From the records only four rows in the name column are returned, some departments do not have a match hence the NULL values are returned after performing the query.

Full join It combines the results of a left and right join. A full join returns all rows from both tables, matching rows that meet conditions and includes unmatched rows too.

EXAMPLE

NB Some databases like MySQL,MariaDB and SQLite do not support full join and instead a UNION operator is used to join a left and right join.

SELF JOIN In this join operation a table is joined with itself. It is used when you want to combine rows from the same table based on a related column.

Joins are very important in SQL since they allow combining of data from different tables,they also reduce duplication by allowing data to be stored in separate tables and they also improve data flexibility.

WINDOW FUNCTIONS IN SQL

ROW_NUMBER()
RANK()
RUNNING TOTAL
AVG() OVER()
ROW_NUMBER.
It assigns a unique number to each row based on a condition.
In the case of 2 rows having similar values, this window function issues different numbers to the given rows.

RANK. This window function issues a ranking number to each row based on the given conditions. If two rows share the same value, they are assigned with the same value and the next number is skipped.

RUNNING TOTAL. This is a cumulative sum that adds the current value to previous values based on a specified order.

AVG() OVER() It calculates average without removing rows.

POINT TO REMEMBER

PARTITION BY. It is a part of the window function that divides data into groups before calculations. It divides the results in groups just like GROUP BY but does not remove rows.

CONCLUSION
Window function and joins are very important in data analysis ,without which data analysis would be cumbersome.Joins allow you to combine data from different tables for effective analysis and window functions enable you to keep every detail of your data.

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI.

maggy njuguna — Sun, 08 Feb 2026 18:03:28 +0000

Introduction
Power BI is one of the most powerful tools designed for cleaning data ,creating reports and dashboards.It is preferred over other tools due to its ability to handle large data sets,create real-time dashboards and its beginner friendly.Power BI desktop is a free version provided by Microsoft.
Main uses of Power BI

Connecting data from different sources:web,excel,SQL.
Cleaning and transforming data.
Data modelling.
Data analysis using DAX.
Data visualisation:charts,filters,slicers.
Reports and dashboard creation.
Data automation, sharing and collaboration using Power BI service.

Data cleaning in Power BI
Data analysts work with complex data sets and cleaning data goes beyond deleting rows and renaming column.Power Bi provides a powerful ETL(Extract, Transform, Load) engine that plays a key role in cleaning messy data to ensure accuracy,consistency and effective analysis.Power BI shows column Quality and distribution which helps analysts identify inconsistencies in their data.Power query is used to clean data in Power BI.

Messy Data
Just like the name suggests messy data has this qualities

Blank rows and missing values.
Duplicate records.
Wrong data types.
Inconsistent text formats("kiambu","MOMBASA")

Removing duplicates
If your data must have unique values select the columns and remove duplicates.
Correct formatting
Your data should be formatted correctly.

Text: Employee Name, Country, Product Type.
Decimal Number: Market Price, Revenue, Gross profit.
Whole Number or Decimal: Farmer Code, depending on use.
Date: Planting Date, Harvest Date. Use Transform > Detect Data Type to auto-detect, then adjust manually if needed.

Removing errors and blank values
For numeric columns remove the errors but do not delete the blanks.
For text columns consolidate the null,errors to "Not provided",use consistent labels for the entire data.
Blank values can be replaced with 0 in numeric columns.
Do not delete rows unless it is necessary.

use Replace Values to change "Error" → "Not provided".

Standardising text columns

Trim to remove extra spaces.
Uppercase to capitalise each word.

Impacts of a well cleaned data

Accurate analysis. -Enhanced Power BI performance.
Proper data modelling. After cleaning your data, Use Transform > Detect Data Type again. Click Close & Apply to load your cleaned data into Power BI.

DAX in Power BI

Sounds like programming,right?
DAX stands for Data Analysis Expressions.
DAX is a formula language used in Power BI to build measures,create calculated columns and calculated tables to transform raw data into meaningful insights.
DAX mainly performs aggregation and iterator functions.
Aggregation Functions
Aggregation is the process of combining rows of data into a single value.
Common Aggregation functions.

SUM -This adds up all values in a numeric column. Example: Total Revenue Total Revenue = SUM('Kenya Crops'[Revenue (KES)])
AVERAGE-This adds up the mean of a column. Example: Average Market Price Average Market Price = AVERAGE('Kenya Crops'[Market Price (KES/Kg)])
MIN/MAX- Calculates the largest and smallest values. Example: Minimum Yield Minimum Yield = MIN('Kenya Crops'[Yield (Kg)]) Example: Highest Profit Maximum Profit = MAX('Kenya Crops'[Profit (KES)])
MEDIAN Returns the middle value of a column. Example: Median Profit Median Profit = MEDIAN('Kenya Crops'[Profit (KES)])
COUNT Counts numeric values in a single column. Example: Count Yield values Yield Count = COUNT('Kenya Crops'[Yield (Kg)])

Iterator functions
Performs row by row evaluation and gives a final result.
Examples

SUMX Evaluates an expression for each row in a table and then sums those results. Total Profit (SUMX) = SUMX( 'Kenya Crops', 'Kenya Crops'[Revenue (KES)] - 'Kenya Crops'[Cost of Production (KES)])
AVERAGEX Evaluates an expression for each row and then returns the average of those results. Example 2: Average Profit per Acre Average Profit per Acre = AVERAGEX( 'Kenya Crops', DIVIDE( 'Kenya Crops'[Profit (KES)], 'Kenya Crops'[Planted Area (Acres)])
COUNTX COUNTX counts rows where an expression returns a non-blank value. Example 1: Count profitable farms Profitable Farms Count = COUNTX( 'Kenya Crops', IF('Kenya Crops'[Profit (KES)] > 0, 1))

Logical Functions in Power BI
They are used to make decisions based on conditions.
Examples IF,Nested IF,OR function, AND function,IF WITH&&.

Example: Profit or Loss Classification (Measure)
Profit Status =IF( SUM('Kenya Crops'[Profit (KES)]) > 0,
"Profitable", "Loss"))

Dashboards in Power BI.

A dashboard is a carefully designed collection of charts that allows businesses to answer specific questions easily and quickly.A dashboard contains a one page high level view of key factors that tell a story at a single glance.Main elements of a dashboard include

KPI Cards (Revenue, Visits, Profit)
Trend lines (Sales over years)
Top / Bottom performers
Alerts (threshold-based)
Simple filters and slicers(date, region)
To create a good dashboard avoid too many visuals ,complex calculations and very large tables.
Users read dashboards from top to bottom and from left to right. The most important information should be placed at the top and towards the left.
The top section of a dashboard should contain KPIs. These should be displayed using cards.
visuals and should be immediately visible.
The middle section should contain trend and performance visuals.
The bottom section should contain supporting breakdowns and detailed comparisons.
Filters should be placed where they are easy to find, at the top or along one side of the dashboard.
Main Visuals used in a dashboard
Charts
Column chart,A column chart displays data using vertical bars to compare values across categories.It is used to compare values across categories.

Bar chart ,it is similar to a column chart but uses horizontal bars.

Line chart , line chart shows trends over time using connected data points.Example Gross profit over years.

Pie charts and Donut chart, these are best for showing percentage contribution of categories to a whole. Examples Revenue share by crop type. Best used when there are few categories (2–5).

Card visual, it displays a single aggregated value. Use cases: Total revenue and Total profit.

Matrix visual, it is similar to a pivot table with rows and columns. Examples: Revenue by county and seasons.

Scatter chart ,Shows relationship between two numeric variables. Examples: Cost vs revenue, Yield vs profit.

Map (Bubble map), it displays data using geographic locations with bubbles.Example Gross profit by Country. Interactive dashboard

Conclusion.

Effective analysis,accurate and consistent data is a reflection of a well cleaned data.DAX allows data analysts to transform cleaned data and give meaningful insights.A well designed dashboard enables stakeholders to make effective decisions.

SCHEMAS AND MODELLING IN POWER BI.

maggy njuguna — Sun, 01 Feb 2026 15:00:00 +0000

Data Modelling .

This process involves structuring,organizing and connecting data tables for effective performance.Designing a good data model is important since it makes reports accurate and easy to understand.To set up a data model in Power BI is done by opening the report view and go to Data Model.It involves bringing many data tables together and connecting the relationship between them.
A schema is a structure that shows how data is organized and related in a data model
The two common data models are Star Schema and Snowflake

Star Schema

This data model contains one central fact table and many dimension tables.The dimension tables are connected directly to the Facts table.
The fact table is at the center sorrounded by the dimension tables forming the star shape.
Star schema is the data model mostly used in Power BI because it is simple to understand,executes DAX Functions effectively and its great for queries performance.

Snow flake schema

In this data model the Fact table is connected to dimension tables and the dimension tables can have a few dimension tables connected to them. A dimension table is connected indirectly to the fact table through another dimension table.
It is less preferred because of its complex nature, slow performance and its low speed in performing DAX functions.

Key difference between Star schema and Snowflake Data models

Star schema contains a fact table sorrounded by dimension tables while snowflake contains a fact table connected to dimension table which are in turn connected to other dimension tables.
In star schema a single join is used to connect the fact table to dimension table while in snowflake many joins are required.

Relationships in Data models

This explains how tables are connected in a data model.

Types of relationships

1 to many(1:*)-One record in a dimension data relates to many records in a fact table.
Many to 1(*:1)-Same as 1 to many but viewed in the oppositedirection.
Many to many(:)-multiple records in one table match multiple records in another table.

Facts table

This table stores the key values for your analysis.It stores measurable and quantitative values and the values are mostly numeric.
Example :Revenue,Gross profit,total sales,Quantity sold

A fact table contains many records,used for calculations(SUM,AVERAGE,MIN,MAX)
A fact table has many rows and few columns.

Dimension tables.

This tables act like a look up table for the values in the facts table.
Example:Employee name,Month,Country ,Region,Product category
This tables contain textual data and are used for grouping and filtering data.
They have many columns and fewer rows.

Why is good data modelling critical?

Good data modelling ensures data accuracy by reducing data ambiguity and duplication.
A good data model improves performance by reducing the number of joins and relationships.Like in the case of star schema which has one fact table and a few dimension tables.
Simplified analysis -a well designed data model makes it easy to perform accurate DAX calculations.
A good data model ensures data integrity by enhancing accurate storage of data hence maintaining its integrity over time.

Conclusion.

It is important for data analytics to understand data modelling since it plays a critical role in data analysis, reporting and decision making.A good data model improves performance and provides accurate data insights.

Introduction to MS Excel for data analytics

maggy njuguna — Sun, 25 Jan 2026 13:46:16 +0000

Data Analysis is the process of analysing raw data to uncover hidden patterns, trends and actionable insights.Common tools used in data analysis include Excel,Power BI, SQL and Python.

Core components of Data Analysis.

1. Data Collection

This process involves gathering data from different sources. Data can come from surveys, databases, spreadsheets, websites, sensors, or business systems.

2. Data Cleaning and Preparation

Raw data is often incomplete, duplicated, or inconsistent. Data cleaning involves removing errors, handling missing values, correcting formats, and ensuring consistency.

3. Data Storage and Management

Data must be stored in a structured and secure way so its retrieval can be easy. This may involve spreadsheets, databases, or data warehouses. This process ensures data is reliable and easy to retrieve.

4. Data Analysis

This is the stage where data is examined to find patterns, trends, and relationships. It involves using statistical methods, formulas, and analytical tools.

5. Data Visualization

Data visualization uses charts, graphs, dashboards, and reports to present results clearly. Visuals make complex data easier to understand and help communicate insights to both technical and non-technical audiences.

6. Interpretation and Insights

At this stage, the analyst explains what the results mean. Interpretation connects the analysis to real-world situations and business goals, turning numbers into meaningful insights.

7. Decision Making

The final component involves using insights to support decisions. Data-driven decisions help organizations improve performance, reduce risks, and identify new opportunities.

How Microsoft Excel Can Be Used for Basic Data Analysis

Microsoft Excel is a popular tool used for basic data analysis. It is easy to learn, widely available, and very powerful for organizing and understanding data.It provides simple features that help turn raw data into useful information without needing advanced skills.

Basic Data Cleaning in Excel.

Data cleaning in Excel involves preparing raw data so it is accurate, consistent, and ready for analysis. Excel provides easy tools and functions that help beginners clean data efficiently.

Removing Duplicates

Excel allows users to remove repeated records using the Remove Duplicates feature. This ensures that each record appears only once, improving data accuracy.

Correcting Data Formats

Excel helps standardize formats such as dates, numbers, and text. For example, dates can be converted from text format to date format, and numbers can be corrected if they are stored as text.

Trimming Extra Spaces

Extra spaces can cause errors in analysis. The TRIM function is used to remove unwanted spaces before and after text.

Standardizing Text

Excel helps make text consistent using functions such as:

UPPER – converts text to uppercase

LOWER – converts text to lowercase

PROPER – capitalizes the first letter of each word

Correcting Spelling Errors

Excel’s Find and Replace feature is used to correct spelling mistakes or replace incorrect values across the dataset.

Using Data Validation

Data Validation restricts the type of data entered into a cell, reducing future errors. For example, limiting a column to accept only numbers or specific text values.

Data Sorting in Excel (Text, Number, Date)

Data sorting in Microsoft Excel refers to the process of arranging data in a specific order to make it easier to read, analyse, and compare. Excel allows sorting of text, numbers, and dates in both ascending and descending order.

Text sorting arranges data alphabetically from A to Z or Z to A.

Number sorting organises values from Smallest to Largest or Largest to Smallest.
Date sorting orders records from Oldest to Newest or Newest to Oldest.

Sorting helps users quickly identify patterns, trends, within a dataset.

Filtering in Excel (Text, Number, Date)

Filtering enables users to display only the data that meets specific criteria while temporarily hiding the rest.

Text filters include options such as Equals, Contains, Begins With, and Ends With.

Number filters allow filtering using conditions like Greater Than, Less Than, Between, and Top 10.
Date filters include options such as Today, This Month, This Year, and Between.

Filtering is essential for narrowing down large datasets to focus on relevant information without deleting any data.

Functions in Excel

Arithmetic Functions

Arithmetic functions perform basic mathematical calculations. Common arithmetic operations include addition, subtraction, multiplication, and division, which are used in formulas to compute values across cells.

Basic Data Analysis Tasks in Excel

Calculating Average Salary

Excel can be used to calculate the average salary of employees.

Excel Formula:

=AVERAGE(I2:I877)

This helps HR understand the general salary level in the organization.

Counting Employees by Department

Using COUNTIF, Excel can count how many employees work in each department.

Example:

=COUNTIF(I2:I877,"HR")

This helps HR see department distribution.

Finding Highest and Lowest Salary

Excel functions help identify salary ranges.

Formulas:

=MAX(I2:I877)
=MIN(I2:I877)

This is useful for compensation analysis.

Analyzing Experience Levels

Excel can calculate the average years of service:

=AVERAGE(R22:R877)

This shows how experienced the workforce is

Logical Functions

Logical functions evaluate conditions and return results based on whether those conditions are TRUE or FALSE.
The most commonly used logical function is:

IF – Returns one value if a condition is TRUE and another value if it is FALSE.
Using the IF Function
Example 1: Salary Category (Low or High)

HR wants to know whether an employee’s salary is Low or High.

Date Functions

Date functions in Excel work with dates and time values. They are used to calculate durations, extract parts of a date, and perform date-based analysis, such as calculating the difference between dates or determining deadlines.

PivotTables in Microsoft Excel

A PivotTable is a data summarization tool in Microsoft Excel that automatically sorts, counts, totals, or averages data stored in a worksheet. It allows users to reorganize and group data dynamically without changing the original dataset.

PivotCharts in Microsoft Excel

A PivotChart is a visual representation of data from a PivotTable. It updates automatically when the PivotTable changes, making it ideal for interactive analysis.

Types of Pivot Charts and Their Specific Uses
Column Chart

Use: Comparing values across categories

Bar Chart

Use: Comparing categories with long labels
Best for: Performance comparisons

Line Chart

Use: Showing trends over time
Best for: Monthly sales, yearly growth

Pie Chart

Use: Showing proportions of a whole
Best for: Market share, percentage contribution

Slicers in Microsoft Excel

A Slicer is a visual filtering tool that allows users to filter PivotTables and PivotCharts interactively using buttons.

Connecting Slicers to Multiple PivotTables

Connecting slicers allows one slicer to control multiple PivotTables and PivotCharts, ensuring consistent filtering across the dashboard.

Creating an Ineractive Excel Dashboard

An Excel Dashboard is a visual summary that displays key performance indicators (KPIs), charts, and insights.

Understanding version control:How Git and GitHub play a vital role.

maggy njuguna — Sat, 17 Jan 2026 12:32:20 +0000

Version control

This is a system that enables tracking changes made to files over time.It enables collaboration between people working on same projects without interfering with each other's work.Git and GitHub are essential tools in version control.
Git A version control system used to track changes made on files and also allows collaboration of teams dealing with same projects by recording every change made.
Git Bash It allows Windows users to fully utilise Git by acting as a command line.
GitHub An online platform that works with Git to manage and share code.It allows for many people to work on the same project without conflicts. It acts as a backup for projects being done and allows sharing projects with others.

Setting up git

On Windows Git Bash is used as a command line for Git.The following commands are used to configure your user name and email.
git config --global user.name"name"
git config --global user.email"email address"

The process of creating a folder and adding it to Git

Action Command
Create folder mkdir folder_name
Enter folder cd folder_name
Go back cd ..
Create file touch file.txt
Check location pwd
Check git status git status
Add to git git add folder_name
Commit git commit -m "message"

Initialising Git

Once you create a project folder initialise Git using this command
git init

Adding files to be tracked

Use this command git add filename

Pushing code to Git Hub

This is the process of uploading changes made on your projects from your computer to an online platform including GitHub and Git Lab.It is important because it enables you to share your work with your teammates,it updates the project repository with the latest changes and also keeps a backup of your work online.

Commands to push code

Add files to staging area This is choosing what changes of your code or project you want saved.It is done using this command

git add filename
git add .

Committing your changes This involves saving a clear snapshot of your project for easy collaboration,tracking progress and avoiding mistakes.The following command is used

git commit -m "a description of what you changed"

Pushing changes to a remote repository Involves sending changes saved(commits)from your computer to a remote repository (GitHub).Done using this command

git remote add origin git@gitHub.com:username/repo-name.git

push your changes to GitHub

git push -u origin main

Pulling code

This is the process of downloading latest changes made on your project from an online repository to you computer(local).
Done using this command

git pull

Conclusion
Understanding version control system is very important for data scientists and analysts.Git and GitHub enable data analysts to effectively manage their projects and share their work with others.