DEV Community: Joseph Nganga

Connecting power Bi to sql databases

Joseph Nganga — Sun, 22 Mar 2026 21:11:44 +0000

what is power Bi

Power BI is a business analytics platform. It allows one to analyse raw data and transform it into interactive visualizations and actionable insights.

Power BI allows a user to connect to various data sources, such as cloud services, Excel, and various databases. Once connected, a user can clean and model the data, then create interactive and dynamic dashboards and reports that enable the final user to get an overall picture of the business and make insightful decisions.
Connecting to a live database provides the following advantages:

Centralized data management
Real-time analysis, especially for data that changes quickly.
Managing complex relationships in the data. We shall focus on connecting Poer BI to SQL databases.

What are SQL databases

SQL databases are a relational database management system. They store data in structured tables made up of rows and columns. They allow a user to query and manipulate the data.
They provide:

Scalability to allow growth of datasets
Data integrity and reliability.
Efficient querying for quick retrieval
Structured data that maintains its consistency.

Connecting Power BI to a local database.

A local database is a database within your local machine, meaning the data is stored in your pc.

Open Power BI and click the 'Get Data' icon on the ribbon

It provides a list of sources, so choose the PostgreSQL option

You will now be required to enter the database details, which will allow Power BI to access the database as illustrated below.

The details you provide are login/database credentials that allow you access. They include username and password.
Once connected, the navigator will list tables present in the connected database.

Loading data to Power BI: The tables provided in the navigator section contain select tickboxes, where you will click to add the table to the list, which will be loaded to Power BI. If the data does not need transformation, click the load button to complete the loading process.

Connecting Power BI to a cloud PostgreSQL database.

Create a cloud service that provides a database hosting service. We will use Aiven as our preferred service provider. On your browser, go to aiven.com, create an account, then start a PostgreSQL service.

Note the following details of the PostgreSQL service:

Note the CA certificate, click the download option

3.Link the service to Power BI
Start Power BI application, on the home ribbon, click 'Get Data' click more and look for the PostgrSQL option

4.Proceed to enter the aiven service details you took note of as prompted by Power BI.

On your left, the navigator shows tables available on the database you just connected to. to load the data, click the checkbox next to the tables for the tables you wish to load. If the data needs cleaning, click the transform data option on the bottom right corner to clean the data. Once done, click the load option to load data and proceed.

Now that the data is loaded and calculations can be done, lets explore the relationship between the data. Data in each table has a primary key. This key is a unique identifier for each specific set of data which annot be replicated.
Since this key is used once, we can use it in different tables(it now becomes a foreign key) to create relationships. For example, you make gave a customer table which contains customers information. a customer id, would be the primary key, as its the only unique identifier of the customer in a table with many customers.
In a different table like orders table, we will refer to this primary key to identify the individual customer. When we reference this key, we refer to it as a foreign key.
We now have a relationship between different tables as illustrated in the table below.

We can also create relatioships manually when Power BI does not automatically create them.
Click modelling on the home ribbon and select manage relatioships.Select new relationship then proceed to select the type of relationship.

Select the 'from table' you wish to link and select it's primary key.
Select the 'to table' and then select they foreign key and save.

You have successfully created a relationship.

importance of relatioships.

They allow filtering of related data. If you select one specific set of data in a chart, other linked data charts and tables will automatically filter and show selected data.

importance of SQL

1.Data retrieval.
SQL queries allow filtered and selected data only, avoiding execessive
data that may corrupt final results.

Filtering data. This is handles by the database thus moving the heavy load lifting from your personal pc.
Transformation of data. SQL helps transform large data sets, which would slow down Power BI.

JOINS & WINDOW FUNCTIONS.

Joseph Nganga — Tue, 10 Mar 2026 13:43:05 +0000

Joins are sql clauses that combine two or more tables using a related column that exists between the tables. That way, data that is stored in different tables but is related is retrieved and can be processed and analysed to give meaningful results.
There are several types of joins as follows:

