1. Introduction
Over the past few days at LuxDevHQ, I’ve been learning Power BI as part of my data engineering journey. In class, we covered how data flows at a high level—but I kept feeling like I understood it… without really understanding it.
So I decided to slow down and build a model myself from scratch.
That’s when I realized something:
Data modeling isn’t just a step—it’s the part that decides whether everything else works or breaks.
This article isn’t a perfect guide. It’s more of a reflection of what started making sense to me after actually trying, failing, and fixing things.
2. Shaping Data
What Joins Actually Mean to Me Now
At first, joins just felt like definitions I had to memorize. But when I started using them in Power Query, they became more practical.
When I use Merge Queries, I’m basically deciding:
“Which data do I trust enough to keep?”
INNER JOIN
I use this when I only want “clean matches.”
When I tried this on my Sales and Product tables, I noticed something:
Some sales just disappeared.
At first, I thought I broke something—but then it hit me:
Those records didn’t have matching Product IDs.
So INNER JOIN isn’t just merging—it’s also filtering out bad or incomplete data.
Visualizing how INNER JOIN only keeps matching records between tables.
LEFT OUTER JOIN (My Default)
This is the one I keep coming back to.
It lets me say:
“Keep everything I started with, and just add what matches.”
When I used this with Customers and Orders, I could still see customers who never bought anything—which actually felt important.
LEFT ANTI JOIN (Unexpected Favorite)
I didn’t expect to like this one, but it turned out to be super useful.
It shows what’s missing.
That helped me find:
- Products that were never sold
- Records that didn’t match anything
It felt less like analysis and more like debugging my data.
3. Relationships: The Part That Confused Me Most
Joins made sense after a while—but relationships took longer.
At first, I kept thinking:
“Didn’t I already combine these tables?”
But then I realized:
Relationships don’t combine data—they control how tables talk to each other.
That shift in thinking helped a lot.
One-to-Many (1:M)
This is the structure I now try to aim for.
One Product → Many Sales
Simple in theory—but I ran into problems when my data didn’t behave that way.
That’s when I learned:
If this isn’t clean, everything downstream starts acting weird.
Filter Direction (A Small Setting That Caused Big Problems)
I once left cross-filtering on Both… and my visuals started giving confusing results.
Now I stick to:
Dimension → Fact
It just feels more predictable and easier to reason about.
Active vs Inactive Relationships
This one only clicked when I had two date columns.
I remember thinking:
“Why is Power BI ignoring one of my dates?”
Turns out—it wasn’t ignoring it. It just needed me to explicitly choose when to use it.
The solid line shows the active relationship. The dotted line is inactive—this is why one of my dates wasn’t working until I explicitly used it.
4. Fact vs Dimension: How I Simplified It in My Head
To avoid overthinking, I started labeling tables like this:
- Facts = what happened
- Dimensions = details about what happened
That made things clearer.
Sales table? → Fact
Product table? → Dimension
Once I saw it that way, structuring the model became less confusing.
5. Star Schema: The Structure That Finally Made Things Easier
I tried different ways of organizing tables—but the one that consistently worked was the Star Schema.
It just felt… cleaner.
One central table (Sales), surrounded by supporting tables (Product, Customer, Date).
When I used this:
- My relationships made more sense
- My calculations became easier
- My model looked less chaotic
Snowflake Schema (Looked Nice, Felt Complicated)
Breaking dimensions into smaller tables looked organized at first.
But when I actually used it, I kept losing track of how everything connected.
It felt like too many steps just to answer a simple question.
Compared to a star schema, this felt more complex and harder to follow when I was building my model.
Flat Table (Learned This the Hard Way)
I tried putting everything into one big table.
At first, it seemed easier—no relationships to worry about.
But then:
- The file got heavy
- Everything slowed down
- It became harder to manage
That experiment alone convinced me why structure matters.
6. Mistakes That Taught Me the Most
This is probably where I learned the most.
Many-to-Many Problem
My numbers didn’t make sense. Totals were too high.
This kind of relationship caused my totals to be incorrect because values were being counted multiple times.
I didn’t understand why—until I realized:
I had duplicates on both sides.
Fixing it with a bridge table felt like unlocking something.
Circular Relationships
At one point, my filters were looping—and I didn’t even notice at first.
The model looked fine, but results were off.
Fixing the direction of filters made everything clearer.
Multiple Dates (This One Was Subtle)
Having Order Date and Ship Date in the same table confused me.
Connecting both to the same Date table felt right—but only one worked at a time.
Learning about inactive relationships helped me finally understand why.
7. What I’m Taking Away From This
If there’s one thing this experience taught me, it’s this:
A dashboard isn’t powerful because of visuals—it’s powerful because of the model behind it.
Right now, my approach is simple:
- Clean data in Power Query
- Keep relationships clear and structured
- Stick to a Star Schema whenever possible
Final Thought
I’m still learning—but this process made one thing clear:
Data modeling is less about memorizing concepts and more about understanding how data behaves.
And honestly, most of what I’ve learned so far didn’t come from getting it right—it came from fixing what I got wrong.
Top comments (0)