DEV Community: Ngigi nyawira

#Connecting Power BI to SQL Databases: A Complete Guide

Ngigi nyawira — Sun, 15 Mar 2026 20:39:52 +0000

Introduction: PowerBi and SQL databases

What is PowerBi? It is a business intelligence platform that helps transform data from various sources into interactive, actionable insights, reports and dashboards. At the heart of most powerBi deployments is an SQL database(Structured Query Language) - such as PostgreSQL, MySQL, and others are the gold standard for storing, organizing and retrieving data.

Connecting PowerBi to a database gives analysts a live, reliable pipeline from raw operational data to polished dashboards. Instead of exporting CSV files manually and re-importing them, a direct database connection means the reports refresh automatically, dashboards reflect current data, and analysts spend less time wrangling files and more time generating insights.

Connecting to a local PostgreSQL database

Step 1: open power Bi and select get data

launch Power Bi and on the home ribbon, click on get data button. A dropdown appears select more to open the full source browser.

Step 2 : Choose postgreSQL database.
Navigate to the database category on the left the select PostgreSQL Database and click connect.

Step 3 : Enter the server and database details
A connection dialog appears asking for the server and database name. For local postgreSQL instance, enter localhost as the server. If your PostgreSQL is running on a non-default port, append it with a colon (for example, localhost:1234). Enter the name of your target database in the Database field.

Step 4 :Enter credentials
Power Bi then asks you to authenticate. Enter your postgresql username and password, then click connect.

Step 5 : Select and load tables
A navigation panel appears showing all the tables and views in our database. Check on the tables you want to work with then click load to import the data or transform data to open Power query editor for cleaning.

Connecting Power Bi to a cloud database such as AIVEN PostgreSQL.

Aiven is an open source data platform that provides database management services for a wide range of popular open-source data technologies. Instead of hosting data in local computers, organizations host databases in the cloud