1. Inner join

An inner join returns only rows where there is a match in both tables based on a condition. Consider the following example;

consider a transaction table and a customer table.

we use;
select *
from transaction inner join customers
on transaction.customer_id = customers.customer_id

the customer_id i this case is the common column between the two tables;

on exacution, the new table looks as follows:

There is a return on every instance the customer_id has a match on bith tables.

2. Left outer join

The left outer join returns all rows from the left table and matching rows from the right table. Rows that do not match from the right table return nulls.

3. Right outer join

The right outer join returns all rows from the right table and matching rows from the left table. Any non matching row from the left returns a null.

4. Full outer join

A full outer join returns all rows from both tables with null values where no match exists.

5. Cross join

This join combines every row from the first table with every row from the second table.

WINDOW FUNCTIONS.

These are sets of tools that perform calculations across a set of rows that are related and do not collapse the results into a single output row, unlike traditional aggregate functions.
Their main components are as follows;

Over clause It defines the window of rows over which the function operates.
Partitioning by This clause divides results into groups e.g divide by region or department. 3.order by This clause specifies the order of rows within each partition.

Common window functions

Row number()
It assigns a unique sequential number to each row within a partition.
Rank()
It assigns rank within a partition when gaps occur. For example when there is a tie.
Dense rank()
It assigns rank without a gap.

#Using Power BI to translate messy data, dax and dashboards into actionable insights.

Joseph Nganga — Mon, 09 Feb 2026 16:28:52 +0000

In the business world, decisions are made using insight obtained from large data sets. Real-world data is often chaotic, so the process follows a structured workflow that involves: cleaning the messy data, properly modelling it, then applying intelligent calculations with DAX in order to create interactive dashboards that highlight what matters the most.

Handling Messy data.

Power BI's Power Query editor is the main tool we use to clean data, since raw data is rarely clean. It often comes with missing values, inconsistent data formats, duplicate values, and mismatched columns as well.
The following actions are taken to clean our data:

Removing duplicates by clicking transform data, highlighting the suspect column(s), and clicking remove duplicates on the home option.
Managing missing values by filling the blanks with averages or the median for the data.
Standardizing text. using proper case and uppercase/lowercase where applicable. Work becomes easier and more accurate when we do as much transformation as possible at the transformation stage.

Building a data model.

We now create a logical structure of tables that defines how different tables are related and connected in order to support efficient analysis and reporting. This is called a schema, with the most commonly used one being called a star schema.
On the left pane, we click model view, then create relevant relationships with the fields. The fact table is always at the centre, while other dimension tables are connected via relationships.
There are one-to-one relationships, one-to-many relationships and hierarchies.

DAX

Data analysis expressions create meaningful and insightful metrics from cleaned data. For example, to get total sales data, we use the following:
Total Sales = SUM(Sales[Amount])
DAX hardcodes business metrics like costs, performance of products and others so that dashboards not only show numbers but answer big business questions like products with the best margins, growth trends, and seasonal adjustments, among other key metrics.

Dashboards.

Dashboards are the final product of the entire process, and they are decision tools.
They tell a story and give information on the most important KPI's. Thus, we use these kpi's and cards with conditional formating . We must also include interactive elements like slicers. We include graphs that focus on trends , give significant comparisons as well as edge cases and outliers. Dashborads therefore explain data in a visual form to non technical users.
The dashboard must always be easy to understand, fit in one page therefore avoiding clutter.

Basic data analytics using Microsoft excel

Joseph Nganga — Sun, 25 Jan 2026 16:21:18 +0000

Microsoft excel, is a powerful tool when it comes to data analysis. Data analytics is the process of collecting data, cleaning, processing and visualizing the data to provide insights or enable making of data-driven decisions.
We manipulate data using functions such as sorting and filtering, removing duplicates.
We can use different formulas and functions like sum, average, count, vlookup, and other functions to manipulate the data.
We use pivot tables to instantly summarize large data sets.

