This guide covers how to create and update common Dataverse column data types using the Dataverse Web API with PowerShell. While standard types are relatively simple to handle, complex types like Lookup require specific formatting and payload structure to be processed correctly.
The Basics
I already covered the basic CRUD operations using the Web API so I strongly recommend reviewing this post first. Basically, you’ll either be using the methods post for create and patch for update. Then, we will pass the payload containing all the columns we want to create or update. We also need to ensure that the body is converted to json format. When working in PowerShell, we’ll create a hashtable and then convert it using the ConvertTo-Json cmdlet. You’ll also want to ensure you are using the logical name for the columns not the Display Name. There are some exceptions to this such as for the Lookup Field using the schema name. More on this later.
The Transaction Table Example
I have the following Dataverse table called “Transaction” (Logical Table Name: crbed_transaction) with the following columns and their data types:
| Display Name | Logical Name | Data Type |
|---|---|---|
| Item | crbed_item | Single line of Text |
| Transaction Date | crbed_transactiondate | Date Only |
| Transaction Date and Time | crbed_transactiondateandtime | Date and Time |
| Amount | crbed_amount | Currency |
| Category | crbed_category | Choice |
| Vendor | crbed_vendor | Lookup |
| Paid For | crbed_paidfor | Boolean |
| Notes | crbed_notes | Text Area |
I also have a csv called TransactionItems.csv. I want to populate it’s row into the table and it’s columns correspond to the columns in my Dataverse table.
In the next section, I’ll breakdown each of the column types before showcasing the example PowerShell script to import this data.
Column Types
Text
Text (and by extension text area and multiple lines of text) are pretty straight-forward. Just enclose the string in double-quotes.
$Body = @{
"crbed_item" = "Target Shopping"
"crbed_notes" = "For the upcoming wedding"
} | ConvertTo-Json
Be mindful of your character limits on your text fields. By default, the single line of text and text area is 100 characters while multi-line is 150. It can be increased on the column’s settings. If you exceed that column’s limit, the web request will error indicating the limitation. You can read more about the limits here.
Number
Depending on the type (whole, decimal, or float), you’ll need to declare the type in your script for precision. For example, using the currency type, we will need to convert to decimal, but for whole numbers, use integer
$Body = @{
"crbed_amount" = 40.34 -as [decimal],
"crbed_quantity" = 2 -as [int]
} | ConvertTo-Json
Choice
The choice value, not the actual choice label, is expected. From the local choices in my Transactions table, here are the choices:
$Body = @{
"crbed_category" = 624120000
} | ConvertTo-Json
Lookup
Lookup is the tricky one. We need to use the schema name for the lookup column as part of the body and also reference end point logical name for the associated table. For example, I have a vendors table which is used as a lookup for the transaction record. We need to get the guid of the vendor we want to associate.
$Body = @{
"[email protected]" = "/crbed_vendors(711f8360-6d1f-ee11-9cbd-000d3a34dc1d)"
} | ConvertTo-Json
In my example script at the end, I’ll showcase making the web request to get all the vendors, storing them as a pscustomobject, and filtering on that object to get the desired record.
Boolean
Even though in Dataverse the Yes/No column values are represented as 1 or 0 when reviewing the column details, we will need ensure it evaluates to “TRUE” or “FALSE” as a string. The boolean $true and $false also works from my testing.
$Body = @{
"crbed_paidfor" = "TRUE"
} | ConvertTo-Json
Date and Time
For columns where date and time used, ensure that the string format follows the yyyy-MM-dd format. If your column includes time use the format shown below.
When you set Date Time columns to user local, the datetime submitted is assumed to be UTC time. Unless you are in UTC time, you likely need to add a timezoneoffset that matches your computer’s timezone settings. For example, if you are in PST time, currently as of April 2025, it’s 7 hours behind UTC, so I’ll need to add 7 hours to my date time. The example below, I am using my local system’s timezone info.
$offsetHours = [Math]::Abs([System.TimeZoneInfo]::Local.GetUtcOffset([datetime]::Now).TotalHours)
$CurrentDate = get-date
$PSTTimeConvert = ($CurrentDate -as [datetime]).AddHours($offsetHours)
$TransactionDate = ($CurrentDate -as [datetime]).ToString("yyyy-MM-dd")
$TransactionDateTime = ($PSTTimeConvert).ToString("yyyy-MM-dd hh:mm:ss tt")
$Body = @{
"crbed_transactiondate" = $TransactionDate
"crbed_transactiondateandtime" = $TransactionDateTime
} | ConvertTo-Json
Putting it all Together
Here’s the completed script. We make requests to gather both the choices values for Categories and Vendors so we can reference these values dynamically. As we loop through the csv file, we use where-object to filter on the name where it match the corresponding name in the csv file.
#Loop through Transaction CSV File and Populate Dataverse Custom Table
$TransactionFilePath = "D:\My Downloads\BudgetItems.csv" # Replace with your file path
$TransactionFileImport = Import-Csv $TransactionFilePath
# Dot Source Access Token
. .\GetDataverseAccessToken.ps1
$Params =@{
ClientId = "CLIENT ID HERE"
TenantId = "TENANT ID HERE"
ClientSecret = "CLIENT SECRET HERE"
EnvironmentURL = "https://ORGNAMEHERE.crm.dynamics.com/"
}
$offsetHours = [Math]::Abs([System.TimeZoneInfo]::Local.GetUtcOffset([datetime]::Now).TotalHours)
$authResponseObject = Get-DataverseAccessToken @Params
$HeaderInfo = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
$BaseURL = "$($Params.EnvironmentURL)api/data/v9.2"
# Define tables as customobject to reference
$CategoryTable = @{
entityLogicalName = "crbed_transaction"
attributeLogicalName = "crbed_category"
selectQuery = "`$select=LogicalName"
expandQuery = "`$expand=OptionSet(`$select=Options)"
}
$TransactionTable = @{
entityLogicalName = "crbed_transaction"
entitySetName = "crbed_transactions"
entitySchemaName = "crbed_Transaction"
}
$VendorTable = @{
entityLogicalName = "crbed_vendor"
entitySetName = "crbed_vendors"
entitySchemaName = "crbed_Vendor"
}
$VendorURI = $BaseURL + "/" + $($VendorTable.entitySetName)
$TransactionURI = $BaseURL + "/" + $($TransactionTable.entitySetName)
$CategoryOptionsURI = "$BaseURL/EntityDefinitions(LogicalName='$($CategoryTable.entityLogicalName)')/Attributes(LogicalName='$($CategoryTable.attributeLogicalName)')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$($CategoryTable.selectQuery)&$($CategoryTable.expandQuery)"
# Get Transaction Table's Local Category Choices for filtering on later
$LocalCategoryChoices = (Invoke-RestMethod -Uri $CategoryOptionsURI -Headers $HeaderInfo -Method Get).OptionSet.Options `
| Select-Object Value, @{l='Name';e={$_.Label.LocalizedLabels.Label}}
# Get Vendor LookUp Data for filtering on later
$VendorData = (Invoke-RestMethod -Uri $VendorURI -Headers $HeaderInfo -Method Get).value | Select-Object crbed_vendorid,crbed_vendorname
Foreach ($transaction in $TransactionFileImport){
# Map columns
$Name= $transaction.Name
$TimeConvert = ($transaction.TransactionDateTime -as [datetime]).AddHours($offsetHours)
$TransactionDate = ($transaction.TransactionDate -as [datetime]).ToString("yyyy-MM-dd")
$TransactionDateTime = ($TimeConvert).ToString("yyyy-MM-dd hh:mm:ss tt")
$VendorLookUpID = ($VendorData | Where-Object {$_.crbed_vendorname -eq $transaction.Vendor}).crbed_vendorid
$PaidFor = $transaction.PaidFor -as [string]
$CategoryValue = ($LocalCategoryChoices | Where-Object {$_.Name -eq $transaction.Category}).Value -as [int]
$Notes = $transaction.Notes
$Amount = $transaction.Amount -as [decimal]
$Body = @{
"crbed_item" = $Name
"crbed_transactiondate" = $TransactionDate
"crbed_transactiondateandtime" = $TransactionDateTime
"crbed_amount" = $Amount
"crbed_notes" = $Notes
"crbed_category" = $CategoryValue
"crbed_paidfor" = $PaidFor
"[email protected]" = "/crbed_vendors($VendorLookUpID)"
} | ConvertTo-Json
$PostRequest = [ordered]@{
URI = $TransactionURI
Headers = $HeaderInfo
Method = 'Post'
Body = $Body
}
Write-Host "Creating Transaction: $($Name)"
# Create the transaction record
Invoke-RestMethod @PostRequest -ErrorAction Stop | Out-Null
}
