4.3 Table and Column Properties

Quick Answer: Table and column properties control how data behaves in reports. Key settings: Data Category (City, Country, URL) for smart visual behavior, Sort By Column (sort Month Name by Month Number), Summarize By (default aggregation), and visibility (hide technical columns from report builders).

Table Properties

Storage Mode

Set per table in Model view:

Mark as Date Table

Table Tools > Mark as Date Table > select the date column.

A valid date table must contain a Date/DateTime column with no missing values (no gaps), unique values (one row per date), and coverage of the full date range in related facts. Marking it enables proper time intelligence (YTD, SAMEPERIODLASTYEAR) and stops auto-generated hidden date tables for related columns.

Column Properties

Data Type and Format

Data Type is usually set in Power Query; the Model view value affects display and DAX behavior, not the source. Format is display-only and does not change the underlying type or how DAX calculates.

Data Category

Data categories give semantic hints about a column's meaning.

Map visuals use geocoded columns automatically, table visuals render Web URLs as clickable links, and the mobile app can scan barcode-categorized columns.

Sort By Column

Sort one column by the values in another. The classic case: month names sort alphabetically (April, August, December) instead of calendar order. Fix it by sorting the MonthName column by MonthNumber: Select MonthName > Column Tools > Sort by Column > MonthNumber.

Requirements: the sort-by column must have a one-to-one correspondence with the sorted column, and both must be in the same table. Typical patterns are MonthName by MonthNumber and DayName by DayNumber. A common error, "can't sort by a column with multiple values per sorted value," means the one-to-one rule is violated.

Summarize By

Sets the default aggregation when a column is dragged into a visual.

Best Practice: Set Summarize By to Don't Summarize for ID, key, and code columns so report builders cannot accidentally sum them.

Visibility and Display Folders

Hide foreign keys in facts, technical columns (RowVersion, ETLTimestamp), and intermediate columns used only by measures. Do not hide dimension attributes used for filtering, measure columns shown in visuals, or date-hierarchy columns. Use display folders (Properties pane) to group columns and measures into logical folders such as "Sales Measures" and "Customer Attributes."

On the Exam

The PL-300 tests using Sort By Column to fix month ordering, setting Data Category for geographic columns, the requirements for marking a date table, setting Summarize By to prevent accidental aggregation of IDs, and hiding foreign-key and technical columns.

Why These Properties Matter for Self-Service

Table and column properties are how a modeler shapes a trustworthy self-service experience. A report builder who never opens Power Query relies entirely on the metadata you set: the friendly names, the default aggregation, the format string, and the geographic hints. Getting these right means a business user can drag fields onto a canvas and get a correct, well-formatted visual without writing a single formula, which is exactly the outcome the PL-300 "prepare the data" and "model the data" objectives reward.

Dual Storage Mode in Depth

Dual is the most nuanced storage mode and a common exam target. A Dual table behaves as Import when a query can be answered entirely in memory and as DirectQuery when a query must reach the source, typically because it joins to a DirectQuery fact. Dual is the recommended mode for shared dimensions in composite models and is essential for user-defined aggregations, where a small Import aggregation table sits over a large DirectQuery detail table.

Setting a shared dimension to Import instead of Dual would force the engine to materialize and join in memory for DirectQuery paths, hurting performance; setting it to DirectQuery would slow purely in-memory queries. Dual lets the engine pick per query.

Format String vs. Data Type, a Frequent Trap

Learners often confuse the Format property with the Data Type. Changing the format of a column from General to Currency only changes how the value is displayed, $1,234.50 instead of 1234.5; it does not alter the stored number or how DAX computes with it. To genuinely change the type (text to whole number, for instance) you set the Data Type, ideally back in Power Query so the change folds to the source. On the exam, a question that asks how a number should appear points to Format; a question about how it should be stored or calculated points to Data Type.