Data View & Model View
Report view gets the attention, but the two views behind it are where correctness lives. Data view is your validation surface — the place you confirm what actually loaded. Model view is where relationships are made and broken. This lesson makes both of them familiar.
Learning Objective
Most people build reports without ever looking at Data view or Model view properly, and then cannot explain why a number is wrong. These two views are where the answers are.
- Use Data view to validate a load and inspect actual row values
- Set column properties — data type, format, summarization, sort-by column, data category
- Read a Model view diagram: tables, relationship lines, cardinality and filter direction
- Explain why Data view is missing on a DirectQuery-only model
- Organise a model with display folders, hidden fields and layouts
Why This Matters
A concrete scenario. Your monthly revenue chart shows the months in alphabetical order: April, August, December, February… It looks absurd and there is no obvious fix in the chart.
The fix is not in Report view at all. It is a Sort by column property set in Data view, telling Power BI to order MonthShort by MonthNumber. People lose genuine hours to this because they look for the answer where the symptom is.
Similarly: a map visual that plots everything in the wrong country is almost always a missing Data category — another Data view property. And a measure that returns blank when sliced is almost always a relationship problem, visible only in Model view.
Concept
Data view
Data view (labelled Table view in recent releases) shows the loaded rows of one table at a time, as a grid. It is the closest thing Power BI has to opening the data in Excel.
What you use it for
- Validating a load. The status bar at the bottom shows the row count. Compare it against the source. This is the single most valuable thing in this view.
- Looking at actual values. When a measure returns something odd, seeing real rows often makes the cause obvious immediately.
- Setting column properties. Select a column and the Column tools contextual tab appears.
- Checking a calculated column. Unlike measures, calculated columns are materialised per row and you can see their values here.
Column tools — the five properties that matter
| Property | What it does | The problem it solves |
|---|---|---|
| Data type | Text, Whole number, Decimal, Date, True/False, etc. | Numbers stored as text will not aggregate; dates as text break time intelligence |
| Format | How the value is displayed — currency, decimals, date format, percentage | Set it here once and every visual inherits it, instead of formatting each visual separately |
| Summarization | Default aggregation, or Don't summarize | Stops Power BI summing numeric dimensions like Year, scores and ID numbers |
| Sort by column | Orders this column by the values of another | The alphabetical-months problem. Sort MonthShort by MonthNumber |
| Data category | Marks a column as Address, City, Country, Postal code, Web URL, Image URL… | Tells map visuals how to geocode; makes URL columns clickable and image URLs render |
MonthShort value must map to exactly one MonthNumber. If "Jan" mapped to both 1 and 13, Power BI refuses with a circular-dependency error. This is why you sort month names by month number, not by date.Model view
Model view shows your tables as boxes and your relationships as lines. It is a diagram, and it is also the editor — you create relationships by dragging between columns.
Reading a relationship line
| Feature | Meaning |
|---|---|
| Solid line | Active relationship — filters flow along it automatically |
| Dashed line | Inactive relationship — exists but only used via USERELATIONSHIP in DAX |
| 1 and * at the ends | Cardinality. 1 on the dimension side, * (many) on the fact side |
| Single arrow mid-line | Filter flows one way — from the 1 side to the many side |
| Double arrow | Bidirectional filtering. Powerful and a common source of trouble |
| No line at all | No relationship. Slicing across these tables will not work |
Module 4 covers cardinality and filter direction properly. For now, the point is that all of this information is readable at a glance from the diagram — and that reading it is the fastest way to diagnose a class of problems that look mysterious from Report view.
Organising the model
Model view is also where you make a model pleasant to use:
- Hide in report view. Right-click any field. Hidden fields still work in DAX and relationships; they simply stop cluttering the Data pane for report authors. Key columns and raw fact columns should almost always be hidden.
- Display folders. Group related measures in the Properties panel. A model with thirty measures becomes navigable with four folders.
- Layouts. Create additional diagram tabs showing a subset of tables. On a twenty-table model this is the difference between a readable diagram and spaghetti.
- Descriptions. Add one to any field in the Properties panel. It surfaces as a tooltip in the Data pane — the cheapest documentation available.
Visual Explanation
A star schema as Model view renders it. Select any table to inspect it.
Region exists in both Customer and Store. That is legitimate but ambiguous — a visual sliced by "Region" must be explicit about which one, and the two will give different answers. Rename them Customer Region and Store Region.Second,
ShipDate and OrderDate both point at the date table, so only one relationship can be active. That is the role-playing dimension problem, and Module 4 Lesson 5 solves it.Power BI Demonstration
MonthShort column → Column tools › Sort by column › MonthNumber. Return to Report view and the axis is now in calendar order.Year → Column tools › Summarization › Don't summarize.NetSales → Column tools › Format › Currency, 2 decimals. Every visual using it now formats correctly without per-visual work.CustomerKey, ProductKey, StoreKey) → Hide in report view. Report authors never need to see them.Example Dataset
Load this alongside the sales file if you want to practise the sort-by-column and summarization fixes on a real date table. You will build your own in Module 4, but having one now makes this lesson's exercises concrete.
bwb-date-table.csv47 KB · 731 rows · 14 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
Date | Date | One row per day, no gaps. This is the key column. |
Year | Whole number | Calendar year. |
MonthNumber | Whole number | 1–12. Use this to sort MonthShort correctly. |
MonthShort | Text | Jan, Feb, Mar … Needs Sort By Column set to MonthNumber. |
MonthName | Text | Full month name. |
QuarterNumber | Whole number | 1–4. |
Quarter | Text | Q1–Q4. |
WeekOfYear | Whole number | ISO week number. |
DayName | Text | Monday … Sunday. |
DayOfWeek | Whole number | 1 = Monday. Sort key for DayName. |
IsWeekend | Text | Yes / No flag. |
YearMonth | Text | YYYY-MM, handy for a continuous month axis. |
FiscalYear | Whole number | Fiscal year starting 1 July. |
FiscalMonth | Whole number | 1–12 where 1 = July. |
Questions this dataset can answer
- How does a fiscal-year view change the picture versus calendar year?
- Which weekday generates the most revenue?
Step-by-Step Exercise
bwb-date-table.csv → Transform Data → Close & Apply.MonthShort on the axis. Note the alphabetical order.MonthShort to sort by MonthNumber. Return and confirm.Year, MonthNumber, QuarterNumber, WeekOfYear, DayOfWeek, FiscalYear and FiscalMonth.DayName to sort by DayOfWeek. Confirm a weekday chart now runs Monday to Sunday.Date[Date] onto Sales[OrderDate]. Note the cardinality and direction Power BI chose.Date[Date] onto Sales[ShipDate] as well. Power BI creates it but makes it inactive (dashed). Note this and move on — Module 4 explains it.Expected Result
| Check | Expected |
|---|---|
| Date table row count | 731 |
| MonthShort chart order | Jan, Feb, Mar … Dec |
| DayName chart order | Monday … Sunday |
| Year in a card | Blank or the year — not 4,049 |
| Date → Sales[OrderDate] relationship | Solid line, 1 on Date, * on Sales, arrow pointing to Sales |
| Date → Sales[ShipDate] relationship | Dashed line — inactive |
USERELATIONSHIP.Common Mistakes
Fix: Set Sort by column on the column itself, in Data view. Fix it once at model level and every visual inherits it.
Fix: Set Format on the column or measure once, in Column tools or Measure tools. Every visual inherits it automatically.
Fix: Check your storage mode. If everything is DirectQuery, this is expected behaviour.
CustomerKey, ProductKey and every raw fact column, and inevitably use one of them by mistake.Fix: Hide every key column and every raw fact column once measures exist. A clean Data pane is one of the clearest signals of a well-built model.
Region exists in both Customer and Store. Two visuals sliced by "Region" give different answers and nobody can see why.Fix: Rename them to be unambiguous —
Customer Region, Store Region. Do it in Power Query or in the model, but do it.Professional Tip
_Validation (the underscore sorts it first) and hide it. On it, put cards showing:•
COUNTROWS of each table•
MIN and MAX of your main date column•
SUM of your primary measure with no filters•
DISTINCTCOUNT of each key columnEvery time you refresh, glance at this page. If a number moves unexpectedly, you know immediately — before anyone else sees a wrong figure. This takes ten minutes to build and is the single most useful defensive habit in Power BI.
One more: add a description to every measure as you write it. The Properties panel field takes ten seconds and surfaces as a hover tooltip in the Data pane. It is the only documentation that lives where people will actually see it.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
Your month names sort alphabetically on a chart axis. Where is the fix?
A relationship line in Model view is dashed rather than solid. What does that mean?
The Table (Data) view icon is missing from the view switcher. What is the most likely reason?
Why does hiding key columns from report view improve a model?
You set Format to Currency on the NetSales column. What happens?
Practical Challenge
Audit and clean up a model
Load bwb-retail-sales.csv, bwb-products.csv, bwb-customers.csv, bwb-stores.csv and bwb-date-table.csv. Then perform a model audit using only Data view and Model view, and produce a written list of every problem you find with its fix.
- Verify the row count of every table against its source file
- Find every numeric column that should not summarize and fix it
- Find every text column that needs a sort-by column and fix it
- Identify every ambiguous column name that appears in more than one table
- List every column you would hide from report view, with a reason
- Note the relationship state for each pair of tables, including any inactive ones
Work table by table, and for each column ask three questions:
- Is it numeric but categorical? → Don't summarize
- Is it text with an inherent order that is not alphabetical? → Sort by column
- Is it a key, or a raw fact column that a measure will wrap? → Hide
For ambiguity, list every column name across all five tables and look for duplicates. Two are obvious; one is easy to miss.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The audit
1 · Row counts
| Table | Expected rows |
|---|---|
| Sales | 15,067 |
| Products | 114 |
| Customers | 900 |
| Stores | 8 |
| Date | 731 |
Any mismatch means Power Query dropped rows — investigate before doing anything else.
2 · Columns needing Don't summarize
- Date table:
Year,MonthNumber,QuarterNumber,WeekOfYear,DayOfWeek,FiscalYear,FiscalMonth - Stores:
OpenedYear - Sales:
Discount— it is a rate. Summing discount rates across rows is meaningless. It can be averaged (weighted) or banded, never summed.
3 · Columns needing a sort-by column
MonthShort→ sort byMonthNumberMonthName→ sort byMonthNumberDayName→ sort byDayOfWeekQuarter→ sort byQuarterNumber(Q1–Q4 happens to sort correctly alphabetically, but setting it explicitly protects you if the labels are ever changed to something like "Qtr 1")
4 · Ambiguous column names
| Name | Appears in | Why it matters | Fix |
|---|---|---|---|
Region | Customers and Stores | Two different concepts — where the customer is, versus where the store is. A visual labelled "Region" is genuinely ambiguous and the two give different answers. | Rename to Customer Region and Store Region |
ProductKey | Sales and Products | Expected and correct — this is the join key. Not a problem. | Leave it; hide the Sales copy |
Year | Date table, and OpenedYear in Stores | Not an exact clash, but close enough to confuse. The easy one to miss. | Leave as is — the names already differ. Worth noting in documentation |
5 · Columns to hide from report view
| Column | Reason |
|---|---|
Sales[CustomerKey], [ProductKey], [StoreKey] | Join keys. Report authors never need them; showing them invites misuse |
Products[ProductKey], Customers[CustomerKey], Stores[StoreKey] | Same reason on the dimension side |
Sales[NetSales], [COGS], [Profit], [Quantity] | Hide these once measures exist. Until then you need them. After that, hiding them forces everyone through your defined measures rather than an implicit sum |
Sales[UnitPrice] | Duplicated from the product dimension at transaction time. Keeping it visible invites someone to average it, which is a weighted-average trap |
Date[MonthNumber], [DayOfWeek], [QuarterNumber] | Sort-by helper columns. They must exist, but nobody should put them on a chart |
6 · Relationship state
| From | To | State |
|---|---|---|
| Date[Date] | Sales[OrderDate] | Active (solid), 1 to *, single direction |
| Date[Date] | Sales[ShipDate] | Inactive (dashed) — only one active path allowed |
| Products[ProductKey] | Sales[ProductKey] | Active, 1 to *, single direction |
| Customers[CustomerKey] | Sales[CustomerKey] | Active, 1 to *, single direction |
| Stores[StoreKey] | Sales[StoreKey] | Active, 1 to *, single direction — but note that most Sales rows have a blank StoreKey, which lands them in a blank row on the Stores side |
Region ambiguity is the finding that matters most here, and it is the one least likely to be caught by a mechanical pass. It will not produce an error. It will produce two visuals labelled "Revenue by Region" that show different numbers, and a stakeholder who concludes the report cannot be trusted.Modelling problems that produce plausible disagreement rather than visible breakage are the ones worth hunting for deliberately — which is the same lesson as the detectability ranking from Lesson 2.
Lesson Summary
- Data view validates loads (row count in the status bar), shows real values, and hosts column properties.
- The five column properties that matter: data type, format, summarization, sort-by column, data category.
- Sort by column fixes alphabetical months. The sort-by column must map one-to-one with the column being sorted.
- Model view shows tables and relationships. Solid = active, dashed = inactive; 1 and * show cardinality; the arrow shows filter direction.
- Only one active relationship path is allowed between two tables — which is why a second date relationship arrives inactive.
- Hide keys and raw fact columns once measures exist. Hidden fields still work in DAX and relationships.
- Data view is absent on pure DirectQuery models — that is expected, not a fault.
- Build a hidden validation page with row counts, date ranges and unfiltered totals. It is the cheapest defence against silent breakage.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.