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 the Model view:

Mark as Date Table

Designating a table as the official date table:

Table Tools → Mark as Date Table → Select the date column

Requirements for a valid date table:

• Contains a column of Date or DateTime data type
• The date column has no missing values (no gaps in the date range)
• The date column has unique values (one row per date)
• Covers the full range of dates in related fact tables

Why mark a date table:

• Enables proper time intelligence DAX functions (YTD, QTD, SAMEPERIODLASTYEAR)
• Disables auto-generated hidden date tables for related columns
• Ensures consistent time-based calculations across the model

Date Table vs. Auto Date/Time

Column Properties

Data Type

Already set in Power Query, but can be verified in the Model view:

• Changing here does NOT change the source — it affects display and DAX behavior
• Ensure consistency with Power Query types

Format

Controls how values are displayed in visuals:

Important: Format is a display-only setting. It does NOT change the underlying data type or how DAX calculates with the values.

Data Category

Data categories provide semantic hints about what a column represents:

How data categories help:

• Map visuals automatically use geocoded columns
• Table visuals render URLs as clickable links
• Mobile app can scan barcode-categorized columns

Sort By Column

Sort one column by the values in another column:

Classic example: Sort month names alphabetically → incorrect order

• "April" before "December" before "February"

Fix: Sort the MonthName column by MonthNumber:

Select MonthName column → Column Tools → Sort by Column → MonthNumber

Requirements:

• The sort-by column must have a one-to-one correspondence with the sorted column
• Both columns must be in the same table
• Common patterns: MonthName sorted by MonthNumber, DayName sorted by DayNumber

Summarize By

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

Best Practice: Set Summarize By to "Don't Summarize" (None) for columns that should never be aggregated, like ProductID, OrderNumber, or ZipCode. This prevents report builders from accidentally summing these values.

Column Visibility

Hide columns that report builders should not see:

Right-click column → Hide in report view

Hide these columns:

• Foreign keys in fact tables (DateKey, ProductKey)
• Technical/internal columns (RowVersion, ETLTimestamp)
• Intermediate calculation columns used only by measures
• Columns with sensitive data not meant for self-service

Do NOT hide:

• Dimension attributes used for filtering and slicing
• Measure columns that appear in visuals
• Date hierarchy columns

Display Folders

Organize columns and measures into logical folders:

Select column/measure → Properties pane → Display Folder → Enter folder name

Example folder structure:

• Sales Measures
  • Revenue
  • Quantity
  • Profit Margin
• Customer Attributes
  • Demographics
  • Geography
  • Contact Info

On the Exam

The PL-300 frequently tests:

• Using Sort By Column to fix month name ordering
• Setting Data Category for geographic columns used in map visuals
• Marking a table as the Date Table and knowing the requirements
• Setting Summarize By to prevent accidental aggregation of ID columns
• Hiding foreign key and technical columns from report view