Organizing data.

Before we can analyse any data, we first need to clean and organize it, which ensures the data is uniform so functions and calculations give correct information.

in the baove image, we see that the data is in columns and rows that are uniform for each criterion. This makes it easier to do complex calculations such as averages, sums, and even create pivot tables as well as interactive dashboards from the data.

sorting and filtering.

To arrange data in a coherent, understandable manner, we use sort and filter.

On the ribbon, click home
Go to sort and filter, from here you can sort in ascending or descending order.

Data analysis

We use functions to analyse a data set. From there, we deduce patterns and trends. We understand what the large data set tells us.
These functions include the following,

Sum. This adds value to give the total of the specified range of figures.
Average. This gives the average of the numbers that are provided in the specified range.
Vlookup. This function helps us look for a value and match it to another value.
Logical if, and/or help us create complex filtering criteria.
A pivot table is one of the most powerful features in Excel, as it automatically summarizes large data sets and groups them by categories. It calculates totals, averages, counts, and other key metrics instantly. Below is an example.

It allows us to view a summarized analysis of a very large data set in one or more Excel sheets.

Presenting data for decision making.

After the analysis, we need to present the results in an interactive way that allows users of the information to make informed decisions. We create such interactive dashboards using slicers.
To create slicers, go to Pivot Table Analyze and click Slicers as shown below:

The final presentation.

We now create an interactive dashboard that summarizes the most important metrics driving decision-making. Below is an example of an interactive dashboard:

WE have now done an informative data analysis project in Excel, from cleaning information, sorting, and analyzing to presenting information that enables clear decision making.

Code, Version control and Git.

Joseph Nganga — Sun, 18 Jan 2026 09:52:57 +0000

Hey, in the digital era we're in today, projects have gotten complicated, and we need a place to store the code/ project, a standard procedure to track changes and improvements we make to the project. This gets even more complicated when the project is not just an individual one, but involves many developers' contributions to the project.
So, how do we track who made what change, what version of the project is the latest, what changes have been made, and what are the new updates?
Enter a version control system.
The version control system records all changes made to a file over time.
When multiple developers are involved, it allows them to work together on the same project and prevents individuals from overwriting others' work.
The most widely used version control system is called Git. It's a history of all your projects. It contains an earlier version of your project, then it records changes made to the project, records who made the changes, and the time the changes were made. While git does all this on your local machine, we can alternatively do all this on Github, a cloud-based service.

How to set up GitHub.
Create a free account on
Next, download Gitbash, a command-line module on your local pc. It is used to manipulate/ edit projects on GitHub.
Gitbash allows you to write git instructions thus directly interact with GitHub, where your projects are stored.

On your gitbash, you first set it up to connect to your github by telling it who you are, as follows:

git config --global user.name "Your Full Name"
git config --global user.email "youremail@example.com"

To confirm the details, you run the following command:

git config --global --list

We then need to link our git bash to our GitHub, using an ssh key.
on our git bash, we generate this key using:
ssh-keygen -t ed25519 -C "your_email@example.com"

We then turn the key into an agent using: eval "$(ssh-agent -s)"
command.
We then add the ssh key agent to git bash using: ssh-add ~/.ssh/id_ed25519

Creating a repository.

On your git bash, use the command 'mkdir' followed by a name to create an empty directory, and cd followed by the name to move into that directory. In this directory, you can write code. All this is done on our local pc. Therfore to push this code to GitHub, we use the command 'git add.'
This tells git to stage all changes made to your file/directory. Once the command is accepted without error, we use the command 'git commit' and add a commit message(notes or explanations). We will use this on GitHub to track changes and give explanations for what changed and why. Finally, we use the command 'git push' to push all our changes to GitHub.

Tracking changes and pulling code.

If the project has contributions from different developers, we use the command 'git pull' to download changes made on the project to our local pc
before we push, to avoid merge conflicts. This has the advantage of also showing us what changes were made to the project by other contributors using the command 'git status'