Power BI is Microsoft's tool for turning raw data , an Excel file, a database, into interactive dashboards and reports, without needing to write much code. You connect a data source, clean it up, build relationships between tables, and then drag fields onto charts that anyone can click through and filter. What makes it powerful, from what I've seen building this project, is that it isn't just a chart-maker. It handles genuinely large datasets without any struggles , it can refresh itself automatically once published to the cloud, and DAX (a formula language) ,lets you build calculations far beyond what a spreadsheet can do ,year-over-year comparisons, running totals, conditional logic, all recalculating instantly as someone filters the report. And because it's free to start with (Power BI Desktop costs nothing), the barrier to trying it is basically zero. For a dataset like mine , a few hundred farm records ,it's overkill in raw power, but that's exactly why it scales so well once a project grows past a class assignment. *Why I started with data modelling first * Before I built a single chart, I had to decide how my tables should be organized . That decision ,data modelling is easy to skip past, but it quietly controls four things later: How fast my reports load — a messy model makes Power BI do more work for every click. How easy my formulas are to write — a clean model means shorter, simpler DAX. How well the model grows — can I add a new county or crop next season without breaking everything? How easy it is for someone else to follow — a teammate (or future me) should be able to open my file and understand it in minutes. So before touching visuals, I worked through the three common ways to organize tables in Power BI: a flat table, a star schema, and a snowflake schema — and looked at which one actually fits my project. 1.The Flat Table A flat table is the simplest possible design: everything lives in one wide table. Every fact (like revenue or yield) sits in the same row as every descriptive detail (like the farmer's name, the county, the crop type, the season). This is exactly how I built my Kenya Crops Power BI file , one single table called Kenya Crops Data set , with columns for the farmer, county, crop type, season, soil type, dates, yield, revenue, cost, and profit, all in the same table. What I like about it It's very easy to follow ,what I see is what I get. No relationships to set up. Nothing to link. It was the fastest way to get my data in and start building measures and visuals early on. What bothers me about it There's a lot of repetition. If "Kericho" is the county for 500 of my records, the word "Kericho" is physically stored 500 times instead of once. As I add more rows, the file gets heavier and slower to work with. If I ever needed to correct a county's spelling, I'd risk missing some of the rows it appears in. I can't easily reuse my "County" or "Crop Type" list in another table if I add one later. Why I still went with it for now For a project this size ,a single class dataset , a flat table was genuinely a reasonable starting point, which is why I built it this way first. I don't think it would hold up well if I connected more data sources or scaled this into a real reporting tool, but for getting the measures and visuals working, it did the job. On performance: I noticed flat tables are simple to build but get slower to use as the data grows, because DAX has fewer shortcuts to lean on when everything sits in one giant table. 2. The Star Schema (what I'd redesign it into) A star schema splits the same information into two kinds of tables: One central table of events (a fact table) -in my case, one row per farm's crop record: yield, revenue, cost, profit. Several smaller tables around it that describe things about those events (dimension tables) ; one table for farmers, one for crop types, one for counties, one for dates. Each dimension table connects straight to the fact table, like the points of a star. (See Diagram 2 below for how I'd redesign my own data this way.) What I like about this design No repeated text ;"Kericho" would be stored once in DimCounty, and every fact row just points to it. It's fast. Power BI and DAX are built to work brilliantly with this shape. Filtering becomes simple ,dragging "County" onto a chart automatically filters my fact table correctly. Anyone looking at the model diagram could immediately understand what connects to what. The trade-off It's more setup work upfront ;I'd have to split my one flat table into several related tables, which takes planning. A little duplication within a dimension table is normal and fine (that's the trade-off for speed). Why I'd choose this if I rebuilt the project If I were taking this dataset further , this is the design I'd commit to. It's the standard choice for a proper Power BI report. 3. The Snowflake Schema A snowflake schema takes a star schema one step further: one or more dimension tables gets split again into smaller, more detailed tables. For my data, I could split my DimCounty table further ;pulling "Region" out into its own DimRegion table connected to DimCounty. (See Diagram 3 below.) *What it would give me * Even less repeated data ; a region name stored once, not once per county. It would make sense if "Region" was a big, shared list reused across several fact tables. Why I'm not going this route Power BI has to "hop" through an extra table to answer some questions, which adds a bit of complexity and can slow things down slightly. It's more tables for me to manage and a busier model diagram, for a benefit I don't really need on a project this size. My take: for my Kenya Crops project specifically, I decided snowflaking isn't worth it ; my dimensions (farmer, crop, county, date) aren't big or reused enough to justify the extra hops. Quick comparison , how I'd weigh my three options Flat Table (what I have)|Star Schema (what I'd move to) |Snowflake Schema Setup effort Lowest Medium Highest Report speed Slows down as I add data Fast Fast, with tiny extra hops Data repetition High Low Lowest Easiest for me to read Yes, for now Yes, overall Gets busy fast Best fit for my project Early exploration If I scale this up Not needed here 4. Fact Tables vs Dimension Tables, in my own data Once I split away from my flat table, everything in my model would fall into two roles: A fact table stores the numbers that happened ; the measurable events. In my dataset that's Yield (Kg), Revenue (KES), Cost of Production (KES), and Profit (KES). Common naming elsewhere: FactSales, FactOrders, FactTransactions ;mine would be FactCropProduction. A dimension table stores the descriptions ;who, what, where, when ;the things I'd use to slice, filter, or label a report. Mine would be DimFarmer, DimCropType, DimCounty, DimDate. The way I remember it: numbers I add up belong in the fact table; words I filter by belong in dimension tables. One more idea I had to get straight for myself: grain. The grain of my fact table is the answer to "what does one row actually represent?" For my data, that's one farmer's one crop record for one season ,not one farmer, and not one county on its own, but that specific combination. Every measure I write assumes that grain, so I made sure I understood it before writing any DAX. The practical picture of my fact table connected to its dimension tables is the same star layout as Diagram 2 below. 5. Relationships — how I'd connect my tables A relationship is a link between two tables that tells Power BI "these two are connected, and here's how." I'd need relationships the moment I split my flat table into a star schema, so Power BI knows how to bring everything back together for a report. There are three kinds, based on cardinality — how many matching rows sit on each side. (See Diagram 4 below for how I'd map each type onto my own tables.) *One-to-Many (1:) **— the one I'd use almost everywhere. One row in a dimension table matches many rows in my fact table. In my case: one crop type ("Maize") would appear on many of my farm records. *One-to-One (1:1) * rare, and I don't actually need it in this project, but I'd use it only if I ever split off a DimFarmerContact table from DimFarmer, where each farmer has exactly one contact record. Many-to-Many (:) I'd avoid this unless I truly needed it. An example from my own domain: if a farm could hold several certifications, and a certification could apply to several farms, that relationship would need to be many-to-many. I don't have this in my current data, so I wouldn't add it just to have it. Why Primary Keys, Foreign Keys, and uniqueness mattered to me here A Primary Key (PK) -is the column that uniquely identifies each row in a dimension table — no duplicates. In my DimCounty, CountyID would be unique: each county appears exactly once. ** A foreign key** is the matching column in my fact table that points back to that primary key — and it's expected to repeat. CountyID would show up once for every single farm record in that county. This is exactly why, in my data, CountyID would be unique in the dimension table but appear many times as a foreign key in the fact table — that repetition is what makes the "many" side of my one-to-many relationship work. Referential integrity means every foreign key in my fact table actually has a matching row in the dimension table — I wouldn't want a farm record pointing at a county that doesn't exist anywhere in DimCounty. Active vs inactive relationships: Power BI only lets one relationship between two tables be "active" at a time. If I needed a second connection — say, filtering by both Planting Date and Harvest Date against the same date table — the second one would be created inactive, and I'd only switch it on inside a specific formula when I needed it. 6. Filter Direction — the part I had to slow down and think about Once my tables are connected, Power BI needs to know which way a filter is allowed to travel through that connection. (See Diagram 5 below.) Single-direction filtering (the default, and what I'd keep almost everywhere): picking a value in a dimension table filters my fact table, but not the other way round. If I picked "Maize" in a DimCropType slicer, it would filter FactCropProduction down to maize rows only — predictable, and exactly what I'd want. Bidirectional (both) filtering: the filter can travel both ways. I'd only switch this on deliberately, and only if I had a genuine reason. Why I'd be careful with it It can create ambiguous filter paths if two routes exist between two tables, Power BI might not know which one to use, and can refuse to calculate correctly. It adds hidden complexity — a report could start behaving oddly because a filter is quietly touching a table I didn't expect it to affect. It makes troubleshooting harder. My plan is to leave relationships single-direction unless I hit a specific case that needs otherwise. *7. Joins — what I'd use in Power Query to combine tables * Everything above happens inside the data model. But there's an earlier stage — Power Query — where I can also combine tables using a Merge. This is Power BI's version of a "join." To explain this to myself, I used two simple tables from my own domain: a Farmers table (farmer name, county, crop) and a CountyInfo table (county, region, soil type). (See Diagram 6 below for all six join types laid out.) Inner Join — keeps only rows that exist in both my tables. If a farmer's county isn't listed in CountyInfo, that farmer would be dropped from the result. Left Outer Join — keeps every row from my first (left) table, and adds matching details from the second table where available. Unmatched rows just get blanks. This is the one I'd reach for most, since I never lose data from my main table. Right Outer Join — the mirror image: keeps every row from the second (right) table, adding details from the first where they match. *Full Outer Join *— keeps every row from both tables, matched where possible, kept even where there's no match on either side. Left Anti Join — keeps only the rows from my first table with no match at all in the second. I could use this to check: "which of my farmers have a county that isn't in my reference list?" *Right Anti Join *— the mirror image: "which counties in my reference list have no farmer records at all?" A practical example from my own data: if I merged Farmers with CountyInfo using a Left Outer Join on County, I'd get every farmer, now with Region and Soil Type added as new columns — and any farmer whose county wasn't in CountyInfo would just show blanks for those two columns instead of disappearing. ***8. Power Query Joins vs Power BI Relationships* — clearing this up for myself ** This was the part I had to slow down on the most, so I'm explaining it the way I'd want it explained to me. A Power Query merge physically combines data. If I merge Farmers with CountyInfo, Power Query actually copies the matching columns (Region, Soil Type) into my Farmers table. I end up with one new, wider table. This happens before the data even reaches my data model — it's part of loading and cleaning. A Power BI relationship doesn't combine anything. It just tells my model "these two tables are related." The tables stay separate; Power BI knows how to look across them when a report needs to. No columns get copied anywhere. **Power Query Merge |Power BI Relationship** Happens at, Data loading / cleaning stage Inside the data model, after loading Result One new, combined table Two tables stay separate, just linked Effect on my data size, Makes tables wider No change to table size I'd use it for ,Pulling in one or two lookup columns I need permanently Connecting my fact and dimension tables for reporting *When I'd choose a merge instead of a relationship: * mainly when I need one column from another table baked directly into my main table — for example, pulling a single "Region" column in so it shows up in one specific visual, without building a whole separate dimension table just for that. *Why I wouldn't just merge everything: * if I merged all my tables into one giant table "just in case," I'd really just be rebuilding my flat table — and I'd lose everything a star schema gives me: the repeated data comes back, the file gets heavier, and I couldn't reuse a clean DimCounty table if I added a second fact table later. *Why I'd keep my fact and dimension tables separate: * each piece of information gets stored once, relationships handle the joining automatically and efficiently at report time, and my model stays flexible — I could plug in a new fact table (say, FactFertiliserUsage) later and reuse the same DimCounty and DimFarmer tables without touching them. 9. My recommendation If I were rebuilding this for a real, ongoing project rather than a one-off Task, I'd move to a star schema, with: Relationships: one-to-many, from each dimension table to my fact table (DimFarmer → FactCropProduction, DimCropType → FactCropProduction, and so on). Filter direction: single-direction by default, flowing from my dimension tables toward the fact table. I'd only turn on bidirectional filtering for a specific report that genuinely needed it, and only after checking it doesn't create conflicts. **Power Query: **I'd use it to clean and shape each table individually (fix data types, trim text, handle blanks) — not to merge everything into one big table. Here's my reasoning, factor by factor: Query and report performance — a star schema is what Power BI's engine is built to optimise for, so my reports would stay fast even as I add more data. DAX simplicity — measures like SUM(Revenue) filtered by county or crop would "just work" through the relationships, instead of me writing complicated formulas to compensate for a messy structure. Model readability — anyone opening my model view could see the star shape and understand what connects to what, which my current flat table doesn't give them. Scalability — adding a new dimension later (say, DimWeather) or a new fact table wouldn't require me to rebuild anything that already exists. Data redundancy — county names, crop types, and farmer details would be stored once each, not once per transaction row like they are now. Maintainability — fixing a typo in a county name would mean editing one row in DimCounty, not hunting through thousands of fact rows. Ease of building reports — dragging fields from clearly-named dimension tables onto a visual is far more intuitive than digging through my one giant flat table. Filter propagation — single-direction filters flowing from my dimensions into the fact table would behave predictably, which is what I'd want for most of my reports. Model complexity — a star schema keeps my complexity manageable; I'd only introduce snowflaking or heavier merging if I hit a specific reason that actually needed it. My honest conclusion: keeping my current flat table was a fine way to get this task moving quickly, but it's not the design I'd defend for anything beyond that. Given what I now understand about performance, maintainability, and how DAX actually behaves, a star schema is the model I'd choose if I took this project further.