DEV Community: Kinyanjui

Connecting Power BI Desktop to a SQL Database.

Kinyanjui — Thu, 19 Mar 2026 12:53:41 +0000

Introduction

Power BI is a Microsoft busniness analytics service that provides interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create reports. The platform makes it possible for connecting to, cleaning, and modeling data from different sources like, Excel and Databases, to create insights important to the business progress via cloud.

The key components are Power BI Desktop for creating reports, the Power BI service for publishing, and apps for visualization.
Connecting Power BI to databases e.g SQL Server, PostgreSQL, AzureSQL, or MySQL is important for going past manual, static reporting to scalable, automated, and real-time business intelligence.

While Excel files or CSVS work for small datasets, datasets provide the necessary pipeline to manage large volumes of data securely.

Why is Connecting Power BI to a database Important?

The following are the main reasons why connecting Power BI to a database is essential

Automation and Real-time Insights: Connecting directly makes it possible for scheduled refreshes or, using DirectQuery, live connections where data is updated instantly. This ensure no existence of manual Export/Import steps, ensuring decision-makers see the latest data as it flows in.

** Scalability and Performance:** Databases are optimised to handle large datasets with millions or even billions of rows. Querying a database allows Power BI to retrieve only the necessary aggregated data rather than loading raw files into memory, leading to faster report performance and better handling of raw data volumes.

Single Source of Truth(Data centralization): Databases serve as a central pipeline for data, ensuring that all segements are analyzing the same, trusted data. In turn, this avoids situations where discrepancies occur, meaning more than one user are able to work on their own seperate, local files.

Enhanced Data Integrity and Structure: SQL databases organize data into structured tables with defined relationships. For instance, Foreign/Primary keys. This reduces data redundancy and guarantees that relationships remain consistent across complex reports.

Efficient Data Transformation(Query Folding): When conncted toa database, Power BI can leverage 'Query Folding', where data transformation like filtering and merging are pusshed back to the database engine. Essesntially, this enabkes for loading and transformation faster efficiently.

Importances of SQL Databases for storing and managing analytical data.

Due to their ability to provide structure, data integrity, efficient handling of large datasets, and semaless integration of tools, SQL Databases are essential for data storage and management.Business operations and key decision-making processes depend on SQL databases as their backbone.

Importances of Data Storage

Structured Data Organization: SQL databases organize data into well-defined tables with columns and rows, just like in spreadsheets. This structured format, or predefined schema, makes the interaction clean, reliable, and easy to manage.
Data Integrity and Consistency: Relational databases enhance data integrity via constraits like primary and foreign keys, ensuring data accuracy, consistency, and adhearance to predefined rules. In addition, adhearnce to ACID(Atomicity, Consistency, Isolation, Durability) properties. These properties guarantee reliable transaction processing, even in the event of system failures.
Reduce redundacy: By organizing data into tables(Normalization) SQL databases reduce data duplication, making storage more efficient and less prone to errors.

Importance of Analytical data and Analytical management.

Efficient retreival and manipulation: SQL is an efficient and powerful language for retreiving, filtering, sorting, and aggregating data, ecen from large datasets.
Handling Large datasets: SQL databases are designed to manage and analyze huge volumes of data without affecting the overall performance, whcih becomes important for enterprise-level analytics that would cause tools like Excel sluggish.
Complex Querying Capabilites. Analysts have the ability to perform operations involving complex data without compromising performance.
Data Cleaning and Transformation. SQL is used extensively in data preparation workflows for cleaning data. (Removing duplicates, handling missing values) and transforming it into the required format for analysis, ensuring high-quality input reporting modelling.
Collaboration and Standardization: SQL is a universal standard language across many industries, which enables harmony across teams and ensures that skills sets remain relevant and transferable across different systems and roles.

Connecting Power BI to Local PostgreSQL database.

Power BI connects to a local PostgreSQL databases using the built-in PostgreSQL connector, requiring the server to be running(Usually localhost or 127.0.0.1), port 5432, and database credentials. It supports “Import” (snapshot) or “DirectQuery” (live) modes, typically requiring a local gateway for refreshing dashboards in the Power BI service.

