DEV Community: Philip Weit

Connecting Power BI to a PostgreSQL Database & Building a Dashboard

Philip Weit — Sun, 22 Mar 2026 07:37:49 +0000

Power BI is a business intelligence tool developed by Microsoft that allows users to analyze data and create interactive reports and dashboards. It helps organizations transform raw data into meaningful insights that support decision-making.

Companies use Power BI because it can connect to multiple data sources, especially SQL databases, where most business data is stored. SQL databases such as PostgreSQL are important because they:

Store structured data efficiently
Allow querying using SQL
Support large-scale data analysis
Serve as the backbone of reporting systems

Connecting Power BI to a database allows analysts to build dashboards directly from real, structured data.

Connecting Power BI to a Local PostgreSQL Database

Below is the actual process followed in Power BI Desktop:

Step 1: Open Power BI and Select PostgreSQL

Open Power BI Desktop
Click Get Data
Select PostgreSQL database
Click Connect

Step 2: Enter Connection Details

Server: localhost:5432
Database: postgres (name of the database)
Select Import mode
Click OK

If an encryption warning appears, click OK to proceed without SSL (this is acceptable for local databases).

Step 3: Load Tables into Power BI

**
Enter your username and password
The Navigator window will appear
Select the tables:
customers
products
sales
inventory
Click Load

See below the loaded data in model mode.

SQL Joins & Window Functions

Philip Weit — Wed, 11 Mar 2026 03:09:07 +0000

JOINS

They allow us to work with multiple tables and allows us to join data in different tables. Joins happen when there's is a primary and foreign keys-they allow us to reference from our tables uniquely. There are diff types of joins. Below briefly explains the most common ones.

Inner Join -Returns only the rows that have matching values in both tables.

select * from sales s inner join products p on s.product_id = p.product_id

That's generally the syntax for joins across the diff types of joins. The first tables(sales) is the left table and the other the right - this part will be important when we dive into other types of joins. In this case what will be returned will be rows with product_id in both tables. s.product_id is the primary key while the p.product_id id the foreign key.

Left Join - Returns all rows in the left table and match rows from right table. Syntax is similar to inner join bar instead of inner its left
select * from sales s left join products p on s.product_id = p.product_id
Right Join - Returns all rows in the right table and match rows from left table. Syntax is similar to inner join bar instead of inner its left
select * from sales s right join products p on s.product_id = p.product_id

The most common joins are inner join and left join. Other types of joins include full join which returns all rows from the right and left table. We also have a self-join which is used to join a table to itself using diff aliases

WINDOW FUNCTIONS

A window function performs a calculation across a group of rows that are related to the current row. This group of rows is called a window. Unlike aggregate functions, window functions do not collapse rows into a single result. Instead, they return a value for every row. See below general syntax

function_name() OVER ( PARTITION BY column ORDER BY column )

function_name() - the calculation (ROW_NUMBER, RANK, SUM, DENSE_RANK)
OVER () → defines the window
PARTITION BY - splits data into groups
ORDER BY - determines calculation order within the group

Below are some common window functions:

ROW_NUMBER () - Assign unique ranking
RANK () - Ranking with gaps
DENSE_RANK () - Ranking without gaps
SUM () - Running totals
LAG () - Access previous row
LEAD () - Access next row

SQL window functions are powerful tools that extend the capabilities of SQL beyond simple aggregation. By allowing calculations across related rows while preserving the dataset structure, they make tasks like ranking, running totals, and comparisons much easier for analysts.

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

Philip Weit — Tue, 10 Feb 2026 01:56:18 +0000

Data rarely arrives clean, logical, or decision-ready.

It shows up late, incomplete, duplicated, mislabelled, and sometimes straight-up wrong. Yet somehow, analysts are still expected to turn this chaos into clear insights and confident decisions.

This is the invisible work behind Power BI dashboards—and it’s more than dragging visuals onto a canvas.

This article walks through how analysts translate messy data, complex DAX, and dashboards into real business action using Power BI.

1. Messy Data Is the Default, Not the Exception

In theory, data pipelines are clean and structured.
In reality, analysts deal with:

Multiple Excel files with different column names

Dates stored as text (01/07/25 vs 7-1-2025)

Missing values, duplicates, and manual entries

Data coming from ERP systems, CRMs, fuel logs, or Google Sheets

Power BI starts here—not at visuals.

The Analyst’s First Job: Make Data Trustworthy

Before any DAX or dashboarding:

Clean and standardize data in Power Query

Align naming conventions and units

Create consistent date tables

Validate totals against source systems

If the data isn’t trusted, the dashboard won’t be used—no matter how good it looks.

A dashboard isn’t wrong because of bad visuals.
It’s wrong because the data underneath wasn’t questioned.

2. DAX Isn’t About Complexity — It’s About Meaning

DAX scares a lot of people because it looks like programming.

But good DAX isn’t clever—it’s intentional.

Analysts use DAX to answer questions like:

What does “average” really mean here?

Should this metric respect filters or ignore them?

Are we comparing performance over time correctly?

What’s the business definition of “profit” or “efficiency”?

Example: A Simple Measure, Big Impact
Fuel Efficiency =
DIVIDE(
SUM(Fuel[Distance Covered]),
SUM(Fuel[Fuel Consumed])
)

Looks simple—but behind it are decisions:

Should returns be excluded?

What happens when fuel consumed is zero?

Does this respect vehicle, month, and region filters?

DAX is where business logic becomes math.

