--- title: "Star Schema Done Right, Part 4: Migrating a Flat Table to a Star Schema in Power BI" date: 2026-06-25T00:00:00Z updated: 2026-06-27T11:13:15Z tags: ["Data Modeling", "Power BI", "Power Query", "DAX"] canonical: https://bianca.codes/blog/star-schema-done-right-part-4-migrating-a-flat-table-to-a-star-schema/ --- # Star Schema Done Right, Part 4: Migrating a Flat Table to a Star Schema in Power BI _Power Query carves one flat sales table into a fact and three dimensions, you build the relationships, and you prove the numbers didn't move - a restructure you can't validate is just hope._ ← Previously: [Star Schema Done Right, Part 3: Slowly Changing Dimensions](/blog/star-schema-done-right-part-3-slowly-changing-dimensions/) (18 Jun) · Series complete **What this post covers**: the flat table everyone actually starts with and why it's the right place to start; using Power Query to reference one query into a fact table and three dimensions without duplicating the source pull; assigning keys so the fact points at dimension rows instead of repeating their text; building the relationships Part 2 specified; and the validation step that proves your measures return the same numbers as before, which is the only thing that turns a restructure into a migration. ## You already have the flat table, and that's fine Three parts of theory and you still don't have a star schema, you have a spreadsheet. That's not a failure, it's the normal starting point. Somebody exported a sales report, or a system dumped one wide table, and every order line sits in a single rectangle with the customer's name, region and tier repeated on every row, the product's name and category repeated next to it, and the order date stamped alongside the revenue. One table, everything in it, denormalised to the back teeth. ```plaintext OrderID OrderDate CustomerID CustomerName Region Tier ProductID ProductName Category Qty UnitPrice Revenue ----------------------------------------------------------------------------------------------------------------------------- 10001 2026-01-04 1247 Acme Pty NSW Silver P-88 Bolt Cutter Hardware 3 49.00 147.00 10002 2026-01-04 1190 Borden Co VIC Gold P-12 Hex Key Set Hardware 10 12.50 125.00 10003 2026-01-05 1247 Acme Pty NSW Silver P-40 Work Gloves Safety 5 9.80 49.00 ``` This works right up until it doesn't. Part 1 covered what makes the Revenue and Qty columns a fact and the rest context; Part 2 covered why Acme Pty repeated across ten thousand rows is a cardinality problem waiting to slow your model and corrupt your distinct counts. This post is the part where you actually fix it. The destination is the shape Part 2 drew: one fact table of measurements and keys, three dimension tables of descriptive attributes, related one-to-many. We'll carve out a customer dimension, a product dimension, and a date dimension, and leave the fact holding numbers and the keys that reach them. ## Reference, don't duplicate, and definitely don't re-import The instinct is to copy the flat query a few times and trim each copy. Power Query gives you two ways to do that and the difference matters. **Duplicate** clones every step including the source connection, so each dimension re-runs the original pull against your file or database - four queries, four round trips, four things to update when the source path changes. **Reference** creates a new query that starts from the output of the existing one, so the source is loaded once and the dimensions branch off that single result. Reference. Always reference. Right-click the flat query, choose Reference, and you get a new query whose first line is just the name of the one it came from: ```fsharp let Source = FlatSales in Source ``` Do that three times, name the results DimCustomer, DimProduct and DimDate, and you've got three branches off one trunk. Disable load on the original FlatSales query (right-click, untick Enable Load) so it stays a staging query feeding the others rather than a fourth table cluttering your model. One source pull, four queries hanging off it. ## Carving a dimension is select, then dedupe, then key A dimension is the distinct list of one entity's attributes. Building it is three moves. Take DimCustomer: keep only the customer columns, remove the duplicate rows so each customer appears once, then give each row a key. ```fsharp let Source = FlatSales, Customers = Table.SelectColumns(Source, {"CustomerID", "CustomerName", "Region", "Tier"}), Distinct = Table.Distinct(Customers), // Index becomes the surrogate key - a meaningless integer, one per dimension row Keyed = Table.AddIndexColumn(Distinct, "CustomerSK", 1, 1, Int64.Type) in Keyed ``` `Table.Distinct` collapses the ten thousand Acme Pty rows down to one. `Table.AddIndexColumn` mints the surrogate key Part 3 leaned on so hard - `CustomerSK`, starting at 1, one per row. For a plain Type 1 migration you could relate on `CustomerID` and skip the surrogate entirely, and plenty of working models do. I'm adding it here because the moment customer attributes need history you're back in Part 3 territory, and a fact table already pointing at a surrogate is a model that can grow a Type 2 dimension without restructuring the relationships. The cost today is one index column. Pay it. DimProduct is the same three moves on `{"ProductID", "ProductName", "Category"}` with a `ProductSK`. DimDate is the one with an asterisk on it. ## The date dimension is the one you don't carve You can build DimDate by pulling the distinct `OrderDate` values out of the flat table, and for a first pass that's defensible. But a date dimension assembled from dates that happen to appear in your sales data has holes in it - every day with no order is simply missing, and the instant someone builds a time-intelligence measure or a month-over-month visual, the gaps bite. A proper date table is generated, contiguous, and covers every day in the range whether or not anything happened on it. That's a whole topic, and I wrote it up separately in [the date-table piece](/blog/the-part-of-data-modelling-everyone-skips/) - read it before you ship this. For the migration, generate a continuous date table with a `DateKey`, and relate the fact's order date to it rather than carving distinct dates out of the flat pull. The principle is the same as the open `ValidTo` from Part 3: the boundary has to exist even on the days that feel empty. ## The fact table is what's left when the descriptions leave Now the fact. Start from another reference off FlatSales, and instead of keeping a few columns, remove the descriptive ones - everything that lives in a dimension now - and merge the surrogate keys back in so the fact points at dimension rows instead of repeating their text. ```fsharp let Source = FlatSales, // Bring CustomerSK in by matching on the natural key, then drop the descriptive columns JoinCust = Table.NestedJoin(Source, {"CustomerID"}, DimCustomer, {"CustomerID"}, "c", JoinKind.LeftOuter), AddCustSK = Table.ExpandTableColumn(JoinCust, "c", {"CustomerSK"}), JoinProd = Table.NestedJoin(AddCustSK, {"ProductID"}, DimProduct, {"ProductID"}, "p", JoinKind.LeftOuter), AddProdSK = Table.ExpandTableColumn(JoinProd, "p", {"ProductSK"}), // Keep keys and measures; drop every descriptive attribute - it lives in a dimension now FactCols = Table.SelectColumns(AddProdSK, {"OrderID", "OrderDate", "CustomerSK", "ProductSK", "Qty", "Revenue"}) in FactCols ``` What's left is a narrow table: the order grain (`OrderID`), the keys that reach each dimension (`OrderDate` into DimDate, `CustomerSK`, `ProductSK`), and the two measures (`Qty`, `Revenue`). Every repeated name, region and category is gone, replaced by an integer that points at the one row holding it. This is the entire space-and-speed argument from Part 2 made concrete: the fact stops storing "Acme Pty / NSW / Silver" fifty thousand times and stores `5101` instead, and VertiPaq compresses a column of repeated integers far harder than a column of repeated text. ## Building the relationships is anticlimactic, which is the point Load the four tables, open Model view, and connect each key. Drag `FactSales[CustomerSK]` to `DimCustomer[CustomerSK]`, `FactSales[ProductSK]` to `DimProduct[ProductSK]`, `FactSales[OrderDate]` to `DimDate[DateKey]`. Power BI auto-detects each as many-to-one with a single-direction filter flowing from the dimension to the fact, which is exactly the contract Part 2 spelled out and exactly what you want. If any relationship comes up as many-to-many, stop - it means the dimension key isn't unique, which means your `Table.Distinct` didn't fully dedupe or you're relating on the wrong column. A clean star has every relationship one-to-many, every arrow pointing from dimension to fact. No bidirectional filters, no exceptions, no "it's fine for now." The diagram should look like the picture from Part 1: one table in the middle, three hanging off it, every line a single arrowhead. If it looks like a railway junction, something's relating to something it shouldn't. ## Validation is the step that makes it a migration Here's the part the tutorials skip, and it's the only part that matters. You have just restructured the thing that produces your numbers. Until you prove the numbers didn't change, you don't have a migration, you have a hopeful guess with better diagrams. The check is mechanical. Before you touch anything, write down the answer the flat table gives: ```dax Total Revenue Flat = SUM ( FlatSales[Revenue] ) ``` Then write the same measure against the new fact, and put both on a card next to each other, sliced every way that matters - by region, by category, by month: ```dax Total Revenue = SUM ( FactSales[Revenue] ) ``` They must match to the cent, at the grand total and inside every slice. If the totals agree but Revenue-by-Region disagrees, your customer key didn't join cleanly and some fact rows are pointing at the wrong dimension row, or at nothing. A `LeftOuter` join that found no match leaves a blank `CustomerSK`, and blank keys land in a blank dimension row that quietly swallows revenue - so a distinct count or a regional split exposes it even when the grand total hides it. Keep the flat query loaded and disabled until both numbers tie out across every slice you care about. Only then delete it. That's the whole migration: reference into four queries, dedupe-and-key the dimensions, strip the fact to keys and measures, wire the relationships, and prove the totals with the old table sitting right beside the new one until they agree. The restructure is easy. The proof is the job. ## Frequently Asked Questions ### **Should I do this transformation in Power Query or in the source database?** Upstream wins whenever you control it, same as the SCD answer in Part 3. A view or a proper warehouse build that hands Power BI a fact and its dimensions already shaped is faster to refresh and shared across every report that connects. Power Query is the right tool when the source is a flat export you don't own - a CSV, a SharePoint list, a system dump - which is exactly the case this post is written for. Do it in Power Query when the flat table is what you're given; push it to the source the moment you can. ### **Do I really need surrogate keys for a simple model?** Not strictly. If every attribute is Type 1 and you're certain none will ever need history, relating on the natural business key works and is one less column to maintain. The surrogate earns its place the instant a dimension might go Type 2, because a fact already pointing at a surrogate absorbs that change without re-keying. It's cheap insurance, and re-keying a fact table after the fact is not cheap. I add them by default and have never regretted it. ### **What happens to a flat row whose customer isn't in the dimension?** It shouldn't happen if you built the dimension from the same flat table, because every customer in the fact came from there. It absolutely happens when the dimension comes from somewhere else - a master list, a separate system - and a `LeftOuter` join finds no match, leaving a blank surrogate key. Those rows still carry revenue, so they don't vanish, but they collect in a blank dimension row that breaks any per-customer split. Catch them by counting blank keys after the join; a star with silent orphans is the validation failure that hides at the grand total and only shows up in a slice. ### **My refresh got slower after splitting, not faster. Why?** Probably the joins. Merging surrogate keys back onto a large fact in Power Query is real work at refresh time, and on a big table it can cost more than the flat pull did. The query-time speed you gained in the model is separate from the refresh-time cost you added in the ETL. If refresh hurts, that's the strongest argument for pushing the whole transformation upstream into a view or warehouse, where the join happens once on load instead of every refresh. The model stays fast either way; it's the build that moved. ### **Can I keep some descriptive columns on the fact table?** A couple of genuinely fact-grained ones, yes - a transaction-level flag, an order note, something that's truly one-per-order and not an attribute of a dimension. The test is the grain: if the value is a property of the customer or the product, it belongs in that dimension, and copying it onto the fact just rebuilds the flat table you're trying to leave. Degenerate dimensions like `OrderID` legitimately live on the fact because they have no attributes of their own. Everything describable belongs in a dimension. ### **How does this handle the slowly changing dimensions from Part 3?** This migration builds Type 1 dimensions - one row per entity, current values only. Upgrading a dimension to Type 2 is a change to that dimension's query and load logic, not to the fact's relationships, which is the whole reason the surrogate key was worth adding now. The fact already points at `CustomerSK`; teaching DimCustomer to mint a new surrogate per version, per [Part 3](/blog/star-schema-done-right-part-3-slowly-changing-dimensions/), slots straight in without the fact noticing. Build Type 1 first, prove it, then add history where a column actually needs it. ## Where the series ends Four parts, and the shape is built. Part 1 drew the line between [what a fact table is and isn't](/blog/star-schema-done-right-part-1-what-a-fact-table-actually-is-and-isnt/); Part 2 made the case for [dimensions and the cardinality](/blog/star-schema-part-2-dimension-tables-cardinality/) that makes relationships behave; Part 3 handled [slowly changing dimensions](/blog/star-schema-done-right-part-3-slowly-changing-dimensions/) and the surrogate key that makes history possible; and this part took the flat table you actually had and turned it into all of the above, with the numbers validated to prove the restructure was honest. The thing to carry out of the whole series: a star schema isn't a diagram you draw, it's a set of decisions about grain, keys and relationships that your measures then inherit for free. Get the model right and the DAX gets short. Get it wrong and no amount of clever measures will save you. Start flat, migrate deliberately, validate ruthlessly, and the rest of Power BI stops fighting you.