Steps to Connect Power BI to local PostgreSQL
Preparation: Ensure your PostegreSQL server is active, and you know the server name. (localhost), port (5432), database name, username, and password.
Get data: In Power BI Desktop, go to Home> Get Data>More…>Database>PostgreSQL database and click Connect.

Configure Connection:

Server: Type localhost or 127.0.0.1 (or localhost5432).
Database: Enter the name of your specific PostgreSQL database.
Data connectivity mode: Select Import (faster, static data) or DirectQuery (live connection).
Enter Credentials: Choose Basic and enter your PostgreSQL username and password.
Select Tables: In the navigator window, window, select the tables or views you wish to load, then click Load or Transform Data.

NOTE: Driver Requirements: Power BI usually comes with the necessary drivers, but if not, you may need to install the psqlODBC driver.
Firewall: Ensure Windows Firewall allows traffic on the Postgre SQL port
(default: 5432)
Cloud refresh: To refresh data in the Power BI Service, you must install and configure an- on premises data gateway.

How to connect Power BI to a cloud database such as Aiven PostgreSQL:

To connect Power BI to an Aiven PostgreSQL database, one needs to collect the details from the Aiven Console, ensure you have the correct driver installed, and configure a secure connection in Power Desktop.
Prerequisites

Power BI Desktop Installed
An active Aiven for PostgreSQL service with your connection details (host, port database name, username, password).

The PostgtreSQL ODBC driver installed on your machine (If not already included with Power BI).
The SSL CA certificate download from your Aiven service. Aiven requires secure connections, and this certificate is used for verification.

Connection Steps:
1.Download the SSL Certificate from Aiven
-Log in to your Aiven console and navigate to your Postgre SQL server’s Overview tab.
-Scroll to the Connection Information section and download the CA certificate file (Usually ca.pem).
-(Optional but recommended) Install the Certificate in your Windows machine’s Trusted Root Certification Authorities store for seamless verification.

2. Get data In Power BI Desktop.
-Open Power BI Desktop.
-Open the Home tab, click Get data> More…
-In the Get Data Window, search for “ PostgreSQL” and select PostgreSQL database then click connect.

3. Configure Connection Settings
-In the PostgreSQL database dialog box, enter the server (hostname and port, e.g., your-service-project.aivencloud.com:port) and Database name from your Aiven details.
-Select the Data Connectivity mode (Import is recommended for most cases).
-In the Advanced options, you may need to specify SSL parameters like sslmode=require or sslmode=verify-full&sslrootcert=path\to\ca.pem. The built-in connector often handles the SSL encryption automatically if the certificate is correctly installed on the system.

4. Authenticate
-Power BI will prompt you for credentials. Select the Database authentication method.
-Enter the Username and Password from your Aiven connection details.
-Click Connect.

5. Load data.
-In the navigator window, select the tables you want to analyze. A data preview will be shown.
-Click Load to import the data directly into Power BI, or click Transform Data to use the Power Query Editor for cleaning and shaping the data before loading.

Once the data is loaded, the tables will appear in the Fields pane, and you can begin creating visualizations.

Why SSL Certificates are required for secure database connections.

SSL (Secure Sockets Layer/TLS) certificates are required for database connections to ensure that data travelling between the application(Client) and the database server remains secure, private, and untampered with.

The following outlines why they are essential:

Data Encryption in Transit: Without SSL, data – including database querries, query results, and login credentials – is transmitted in plain text. SSL encrypts this data, making it unreadable if intercepted by attackers.

Preventing Man-in-the Middle attacks: SSL certificates provide server authentication, ensuring the client is connecting to the legitimate database server rather than a malicious imposter aiming to capture data.

Ensuring Data Integrity: SSL ensures that data cannot be modified or corrupted during transit without being detected.
Regulatory Compliance: Many industry regulations, including PCI-DSS, HIIPA, and GDPR, mandate the encryption of sensitive data in transit, making SSL a necessity for compliance.
Security for Remote/Cloud Networks: Even within a company network, SSL enforces a “Zero trust” approach. It is particularly crucial when databases are hosted in the cloud or accessed over public, untrusted networks.

