Connecting to Dataverse using PowerShell

Microsoft Dataverse is the data platform for Power Platform, enabling secure and scalable data storage. Using PowerShell, you can connect to Dataverse, manage data, and automate administrative tasks.

This guide provides a step-by-step process to connect to Dataverse using PowerShell, including prerequisites, authentication, and running queries.

Step 1: Prerequisites

Before connecting to Dataverse, ensure you have the following:

1. Required Permissions

You must have Global Admin, Power Platform Admin, or System Administrator roles in Dataverse.
Ensure that API Access is enabled for your environment.

2. Install PowerShell Modules

Microsoft Power Platform Modules for Dataverse
Microsoft Graph Modules (optional for advanced tasks)

Step 2: Install and Import PowerShell Modules

Run the following commands to install the required PowerShell modules:

# Install Power Platform Administration module
Install-Module -Name Microsoft.PowerPlatform.Administration -Scope CurrentUser -Force

# Install Power Platform Dataverse Client module
Install-Module -Name Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser -Force

# Import the modules
Import-Module Microsoft.PowerPlatform.Administration
Import-Module Microsoft.PowerPlatform.Cds.Client

Step 3: Authenticate and Connect to Dataverse

To interact with Dataverse, you need to authenticate using your Microsoft 365 credentials.

Option 1: Connect Using Interactive Login

# Connect to Dataverse interactively
$connection = Connect-CdsService -ConnectionString "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Prompt=Login"

You will be prompted to enter your Microsoft 365 admin credentials.

Option 2: Connect Using Service Principal (App Registration)

For automation, use an Azure AD App Registration with a client secret.

# Define connection details
$clientId = "your-app-client-id"
$clientSecret = "your-app-client-secret"
$tenantId = "your-tenant-id"
$orgUrl = "https://yourorg.crm.dynamics.com"

# Create authentication context
$secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)

# Connect to Dataverse
$connection = Connect-CdsService -Url $orgUrl -ClientId $clientId -ClientSecret $secureSecret -TenantId $tenantId

Step 4: Retrieve Dataverse Data

Once connected, retrieve data from tables (entities).

1. List All Dataverse Tables

# Get all tables in Dataverse
$tables = Get-CdsTable -Connection $connection

# Display table names
$tables | Select-Object LogicalName, DisplayName

2. Retrieve Records from a Specific Table

Example: Get all accounts from the Account table.

# Fetch account records
$accounts = Get-CdsRecord -Connection $connection -EntityLogicalName "account"

# Display account names
$accounts | Select-Object accountid, name

3. Retrieve Specific Fields from a Table

Example: Fetch specific fields from the Contact table.

# Fetch contacts with selected fields
$contacts = Get-CdsRecord -Connection $connection -EntityLogicalName "contact" -Select name, emailaddress1, telephone1

# Display contact details
$contacts | Format-Table name, emailaddress1, telephone1 -AutoSize

Step 5: Create, Update, and Delete Records in Dataverse

1. Create a New Record

Example: Create a new account in Dataverse.

# Define new account data
$newAccount = @{
    name = "New Company Name"
    telephone1 = "123-456-7890"
    emailaddress1 = "contact@company.com"
}

# Create the record
$newAccountId = New-CdsRecord -Connection $connection -EntityLogicalName "account" -Fields $newAccount
Write-Host "New account created with ID: $newAccountId"

2. Update an Existing Record

Example: Update an existing account’s phone number.

# Define update data
$updateFields = @{
    telephone1 = "987-654-3210"
}

# Update account by ID
Update-CdsRecord -Connection $connection -EntityLogicalName "account" -Id $newAccountId -Fields $updateFields
Write-Host "Account updated successfully."

3. Delete a Record

Example: Delete an account by ID.

# Delete the account
Remove-CdsRecord -Connection $connection -EntityLogicalName "account" -Id $newAccountId
Write-Host "Account deleted successfully."

Step 6: Automate Data Export from Dataverse

Export Data to CSV

Example: Export all account records to CSV.

# Fetch all accounts
$accounts = Get-CdsRecord -Connection $connection -EntityLogicalName "account"

# Export to CSV
$accounts | Export-Csv -Path "C:\Dataverse_Accounts.csv" -NoTypeInformation
Write-Host "Account data exported to C:\Dataverse_Accounts.csv"

Export Data to Excel

Example: Export contact details to an Excel file.

# Install Excel module if not installed
Install-Module -Name ImportExcel -Scope CurrentUser -Force

# Fetch contacts
$contacts = Get-CdsRecord -Connection $connection -EntityLogicalName "contact" -Select name, emailaddress1, telephone1

# Export to Excel
$contacts | Export-Excel -Path "C:\Dataverse_Contacts.xlsx" -WorksheetName "Contacts"
Write-Host "Contacts exported to C:\Dataverse_Contacts.xlsx"

Step 7: Disconnect from Dataverse

Once done, disconnect the session.

Disconnect-CdsService -Connection $connection
Write-Host "Disconnected from Dataverse."