Automating Dataverse Table Schema Updates using PowerShell

Automating Dataverse table schema updates using PowerShell helps streamline modifications to table structures, columns, relationships, and metadata without manual intervention. This guide explains how to modify and update a Dataverse table schema programmatically.

Step 1: Install and Import Power Platform PowerShell Modules

Ensure you have the required Power Platform PowerShell modules installed.

# Install Dataverse PowerShell modules
Install-Module -Name Microsoft.PowerApps.Administration.PowerShell -Scope CurrentUser -Force
Install-Module -Name Microsoft.PowerApps.PowerShell -Scope CurrentUser -Force

# Import modules
Import-Module Microsoft.PowerApps.Administration.PowerShell
Import-Module Microsoft.PowerApps.PowerShell

Step 2: Authenticate to Dataverse

Connect to Dataverse as an administrator.

# Authenticate using interactive login
Add-PowerAppsAccount

For service principal authentication, use:

$AppId = "YOUR_APP_ID"
$TenantId = "YOUR_TENANT_ID"
$CertificateThumbprint = "YOUR_CERT_THUMBPRINT"

Connect-AdminPowerAppEnvironment -ApplicationId $AppId -TenantId $TenantId -CertificateThumbprint $CertificateThumbprint

Step 3: Retrieve Existing Dataverse Table Schema

Before modifying the schema, retrieve current table details.

# Get all Dataverse tables
$tables = Get-AdminPowerAppTable
$tables | Format-Table LogicalName, DisplayName, SchemaName

For a specific table:

$tableName = "new_customtable"  # Replace with your table name
$table = Get-AdminPowerAppTable -LogicalName $tableName
$table | Format-List *

Step 4: Update Table Display Name or Description

Modify metadata such as table name or description.

# Update table display name
$tableName = "new_customtable"  # Replace with your table name
Update-AdminPowerAppTable -LogicalName $tableName -DisplayName "Updated Table Name" -Description "Updated table description"

Step 5: Adding a New Column to the Table

Add a new column dynamically to a Dataverse table.

$columnParams = @{
    TableName      = "new_customtable"
    DisplayName    = "New Column"
    LogicalName    = "new_newcolumn"
    SchemaName     = "new_NewColumn"
    DataType       = "Text"
    RequiredLevel  = "None"
}

New-AdminPowerAppTableColumn @columnParams

DataType can be: Text, WholeNumber, Decimal, Boolean, DateTime, Choice, etc.
RequiredLevel: None, Recommended, or Required.

Step 6: Modifying an Existing Column

Change column properties like display name, requirement level, or type.

# Update column properties
$columnParams = @{
    TableName      = "new_customtable"
    LogicalName    = "new_newcolumn"
    DisplayName    = "Updated Column Name"
    RequiredLevel  = "Required"
}

Update-AdminPowerAppTableColumn @columnParams

Step 7: Deleting an Unused Column

Remove an unwanted column from a table.

# Delete a column
$columnName = "new_newcolumn"
Remove-AdminPowerAppTableColumn -TableName "new_customtable" -LogicalName $columnName -Confirm:$false

Step 8: Automate Schema Updates Using a PowerShell Script

Save this script as UpdateDataverseSchema.ps1 to automate schema updates.

# PowerShell script to update Dataverse schema
$TableName = "new_customtable"

# Check if the table exists
$table = Get-AdminPowerAppTable -LogicalName $TableName

if ($table) {
    Write-Host "Updating Schema for Table: $TableName"

    # Add a new column
    $columnParams = @{
        TableName      = $TableName
        DisplayName    = "Automated Column"
        LogicalName    = "new_autoColumn"
        SchemaName     = "new_AutoColumn"
        DataType       = "Text"
        RequiredLevel  = "None"
    }
    New-AdminPowerAppTableColumn @columnParams
    Write-Host "Added new column: new_autoColumn"

    # Update an existing column
    Update-AdminPowerAppTableColumn -TableName $TableName -LogicalName "new_autoColumn" -DisplayName "Updated Auto Column" -RequiredLevel "Recommended"
    Write-Host "Updated column: new_autoColumn"

    # Remove an unnecessary column
    Remove-AdminPowerAppTableColumn -TableName $TableName -LogicalName "old_unusedColumn" -Confirm:$false
    Write-Host "Removed old column: old_unusedColumn"
} else {
    Write-Host "Table $TableName does not exist!"
}

Step 9: Schedule the Automation Script

Schedule a Windows Task to run the script regularly.

$Trigger = New-ScheduledTaskTrigger -Daily -At 3:00AM
$Action = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "C:\Scripts\UpdateDataverseSchema.ps1"
Register-ScheduledTask -TaskName "DataverseSchemaUpdate" -Trigger $Trigger -User "Administrator" -Action $Action -RunLevel Highest -Force

Step 10: Validate Schema Updates

Check if the updates were applied successfully.

Get-AdminPowerAppTableColumn -TableName "new_customtable" | Format-Table LogicalName, DisplayName, DataType

Step 11: Disconnect Power Platform Session

(Optional) Close the session after running the script.

Disconnect-AdminPowerAppEnvironment