In a nutshell, SLL certificates turn an insecure, transparent connection into a secure, encrypted tunnel, protecting critical data from being tampered.

How tables are and relationships are created in Power BI Desktop.

Loading the Data. Before you can analyze anything, you must bring tables into Power BI environment.

Connect to Source: Use “Get Data” to connect to your data source (Excel, PostgreSQL, or a CSV).
Power Query: Once you select your four tables (Customers, Products, sales, and Inventory), you should click Transform Data. This opens the Power Query Editor where you clean data - Ensuring date formats are correct, removing duplicates from the “Customers” table, and making siure the “Products” IDS match tables.
Load: After cleaning, click Close & Aply. The data is then loaded into the Power BI ‘internal data source.’

The core Concept: Facts Vs Dimension Tables To model the data correctly, you must understand the two types of tables you are working with:

Fact Tables (The “What Happened”): These contain quantitative data about business processes. Example; Sales (Contains price, quantity, and date) and Inventory (contains stock levels).
Fact tables (The ‘Who, What, Where’): These contain descriptive attributes.
Example; Customers (names, emails) and Products (Categories, brands, Cost).

Creating Relationships. Relationships tell Power BI how a row in one table relates to a row in another. This is usually done in the Model View (The relationship icon on the left sidebar).

The common Key: You connect using “Keys”. Example, you drag the productid from the Products table to the ProductID in the Sales Table.
Cardinality (One-to-many): This is the most common relationship.
Direction of Flow: By default, the relationship filters in one direction (From the Dimension table to the Fact table). When you select a specific product name, the relationship tells the Sales table to only show the rows for that specific ID.

Why data Modeling matters. Without these relationships, Power BI is just a collection of separate spreadsheets. Data modelling allows for the Cross-Filtering:

Accurate Aggregation: It ensures that when you drag “Customer Name” and “Toal Revenue” into a chart, Power BI knows exactly which sales belong to which person.
Connecting Disparate Data: Relationships allow to compare Sales Vs Inventory. You can see if a dip Sales was caused by a specific product being out of stock, even though that data lives in two different tables.
Optimization: Instead of having one massive, messy table with 100 columns, you can have a clean “Star Schema” that makes the report run faster and DAX formulas easier to write.

Conclusion

SQL skills are essential for Power BI analysts because they form the foundation of efficient data handling before visualization begins. With SQL, analysts can directly retrieve relevant data from databases, apply precise filters to narrow down large datasets, and perform aggregations such as sums, averages, and counts at the source level, reducing the load on Power BI. This pre-processing ensures cleaner, well-structured data, which leads to faster report performance and more accurate insights. Additionally, SQL enables analysts to join multiple tables, create calculated fields, and shape data into analysis-ready formats, making the overall dashboard-building process smoother, more scalable, and more reliable.

Joins and Window Functions In SQL

Kinyanjui — Tue, 03 Mar 2026 09:08:12 +0000

Introduction

In the world of SQL, there is a fundamental difference between having data and understanding it. If your database is a library, raw tables are just books scattered across different book-shelves. To give an insightful story, you need to have a connection between those books and analyze the narrative within them. This is where the two most powerful tool in data analysis come in. Joins and Windows.
Often, data analysis feels like a jigsaw puzzle where the pieces are not consolidated and are scattered across different boxes. Joins and Window functions are the basic tools to arrange those pieces together to create order. While they might appear similar - both combine data - Meaning they serve in a different capacity in your analytical workflow. In this article, we shall break down how they work, when to use them and how to combine them in solving complex real-world problems.

JOINS: The "Horizontal" Connection

Joins are used to link two or more tables together on a thread column between them. They act like a way to enrich your data. If you have a listof orders but require the customers' names, you join the orders table to the customers' table.

Types of Joins:

