You have one export: every support ticket on one row, the agent's name and team next to it, the customer's company and tier next to that, a category, a priority, and how many hours it took to close. One wide CSV, thousands of rows, the agent name and company name repeated on every single one. By the end of this post you'll have split it into a fact table and two dimension tables, related them properly, and proved the numbers still add up. You need Power BI Desktop and a flat export with at least one date column, one "who did it" column, and one "who it was for" column. A CSV works. A SharePoint list works. Anything Power Query can read works.
Why this approach
The obvious move is to load the flat file and start slicing. It works for about a week, right up until someone asks "average resolution hours per agent, split by month" and you discover a plain AVERAGE walks every row including the nine hundred repeats of the same agent's name, and Power BI's file size creeps up because VertiPaq is compressing a column of repeated text instead of a column of repeated integers. Splitting the export into a fact table and dimension tables now, while it's still small, costs you twenty minutes. Splitting it after six months of reports are built on top of the flat version costs you a rebuild. This is the compact version of that fix: a working star schema by the end of the post, not the theory behind why it matters. If you want the full derivation, the surrogate-key argument, and a validation pass across every slice, the Star Schema Done Right series covers that at length; this post skips straight to building.
Step 1: Land the export as one staging query
Get Data, point it at the export, and land it as a single query named something like TicketsRaw. Don't touch it yet. Right-click it and untick Enable Load, so it becomes a staging query that feeds the others instead of a table cluttering your model.
Every dimension you build next starts with Reference, not Duplicate. Reference creates a new query that reads off TicketsRaw's output; Duplicate clones the whole query including the original file connection, so you'd be re-reading the CSV once per dimension for no reason. Right-click TicketsRaw, choose Reference, and you get a query whose first line just points back at the one it came from.
Step 2: Carve out the dimension tables
Reference TicketsRaw twice: once for agents, once for companies. For DimAgent, keep only AgentName and AgentTeam, then dedupe and key:
let
Source = TicketsRaw,
Cols = Table.SelectColumns(Source, {"AgentName", "AgentTeam"}),
Distinct = Table.Distinct(Cols),
Keyed = Table.AddIndexColumn(Distinct, "AgentSK", 1, 1, Int64.Type)
in
KeyedTable.Distinct collapses however many hundred rows the same agent appears on down to one. Table.AddIndexColumn mints a surrogate key, a plain integer with no meaning beyond "row 1, row 2, row 3", which is what the fact table will point at instead of repeating the agent's name. DimCompany is the identical move on {"Company", "CompanyTier"} with a CompanySK.
Do the same for your date column if the flat file's date range is small and continuous. If it has real gaps, a generated date table (Power BI Desktop's Home > New Table with a CALENDAR() formula, or a dedicated Power Query date function) does the job properly, since carving distinct dates out of the flat file only gives you the days something happened on, not the days that had nothing to report.
Step 3: Strip the fact table to keys and numbers
Reference TicketsRaw one more time, and this time work backwards from what you're keeping instead of what you're removing. Merge the surrogate keys back in, then select only the ticket grain, the keys, and the measures:
let
Source = TicketsRaw,
JoinAgent = Table.NestedJoin(Source, {"AgentName", "AgentTeam"}, DimAgent, {"AgentName", "AgentTeam"}, "a", JoinKind.LeftOuter),
AddAgentSK = Table.ExpandTableColumn(JoinAgent, "a", {"AgentSK"}),
JoinCompany = Table.NestedJoin(AddAgentSK, {"Company", "CompanyTier"}, DimCompany, {"Company", "CompanyTier"}, "c", JoinKind.LeftOuter),
AddCompanySK = Table.ExpandTableColumn(JoinCompany, "c", {"CompanySK"}),
FactCols = Table.SelectColumns(AddCompanySK, {"TicketID", "CreatedDate", "AgentSK", "CompanySK", "Category", "Priority", "HoursToResolve"})
in
FactColsWhat's left is narrow: the ticket ID, the keys that reach each dimension, and the numbers. Category and Priority stay on the fact here because they describe the ticket itself, not the agent or the company; a column only belongs in a dimension if it describes the thing the dimension represents.
Step 4: Wire the relationships
Load all three tables (FactTickets, DimAgent, DimCompany, plus your date table), open Model view, and drag FactTickets[AgentSK] to DimAgent[AgentSK], then FactTickets[CompanySK] to DimCompany[CompanySK]. Power BI should auto-detect each as many-to-one, filtering from the dimension into the fact. If either comes up many-to-many, your dedupe step missed something and the "unique" key isn't actually unique yet.
Common mistakes
Skipping the validation because the model "looks right".
A star schema that renders correctly in Model view can still be silently wrong. Write the same total in DAX against the old flat table and the new fact table, SUM(FactTickets[HoursToResolve]) against SUM(TicketsRaw[HoursToResolve]), and put them on a card together. If they match at the grand total but disagree once you slice by agent, a join found no match somewhere and rows are landing in a blank dimension row instead of the right one.
Putting a describing column on the fact table because it was easier than building a dimension for it.
AgentTeam living on the fact instead of on DimAgent looks harmless with three teams. It stops looking harmless the day someone renames a team and now has to fix it on every row of the fact table instead of one row of the dimension. If a value describes the agent rather than the ticket, it belongs on DimAgent, full stop.
Frequently Asked Questions
Do I need this for a report with only a few hundred rows?
Not urgently. The pain shows up as the row count grows and the repeated text starts costing you file size and correctness on distinct counts. Building the habit on a small file is cheap practice for when the export isn't small anymore.
What if two dimensions share an attribute, like both AgentTeam and CompanyTier being "region"?
Keep them separate unless they're genuinely the same entity. AgentTeam describes an internal team, CompanyTier describes a customer segment; collapsing them into one shared dimension because the column names rhyme creates a relationship that doesn't mean anything.
Can I skip the surrogate key and relate on the agent's name directly?
You can, and plenty of small models do exactly that. The surrogate earns its keep the moment a dimension might need to track history, an agent changing teams, say, because the fact can keep pointing at a stable integer while the dimension logic underneath it changes.
My fact table refresh got slower after I split it out. Why?
The joins that merge surrogate keys back onto the fact are real work at refresh time. On a large export, that cost can outweigh what a flat pull used to take. If it becomes a problem, the fix is pushing the split upstream into the source system rather than redoing it in Power Query on every refresh.
Does this work the same way in Excel's Power Query, not just Power BI?
The M code is identical; Excel's Power Query editor is the same engine. What changes is the modelling layer afterward, since Excel's data model uses the same relationship concept but without Power BI's full Model view for checking many-to-one at a glance.
Where to go from here
The Hot Take that promised this build, Most Power BI dashboards are just decorated spreadsheets, is the argument for why this matters before you've written a line of M. If you want the deeper version of this exact migration, with surrogate keys explained from first principles and a validation pass across every slice instead of just the grand total, that's the Star Schema Done Right series in full.