DEV Community: Suzanne Orido

Connecting Power BI to SQL Databases

Suzanne Orido — Mon, 07 Sep 2026 12:08:00 +0000

Connecting Power BI to SQL Databases

A practical guide to integrating Power BI Desktop with local PostgreSQL and cloud-hosted Aiven databases, including data modelling and why SQL still matters.

Introduction: Power BI and SQL Databases
Connecting to a Local PostgreSQL Database
Connecting to Aiven Cloud PostgreSQL
Loading Tables and Creating Relationships
Why SQL Skills Matter for Power BI Analysts

1. Introduction: Power BI and SQL Databases

Microsoft Power BI is one of the leading business intelligence platforms in use today. It enables organisations of all sizes to transform raw data into interactive dashboards, reports, and visualisations that help decision-makers act on evidence rather than intuition. From tracking monthly sales performance to monitoring operational KPIs in real time, Power BI sits at the centre of how modern businesses consume their data.

Power BI is available in several forms. Power BI Desktop is the Windows application used to build reports and data models. Power BI Service is the cloud-based platform where those reports are published and shared across an organisation. Together, they cover the full lifecycle of analytical work — from raw data connection to executive-level dashboarding.

Why connect Power BI to a database?

While Power BI can import data from Excel files, CSV exports, and web APIs, these sources have significant limits. They are static, often out of date, and difficult to maintain at scale. A well-structured relational database, by contrast, is the authoritative source of truth for most business data. It stores transactions, customer records, inventory levels, and operational events with precision, consistency, and referential integrity.

When Power BI connects directly to a database, analysts can query the freshest available data, apply complex filters at the database level, and avoid the overhead of manually exporting and re-importing flat files. The database handles storage and retrieval efficiently; Power BI handles visualisation and exploration. Each tool does what it does best.

The role of SQL databases in analytical workflows

SQL (Structured Query Language) databases, including PostgreSQL, Microsoft SQL Server, and MySQL, are the backbone of most enterprise data architectures. They organise data into tables with clearly defined schemas, enforce relationships between entities, and support powerful querying through the SQL language.

PostgreSQL, in particular, is an open-source relational database widely used in both development and production environments. It supports advanced data types, complex joins, window functions, and JSON storage, making it a versatile choice for analytical workloads. Whether self-hosted on a local machine or managed in the cloud through platforms like Aiven, PostgreSQL integrates cleanly with Power BI.

2. Connecting to a Local PostgreSQL Database

What this guide covers: This guide walks through connecting Power BI to both a local PostgreSQL instance and a cloud-hosted Aiven database. It also covers data modelling with four linked tables — customers, products, sales, and inventory — and concludes with a discussion of why SQL fluency is valuable for BI analysts.

A local PostgreSQL database runs on the same machine as Power BI Desktop, or on a machine within your local network. This is the standard setup for development, testing, or environments where the data does not leave the building. The connection process requires no SSL configuration and is straightforward once PostgreSQL is running and a database exists.

Prerequisites

Before beginning, confirm the following are in place:

Power BI Desktop is installed (Windows only).
PostgreSQL is installed, and the target database is created.
The Npgsql PostgreSQL connector is installed. Power BI requires this driver to communicate with PostgreSQL. Download it from the official Npgsql releases page and install it before opening Power BI.
You know the database name, a valid username, and its password.

Step-by-step connection process

Step 1 — Open Power BI Desktop

Launch the application. On the start screen, click Get Data. If you are already inside a report, navigate to Home → Get Data in the ribbon.

Step 2 — Search for PostgreSQL Database

In the Get Data dialogue, type PostgreSQL in the search box. Select PostgreSQL Database from the results and click Connect.

Step 3 — Enter the server and database details

In the connection dialogue, fill in two fields:

Server — enter localhost for a local instance, or a hostname/IP address for a network server.
Database — enter the exact name of the PostgreSQL database you want to connect to (for example, assignment).

Leave the Data Connectivity mode as Import unless you specifically require DirectQuery.

Step 4 — Provide credentials

Power BI will prompt for a username and password. Select Database under the credential type dropdown, then enter your PostgreSQL username (often postgres) and the corresponding password. Click Connect.

Step 5 — Select and load tables