Inner Join - Returns records that have matching values in both tables.
Left Join - Returns all records from them left table, and matched records from the right.
Right Join - Returns all records from the right table, and matched records from the left.
Full Join - Returns all records when there is a match in either table.
Scenario: You have an Employees table and want to see every employees' name right next to their manager's name. Since the manager is also an employee, their data is in the same table.
SELECT e.Name AS Employee, m.Name AS Manager FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

The Rule of Join; Joins change the breadth of your data by adding columns from other tables, but they can also change the row count if there are multiple matches.

WINDOW FUNCTIONS: The "Vertical" calculation.

Window functions perform the role of calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, like Sum, Avg, with GROUPED BY, Window functions do not group rows into a single output row. The rows retain their seperate identities. This is made possible through the OVER() Clause.
Key Components
Partition by: Divides the results set into partitions (Groups)
Order By: Sorts the rows within each partition.
Frame Clause: Defines the subset of rows to look at e.g The last two days.

Example: The Running Total.
If you want to see your daily sales alongside a running total of all sales to date, you use a window function.

SELECT 
    Date, 
    DailySales,
    SUM(DailySales) OVER (ORDER BY Date) AS RunningTotal
FROM Sales;

When to Use Which:

Choosing between a Join and Window Function depends on your 'Data Goal'.
Use a join when;

You need data that lives in another table.
You are building a foundation dataset for a report.
You need to filter one table based on the existence pf records in another.

Use a Window Function when;

You need to compare a row to its neighbors e.g How much more did we sell today than yesterday.
You need to do ranking, e.g Who are the top 4 customers by region?

SELECT 
    Region, 
    Salesperson, 
    TotalSales,
    DENSE_RANK() OVER (PARTITION BY Region ORDER BY TotalSales DESC) AS SalesRank
FROM RegionalSales;

You need to calculate a moving average or a cumulative sum.

Conclusion

At first glance, Joins and Windows might seem like competing methods for combining data. However, as we have explored in the article, the two are actually distinct. By mastering both, you move beyond simply "Retrieving" data and begin generating meaningful insights. You stop asking what data is and start explaining it.

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

Kinyanjui — Sat, 07 Feb 2026 15:35:40 +0000

Introduction

The true value of a Data Analyst is not just their ability to use software; it is their duty to do the right translations They take the mess- the scattered and unassembled business operations- and refine it through the lens of Power Bi. By combining the structural rigor of Power Querry, the mathematical depth of DAX, and the psychology visual design, they turn numbers into a roadmap for growth.

Sales files flow to your email with broken dates, Finance exports do not balance, operations data lives in three different systems, the leadership still wants clear answers by a tight deadline. This is where a Power BI analyst earns their keep-nots by coming up with legible charts, but by translating chaos into decisions.

This article looks into how analysts actually do that: Right from harnessing messy data, to writing purposeful DAX, to designing dashboards that drive action – Not confusion.

Harnessing the Chaos

The following list shows some of the key issues that raw data arrive with.

Inconsistent date formats (Text vs date)
Duplicate records and missing keys
Mixed currencies, units, or naming conventions
Flat files pretending to be relational data

Data profiling: Check for outliers or null values that could skew details.
Transformation: Setting up these steps so that when the next set of uncleaned data arrives, Cleaning happens automatically.
As an analyst, don’t panic. Ask one question first;
What decision will this data support?
The question determines how clean is “Clean Enough”

Power Query: Where order begins

Here, standardize columns and data types
Create surrogate keys
Normalize wide tables into fact and dimension structures
Remove noise without destroying signal

This stage is less about transformation wizardry and more of data empathy- Getting to know how data was created and how it should really behave.
Clean data is not about perfection. It is about trust, Data that can be relied on.

Modeling: Turning Tables into meaning

Once data is clean, an analyst shifts from data fixing to data thinking. The model is the product.
A well designed- data model:

Uses fact and dimension tables intentionally.
Avoids bi-directional relationships unless justified
Aligns grain(Low-level meaning) across tables.

Star schemas are not academic preferences - they make DAX able to make meanigful and reliable insights.when the model is right:

