Configure Table and Column Properties in Power BI
Why It Is Important
Configuring table and column properties is a fundamental skill for Power BI data analysts because it directly impacts how users interact with your reports and how the data model performs. Proper configuration ensures data accuracy, improves report usability, enhances query performance, and creates a professional user experience. For the PL-300 exam, this topic represents a significant portion of the 'Model the Data' section, which accounts for 25-30% of the exam.
What It Is
Table and column properties are metadata settings that define how tables and columns behave within your Power BI data model. These properties control display names, data types, formatting, categorization, visibility, and summarization behavior. They help transform raw data into meaningful, user-friendly information.
Key Table Properties:
- Name: The display name shown in the Fields pane
- Description: Documentation for the table's purpose
- Row Label: Defines which column represents the row in Q&A
- Key Column: Specifies the unique identifier column
- Storage Mode: Import, DirectQuery, or Dual
- Is Hidden: Controls visibility in report view
Key Column Properties:
- Data Type: Text, Whole Number, Decimal, Date/Time, True/False, etc.
- Format: Currency, percentage, date formats, decimal places
- Data Category: Geographic (City, Country, Latitude, Longitude), URL, Image URL, Barcode
- Default Summarization: Sum, Average, Count, Min, Max, Don't Summarize
- Is Hidden: Hides columns from report authors
- Sort By Column: Defines how one column sorts based on another
How It Works
Setting Data Types:
In Power BI Desktop, select a column in the Data view or Model view, then use the Column Tools tab or right-click to change the data type. Correct data types ensure proper calculations, filtering, and relationships.
Configuring Data Categories:
Data categories enable special behaviors. For example, setting a column's data category to 'City' or 'Country' allows map visualizations to geocode locations automatically. Image URL categories display images in tables.
Default Summarization:
When you drag a numeric field to a visual, Power BI applies the default summarization. Setting this appropriately prevents errors—for instance, ID columns should be set to 'Don't Summarize' to avoid meaningless sums.
Sort By Column:
This feature lets you sort text columns by related numeric columns. A common use case is sorting month names (January, February, etc.) by a month number column rather than alphabetically.
Hiding Columns and Tables:
Hide supporting columns (like sort columns or key columns used only for relationships) to declutter the Fields pane while keeping them functional for calculations.
Exam Tips: Answering Questions on Configure Table and Column Properties
1. Know your data types: Understand when to use Whole Number vs. Decimal Number, and when Text is appropriate even for numeric-looking data (like postal codes or product codes).
2. Memorize data categories: Questions often test knowledge of geographic categories (Address, City, State, Country, Postal Code, Latitude, Longitude) and web categories (Web URL, Image URL).
3. Understand Sort By Column scenarios: If a question mentions months or weekdays displaying in wrong order, the answer typically involves Sort By Column with a corresponding number column.
4. Default Summarization use cases: Remember that ID fields, years, and categorical numbers should use 'Don't Summarize' to prevent inappropriate aggregations.
5. Hidden vs. Deleted: Hidden columns remain in the model and can be used in DAX calculations and relationships. Questions may test whether to hide or remove a column based on whether it's needed for calculations.
6. Storage Mode implications: Know the differences between Import (best performance), DirectQuery (real-time but slower), and Dual (composite models) storage modes.
7. Read questions carefully: Look for keywords like 'map visualization' (needs geographic data category), 'sorted incorrectly' (needs Sort By Column), or 'users should not see' (needs hidden property).
8. Practice in Power BI Desktop: Hands-on experience with the Column Tools tab and Properties pane will help you recall options during the exam.