The Navigator pane will display all schemas and tables in the database. Check the tables you want to load — for example, customers, products, sales, and inventory. Click Load to import them directly, or Transform Data to open the Power Query Editor first.

Connection flow:

[Power BI Desktop] → [Get Data / PostgreSQL] → [Server & Credentials] → [Navigator] → [Load]
      Step 1                  Step 2                  Steps 3–4           Step 5        Final

Figure 3 — Local PostgreSQL connection flow

Tip: If the connection fails with a driver error, install the Npgsql connector and restart Power BI Desktop before trying again.

3. Connecting to Aiven Cloud PostgreSQL

Aiven is a managed cloud database platform that hosts PostgreSQL (and other databases) on your choice of cloud provider — AWS, Google Cloud, or Azure. Connecting Power BI to an Aiven PostgreSQL instance follows the same general steps as a local connection, with two important differences: the connection details are specific to your Aiven service, and SSL must be used to encrypt the connection.

Obtaining connection details from Aiven

Log in to the Aiven Console and open your PostgreSQL service. On the service overview page, you will find all the information needed to establish a connection:

Parameter	Where to find it	Example value
Host	Service Overview → Connection Information	`pg-abc123.aivencloud.com`
Port	Next to the host, typically a custom port	`15432`
Database	Listed under the service name	`defaultdb`
Username	Connection Information section	`avnadmin`
Password	Click the eye icon or copy button	(hidden — copy directly)
SSL Certificate	Download button in Connection Information	`ca.pem`

Download the CA certificate (ca.pem) and save it to a location you can reference easily, such as C:\certs\aiven-ca.pem on Windows.

Why SSL certificates are required

A cloud database is accessible over the public internet. Without encryption, data transmitted between Power BI and the Aiven server — including credentials and query results — would be visible to anyone monitoring the network. SSL (Secure Sockets Layer) / TLS (Transport Layer Security) encrypts the entire connection, preventing interception.

The CA certificate serves a second purpose: it allows Power BI to verify that it is connecting to the genuine Aiven server and not an impostor. This is known as certificate verification, and it protects against man-in-the-middle attacks. Aiven requires SSL on all connections; it cannot be disabled.

Step-by-step: connecting via Power BI Desktop

Step 1 — Open Get Data → PostgreSQL Database

Follow the same steps as the local connection: Home → Get Data → PostgreSQL Database → Connect.

Step 2 — Enter the Aiven host and port

In the Server field, enter the full Aiven hostname followed by a colon and the port number:

pg-abc123.aivencloud.com:15432

In the Database field, enter the database name (often defaultdb unless you created a named database).

Step 3 — Expand Advanced Options and add the SSL certificate

In the connection dialogue, click Advanced Options to reveal additional fields. In the Additional connection string parameters box, enter the SSL certificate path in the following format:

sslmode=verify-ca;sslrootcert=C:\certs\aiven-ca.pem

This tells Power BI to use SSL, verify the server's certificate, and trust only certificates signed by the CA you downloaded.

Step 4 — Enter credentials and connect

Click OK. When prompted, select Database authentication, enter the Aiven username (typically avnadmin) and password copied from the Aiven Console. Click Connect.

Step 5 — Select tables in the Navigator

The Navigator pane will display the available tables. Select the required tables and click Load or Transform Data.

Note: The certificate path must use the correct syntax. On Windows, use double backslashes or forward slashes in the path. If the ca.pem file is not found, Power BI will fail to connect with an SSL handshake error. Verify the file exists at the exact path specified.

4. Loading Tables and Creating Relationships

Once the connection is established, Power BI loads the selected tables into its internal data model. For this guide, the PostgreSQL database contains four tables organised around a retail business scenario:

Table	Primary key	Key columns	Description
`customers`	`customer_id`	`first_name`, `last_name`, `email`, `registration_date`, `membership_status`	50 customer records with registration and membership data
`products`	`product_id`	`product_name`, `category`, `price`, `supplier`, `stock_quantity`	15 products across categories with pricing and supplier info
`sales`	`sale_id`	`customer_id` (FK), `product_id` (FK), `quantity_sold`, `sale_date`, `total_amount`	15 transaction records from 2023–2024
`inventory`	`product_id` (FK)	`stock_quantity`	Current stock levels for each product