Measures become simpler
Visuals behave predictably
Business logic lives in one place.

Unpleasant models come up with dashboards that look okay but answer the wrong questions.

DAX: Encoding Business Logic, not Math tricks

DAX intimidates many people because it feels like Excel formulas -But behaves very differently.
Good analysts stop asking: “How do I write this formula?” and start asking: What question should this measure answer?”

Turning insights into action

This step isn’t the dashboard itself, rather, it is the interpretation. A great analyst uses Power BI features to trigger real-world movement:

Data-Driven Alerts: having notifications so a manager gets an email if inventory drops below 10%
Power automate integration: Allowing users to initiate a business process(Like refreshing budget) directly from the report.
The narrative: Using the “Smart Narrative” tool to summarize the key takeaways in plain English, ensuring no one misses the “Call to action”

Final Thoughts

In the end, the journey from messy data to a polished Power BI dashboard is about more than just technical proficiency – It is about decision enablement. A dashboard that sits idle is a failure, no matter how complex the DAX or how clean the data model is.
The true milestone of a successful analyst is the ability to fade into the background, leaving the stakeholder with a clear, undeniable path forward. When Power BI is used correctly, the technology disappears, and the insights take the center stage. By mastering the power of translation, analysts do not just report on the past; they provide the clarity needed to build a more efficient, profitable future.

From Raw Data to Insights: Schemas and Data Modeling

Kinyanjui — Mon, 02 Feb 2026 04:56:26 +0000

Introduction

Data doesn't become useful the moment you collect it, rather, it becomes useful when it is well structured. The structure is what schemas and data modeling are all about. Whether you're working in Excel or PowerBi or a full data warehouse, getting to know, understanding schemas and data modeling is the foundational skill for any data analyst.
This article breaks down schemas and data modeling in an elaborate way well suited for beginers.

Now lets dive in.

A Schema

(The blueprint)
Refers to the way tables are structred and the interelation they have with eachother. in other words, schemas define the logical structure, organization and relationships of tables within a data model. Majorly, they narrow down on structuring data for optimal performance and reporting.

Data Modeling

(The Plan)
It essentially refers to the crucial process of arranging and creating an interelation between data tables to create a logical, efficient sematic model, which in turn helps in creating accurate analysis and powerful reports by defining relationships, creating DAX computations(Measures) and having organized data by using schemas within Power querry and Model view.

Lets now connect the two. Data modeling is the process of designing a blueprint for organizing data based on various business needs and requirements, while a schema is the ground and technical implimentation of that blueptint within DBMS. The interelation is a direct progression from concept to physical reality.
The data model guides the schema design - The schema is the actualization of the model.

Why Schemas Matter

Make data easier to understand
Reduce query performance
Reduce data duplication
Support scalable reporting and analysis

Types of Schemas in Power BI

1. Star Schema

Refers to a mature modeling approach widely adopted by the relational data warehouses. It needs user to classify their model tables as their dimension or fact. It is designed for high performance. In addition it consists one or more central Fact tables (With metrics/measures) sorrounded by de-normalised dimensions tables (Containing Descriptive attributes)
The strucure:

Fact Table(Center) >> Contains Numeric measures (Things you aggregate) and foreigns keys to dimensions.

Dimension Tables (Around it) >> Contains descriptive attributes and one row per entity.

Reasons why Star Schema is recomended

Faster performance
Simpler DAX
Fewer relationship
Easy to explain to stakeholders

Tips:
If it describes something>> Dimension
If it is a sum of a number>> Fact

2. Snowflake Schemas

This is a more complex, and widely normalised version of the Star schema. This refers to when dimensions are normalised into multiple tabls. This is further broken down into Sub-dimensions to reduce data redundancy. Its basically an extension of the star schema often utilised for complex, hierachical data, improving storage efficiency at the cost of Query performance due to more required joins.

Pros: Reduces duplication
Cons: Slower, More complex DAX, and harder to maintain.

NOTE: Flatten demensions where possible. Storage is cheap, Confusion is expensive.**