3. Dashboards Are Translation Tools, Not Data Dumps

A common mistake is trying to show everything.

Great Power BI dashboards do the opposite:
They reduce noise and guide attention.

An analyst asks:

What decision should this dashboard support?

Who is the audience?

What should they do differently after seeing this?

Effective Dashboards:

Highlight exceptions, not averages

Use KPIs to show performance vs targets

Tell a story from left to right, top to bottom

Use color sparingly and intentionally

If a dashboard needs a 30-minute explanation, it failed.

4. From Insight to Action: The Analyst’s Real Value

The real win isn’t a beautiful report—it’s behavior change.

Examples:

A fleet manager changes driving policies after seeing fuel inefficiency trends

Finance questions supplier pricing due to cost variance visuals

Operations schedules maintenance earlier based on usage patterns

This happens when:

Metrics are clearly defined

Trends are easy to interpret

Insights are tied to real-world decisions

Power BI doesn’t create action.
Analysts do.

5. The Skill That Matters Most: Context

Tools evolve. DAX functions change. New visuals get released.

But the most valuable analysts understand:

The business problem

The operational constraints

The human using the dashboard

They know when not to build a report.

They translate:

Data → Insight

Insight → Decision

Decision → Action

That’s the job.

Final Thoughts

Power BI is powerful—but it’s just a tool.

What makes dashboards valuable isn’t:

Fancy visuals

Complex DAX

Big datasets

It’s the analyst’s ability to translate chaos into clarity.

And that skill will always be in demand.

Schemas and Data Modelling in Power BI

Philip Weit — Sun, 01 Feb 2026 01:14:41 +0000

Data modelling is a critical part of building effective Power BI reports. It involves organizing data into tables, defining relationships, and structuring the model in a way that supports fast performance and accurate analysis. A well-designed data model makes reports easier to understand, improves performance, and ensures reliable insights.

At the core of Power BI data models are fact tables and dimension tables. Fact tables store measurable data such as sales, revenue, or quantities, and usually contain a large number of rows. Dimension tables provide descriptive information—such as product, customer, date, or region—that helps users filter and analyze the facts.

One of the most recommended modelling approaches in Power BI is the star schema. In a star schema, a central fact table is connected to multiple dimension tables through one-to-many relationships. This design is simple, easy to understand, and highly optimized for Power BI’s in-memory engine. Star schemas improve report performance, simplify DAX calculations, and reduce the risk of ambiguous relationships.

A snowflake schema is a variation of the star schema where dimension tables are further normalized into additional tables. While this reduces data redundancy, it introduces extra relationships and complexity. In Power BI, snowflake schemas often lead to slower performance and more complicated DAX, making them less desirable than star schemas.

Relationships define how tables interact in Power BI. One-to-many relationships with single-direction filtering are considered best practice. Poorly designed relationships—such as unnecessary many-to-many or bi-directional relationships—can cause incorrect totals and unexpected results.

Good data modelling is essential for performance and accuracy. It enables faster queries, correct aggregations, simpler DAX formulas, and scalable report designs. In Power BI, strong data modelling is the foundation of reliable reporting and effective decision-making.

Introduction to MS Excel for Data Analytics

Philip Weit — Sun, 25 Jan 2026 11:01:13 +0000

Microsoft Excel is one of the most commonly used tools for basic data analytics, especially for beginners. It allows users to store, organize, analyze, and visualize data without needing programming skills.

At its core, Excel works with rows and columns. Each column represents a variable (such as Date, Region, Revenue), while each row represents a record. This structure makes data easy to understand and analyze.See below image:

Excel is widely used for data cleaning and exploration. Users can sort data (for example, highest to lowest sales) and filter data to focus on specific regions, products, or time periods. These simple features already provide powerful insights.

Excel also supports basic calculations using formulas. Common functions like SUM, AVERAGE, and COUNT help transform raw data into meaningful metrics such as total revenue, average sales, or number of transactions.

One of Excel’s strongest features for data analytics is the PivotTable. PivotTables allow users to quickly summarize large datasets, such as total revenue by region or sales by channel, without writing complex formulas.See below:

Finally, Excel makes it easy to visualize data using charts. Bar charts, line charts, and pie charts help reveal trends, comparisons, and proportions, making insights easier to communicate.

In summary, Excel is an excellent starting point for data analytics. It is easy to learn, widely used in industry, and powerful enough for many real-world analysis tasks.

Git Basics for Beginners: Push, Pull, and Track Changes

Philip Weit — Sat, 17 Jan 2026 18:57:06 +0000

What Is Git?

Git is a tool that helps you track changes in your files. It lets you save versions of your work and go back if something breaks.
GitHub is where your Git projects are stored online.

What Is Git Bash?

Git Bash is a terminal for Windows that allows you to use Git commands to manage your projects.

Starting a Git Project

Go to your project folder:

cd path/to/project

Start Git tracking:

git init

Tracking and Saving Changes

Check file status:

git status

Stage files:

git add .

Save changes:

git commit -m "My first commit"

A commit saves a version of your work.

Push Code to GitHub

Connect your project to GitHub:

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

Upload your code:

git push -u origin main

Pull Updates from GitHub

Get the latest changes:

git pull origin main

Simple Git Workflow

Edit files
git add .
git commit -m "message"
git push

Conclusion

Git helps beginners manage projects safely and build a GitHub portfolio. Once you understand the basics, it becomes an essential everyday tool.

This article was written as part of my data analytics bootcamp to document my learning journey with Git and GitHub.