How Power BI auto-detects relationships

After loading these tables, Power BI may automatically detect relationships based on matching column names and data types. In this schema, it will likely identify that sales.customer_id references customers.customer_id, and that sales.product_id references products.product_id. The inventory table shares product_id with products.

To inspect and manage relationships, navigate to the Model view in Power BI Desktop — the icon that looks like three connected boxes in the left sidebar. Here you can see a visual map of all tables and the lines connecting them.

Creating and editing relationships manually

If Power BI does not detect relationships automatically, or if a detected relationship is incorrect, you can manage them manually. In the Model view, drag from a foreign key column in one table to the primary key in another. Power BI will draw the relationship line and ask you to confirm the cardinality and cross-filter direction.

For this schema, the three key relationships are:

customers.customer_id → sales.customer_id (one-to-many)
products.product_id → sales.product_id (one-to-many)
products.product_id → inventory.product_id (one-to-one)

What data modelling enables

With relationships in place, Power BI treats the tables as a unified data model rather than isolated datasets. When you build a chart showing total sales revenue by product category, Power BI knows to join sales to products via product_id to retrieve the category name. When filtering by customer membership status, it traverses the relationship from customers to sales automatically.

This is the core principle of star schema data modelling: a central fact table (sales) linked to dimension tables (customers, products) that describe the who, what, and when of each transaction. The inventory table functions as a supplementary dimension providing current stock context alongside product data.

Note on cross-filter direction: By default, Power BI uses single-directional filtering; filters flow from the dimension table into the fact table. In most cases, this is correct. Avoid enabling bidirectional filtering unless you have a specific requirement, as it can produce unexpected aggregation results and slow report performance.

5. Why SQL Skills Matter for Power BI Analysts

Power BI's graphical interface makes it possible to build dashboards without writing a single line of SQL. But analysts who understand SQL bring a fundamentally different level of capability to their work. SQL is not a requirement for using Power BI — it is a requirement for using it well.

"An analyst who can write SQL is not just faster — they understand the data at a structural level that shapes every design decision they make in Power BI."

Four ways SQL strengthens Power BI work

1. Precise data retrieval

Rather than loading an entire table and filtering inside Power BI, an analyst with SQL knowledge writes a query that retrieves only the rows and columns needed. This reduces memory usage, speeds up refresh times, and keeps the data model lean.

2. Filtering at the source

SQL WHERE clauses filter data before it reaches Power BI. An analyst who understands this can avoid importing years of historical records when only the past 12 months are relevant to the dashboard — a significant difference at scale.

3. Pre-aggregation and joins

Complex calculations, such as monthly revenue per customer segment, or average order value by product category, can be computed in SQL before the data is loaded. This offloads processing to the database engine, which handles large aggregations far more efficiently than Power BI's in-memory model.

4. Data preparation and quality

SQL lets analysts clean and reshape data at the source — standardising date formats, handling nulls, concatenating name fields, or pivoting rows into columns — before the data ever reaches Power Query. This keeps the Power BI model simple and the transformation logic auditable.

SQL in the context of this schema

With the assignment database used throughout this guide, a Power BI analyst who understands SQL can write queries like the one below to pre-aggregate sales data before loading it, rather than importing all 15 raw transaction rows and computing totals inside DAX:

SELECT
    p.category,
    c.membership_status,
    SUM(s.total_amount) AS revenue,
    COUNT(s.sale_id) AS transactions
FROM sales s
INNER JOIN products p ON s.product_id = p.product_id
INNER JOIN customers c ON s.customer_id = c.customer_id
GROUP BY p.category, c.membership_status
ORDER BY revenue DESC;

This query produces a compact summary table — joined, grouped, and sorted, ready for Power BI to visualise. An analyst who cannot write this query must load three raw tables, build the join in Power Query, and compute the aggregations with DAX. The result is the same, but the path is longer, more error-prone, and harder to debug.

Summary: SQL and Power BI as complementary layers

Layer	Tool	Responsibility
Storage & retrieval	PostgreSQL	Tables, indexes, relationships, data integrity
Transformation	SQL	Filtering, joining, aggregating, cleaning at source
Modeling	Power BI Desktop	Star schema, relationships, DAX measures
Visualisation	Power BI Desktop / Service	Dashboards, reports, interactive charts