Comparing A Good data model Vs A Bad data model.

Good Model	Bad Model
Multiple small dimension tables	One big table with over 50 columns
Simple relationships	Many to many relationships everywhere
No duplicates	Duplicate in columns

Role of Power Query in data modeling.

Removes duplicates
Creates dimension tables
Geenarate surrogate keys
Clean and starndardize columns

Common Modeling mistakes and how to avoid them

Using data columns in fact table only	Create a proper Data table
Many-to-many relationships	Introduce bridge tables
Too many bidirectional filters	Redesign tables
Calculated columns everwhere	Prefer measures

Data modeling is the bridge between raw data and clean insights, once your model are solid, DAX becomes silple and not scary.
Schemas and Data modeling may feel abstract at first, but they are what seperates messy data from insightful and reliable analytics systsems.Mastering them will Improve your dashboards, reduce errors, and make analysis scalable.

The Excel Evolution: Navigating from Data Entry to Data Artistry without stress

Kinyanjui — Sun, 25 Jan 2026 17:01:42 +0000

Introduction

Microsoft Excel is one of the tools that might look simple at first - But the deeper you dive, the more powerful and insightful it gets. Regardless of whether one is tracking expenses, analyzing business or building dashboards, Excel can handle it all. This article servers to give a roadmap to preventing you from getting stuck or overwhelmed. Each stage in the article builds on the previous one just like levelling up in a game.

STAGE 1: Understanding the Interface

Excel consists of:

Workbooks - The Excel file.
Worksheets - Individual sheets inside a workbook.
Cells - Boxes that contains data e.g. A1, B2, C7
Rows and Columns - Rows run horizontally, columns vertically.

Basic Data Entry

Enter texts, Numbers and dates
Edit and Delete cell content
Copy & paste data.
Use autofil to quickly fill patterns.
Basic Formatting
Formatting makes your data legible
Change font size, color, and style
Apply borders and cell colors
Format numbers (Currency, Percentage and Dates)
Change row height and column width.

Simplple Formulae

`=SUM (A1:A10)`
`=AVERAGE (A1:A10)`
`=MIN ()` & `=MAX ()`
`=COUNT ()

`
At this stage, Excel may start to feel like a smarter calculator and thats fine. It should feel like that.

Stage 2: Logical and Conditional Formatting

Here, Start thinking logically with excel.
These functions help Excel 'think.'

=IF() -Makes Decisions =AND()Combines conditions =OR() Also Combines conditions
Working with data

