ExamFinch.com My Study

Study guide

4.3 Table and Column Properties

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

4.3 Table and Column Properties

Key Takeaways Explain This Topic AI

Table properties include storage mode (Import, DirectQuery, Dual) and Mark as Date Table.

Key column properties: Data Type, Format, Data Category, Sort By Column, Summarize By, and visibility.

Data Category hints map columns to map visuals (City, State/Province, Country/Region, Postal Code, Latitude, Longitude) and enable Web/Image URL behavior.

Sort By Column sorts one column by another (Month Name by Month Number) and requires a one-to-one match within the same table.

Summarize By sets a column's default aggregation; set ID and code columns to Don't Summarize to prevent accidental sums.

Last updated: June 2026

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:

  • Mode Description
  • Import Data cached in memory (default, fastest)
  • DirectQuery Queries sent to the source at runtime
  • Dual Behaves as Import or DirectQuery depending on context; key for composite models and aggregations
  • 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.

  • Feature Mark as Date Table Auto Date/Time
  • Visibility Visible, full control Hidden, auto-generated
  • Customization Custom fiscal years, hierarchies Standard calendar only
  • Model Size One table for all dates One hidden table per date column
  • Time Intelligence Full support Limited
  • Recommendation Use this Disable globally
  • 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.

  • Format Example Use For
  • Whole Number 1,235 Counts, IDs
  • Decimal Number 1,234.50 General decimals
  • Currency $1,234.50 Revenue, prices
  • Percentage 45.00% Rates, proportions
  • Date 3/31/2026 Dates
  • Data Category

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

  • Category Effect Example
  • City Auto-geocode for maps "Seattle"
  • State or Province Auto-geocode for maps "Washington"
  • Country/Region Auto-geocode for maps "United States"
  • Postal Code Geographic grouping "98101"
  • Latitude / Longitude Map positioning 47.6062 / -122.3321
  • Web URL Clickable links in tables " https://example.com "
  • Image URL Display images in tables " https://.../logo.png "
  • Barcode Mobile scanning "5901234123457"

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.

  • Setting Best For
  • Sum Revenue, Quantity (numeric default)
  • Average Ratings, Scores
  • Min / Max Start / end dates
  • Count / Count (Distinct) Items / unique customers
  • Don't Summarize (None) ProductID, OrderNumber, ZipCode

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.