SQL and Power BI are not competing tools; they are complementary layers in the same analytical pipeline. SQL handles structured retrieval and transformation at the database level. Power BI handles interactive visualisation and self-service exploration at the consumer level. Fluency in both means the analyst decides, with full awareness, where each operation belongs.

Mastering SQL Joins and Window Functions: A Comprehensive Guide

Suzanne Orido — Mon, 07 Sep 2026 12:04:11 +0000

SQL is the backbone of data manipulation in relational databases, and two of its most essential features are joins and window functions. Joins allow you to combine data from multiple tables, while window functions enable advanced calculations across rows without collapsing them into aggregates. Whether you're a beginner or an experienced data analyst, understanding these concepts can significantly enhance your querying skills. In this article, we'll dive deep into both, with explanations, examples, and tips.

What Are SQL Joins?
Joins are used to retrieve data from two or more tables based on a related column between them.

Let's imagine a small e-commerce database with two tables:

-- customers
+----+---------+
| id | name    |
+----+---------+
| 1  | Aisha   |
| 2  | Brian   |
| 3  | Carla   |
+----+---------+

-- orders
+----+-------------+--------+
| id | customer_id | amount |
+----+-------------+--------+
| 1  | 1           | 250    |
| 2  | 1           | 100    |
| 3  | 2           | 400    |
+----+-------------+--------+

Notice Carla (id 3) has no orders yet. Keep that in mind: it matters a lot once we get to joins.

Part 1: SQL Joins

A join combines rows from two or more tables based on a related column, in our case customer_id.

INNER JOIN

Returns only rows that match in both tables.

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Result:

Aisha | 250
Aisha | 100
Brian | 400

Carla disappears entirely because she has no matching order. This is the most common join, but it silently drops unmatched rows, a frequent source of "wait, why is my data missing?" bugs.

LEFT JOIN

Returns all rows from the left table, plus matches from the right table. Unmatched rows get NULL.

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Result:

Aisha | 250
Aisha | 100
Brian | 400
Carla | NULL

Now Carla shows up, with NULL for amount. Use LEFT JOIN whenever you need to keep every record from your "main" table, matched data or not, e.g. "show me all customers, even ones who haven't ordered."

RIGHT JOIN

The mirror image of LEFT JOIN, it keeps all rows from the right table instead. It's less commonly used in practice; most people just flip the table order and use LEFT JOIN instead, since it's easier to read.

FULL OUTER JOIN (MySQL workaround)

MySQL doesn't support FULL OUTER JOIN natively. You simulate it with UNION:

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
UNION
SELECT customers.name, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

This gives you every row from both tables, matched where possible.

Quick rule of thumb: if you're not sure which join to use, ask "do I want to keep unmatched rows, and from which table?" That answer picks your join for you.

Part 2: Window Functions

Joins combine tables. Window functions let you do calculations across a set of rows, like running totals or rankings, without collapsing them into a single row (which is what GROUP BY does).

The basic shape:

SOME_FUNCTION() OVER (PARTITION BY column ORDER BY column)

PARTITION BY splits your data into groups (like GROUP BY, but without merging rows).
ORDER BY decides the order within each group, which matters for ranking and running totals.

ROW_NUMBER()

Assigns a unique, sequential number to each row within a partition.

SELECT
  customer_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS row_num
FROM orders;

This numbers each customer's orders from biggest to smallest, handy for finding "each customer's top order."

RANK() and DENSE_RANK()

Similar to ROW_NUMBER(), but they handle ties differently:

RANK(): ties share a rank, but leaves a gap afterward (1, 1, 3).
DENSE_RANK(): ties share a rank, no gap (1, 1, 2).

SELECT
  customer_id,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS overall_rank
FROM orders;

Running Totals with SUM()

SELECT
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY id) AS running_total
FROM orders;

For Aisha, this gives 250, then 350 (250+100), a running total per customer, without needing a self-join or subquery.

Comparing Rows with LAG() and LEAD()

LAG() looks at the previous row; LEAD() looks at the next row, great for comparing an order to the one before it.