Relative and Absolute cell referencing. (A1 vs $A$1 Relative cell referencing is the Default Excel referencing; Makes changes based on relative position. On the other hand, Absolute Cell referencing is locked- Always points to the same exact cell.
Sortinng & and Filtering. Sorting rearrages rows alphabetically or numerically(A-Z, Z-A, Smallest-Largest) Where as Filtering hides rows that do not meet the specified or desired criteria, displaying only the relevant data for analysis.
Remove duplicates. This functions gets rid of repetitive unnecessary data. In Excel, select Data tab, click Remove duplicates, check the relevant columns in the columns in the dialog box(Ensuring headers are noted if present), Press OK.
Texts to columns
Page set up and printing.

Stage 3: Data Handling

You should concentrate on the following areas to help in handling large datasets.

Data Validation. This function restricts excel from accepting data entry in cells to specific types, values or formats using the rule Data>Data Validation
Remove duplicates. This function removes duplicates contents in a dataset via the Data>Remove dumplicates
Texts functions. e.g; LEFT , RIGHT, LEN, TRIM, CONCAT Excel text functions make changes to strings, extract data, format numbers, and clean data for smooth working.

LOOKUP functions

VLOOKUP() and HLOOKUP()
VLOOKUP function searches for a specific value in the leftmost column of a table and returns a corresponding value from another column in the same row. On the other hand, HLOOKUP searches for a value in the top row of data table and returns a corresponding value from a specified row in the same formula.

XLOOKUP()
This function finds data in the table or range by row, serving as a more effective and flexible alternative to VLOOKUP,HLOOKUP, And INDEX/MATCH.
INDEX() + MATCH()
This function allows lefward serches, column insertions, and faster processing on large data sets. INDEX returns a value from a range, while MATCH finds the row number of a value creating a dynamic, resilient, and Non-volatile LOOKUP tool.
Altogether, these functions allow excel to pull data from massive tables and data sets instantly.
Charts and Visuals
Excel has the ability to turn large, uncleaned data into insights and visializations.
Tools include; Column, bar, line, and Pie charts. - Custom charts formating, adding lables, titles and legends.

Stage 4: Advanced Excel - Becoming a Pro.

Pivot Tables This is an important segment that one cannot ignore. These tables summarize large data sets within seconds without having to write formulas. Before using pivot tables, ensure your data is cleaned. Select your data by clicking anywhere inside the data set, Insert Pivot table, Choose from Table/Range, and Click OK.

When Should one use Pivot Tables?

Summarize large datasets quickly
Compare totals, averages and Counts.
Group data by date, country, category and customer.
Build Dashboards and reports.
Avoid complex formulas like SUMIFS, COUNTIFS

Creating a Pivot table.
Select ypur data
Insert Pivot table(New Worksheet)

Group data by category.
Calculates totals, averages, and counts
Rearrange data dynamically without formulas
Creating Pivot charts
Advanced formulas include;
=SUMIFS()
=COUNTIFS()
=AVERAGEIF()
Array formulas
Nested functions
Dynamic formulas; FILTER, SORT(), and UNIQUE()

NOTE: It is important to understand that the difference between beginner and a Pro isn't speed - It's confidence and accuracy.

Conclusion

Excel is a big ocean, More than just a speadsheet tool - It is a career skill especially in a data driven world. Starting with basic data entry and formulas, moving into analysis and Visualizations, and finally mastering automation and advanced functions. Just identify the right tool for the given problem. You will move from beginner to pro.

The Ultimate Manual: Understanding Git and GitHub without Headache.

Kinyanjui — Sat, 17 Jan 2026 05:27:06 +0000

The concept: Git vs. GitHub on Windows.

Imagine, you are working on a major project. You spend quite a large number of hours writing code, only to find out that the 'fix' you implimented ten minuted ago broke everything. You try and hit the redo button repeatedly but it's not enough. Your work is gone.
That's where Version Control comes in. This article aims at helping beginner developers to bridge the gap between GitHub and Git Bash. The distinct roles seperating the two are;

Git. The local tool- This is the Version Control system that lives in one's computer and tracks changes in files.
GitHub. This is the cloud platform where one hosts Git repositories online so that collaborating with others is made easy.
Advantages include; Safety, Tracability and Accountability, High-Confidence Experimentation, Collaboration, and Off-site Back-up.

Setting Up the Environment(Git Bash).

To begin with, one needs to install GIT
The next step involves configuring your identity(Email and Name). This crucial activity helps Git Identify who is making the changes.

git config -global user.name "your name"

git config -global user.email "youremail@example.come

Setting Up the environment(GitHub)

Sign in to GITHUB and set SSH key and Personal Access Token for secure connection.
A SSH key provides a secure password-less way to connect your local machine to a remote server or a service.
To generate a key, Open terminal(GitBash on Windows) and run the command:

ssh-keygen -t ed25519 -C "youremail@example.com"

When prompted to 'Enter a file to save the Key', just press Enter to accept the default file location.
To check for existing SSH keys, run the command;

id_ed25519.pub

Adding a Public key to your GitHub account.

On windows(GitBash) run the command;

cat ~/ssh/id_ed2519.pub

On GitHub, navigate to;

Settings>SSH and GPG Keys>Select **New SSH Key**, Provide a descriptive title and paste the Public key generated from GitBash in the **key field.** >add key.

In your terminal, run;

ssh -T git@github.com

A message confirming a successiful authentication will be seen.

The Push and Pull commands.

Git does not automatically save everything. It needs you to be intentional . Think of it like capturing a photo.

The working directory(The Set). This is in the occassion where one is currently editing files.
The staging area(The pose). One chooses which changes are ready to be saved. You run the command;
git add to put them in the frame.
The repository.(The Photo). You run git commit -m "your message" to snap the shutter. These changes now become permanent in history.

To master Git, one needs to understand the journey code takes from a local machine to a remote server like GitHub. This process involves three stages; Your working directory(Files you're editing), The staging Area(Files you've marked to save), The Repository(The permanent history.)

Pushing Code to GitHub.
Pushing is the act of uploading local commits to a remote server. This makes your code accessible to others and act as backup.
git push origin main
Pulling Code from GitHub.
Pulling refers to how you download the latest changes that others have uploaded. It keeps the local version in sync with the master copy online.
git add & commit >Local folder to Local history.
git pull >GitHub to Local History.
git push >Local History to GitHub.

The table below shows _the big five commands on Git, the action they represent, and the real world Analogy.

Command	Action	Real-World Analogy
'git innit'	Initializes a new repository.	Setting up a new filing cabinet.
'git add'	Initializes your changes	Putting a letter in the envelope.
'git commit -m "msg"'	Sends changes to GitHub	Dropping the letter in the mailbox.
'git pull'	fetches changes from GitHub	Checking your mail for updates.

Your Firts Repository.

To start tracking your project, navigate to your project folder in the terminal bu using the command git init. Once you have written some code, follow the "Save Ritual."
Saving your code on your laptop is good. However, saving it on GitHub is much better. It protects projects in instances where a laptop crashes and allows one to show off their work.

Conclusion.

Learning Git and GitHub can sometimes feel like learning a new language on top of your programming langauge. However, it is an important step that helps one level up as a developer. By understanding the cycle of tracking, pushing, and pulling, you are not just saving files - you re building a professional portfolio and a safety net which give you more confidence to experiment.
Do not panic if you will have to look up commands(Even the pros do). Practice makes perfect, the more you use these command, the more it becomes second nature. My advice would be, go ahead: create a repository, make a mess and use this "Headcache-Free Manual."

What was the first thing you ever 'pushed' to GitHub? Let me know in the comments!

DEV Community: Kinyanjui

Connecting Power BI Desktop to a SQL Database.

Introduction

Why is Connecting Power BI to a database Important?

Importances of SQL Databases for storing and managing analytical data.

Importances of Data Storage

Importance of Analytical data and Analytical management.

Connecting Power BI to Local PostgreSQL database.

How to connect Power BI to a cloud database such as Aiven PostgreSQL:

Why SSL Certificates are required for secure database connections.

How tables are and relationships are created in Power BI Desktop.

Conclusion

Joins and Window Functions In SQL

Introduction

JOINS: The "Horizontal" Connection

Types of Joins:

WINDOW FUNCTIONS: The "Vertical" calculation.

When to Use Which:

Conclusion

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

Introduction

Harnessing the Chaos

Power Query: Where order begins

Modeling: Turning Tables into meaning

DAX: Encoding Business Logic, not Math tricks

Turning insights into action

Final Thoughts

From Raw Data to Insights: Schemas and Data Modeling

Introduction

A Schema

Data Modeling

Why Schemas Matter

Types of Schemas in Power BI

1. Star Schema

Reasons why Star Schema is recomended

2. Snowflake Schemas

Comparing A Good data model Vs A Bad data model.

The Excel Evolution: Navigating from Data Entry to Data Artistry without stress

Introduction

STAGE 1: Understanding the Interface

Stage 2: Logical and Conditional Formatting

Stage 3: Data Handling

Stage 4: Advanced Excel - Becoming a Pro.

When Should one use Pivot Tables?

Creating a Pivot table.

Conclusion

The Ultimate Manual: Understanding Git and GitHub without Headache.

The concept: Git vs. GitHub on Windows.

Setting Up the Environment(Git Bash).

Setting Up the environment(GitHub)

Adding a Public key to your GitHub account.

The Push and Pull commands.

Your Firts Repository.

Conclusion.