Step 1. Login in to Aiven and getting connection details.
Login to Aiven at [https://console.aiven.io/login]

Step 2. Download the SSL certificate
This certificate ensures secure connection between Power Bi and the database. When you connect to a cloud-hosted database, your data travels over the public internet between your machine and the cloud server. This certificate tells your local machine to trust Aiven ,without it your connection either fails or falls back to an insecure mode.

Step 3. Establish connection in Power Bi
Follow the same steps get data> postgreSQL database as before.
in the connection dialog ,enter Aiven host and port in the server field, and enter database name.

Loading tables and building the data model

Power BI automatically detects relationships between tables based on matching columns based on the primary key & foreign keys. You can also create them manually by dragging a column from one table onto the matching column in another.

Why SQL skills matter for power Bi analysts

Bi analysts are not just visual thinkers, they are fluent in SQL. SQL give analysts a significant advantage at every stage of the Bi workflow.

1. Data retrieval - with SQL you can write a query that pulls only the columns and rows relevant to your analysis.

2.Total Data Control: You are not limited to what someone else has exported for you. With SQL, you can reach into the database yourself and grab exactly the data you need for a specific report.

3.Complex Logic is Easier: Some data cleaning tasks—like handling duplicates or complex date logic—are often faster and more readable in SQL than using Power BI's "Power Query" (M language).

4.Industry Standard: Almost every company uses a SQLL-based database (like Snowflake, SQL Server, or Oracle). It is the universal language of data that stays with you even if you switch from Power BI to another tool.

5.Join and prepare data : Complex multi-table joins, conditional logic, and derived columns are often easier and more performant to express in SQL than to build through Power Query's graphical steps.

Conclusion

Connecting Power BI to SQL databases — whether local PostgreSQL instances or cloud-hosted services like Aiven — is a foundational skill for any serious BI analyst. The connection process is consistent: open Power BI Desktop, navigate to Get Data, select the correct connector, supply the host details and credentials, and use the Navigator to choose your tables.

Once loaded, building a schema with well-defined relationships between your fact table (sales) and dimension tables (customers, products, inventory) is what transforms a set of raw tables into a model that Power BI can reason about. And underpinning all of it — from efficient data retrieval to pre-built aggregations — is SQL, the language that lets analysts speak directly to the database before Power BI ever gets involved.

A TECHNICAL DEEP DIVE INTO SQL JOINS AND WINDOWS FUNCTIONS.

Ngigi nyawira — Mon, 02 Mar 2026 13:17:08 +0000

Relational databases are built on relationships between tables. Joins and window functions enable data retrieval and analytics. Joins combine data across tables, window functions enable row-level analytics without collapsing result sets.

SQL joins

what is a join?
A join combines rows from two or more tables based on a related column (usually a foreign key)

Types of joins

INNER JOIN

Returns only matching rows from both tables
eg ; show employees with their department name.
Only matching rows appear. If an employee had no department, they would be excluded.

SELECT 
    e.employee_id,
    e.name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

LEFT JOIN

Returns all rows from the left table and matched rows from the right table. Non matches become NULL.

SELECT 
    d.department_name,
    e.name
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id;

Explanation; every department appears. If a department has no employees, employee columns are NULL.

RIGHT JOIN

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

Similar to a left join. The only difference is the right table is the base table

FULL OUTER JOIN

Returns all matching rows between two tables, all non matching rows from the left table, and all non matching rows from the right table.
where there's no match, the missing side appears as NULL.

Example Scenario Assume:

Some departments have no employees
Some employees are incorrectly assigned to non-existent departments
Some projects exist without employees assigned

A FULL OUTER JOIN helps detect data gaps.

SELECT 
    d.department_name,
    e.name AS employee_name
FROM departments d
FULL OUTER JOIN employees e
    ON d.department_id = e.department_id;

CROSS JOIN

Join operation that combines every row from the first table with every row from the second table, producing a result known as the Cartesian product. Unlike other join types (like INNER JOIN or LEFT JOIN), it does not require a join condition using an ON clause.

If We Have: 4 employees 3 projects Result = 12 rows

SELECT 
    e.name,
    p.project_name
FROM employees e
CROSS JOIN projects p;

WINDOW FUNCTIONS

These are the most powerful analytical features in SQL. They allow you to perform calculations across related rows while preserving individual row detail.

basic syntax;

FUNCTION() OVER (
    PARTITION BY column
    ORDER BY column
)

Key components:

OVER() → defines the window
PARTITION BY → divides rows into groups
ORDER BY → defines ordering within the group

RANKING EMPLOYEES WITHIN DEPARTMENTS

SELECT 
    e.name,
    d.department_name,
    e.salary,
    RANK() OVER (
        PARTITION BY d.department_name
        ORDER BY e.salary DESC
    ) AS salary_rank
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

What happens

Data is split by department
within each department, employees are ordered by salary
A rank is assigned.

When to use window functions

Use them when you need;

Ranking within groups
Running totals
Moving averages
Department comparisons
Row-level analytics
Gap analysis

Window functions analyze rows without collapsing them.

Partition by defines grouping logic
Order by defines sequence for calculation
RANK(), ROW_NUMBER(), DENSE_RANK() handle ranking.
SUM(), AVG() OVER() enable contextual aggregation.
They are essential for analytical reporting.

Summary

Joins connect data while window functions analyze connected data.

How Analysts Translate messy data, DAX, and dashboards into action using Power BI.

Ngigi nyawira — Sun, 08 Feb 2026 18:18:35 +0000

The Analyst's Dilemma.

Analysts face a constant challenge of transforming raw disorganized data into clear, actionable insights that drive business decisions. This translation process—from messy data to meaningful dashboards to measurable action—represents the core value proposition of modern business intelligence. Power BI has emerged as a premier platform for this transformation, providing the tools necessary to navigate this journey systematically.
The Reality of Data Sources

Modern organizations rarely benefit from clean, well-structured data. Analysts typically encounter:

Multiple disconnected systems (CRM, ERP, spreadsheets)
Inconsistent formatting and naming conventions
Missing values and duplicate records
Time-series data with irregular intervals
Unstructured or semi-structured data sources

Phase one: Power query

This is a data processing / transformation engine capable of performing a wide variety of ETL tasks and allowing users to "mash-up" data across sources. It basically connects power bi to your external data.

Get data>select data source> connect.

Here the analyst does some data profiling and assessment.

Transformational Patterns:

Standardization: Enforcing consistent formats for dates, currencies, and categorical data
Pivoting/Unpivoting: Restructuring data from wide to tall formats for analysis
Fuzzy Matching: Combining datasets with imperfect key matches
Custom Columns: Creating calculated columns during the import phase
Parameterization: Building dynamic data source connections for refreshed analyses

Phase two: Build smart calculations using DAX (Data Analytics Expressions)

DAX is how you make your data answer business questions
Without DAX, you just have raw data. With DAX, you get insights, trends, comparisons, rankings, and alerts that actually drive decisions.

DAX concepts

Measures vs columns The former are calculations done on the fly (eg: total profit ) The latter are permanent additions to your data table ( eg: creating a new column that calculates profit which is revenue - cost to every row)

Bottom line: DAX transforms your cleaned data from what happened to what does it mean and what should we do about it?

Context is Everything DAX automatically understands what you're looking at:

If you're viewing "Electronics" category, DAX knows to only calculate for electronics

If you drill down to "January," it recalculates for just that month

Time Intelligence The most valuable DAX feature:

Year-over-Year comparisons
Running totals
Moving averages (like 30-day sales trends)

Phase 3: Making Dashboards That People Actually Use

This is where insights become visible. The goal is to create dashboards so clear that anyone can understand them in 30 seconds.

The pyramid structure.

Top: Key KPI's
Middle : Trends and comparisons
Bottom: Details for investigation
Also include slicers on the side to act as filters.
Ensure that every visual answers a question

Phase four: Dashboards to action

This is where insights make an impact and trigger real world action. Here, Power BI connects directly to business processes—sending notifications when metrics hit critical thresholds, generating tasks in Teams or Outlook, updating CRM records, or exporting data to other systems. The ultimate measure of success shifts from how many people view a report to what concrete actions they take because of it, ensuring that every insight translates into a tangible business outcome.

Conclusion: The Analyst as Decision Accelerator

The journey from messy data to meaningful action is not about fancy charts or complex formulas. The decision makers do not even care about the process of how you got to make such insights, it's in the decisions that the dashboard enables.
Your job isn't done when the dashboard is built. It's done when someone looks at it and says, "Now I know what to do next."

Data modelling for Dummies: Because your data shouldn't live in a dumpster.

Ngigi nyawira — Mon, 02 Feb 2026 18:09:29 +0000

If your spreadsheet is a mess, like a teenagers room with clothes all over the floor then you are in the right place.

What is Data Modelling

Designing how your data fits together before you build anything. It's the blueprint that prevents your data from becoming a messy pile.

It translates complex business requirements into technical, organized data structures to improve accuracy, efficiency, and data quality. Common stages include conceptual, logical, and physical modeling, which guide database design, analytics, and data management.

What are schemas?

Schemas are a way to organize your data in the spreadsheet so it does not look like a dump. Schemas, for your spreadsheet are very important to keep your data organized.

A schema is basically a blueprint for your data. It defines:

The tables you have
The columns in each table
The type of data in each column(eg; text, number)
How the tables are related

Types of schemas

Star schema

Think of it as the ultimate dashboard-friendly data structure. It's designed specifically for fast queries and easy analysis, like the perfect spreadsheet layout for Power BI or Tableau.

Identify a fact table: foundation of the data warehouse

Snowflake schema

A snowflake schema is just a star schema with extra organization.
The analogy:
Star Schema = All your clothes in one drawer
Snowflake Schema = Socks drawer + shirts drawer + pants drawer

Why Use It?

Less Duplication (Save storage space)
More Accurate (One place to update "USA" to "United States")
Normalized (Database nerds love it)

Drawbacks:

More Complex (More tables = more confusion)
Slower Queries (More joins = slower results)
Harder for Users (Business users get lost)

When to Use It
✅ Good for:

Data Warehouses (where accuracy matters more than speed)
Complex hierarchies (Country → State → City → Store)
Storage is expensive (saving money on disk space)
Frequent updates (update once, fix everywhere)

❌ Bad for:

Power BI / Tableau (they prefer star schema)
Self-service analytics (too many tables confuse users)

Real-time dashboards (too slow)

Final Word: Data Organization Made Simple

At its core, data modeling is about giving your data a clear, organized home. Whether you choose the speed of a star schema for dashboards or the structure of a snowflake for complex systems, the goal is the same: turn raw chaos into reliable insight. Think of it as building with instructions instead of guessing—you save time, prevent errors, and ensure everyone understands what they're looking at.

For most teams, especially those using tools like Power BI, the star schema is your best friend. It’s built for speed, designed for clarity, and trusted by analysts worldwide. Start with a simple blueprint, organize around facts and dimensions, and watch as messy spreadsheets transform into trustworthy answers. Your data shouldn't just exist—it should work for you.

Welcome to Data World! Your Passport is Microsoft Excel

Ngigi nyawira — Sun, 25 Jan 2026 15:29:18 +0000

ver looked at a spreadsheet and seen more than just rows and numbers? Have you sensed there’s a story, a secret, or a powerful insight hiding inside, just waiting to be discovered? Welcome. You’ve just stepped into Data World, a place of curiosity, clarity, and smart decisions. And the best part? You already have your all-access passport: Microsoft Excel.

This isn't about dry formulas or boring tables. This is your invitation to an adventure where you go from simply storing information to truly understanding it. To show you just how powerful your passport can be, we're going to embark on a real-world mission together. We’ll use a sales dataset—complete with realistic quirks and questions—as our map.

Ready to stamp that first page? Let’s go!

Destination 1: Data cleaning

Imagine arriving in a new city with a messy suitcase, you can't explore until you unpack and organize! Data is the same. Our first stop is Data Cleaning.

our cleaning toolkit

Finding Duplicates: We’ll use

Data > Remove Duplicates.

Is an order an exact copy of another? Gone!

Fixing Broken Dates: Are OrderDates actually stored as text? We’ll make them real dates Excel can understand.

Destination 2:Data analysis (The main Exploration)

Here is the fun part, asking questions. Here we move from staring at a wall of numbers to actually seeing patterns. It's all about visual cues and isolating specific information.

Using filters.

This tool allows you to hide the noise and focus only on the rows you care about.
in the image below we only want to work with the guys who are not full time employees.

Conditional formatting (visual cues)

This tool automatically changes the appearance of cells based on their value
in this case below, we highlighted the cells that include marketing and have only females.

Excel functions cheat sheet

The big five (Basic Math)

These functions follow a simple pattern =Function(range)

=SUM(G2:G10) Adds all the numbers in the selected range
=Average(G2:G10) Calculates the mathematical mean
=COUNT(G2:G10) Counts only cells containing those numbers
=MAX(G2:G10) Finds the highest value in the selected range
=MIN(G2:G10) Finds the lowest value in the selected range

Logic and cleaning

These help you make decisions or fix messy data
=IF(Condition,"yes","no"): the "decision maker."

=IF(M2>6,"great","poor")

=CONCAT(A1,"",B1) Glues texts together. It can turn "John" in A1 and "Doe" in B1 into "John Doe"
=COUNTA(A1:A10)Counts all cells that are not empty(including texts and numbers)

Lookup and References

When you have two lists and need to connect them, these are your bestfriends.
=XLOOKUP(value,lookup_col,return_col): eg lookup
"employee ID" AND return their "Name."
=Vlookup(value,table,col_index,False)

Conditional Functions

=IFS condition allows you to test multiple conditions in a cleaner sequence
eg: =IFS(A1>=90,"A", A1>=80, "B",A1>=70,"C")
AND/OR
These are tucked inside an IF statement to check mltiple things at once.
AND:All conditions must be true.

OR:At least one condition must be true.

Conditional math (SUMIF, COUNTIF, AVERAGEIF) These perform calculations only on cells that meet a certain criteria =COUNTIF(A:A,"PENDING") Count cells that match a criteria =SUMIF(category, "food", Amount) Adds values in a range based on a condition =AVERAGEIF(dept,"sales", salary) Average values based on a condition. #Destination 3: Data Visualization (The "Photo Album") You’ve explored the ancient ruins and bustling markets. Now you need to show your friends back home! Data Visualization is your photo album. To create a pivot table, go to insert>pivot table> create a new worksheet>then select the data you want to visualize.

Our Visualization Toolkit:

Charts That Tell Stories:
To insert a chart go to pivot table analyze>

A Line Chart to show revenue trending up over the months.
A Stacked Bar Chart to compare which channels (Online, Retail) perform best in each region.
A Pie Chart (used sparingly!) to show the product category mix.
Slicers: These are like interactive filters for your charts. Click "Asia" on a slicer, and every chart on the page instantly updates to show only Asian data. It feels like magic.

We’ll build a one-page Dashboard—a beautiful, interactive control room where anyone can see the key insights at a glance.
open a new sheet on excel> name it dashboard> add all your KPI'S> Then add all you charts and slicers.

You’ve explored the ancient ruins and bustling markets of your data. Now you need to show your friends back home the best photos and tell the story! Data Visualization is your photo album, and a Dashboard is your curated, interactive exhibit.

Let's look at a real example—an Employee Performance Dashboard (like the one pictured). In one glance, we can see:

The Big Numbers (KPIs): Total Salary, Total Bonus, Average Age, and Average Performance Score are right at the top. These are the "headline" metrics every manager wants to know.

The Breakdowns (Charts): Below, charts answer specific questions:

"Which department costs the most in salary?" (The bar chart for "Total salary by Department and Gender").

"What's our most common education level?" (The "Education Level" pie/donut chart).

"How is performance trending over time?" (The "Average Performance Score by Hire Date" line chart).

This is the power of a dashboard: It turns 1,000 rows of employee data into a single, actionable page.

Bon Voyage, Data Explorer!

And there you have it—your stamped, validated, and ready-to-use passport to Data World. We started with a simple spreadsheet and transformed it into a living, breathing command center. From the essential clean-up at the Arrival Gate, to the thrilling exploration with PivotTables, to building your own interactive Dashboard, you've traveled the full journey.

Remember, data analytics in Excel isn't about memorizing every function. It's about a mindset: curiosity, clarity, and the courage to ask questions. You now have the core tools:

The clean slate: Turning messy data into a reliable foundation.

The detective's lens: Using PivotTables and functions like SUMIFS and XLOOKUP to uncover truths.

The storyteller's canvas: Building charts and dashboards that make insights undeniable and actionable.

Your Excel passport is not a final destination, but a launchpad. The same logical steps you learned here—clean, analyze, visualize—apply whether you're looking at sales figures, website traffic, personal budgets, or community survey results. Data World is all around you.

Git and GitHub: A Beginner's Guide

Ngigi nyawira — Sat, 17 Jan 2026 00:56:25 +0000

What is Git?

It is a free, open-source, version control system that tracks every change you make to your files. Think of it as a "save system" you literally have the ability to save multiple versions of your project.

Version control and it's importance

Also know as source control or revision control is a system of tracking and managing files (especially code) over time.

why is it essential?

Safety net: you can quickly roll back to a prior functional version of your code if you make a mistake and ruin it.
Collaborations: several people can simultaneously work on the same file. The system enables them to merge their modifications.
Traceability: Each file modification is recorded with a timestamp, a description, and the individual's name.
Experimentation: Ability to test out a new feature without affecting the main project, you can make a "branch"- a different copy of the code. If it succeeds, you merge it; if not you remove the branch.

How to push code on GitHub.

Step 1: Create a local repository

To initialize a new Git repository we use the command line below.

git init

To check the file you want to push, we use the command line below, note that the files will be shown in red

git status

The next step is to add the files to the folder, use the command line below;

git add .

Then check on the files status, it shows up in green to signify that they've been added. use the command line;

git status

Commit it locally, use the command line;

git commit -m "Project added"

Push code to the GitHub Repository: Copy the URL that is generated after creating a new repository on GitHub then use the command line below.

git remote add origin (then paste your URL)

The next step is to push your code to your GitHub, It will bring a pop up tab where you have to sign in to your GitHub Account.

git push origin master

Below is an image of the files on GitHub.

How to pull code from GitHub

step 1

Clone your repository by navigating to your repository and copying the link the paste it after the below command line on git bash

git clone

After that navigate to the folder for directory command use the below command line:

cd Git-Assignment

step 2

create a new branch

git checkout -b update-name

step3

Open repository in your editor and change the name, in our case we will use Vscode. use the command line:

code .

add your changes and then check the status using the below command lines respectively;

git add .
git status

Navigate to your GitHub account for the pull request.

Track changes using Git

When tracking changes, you are telling Git to watch specific files and record their history at specific moments in time. There are 3 zones to track a file.

Working directory : editing files on your laptop. Git sees them but isn't tracking them yet
Staging area : Flagging the changes you want to include in your next save

git add

Repository : Git takes a permanent snapshot of everything in the staging area

git commit

Sequence of commands

check what is currently happening. red files are untracked or modified. Git sees them but hasn't saved them.

git status

Adding files(staging).

git add
or to add everything at once
git add .

The moment the change is officially tracked in Git's history.

git commit -m "brief description of what is changed"

Key Takeaways

Git status is your bestfriend. It is recommended to run it after every single command. It tells exactly which files are bein tracked and which ones are being ignored

LuxDev Markdown Language Class

Ngigi nyawira — Fri, 16 Jan 2026 21:31:53 +0000

How to write using markdown language

sign makes the text a title

2 Hash signs make the text a subtitle

subsection under subtitle

3 hash signs make it a subsection under a subtitle
To highlight on a word in a text that is to make it bold and in italics you use the asterisks before and after the text you want. 2 stars for the former and one star for the latter.

Using Hyphen
appears as a list

adding a block of code

the image below on how to adding a photo shows how to add a block of code.

name -"Gloria"
"yellow"

adding a link

Visit my LinkedIn account on

Adding a photo

click on the picture icon at the top.