SELECT
  customer_id,
  amount,
  LAG(amount) OVER (PARTITION BY customer_id ORDER BY id) AS previous_amount
FROM orders;

Joins vs. Window Functions: When to Use Which

Use case	Tool
Combining data from two+ tables	JOIN
Keeping all rows from one table regardless of matches	LEFT JOIN
Ranking rows within groups	ROW_NUMBER() / RANK()
Running totals, moving averages	Window function with SUM()/AVG()
Comparing a row to the previous/next one	LAG() / LEAD()
Collapsing rows into one summary row per group	GROUP BY (not a window function)

The key mental model: joins add columns from other tables; window functions add calculations across rows you already have, without losing any of them.

Wrapping Up

Start by getting comfortable with INNER JOIN and LEFT JOIN, since they cover the vast majority of real-world queries. Once those feel natural, window functions like ROW_NUMBER() and running SUM() will feel like a natural next step rather than dark magic.

Try rewriting a GROUP BY query you've written before using a window function instead. It's one of the fastest ways to make the concept click.

Happy querying! 🚀

Schemas in Power BI: Designing Models That Perform

Suzanne Orido — Wed, 04 Feb 2026 10:14:10 +0000

Power BI is a business intelligence and data visualization platform developed by Microsoft. It is used to connect to data from multiple sources, model that data, and produce interactive reports and dashboards for analysis and decision-making.
In the world of business intelligence, data modeling is the "blueprint" that determines whether a Power BI report will be a high-performance engine or a slow, confusing mess.

This article goes into detail on what data models are, why they are important in Power BI, and how to use star schemas and snowflake schemas when designing accurate data models.

What is a data model?

It is a visual representation of how different pieces of information relate to one another within a system.
Think of a data model as a map that defines how your information is stored, connected, and filtered. Without this map, Power BI has to guess how your tables relate, which often leads to errors.

Key components of a Data Model:

Tables: These are the containers for your data. In a good model, these are split into Fact tables (the numbers/metrics) and Dimension tables (the descriptive context).

Fact Tables
They store the measurable business events that an organization wants to analyze. Each row represents an occurrence of something that happened, at a defined level of detail. Facts answer “how much,” “how many,” or “how often.

A fact table contains dimension key columns that relate to dimension tables and numeric measure columns. The dimension key columns determine the dimensionality of a fact table, while the dimension key values determine the granularity of a fact table.

Some key characteristics include:

Large row counts
Numeric, aggregatable columns
Foreign keys linking to dimension tables
One clear grain per table

Dimension Tables
They store the descriptive context used to filter, group, and explain measures in fact tables. They answer “who,” “what,” “where,” and “when.”

Some key characteristics include:

Smaller than fact tables
Mostly categorical or textual attributes
One primary key
Referenced by fact tables through foreign keys
Rarely aggregated

Relationships in Power BI
Relationships in Power BI define how tables interact. They control which rows are included in a calculation. If relationships are wrong, results are wrong, regardless of visuals or DAX.

Common relationship types include:

One-to-many: standard and preferred (dimension → fact) Example: One Product → many Sales rows
One-to-one: rare, use cautiously Example: Employee details are split across two tables
Many-to-many: last resort, high risk Example: Customers belonging to multiple segments

Understanding Star Schema

A star schema is a data modelling structure where a central fact table is directly connected to multiple dimension tables, forming a star-like layout. It is the preferred schema for Power BI and most analytical systems.

Structure

One fact table at the center
Dimension tables radiating outward
Each dimension connects to the fact with a one-to-many relationship
No relationships between dimension tables

How it works
The fact table stores business events. Dimension tables provide descriptive context. Filters flow from dimensions to the fact, ensuring correct aggregation and predictable results.
Usually, fact tables represent the "many" aspect of a relationship, while dimension tables represent the "one" aspect.

Understanding Snowflake Schema

A snowflake schema is a data modelling structure in which dimension tables are normalized into multiple related tables rather than being stored as a single, flat dimension. The resulting layout resembles a snowflake rather than a star.

Structure

One central fact table
A dimension connected to the fact
That dimension is further split into sub-dimensions
Multiple joins required to reach descriptive attributes

When they appear in Power BI

When importing data directly from normalized source systems
When modelling is not intentionally redesigned for analytics

