Overview
Dataverse tables are the structured database containers that power Power Apps and Power Automate solutions. Understanding table creation and column configuration is fundamental to building scalable, maintainable business applications.
Prerequisites
- Power Apps licence with Dataverse access (not available in Office 365 E1)
- Access to a non-Default environment with Dataverse database provisioned
- Basic understanding of database concepts (tables, columns, rows)
- Maker permissions in your environment
What is a Dataverse Table?
A Dataverse table is a structured data container that stores business information in rows and columns—similar to an Excel table or SQL database table, but with built-in business logic, security, and integration with the Power Platform.
Unlike SharePoint lists or Excel tables, Dataverse tables provide:
- Rich data types – Currency, lookups, choices, file attachments, and calculated fields
- Relationships – Connect tables together with one-to-many or many-to-many relationships
- Business rules – Enforce validation logic without writing code
- Security roles – Granular permissions down to individual column level
- Audit history – Automatic tracking of who changed what and when
- Power Platform integration – Native support for Canvas Apps, Model-Driven Apps, and Power Automate
Tables are the foundation of professional Power Platform solutions. Every Model-Driven App requires Dataverse tables, and Canvas Apps perform significantly better when connected to Dataverse compared to SharePoint or Excel.
Creating a Dataverse Table
Tables are created in the Power Apps maker portal within a specific environment. Make sure you've selected the correct environment before creating tables—you cannot move tables between environments after creation.
Step-by-step table creation:
- Navigate to make.powerapps.com and confirm you're in the correct environment
- Expand Dataverse in the left navigation
- Click Tables
- Click + New table in the command bar
- Choose New table (not "Start with template")
- Configure the table properties in the creation panel
Configuring Table Properties
The table creation panel requires you to configure several properties that determine how the table behaves and appears throughout the Power Platform.
| Property | Purpose | Example |
|---|---|---|
| Display name | User-facing name shown in apps and forms | Asset |
| Plural name | Name used when referring to multiple records | Assets |
| Description | Optional documentation for what the table stores | Physical assets tracked for maintenance |
| Schema name | Technical name used in code and integrations | cr6a3_Asset |
| Primary column | The main identifier field for each record | Asset Name |
Schema name prefix explained:
Dataverse automatically adds a publisher prefix to your schema names (e.g., cr6a3_). This prevents naming conflicts when multiple solutions are deployed to the same environment. The prefix is assigned to your environment's default publisher and cannot be changed after creation.
Primary column configuration:
The primary column is special—it's the field that identifies each record throughout the system. When you create a lookup to this table from another table, the primary column value is what users see in the dropdown.
Best practices for primary columns:
- Use a human-readable identifier (Asset Name, Request Title, Customer Name)
- Make it required—every record must have a value
- Keep it concise—primary columns appear in narrow columns throughout the UI
- Avoid using auto-numbers unless users understand numeric identifiers (like ticket numbers)
Advanced Table Settings
Below the basic properties, you'll find advanced settings that control table behaviour. Most of these should be enabled for new tables unless you have specific reasons not to.
| Setting | Default | When to Enable |
|---|---|---|
| Enable attachments | Disabled | Users need to attach files to records (e.g., invoices, photos, certificates) |
| Track changes | Disabled | You need an audit trail of who changed what and when |
| Appear in search results | Enabled | Always enable—allows users to find records via global search |
| Provide custom help | Disabled | You want to link to training materials or documentation |
| Creating a new activity | Disabled | You need to link emails, tasks, or appointments to records |
Track changes (auditing):
When enabled, Dataverse records every change to every field in the table, including the old value, new value, who made the change, and when. This data is retained for 90 days and can be viewed from the record's audit history.
Enable auditing for:
- Financial records where compliance requires an audit trail
- Approval workflows where you need to prove who approved what
- Any table where data integrity is critical
Disable auditing for:
- High-volume transactional tables (generates excessive data)
- Temporary or scratch tables used for calculations
- Tables with no compliance requirements
Understanding Dataverse Column Types
After creating your table, you'll add columns to define what data each record can store. Dataverse provides 20+ column types, each optimised for specific data patterns.
Core column types for everyday use:
| Column Type | Stores | Common Use Cases |
|---|---|---|
| Single line of text | Up to 4,000 characters | Names, titles, short descriptions, email addresses |
| Multiple lines of text | Up to 1 million characters | Notes, detailed descriptions, instructions |
| Whole number | Integer between -2.1B and 2.1B | Quantities, ages, counts |
| Decimal number | Precise decimal values | Measurements, percentages, ratings |
| Currency | Money amounts with precision | Prices, costs, invoices, budgets |
| Date and time | Date with optional time | Due dates, created dates, appointment times |
| Choice | Predefined list of options | Status, priority, category, type |
| Yes/No | Boolean true/false | Active/inactive, approved, enabled |
| Lookup | Reference to another table | Asset → Location, Request → User |
The column type you choose determines what validation Dataverse applies, how the data is stored, and which controls appear in forms and apps.
Single Line of Text
Single line of text is the most common column type. It stores short text values like names, titles, codes, or identifiers.
Configuration options:
| Property | Purpose | Recommendation |
|---|---|---|
| Maximum length | Character limit (up to 4,000) | Set to realistic maximum, not always 4,000 |
| Format | Email, URL, Phone, Text, Ticker Symbol | Use Email/URL for automatic validation |
| IME mode | Input method for Asian languages | Leave as Auto unless targeting specific regions |
Format types explained:
- Email – Validates email format and displays as clickable mailto: link
- URL – Validates URL structure and displays as clickable hyperlink
- Phone – Displays with click-to-call functionality on mobile
- Text – Plain text with no special formatting or validation
- Ticker Symbol – Displays stock ticker with link to financial data
Multiple Lines of Text
Multiple lines of text stores longer content like notes, descriptions, or instructions. It supports up to 1 million characters and can optionally include rich text formatting.
Format options:
| Format | Features | Best For |
|---|---|---|
| Text | Plain text, line breaks preserved | Simple notes, instructions, addresses |
| Rich Text | Bold, italic, lists, links, colours | Formatted descriptions, documentation |
When to use each format:
Use Text for:
- Structured data that will be processed by code
- Plain notes where formatting isn't needed
- Data imported from systems that don't support formatting
Use Rich Text for:
- User-facing descriptions shown in portals or apps
- Documentation that benefits from formatting (headings, bullets)
- Content created by end users who expect formatting tools
Whole Number
Whole number columns store integers without decimal places—ideal for counts, quantities, ages, or any value that doesn't require precision beyond whole numbers.
Format options:
| Format | Display Style | Use Case |
|---|---|---|
| None | Plain number: 1234 | General purpose integers |
| Duration | Time format: 1h 30m | Task durations, appointment lengths |
| Language | Language name from code | Storing language preference codes |
| TimeZone | Timezone name from code | User timezone preferences |
Range limits:
You can optionally set minimum and maximum values to enforce business rules. For example, if age must be between 18 and 65, set min=18 and max=65. Dataverse will prevent users from entering values outside this range.
Decimal Number and Currency
Decimal Number:
Decimal numbers store precise fractional values with configurable precision. Unlike floating-point numbers (which have rounding errors), decimal columns maintain exact precision for financial calculations.
Configuration:
| Property | Purpose | Example |
|---|---|---|
| Precision | Total number of digits (1-10) | Precision 5 = up to 99999 |
| Decimal places | Digits after decimal point (0-10) | 2 decimals = 99.99 |
Currency:
Currency columns are specialised decimal columns that automatically apply currency formatting based on the user's locale or a selected currency code.
Key features:
- Supports multiple currencies in the same environment
- Automatic exchange rate conversion (when configured)
- Displays with currency symbol (£, $, €)
- Always shows 2 decimal places by default
When to use Currency vs Decimal:
- Use Currency – For money amounts, prices, costs, invoices, revenue
- Use Decimal – For measurements, percentages, weights, or any non-monetary precise value
Date and Time Columns
Date and time columns store temporal data with configurable behaviour for date-only, date with time, and timezone handling.
Behaviour options:
| Behaviour | How It Works | Best For |
|---|---|---|
| User local | Converts to each user's timezone | Meeting times, deadlines, appointments |
| Date only | Stores date without time component | Birth dates, start dates, expiry dates |
| Timezone independent | Stores exact value, no conversion | UTC timestamps, system logs |
Behaviour explained with examples:
User local: If a UK user creates a record with "Meeting at 2:00 PM", a user in New York sees "9:00 AM" because Dataverse converted to their local timezone. Use this for any time that represents a moment when people in different locations need to coordinate.
Date only: Birth date "1990-05-15" displays identically for all users regardless of timezone. The time component doesn't exist. Use this when the specific time doesn't matter—only the calendar day.
Timezone independent: Stores exactly what you enter with no conversion. If you store "2024-01-15 14:00", everyone sees "2024-01-15 14:00" regardless of their timezone. Use this rarely—typically only for system logs or when integrating with systems that don't support timezones.
Choice (Option Set)
Choice columns let users select from a predefined list of options. They're essential for maintaining data consistency—instead of users typing "High", "HIGH", or "high priority", they select from a standardised list.
Choice types:
| Type | Selection Behaviour | Use Case |
|---|---|---|
| Choice | Single value only | Status, priority, category |
| Choices | Multiple values allowed | Skills, tags, permissions |
Creating choice options:
When you create a Choice column, you define the available options. Each option has:
- Label – What users see ("High Priority", "In Progress")
- Value – The number stored in the database (100000, 100001)
- Colour – Optional visual indicator shown in forms
Global vs Local choices:
Dataverse offers two choice scopes:
- Global choices – Reusable across multiple tables (e.g., "Priority" used in Assets, Requests, Tasks)
- Local choices – Specific to one column in one table
Use global choices when the same set of options appears in multiple places. This ensures consistency—if you change "High" to "Critical" in a global choice, it updates everywhere simultaneously.
Yes/No (Boolean)
Yes/No columns store binary true/false values. They're the simplest column type and perfect for any on/off, active/inactive, or enabled/disabled scenario.
Default value:
You must set a default value (Yes or No) because boolean columns cannot be null. Choose the safest default:
- Default to No – For "Is Approved", "Is Published", "Is Active" (require explicit action)
- Default to Yes – For "Is Enabled", "Send Notifications" (opt-out rather than opt-in)
Custom labels:
You can customise the labels shown in forms instead of "Yes" and "No". For example:
| Column Name | Yes Label | No Label |
|---|---|---|
| Is Active | Active | Inactive |
| Is Approved | Approved | Pending |
| Requires Permit | Required | Not Required |
Custom labels improve user experience by providing context-specific terminology instead of generic Yes/No options.
Lookup (Relationships)
Lookup columns create relationships between tables, allowing you to reference records from one table in another. They're fundamental to building normalised, scalable database structures.
How lookups work:
When you create a lookup from the Asset table to the Location table, each Asset record can reference one Location. In forms, this appears as a dropdown showing all Location records (specifically, their primary column values).
Relationship types:
| Type | Meaning | Example |
|---|---|---|
| Many-to-one | Many records link to one parent | Many Assets → One Location |
| One-to-many | One parent has many children | One Location → Many Assets (reverse of above) |
| Many-to-many | Multiple both ways via junction table | Many Users → Many Skills |
Cascade behaviour:
When creating a lookup, you configure what happens to child records when the parent is deleted:
| Behaviour | What Happens | Use When |
|---|---|---|
| Restrict | Prevents deletion if children exist | Deleting Location should fail if Assets reference it |
| Remove Link | Clears lookup field in children | Deleting Location clears Asset.Location field |
| Cascade All | Deletes all child records | Deleting Request deletes all related Comments |
File and Image
File and Image columns let users upload files directly to Dataverse records, eliminating the need for separate document libraries.
File columns:
Store any file type up to 128 MB. Common uses:
- Contract documents attached to Requests
- Compliance certificates attached to Assets
- Invoice PDFs attached to Purchase Orders
Image columns:
Optimised for image files with automatic thumbnail generation. The image displays directly in forms and views without requiring a separate download.
| Property | File Column | Image Column |
|---|---|---|
| Max size | 128 MB | 32 MB (configurable) |
| Accepted types | Any file extension | Image formats only (.jpg, .png, .gif) |
| Thumbnail | No | Auto-generated |
| Display in forms | Download link | Inline preview |
Storage impact:
Files stored in File and Image columns count against your environment's Dataverse storage quota. If you're storing thousands of large files, consider using SharePoint document libraries linked via lookups instead.
Calculated and Rollup Columns
Calculated columns:
Calculated columns derive their value from a formula based on other columns in the same record. The formula runs automatically whenever a record is created or updated.
Common calculated column scenarios:
- Full Name = First Name + " " + Last Name
- Days Until Due = Due Date - Today
- Total Price = Unit Price × Quantity
- Age = Today - Birth Date (in years)
Limitations:
- Cannot reference lookup fields from related tables
- Cannot trigger workflows or call external services
- Cannot use current user context (User() function not available)
- Read-only—users cannot edit calculated values
Rollup columns:
Rollup columns aggregate data from related child records. They're perfect for summary statistics displayed on parent records.
Examples:
- Total Assets on Location = COUNT of Assets where Asset.Location = this Location
- Total Value on Request = SUM of Line Items where Line Item.Request = this Request
- Open Tasks on Project = COUNT of Tasks where Task.Project = this Project AND Task.Status = "Open"
Rollup calculation timing:
Rollups don't update instantly—they recalculate every hour by default. You can manually trigger recalculation for a specific record, but be aware that rollups on very large datasets may take minutes to compute.
Autonumber
Autonumber columns generate sequential or random unique identifiers automatically when records are created. They're perfect for ticket numbers, case references, or invoice IDs.
Autonumber formats:
| Format | Example Output | Use Case |
|---|---|---|
| Sequential | REQ-0001, REQ-0002 | Support tickets, invoices, case numbers |
| Date-based | REQ-2024-0001 | Annual numbering sequences |
| Random string | A3F7K2 | Access codes, temporary passwords |
Configuration options:
- Prefix – Text before the number (e.g., "REQ-", "INV-")
- Suffix – Text after the number (rarely used)
- Minimum digits – Zero-pad numbers (3 digits = 001, 002... 999)
- Seed value – Starting number (default 1000)
Important limitations:
- Autonumbers are assigned on create only—never on update
- Once assigned, the value never changes even if the record is deleted
- Gaps in sequence are normal (deleted records, failed creates)
- Cannot reset or restart a sequence once production data exists
Saving and Testing Your Table
After adding all necessary columns, save your table to make it available throughout the environment.
- Review all columns to ensure names, types, and requirements are correct
- Click Save Table in the bottom-right corner
- Wait for the save operation to complete (usually 5-10 seconds)
- Click Close to return to the tables list
Testing your table:
Create test records to verify your table structure works as expected:
- Navigate to Tables and select your new table
- Click Edit to open the table designer
- Add 2-3 test records with realistic data
- Verify required fields prevent saving when empty
- Check that choice dropdowns show correct options
- Confirm lookups display related records properly
If you spot issues (wrong column type, missing required field, incorrect relationship), fix them before building apps on top of this table. Structural changes become significantly harder once live data exists.
Dataverse Table Design Best Practices
Following proven design patterns prevents painful restructuring work later in your project.
Column naming conventions:
- Use descriptive names:
AssetSerialNumbernotSerialNo - Avoid abbreviations unless industry-standard (URL, VAT, SLA are fine)
- Use consistent terminology across tables (don't mix "Customer" and "Client")
- Never use spaces in schema names—they cause integration issues
Required vs Optional fields:
- Make fields required only if the record is meaningless without them
- Users resent being forced to enter data they don't have yet
- Use business rules to conditionally require fields based on other values
- Consider making the primary column the only required field initially
Performance optimisation:
- Create indexes on columns frequently used in filters and searches
- Avoid excessive rollup columns (max 10 per table)
- Use choice columns instead of lookups for small, stable lists
- Don't add columns "just in case"—only include data you'll actually use
- Everything in one massive table
- Text fields instead of lookups
- No relationships between tables
- Abbreviated cryptic column names
- Normalised structure with clear purpose
- Lookups for related data
- Proper many-to-one relationships
- Self-documenting column names
Next Steps
You've now created a Dataverse table with properly configured columns. Build on this foundation by exploring:
- Business rules – Add validation logic without writing code
- Security roles – Control which users can read, write, or delete records
- Views – Create filtered, sorted lists of records for different user roles
- Forms – Design the data entry interface users interact with
- Model-Driven Apps – Build full applications on top of your Dataverse tables
The official Microsoft Dataverse documentation provides in-depth guidance on advanced topics like alternate keys, virtual tables, and Dataverse search.