Create a Dataverse Table With Every Common Column Type Using Power Automate
Create a Dataverse Table With Every Common Column Type Using Power Automate
If you have ever clicked through the maker portal to build a Dataverse table column by column, you know how slow it gets. There is a faster, repeatable way: send one HTTP request to the Dataverse Web API and let it build the whole table — primary column, choices, currency, dates, the lot — in a single shot.
In this post I will walk through a Power Automate cloud flow that does exactly that. It creates a demo table called Sample Product with one column of every common type, so you can see how each column type is defined in the Web API.
The idea in one line
Dataverse exposes a metadata endpoint. POST a JSON definition of your table to it, and Dataverse creates the table and all the columns you described.
POST https://yourorg.crm.dynamics.com/api/data/v9.2/EntityDefinitions
Replace yourorg with your own environment URL. You can find it in the Power Platform Admin Center under your environment's settings, or in the address bar when you open any model-driven app.
The flow at a glance
The flow has just two steps after the trigger:
Step
Action
What it does
Trigger
Manually trigger a flow
Lets you run it on demand with a button
1
Compose
Holds the full table definition as JSON
2
HTTP request
POSTs that JSON to the EntityDefinitions endpoint
Keeping the definition in a Compose action makes the flow easy to read and easy to tweak. The HTTP action just points at the Compose output.
Connector note: This example uses the HTTP with Microsoft Entra ID (preauthorized) connector (the "Invoke an HTTP request" action). It is a premium connector, but it handles authentication to Dataverse for you, so you do not have to manage tokens by hand.
Step 1 — Compose the table definition
The Compose action holds an EntityMetadata object. The top part describes the table itself; the Attributes array describes each column.
The internal name. new_ is the default publisher prefix — change it to match your own solution publisher.
DisplayName
The singular label shown in the UI ("Sample Product").
DisplayCollectionName
The plural label ("Sample Products").
OwnershipType
UserOwned means rows belong to a user or team. Use OrganizationOwned for shared reference data.
LanguageCode: 1033
The locale ID for English (United States). Use your own LCID if needed.
Why the double @@?
You will notice @@odata.type instead of @odata.type. This is a Power Automate quirk, not a Dataverse one. In Power Automate the @ symbol starts an expression, so to send a literal @ you have to double it. When the flow runs, @@odata.type is sent to Dataverse as @odata.type. If you copy this JSON somewhere outside Power Automate, drop one of the @ signs.
The column types, explained
Every column lives in the Attributes array and needs a matching @odata.type. Pick the wrong type and you get a 400 Bad Request. Here is each column type used in the demo table:
Column
@odata.type
Dataverse type
Notes
Product Name
StringAttributeMetadata
Single line of text
The primary name column. IsPrimaryName: true, MaxLength: 100. Every table needs exactly one.
If you forget IsPrimaryName: true, or include zero (or more than one) of them, the request fails. This is the most common mistake when building tables this way.
A choice column (single select)
A local choice set lives right inside the column definition. Each option needs a numeric Value and a label:
Set IsGlobal: true if you want a global choice set that other tables can reuse. The multi-select column ("Tags") follows the same shape — the only real difference is the @odata.type and that its AttributeType is reported as Virtual.
Step 2 — The HTTP request
The second action posts the Compose output to Dataverse:
On success Dataverse returns HTTP 204 No Content, with an OData-EntityId header pointing at your new table. No body comes back — the empty 204 is the success signal.
Two improvements worth adding
The base flow works, but two small additions make it production-friendly.
1. Put the table in a solution. As written, the new table lands in the Default Solution, which is an ALM anti-pattern — it makes the table hard to move between environments. Add this header to the HTTP action so the table is created inside your own unmanaged solution:
MSCRM.SolutionUniqueName: YourSolutionUniqueName
Use the solution's unique name, not its display name.
2. Read it back with a strong-consistency header. Metadata is cached, so if you immediately query the new table it might return a 404 because the cache has not caught up. When you read straight after creating, add:
Consistency: Strong
Wrap-up
With one Compose action and one HTTP request you can stand up a full Dataverse table — primary column, numbers, currency, dates, and both flavours of choice — without touching the maker portal. Because the whole definition is just JSON, you can version it, parameterise it, or drive it from a CSV or SharePoint list to build tables on demand.
The same EntityDefinitions endpoint also handles updates (PUT) and lets you add columns to an existing table later (POST to its Attributes collection), so this is a solid foundation for any metadata-as-code approach on the Power Platform.
Quick reference: column type to @odata.type
Column type you want
@odata.type to use
Single line of text
StringAttributeMetadata
Multiple lines of text
MemoAttributeMetadata
Whole number
IntegerAttributeMetadata
Big integer
BigIntAttributeMetadata
Decimal number
DecimalAttributeMetadata
Float
DoubleAttributeMetadata
Currency
MoneyAttributeMetadata
Yes/No
BooleanAttributeMetadata
Date and time
DateTimeAttributeMetadata
Choice (single)
PicklistAttributeMetadata
Choices (multi)
MultiSelectPicklistAttributeMetadata
Full flow JSON
Here is the complete flow, scrubbed of environment-specific values. Before you use it, replace two placeholders:
yourorg.crm.dynamics.com in the HTTP action URL with your own environment URL.
The connection details (connectionName, connectionReferenceLogicalName) will be set automatically when you add your own HTTP with Microsoft Entra ID connection — the placeholder values below are only there to keep the JSON valid.
Note: the @@odata.type double-@ is correct for this Power Automate definition (it is how the editor escapes a literal @). Keep it as-is when pasting into the flow editor's code view. If you ever send the body straight to Dataverse outside Power Automate, use a single @odata.type.
RPA (Robotic Process Automation) is software technology that uses bots to automate repetitive, rule-based tasks that humans perform on computers — clicking, typing, reading files, filling forms, etc.
UiPath is the leading RPA platform. It has three core products:
Product
Purpose
UiPath Studio
Where you design and build automation workflows
UiPath Robot
Executes the workflows (attended or unattended)
UiPath Orchestrator
Web portal to deploy, schedule, monitor, and manage robots
Types of Robots
Attended Robot — works alongside a human; triggered manually. Used for tasks that need human input mid-process.
Unattended Robot — runs fully autonomously, triggered from Orchestrator on a schedule or via API.
Hybrid — combination of both, typically using the RE Framework.
Key Concepts
Process — an automation workflow you build.
Job — a single execution of a process on a robot.
Queue — a list of work items (transactions) stored in Orchestrator for unattended robots to process.
Type text into a field (clears first with Empty field option)
Get Text
Read text from an element
Set Text
Set text without simulating keystrokes
Check
Check/uncheck a checkbox
Select Item
Choose from a dropdown
Send Hotkey
Press keyboard shortcuts (Ctrl+C, Enter, Tab…)
Attach Window
Scope activities to a specific window
Use Application/Browser
Modern UI interaction (Studio 21+)
Input Methods
Method
Speed
Background?
Recommended For
Default
Medium
No
General use
Simulate
Fast
Yes
Web / simple inputs
Window Messages
Fast
Partial
Desktop apps
ChromiumAPI
Fastest
Yes
Chrome / Edge web
Image & Text Automation (Fallback)
When selectors are unreliable (Citrix, remote desktop, legacy apps):
Find Image + Click Image — click based on a screenshot template.
Computer Vision activities — AI-powered element detection.
Web Automation Example
Use Application/Browser: https://example.com
Type Into [Username field]: "myuser"
Type Into [Password field]: "mypass"
Click [Login button]
Get Text [Welcome message] → welcomeMsg
Log Message: welcomeMsg
6. Excel & Data Table Automation
Excel Activities (requires Excel to be installed)
Activity
Purpose
Use Excel File
Open/create an Excel file (modern, recommended)
Read Range
Read cells into a DataTable
Write Range
Write a DataTable to a sheet
Append Range
Add rows below existing data
Read Cell
Read a single cell value
Write Cell
Write to a single cell
For Each Excel Row
Iterate rows one-by-one (modern)
Filter Data Table
Filter rows by condition
Sort Data Table
Sort rows
DataTable Operations
' Create DataTable
Assign: dt = New DataTable
' Add columns
Invoke Code: dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Age", GetType(Integer))
' Add row
Invoke Code: dt.Rows.Add("Alice", 30)
' Get cell value
Assign: name = dt.Rows(0)("Name").ToString
' Row count
Assign: count = dt.Rows.Count
' Filter (LINQ)
Assign: filtered = (From row In dt.AsEnumerable()
Where row.Field(Of String)("Status") = "Pending"
Select row).CopyToDataTable()
Excel Automation Workflow Pattern
Use Excel File: "Input.xlsx"
Read Range [Sheet1] → inputDT
For Each Row In inputDT
' Process each row
Assign: name = CurrentRow("Name").ToString
Assign: email = CurrentRow("Email").ToString
' ... do work ...
Assign: CurrentRow("Status") = "Done"
Write Range → "Output.xlsx" [Sheet1]
7. String Manipulation & Regex
Common String Methods
str.ToUpper() ' "hello" → "HELLO"
str.ToLower() ' "HELLO" → "hello"
str.Trim() ' Remove whitespace from both ends
str.TrimStart() / str.TrimEnd()
str.Replace("old", "new")
str.Contains("search") ' Returns Boolean
str.StartsWith("pre")
str.EndsWith("suf")
str.IndexOf("x") ' Position of first occurrence
str.Substring(start, length) ' Extract portion
str.Split(","c) ' Split into array by delimiter
str.Length ' Character count
String.Join(",", array) ' Join array into string
String Formatting
' Concatenation
result = "Hello, " + name + "!"
' String interpolation (VB.NET)
result = $"Hello, {name}! You are {age} years old."
' Format
result = String.Format("Invoice #{0} — Total: ${1:F2}", invoiceId, total)
Regular Expressions
Used via the Matches, IsMatch, Replace activities or System.Text.RegularExpressions.Regex.
Pattern
Matches
\d+
One or more digits
\w+
One or more word characters
\s+
One or more whitespace characters
[A-Z]{2,3}
2–3 uppercase letters
^\d{5}$
Exactly 5 digits (full string)
(\d{3})-(\d{4})
Phone pattern with capture groups
' Extract all email addresses from text
Matches Activity:
Input: bigText
Pattern: "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}"
Result: emailMatches (IEnumerable(Of Match))
8. Error Handling & Logging
Try/Catch
Wrap risky operations in a Try Catch activity:
Try
[activities that might fail]
Catch (Exception e)
Log Message: "Error: " + e.Message (level: Error)
[recovery actions or rethrow]
Finally
[always runs — good for cleanup]
In the Main.xaml, set Global Exception Handler to catch uncaught errors at the process level. Used in RE Framework's Main.xaml state machine.
9. PDF, Email & File Automation
File & Folder Operations
' File activities (System namespace)
Path Exists → check if file/folder exists
Copy File → copy a file to new location
Move File → move/rename
Delete → delete file or folder
Get Files → list files matching pattern ("*.xlsx")
Create Directory → make a folder
' Common paths in VB.NET expressions
Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
System.IO.Path.Combine(folderPath, fileName)
System.IO.Path.GetFileName(fullPath) ' "report.xlsx"
System.IO.Path.GetExtension(fullPath) ' ".xlsx"
System.IO.Path.GetFileNameWithoutExtension ' "report"
Email Automation
Outlook (via UiPath.Mail.Activities):
Get Outlook Mail Messages:
MailFolder: "Inbox"
Filter: "[Subject] = 'Invoice'"
Top: 50
→ emails (List(Of MailMessage))
For Each email In emails
Assign: subject = email.Subject
Assign: body = email.Body
For Each attach In email.Attachments
Save Attachment → folderPath
Send Email:
Send Outlook Mail Message:
To: "recipient@company.com"
Subject: "Automation Report"
Body: "Process completed. See attached."
Attachments: {"C:\report.xlsx"}
PDF Automation
' Read all text from a PDF
Read PDF Text:
FileName: "invoice.pdf"
→ pdfText (String)
' Then use String activities / Regex to extract fields
Matches: pdfText, "Invoice #(\d+)" → invoiceNum
Matches: pdfText, "Total: \$([0-9,.]+)" → totalAmt
10. Selectors & UI Explorer
What is a Selector?
A selector is an XML path that identifies a UI element. Example:
This is the most important section for production-grade automation. The RE Framework is UiPath's official template for building robust, scalable unattended bots.
Why RE Framework?
Without a framework, most bots:
Crash on unexpected errors and don't recover.
Can't be monitored or restarted cleanly.
Mix "get data" logic with "process data" logic.
Have no retry mechanism.
RE Framework solves all of this.
Architecture — State Machine
RE Framework uses a State Machine with 4 states:
┌─────────────────┐
│ INITIALIZATION │ ← Start here (open apps, read config)
└────────┬────────┘
│ Success
┌────────▼────────┐
┌────►│ GET TRANSACTION │ ← Fetch next work item from Queue
│ └────────┬────────┘
│ │ Item found
│ ┌────────▼────────┐
│ │ PROCESS │ ← Do the actual work
│ │ TRANSACTION │
│ └────────┬────────┘
│ │
└──────────────┘ (loop back for next item)
│ No more items
┌────────▼────────┐
│ END PROCESS │ ← Close apps, send report
└─────────────────┘
File Structure
MyProcess/
├── Main.xaml ← State Machine (do not edit logic here)
├── project.json ← Project metadata
├── Data/
│ └── Config.xlsx ← All settings (URLs, queue names, etc.)
├── Framework/
│ ├── InitAllApplications.xaml ← Open all apps, login
│ ├── CloseAllApplications.xaml ← Close apps cleanly
│ ├── GetTransactionData.xaml ← Get next queue item / row
│ ├── SetTransactionStatus.xaml ← Mark item Success/Fail in Orchestrator
│ ├── InitAllSettings.xaml ← Read Config.xlsx + Orchestrator Assets
│ ├── TakeScreenshot.xaml ← Screenshot on error
│ └── RetryCurrentTransaction.xaml
└── Process.xaml ← YOUR BUSINESS LOGIC GOES HERE
Config.xlsx Structure
Name
Value
Asset
Description
OrchestratorQueueName
OrdersQueue
Queue to read transactions from
MaxRetryNumber
3
Retries per transaction
logF_BusinessProcessName
OrderProcessor
Used in log messages
in_TransactionNumber
0
Counter (do not change)
Transaction Types
Queue-based (default): Items come from an Orchestrator Queue. Best for large volumes, parallel processing.
DataTable-based: Items come from an Excel file or database. Used when Orchestrator isn't available or for simpler use cases.
Exception Types in RE Framework
Exception
Behavior
BusinessRuleException
Item marked Failed (Business). Not retried. Example: invalid data.
ApplicationException
Item marked Failed (Application) and retried up to MaxRetryNumber. Example: app timeout, element not found.
' Throw a BusinessRuleException (your code in Process.xaml)
Throw New BusinessRuleException("Order ID is missing or invalid")
' ApplicationExceptions are caught automatically by the framework
' — just let them propagate up from Process.xaml
Process.xaml — Your Code Goes Here
This is the only file you heavily edit. It receives one transaction at a time:
Input Arguments:
in_TransactionItem (QueueItem or DataRow)
in_Config (Dictionary(Of String, String))
Steps in Process.xaml:
1. Extract fields from in_TransactionItem
2. Open the target application (or it's already open from Init)
3. Perform the business steps
4. If data is invalid → Throw BusinessRuleException
5. If app misbehaves → let ApplicationException propagate
RE Framework Execution Flow (Detailed)
INIT STATE
├── InitAllSettings.xaml → reads Config.xlsx, loads Orchestrator Assets
├── InitAllApplications.xaml → opens apps, logs in
└── On success → go to GET TRANSACTION STATE
GET TRANSACTION STATE
├── GetTransactionData.xaml
│ Queue mode: Get Queue Item from Orchestrator → transactionItem
│ Table mode: Get next unprocessed row
└── If item found → PROCESS TRANSACTION STATE
If no more items → END PROCESS STATE
PROCESS TRANSACTION STATE
├── Process.xaml (your business logic)
├── If BusinessRuleException:
│ Mark item as Failed (Business) — do NOT retry
├── If ApplicationException (1st–Nth time):
│ Take screenshot, log error
│ If retries < MaxRetryNumber → restart Init, retry same item
│ If retries exhausted → mark Failed (Application)
└── If success → SetTransactionStatus "Successful" → loop to GET TRANSACTION
END PROCESS STATE
├── CloseAllApplications.xaml
└── Send summary email (optional)
14. Advanced Topics
Parallel Processing
Parallel Activity — run multiple branches simultaneously:
Branch 1: Process Web App
Branch 2: Process Desktop App
Branch 3: Write to Excel
Note: branches share the same thread pool. Use for I/O-bound work,
not for UI automation (which requires focus on one window at a time).
Invoke Code Activity
Run VB.NET or C# code directly when no activity exists:
' VB.NET code in Invoke Code
Dim result As String = ""
For Each line As String In text.Split(Environment.NewLine)
If line.Contains("Total") Then
result = line.Split(":"c)(1).Trim()
Exit For
End If
Next
' Pass result back via argument