Although this reduces redundancy, too much snowflaking is discouraged because:

It increases query complexity.
It requires more joins.
It may slow down performance

Snowflake schemas are storage-efficient. Star schemas are analytics-efficient.

The importance of good data modelling

Good data modelling is critical because it determines whether the analysis is correct, fast, and repeatable. Visuals and calculations sit on top of the model; they cannot fix structural errors beneath it.

Accuracy
A good model enforces correct filter flow and aggregation. Measures return the same result regardless of visual layout. Poor models cause double-counting and inconsistent totals.

Performance
Power BI’s engine is optimized for star schemas and simple relationships. Clean models reduce joins, improve compression, and deliver faster query execution. Bad models scale poorly as data grows.

Simplicity
Well-modelled data requires fewer complex DAX expressions. Business logic lives in the model, not in workaround calculations. This reduces error rates and maintenance costs.

Consistency
Metrics are defined once and reused everywhere. Reports built by different authors produce the same numbers. This is essential for organizational trust.

Scalability
Good models support new measures, dimensions, and visuals without redesign. Poor models collapse under change and require rewrites.

Decision quality
Executives act on reported numbers. Incorrect models produce confident but wrong answers. That is operational risk.

In Power BI, data modelling is not preparation work. It is the core analytical task.

In conclusion, data modelling is what makes Power BI reports work correctly. When data is well organized into fact and dimension tables with clear relationships, reports are fast, and numbers are accurate. Poor modelling leads to slow reports and wrong results. Good insights start with a good data model.

Beginner-friendly introduction to MS Excel for Data Analytics

Suzanne Orido — Sun, 25 Jan 2026 18:12:59 +0000

Microsoft Excel is a spreadsheet application used to store, organize, and work with data. It helps turn raw data into useful information that supports decisions. It can be described as a digital table or grid for organizing, calculating, and analyzing information.

While professional data analysts often use more advanced tools for big projects, Excel remains popular because it's accessible, familiar, and powerful enough for most everyday data tasks.

In Excel, data is arranged in rows(horizontal groups of cells) and columns(vertical groups of cells). A cell is a single box in Excel's grid where you can store one piece of information.

Why use MS Excel for Data Analytics?

It's easy to learn and use, even for beginners
It quickly organizes large amounts of data
It performs fast calculations and basic statistical analysis
It helps identify trends through charts and pivot tables
It is widely available and accepted in most workplaces
It allows quick insights without complex software

Before analyzing data, it's important to understand the basic interface of Excel. This includes knowing the definition of the following:
Row: A horizontal group of cells labelled 1,2,3... on the left.
Column: A vertical group of cells labelled A,B,C... at the top.
Cell: Intersection of a row and a column.
Workbook: The entire Excel file that contains one or more sheets.
Worksheet: The individual page inside the workbook.

In this article, we will learn:

Data cleaning: Removing duplicates, sorting data, filtering data
Performing calculations using formulas
Performing calculations using operators
Data visualization: Using pivot tables, charts, and dashboards

Data Cleaning

Data cleaning is important because analysis is only as good as the data used.
Some of the cleaning tasks include:

Removing duplicates

This means identifying and deleting repeated entries in a dataset so that each record appears only once.
To do this:
Select the data or the column that may contain duplicates.
Click the Data tab.
Choose Remove Duplicates.
Tick the column(s) Excel should check.
Click OK.

How to clean text with Functions:
TRIM() removes extra spaces
UPPER() and LOWER() standardize text formatting

Sorting Data

Sorting data in Excel means arranging data in a specific order to make it easier to understand and analyze.

To do this:

Select the column or the entire dataset.
Go to the Data tab.
Click Sort A to Z (ascending) or Sort Z to A (descending).

Filtering Data

Filtering data in Excel displays only the rows that meet specific conditions while hiding the rest, allowing you to focus on certain values, dates, or number ranges without deleting any data.

To do this:

Select your dataset.
Go to the Data tab and click Filter.
Small dropdown arrows appear in the column headers.
Click a column’s dropdown arrow.
Choose the criteria you want (specific values, dates, or number ranges).
Excel displays only the rows that meet the selected criteria, hiding the rest.

Performing calculations using formulas:

Click the cell where you want the result to appear.
Type an equal sign (=) to start the formula.
Enter the calculation using numbers, cell references, and operators (like +, -, *, /). For example: =A1+B1 or =C2*D2.
Press Enter to see the result.

=SUM(A2:A877) → Adds all numbers in the range
=AVERAGE(A2:A877) → Calculates the average
=MIN(A2:A877) → Finds the smallest value
=MAX(A2:A877) → Finds the largest value
=COUNT(A2:A877) → Counts numeric values
=COUNTA(A2:A877) → Counts all non-empty cells

Performing calculations using operators:

+ → Addition e.g., =A1+B1
- → Subtraction e.g., =A1-B1
* → Multiplication e.g., =A1*B1
/ → Division e.g., =A1/B1
^ → Exponentiation / Power e.g., =A1^B1

Data Visualization

Pivot Tables
A pivot table is an Excel tool used to quickly summarize and analyze large amounts of data.

It works by reorganizing data to show totals, averages, counts, or comparisons without changing the original dataset.

How to create a Pivot Table:

Select your dataset.
Go to the Insert tab and click PivotTable.
Choose whether to place the pivot table in a new worksheet or the existing worksheet.
Click OK.
In the PivotTable Field List, drag fields into Rows, Columns, Values, or Filters to arrange and summarize your data.

Charts
A chart in Excel is a visual representation of data, like bars, lines, or pie slices, that makes patterns and trends easier to understand.

To create a chart:

Select the data you want to visualize.
Go to the Insert tab.
Choose the chart type you want (e.g., Column, Line, Pie, Bar).
Click the chart style, and Excel inserts it into your worksheet.
Customize the chart using chart tools (titles, labels, colors) if needed.

Dashboards
A dashboard in Excel is a single screen or sheet that displays key information and metrics from your data in a clear, visual way using charts, tables, and pivot tables.

An Excel dashboard typically consists of:

Charts – Bar, line, pie, or combo charts to visualize trends and comparisons.
Pivot Tables – Summarize large datasets into totals, averages, counts, or other key metrics.
Tables – Organized data or key figures for reference.
Slicers and Filters – Interactive tools to quickly view specific data.
Key Performance Indicators (KPIs) – Numbers, percentages, or symbols that show performance at a glance.
Text Boxes / Labels – Titles, headings, or explanations for clarity.

With that, you now know the basics and can Excel in data analytics. Cheers! 😄

Git and GitHub

Suzanne Orido — Fri, 16 Jan 2026 19:31:14 +0000

What is Git?

Git is a distributed version control software system that is capable of managing versions of source code or data. It is often used to control source code by programmers who are developing software collaboratively.

Why is version control important?

It is a system that records every change made to a file or set of files over time so that you can recall specific versions later.
The importance of version control includes:

Safety Net: You can "undo" mistakes by rolling back to any previous version of your project if something breaks.
Teamwork: It allows multiple people to work on the same files simultaneously without overwriting each other's progress.
Organization: It eliminates messy file naming (like final_v2_new.doc) by keeping one clean version while storing the history in the background.
Branching: You can create "branches" to experiment with new ideas in isolation without risking the stability of the main project.
Documentation: Every change is saved with a note (commit message), creating a detailed history of who changed what and why.

How to push code to Github

To push your code to GitHub, you first need a repository (a project folder) hosted on their site. Here is the standard workflow using the terminal.

1. Initial Upload (New Repository)

Use this sequence to link a local project to a newly created GitHub repository.

- Create Remote Repository: On GitHub, create a new repository. Do not initialize with a README, .gitignore, or license if code already exists locally.

- Initialize Local Git: Navigate to the project root and execute: git init

- Stage Files: Add all files to the staging area: git add.

- Create First Commit: Record the snapshot: git commit -m "initial commit"

- Define Main Branch: Ensure the primary branch is named main: git branch -M main

- Link Remote URL: Connect the local repository to GitHub: git remote add origin <github-repo-url>

Execute Push: Upload the code and set the upstream reference: git push -u origin main

2. Standard Synchronization (Existing Repository)

Use this sequence for ongoing updates to a repository that is already linked.

Stage Specific Changes: git add <file-name> (or git add. for all changes)

Commit: git commit -m "description of changes"

Push: git push

How to pull code from GitHub

Code retrieval from GitHub is categorized into two primary operations: Initial Acquisition (Clone) and Synchronization (Pull).

1. Clone:

Used when the code doesn't exist on the local machine

Identify Repository URL: On the GitHub repository page, click the Code button and copy the URL (HTTPS or SSH).
Initialize Download: Open the terminal and execute: git clone <repository-url>
Result: Git creates a directory named after the repository, downloads all files, branches, and the full commit history, and configures the remote reference (origin).

2. Pull/Synchronization

Used to update an existing local repository with changes from Github
- Navigate to Directory: Enter the local project folder.

- Execute Update: Run the following command: git pull origin <branch-name> Example: git pull origin main

- Mechanism: This command executes two sub-operations:

Fetch: Downloads remote data without altering local files.

Merge: Integrates remote changes into the current active branch.

How to track changes using Git

Git tracks changes by managing data between three logical states: the Working Directory (unsaved changes), the Staging Area (prepared changes), and the Local Repository (permanent history).

1. Initialization

Activate Git tracking in a project directory: git init This creates a .git subdirectory to store metadata and object databases.

2. State Verification

Determine the current status of files: git status

- Untracked: New files unknown to Git.

- Modified: Tracked files with unsaved changes.

- Staged: Changes moved to the Index, ready for the next snapshot.

3. Change Preparation (Staging)

Select specific changes for inclusion in the next version:

Single file: git add <file-name>
All changes: git add .
Interactive (partial file): git add -p

4. Version Finalization (Commit)

Record the staged changes as a permanent snapshot: git commit -m "Direct description of change" Standard format: Use imperative mood (e.g., "Fix logic error" rather than "Fixed logic error").

5. Change Analysis Tools

Real-time Differences

Analyze modifications before staging or committing:

- Unstaged changes: git diff (Compares working directory to staging area)

- Staged changes: git diff --staged (Compares staging area to last commit)

Historical Review

Inspect the chronological record of changes:

- Summary list: git log --oneline

- Detailed patches: git log -p

- File-specific history: git log -- <file-path>

Line-Level Attribution

Identify when and by whom specific lines were altered: git blame <file-name>

DEV Community: Suzanne Orido

Connecting Power BI to SQL Databases

Connecting Power BI to SQL Databases

Table of Contents

1. Introduction: Power BI and SQL Databases

Why connect Power BI to a database?

The role of SQL databases in analytical workflows

2. Connecting to a Local PostgreSQL Database

Prerequisites

Step-by-step connection process

3. Connecting to Aiven Cloud PostgreSQL

Obtaining connection details from Aiven

Why SSL certificates are required

Step-by-step: connecting via Power BI Desktop

4. Loading Tables and Creating Relationships

How Power BI auto-detects relationships

Creating and editing relationships manually

What data modelling enables

5. Why SQL Skills Matter for Power BI Analysts

Four ways SQL strengthens Power BI work

SQL in the context of this schema

Summary: SQL and Power BI as complementary layers

Mastering SQL Joins and Window Functions: A Comprehensive Guide

Part 1: SQL Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN (MySQL workaround)

Part 2: Window Functions

ROW_NUMBER()

RANK() and DENSE_RANK()

Running Totals with SUM()

Comparing Rows with LAG() and LEAD()

Joins vs. Window Functions: When to Use Which

Wrapping Up

Schemas in Power BI: Designing Models That Perform

What is a data model?

Understanding Star Schema

Understanding Snowflake Schema

The importance of good data modelling

Beginner-friendly introduction to MS Excel for Data Analytics

Why use MS Excel for Data Analytics?

Data Cleaning

Removing duplicates

Sorting Data

Filtering Data

Performing calculations using formulas:

Performing calculations using operators:

Data Visualization

Git and GitHub

What is Git?

Why is version control important?

How to push code to Github

1. Initial Upload (New Repository)

2. Standard Synchronization (Existing Repository)

How to pull code from GitHub

1. Clone:

2. Pull/Synchronization

How to track changes using Git

1. Initialization

2. State Verification

3. Change Preparation (Staging)

4. Version Finalization (Commit)

5. Change Analysis Tools

Real-time Differences

Historical Review

Line-Level Attribution