Variables, Lists, and Data Tables in Power Automate Desktop

Introduction

Picture this: you're building a desktop flow to process 200 employee records from a legacy HR system. The application doesn't have an API. There's no database connection. It's a Windows form application from 2003, and every week someone has to manually open it, read through each record, copy values into an Excel spreadsheet, and flag anomalies. Your job is to automate that entire process — and the moment you try, you realize that storing, organizing, and manipulating data is where most of the real complexity lives.

Variables, lists, and data tables are the backbone of every non-trivial Power Automate Desktop flow. If you've spent time with desktop flows for automating legacy applications, you already know that RPA isn't just about clicking buttons — it's about intelligently managing state across hundreds or thousands of UI interactions. That means storing intermediate values, tracking collections of items, and querying structured data without ever leaving the desktop flow itself.

By the end of this lesson, you'll be able to design flows that handle real-world data complexity: multi-row Excel extracts, conditional processing based on dynamic values, and structured transformations between data containers. You'll understand not just what to use, but when and why — which is the difference between a flow that works once and one that runs reliably in production.

What you'll learn:

• How Power Automate Desktop's type system works and how to declare, scope, and modify variables correctly
• The difference between lists and data tables, and how to choose the right container for your scenario
• How to populate lists from UI scraping, Excel, and manual initialization
• How to create, filter, sort, and loop through data tables
• How to convert between variable types and avoid the type mismatches that break flows in production

---

Prerequisites

You should already be comfortable opening Power Automate Desktop, building basic flows with sequential actions, and running recorded flows. If you're just getting started, work through Getting Started with Power Automate Desktop first. You should also understand what loops and conditions do conceptually — we'll use them heavily here, and if the concepts are fuzzy, review Working with Conditions, Loops, and Variables in Power Automate before continuing.

---

How Power Automate Desktop Thinks About Data

Power Automate Desktop is a strongly-typed runtime under the hood, even though the designer tries to hide that from you. Every variable has a type, and when types don't match — when you try to use a Text value where a Number is expected, or pass a List where a DataTable is required — the flow fails with errors that can be genuinely confusing if you don't understand the system.

There are six core types you'll work with constantly:

• Text — any string value, including numbers stored as text
• Number — numeric values (integers and decimals)
• Boolean — True or False
• List — an ordered, zero-indexed collection of items of the same or mixed types
• DataTable — a two-dimensional structure with named columns and typed rows
• DateTime — a date and/or time value with timezone awareness

The designer shows you the current type of any variable in the Variables panel on the right side of the canvas. When a variable is first set, PAD infers the type from the action that created it. When you use "Set Variable," you can assign a literal value or an expression — the type is then determined by what you assign.

Setting Your First Variables

To create a variable manually, drag the Set Variable action onto the canvas. In the "Variable name" field, type a descriptive name — PAD will prepend the % sigil automatically when you reference it elsewhere. Give it a value.

Here's a realistic example: you're building a flow that processes invoices from a billing portal. You need to track the current vendor name, the invoice total, and whether the invoice has been validated.

Set variable: VendorName  to "Contoso Manufacturing"
Set variable: InvoiceTotal to 0
Set variable: IsValidated  to False

These three lines establish your working state. As the flow progresses, you'll update them. The key discipline is naming variables for their role, not their content — InvoiceTotal is better than Amount or Num1.

Expressions and Variable References

You reference any variable by wrapping it in % signs: %VendorName%, %InvoiceTotal%. You can also write simple expressions inline:

Set variable: TaxAmount    to %InvoiceTotal% * 0.08
Set variable: InvoiceLabel to "Invoice for: " + %VendorName%

PAD evaluates these expressions at runtime. String concatenation uses +. Numeric operations use standard arithmetic operators (+, -, *, /). There's also a set of built-in functions you can call inside expressions:

Set variable: UpperVendor to %VendorName%.ToUpper()
Set variable: RoundedTax  to %TaxAmount%.Round(2)

These are method-style calls that PAD resolves at runtime. Not every type supports every method — Text variables support .ToUpper(), .ToLower(), .Trim(), .Replace(), .Contains(), .StartsWith() — but Number variables don't support string methods.

---

Working with Lists

A List in PAD is an ordered collection. Think of it as a single-column spreadsheet — rows of values, each accessible by its zero-based index. Lists are the right choice when you have a collection of similar items that you want to iterate through, filter, or accumulate during execution.

Creating a List

There are three common ways to create a list:

1. From the "Create new list" action: In the designer, search for "Create new list" and drop it onto the canvas. Set the variable name — say, VendorNames. This creates an empty list you'll populate as the flow runs.

2. From a "Read from Excel worksheet" or similar data action: Many read actions return a list directly. When you use "Get files in folder," the output is a list of file paths. When you use "Split text," the output is a list of substrings. PAD creates these for you automatically — the variable panel shows the type as List of Text Values or similar.

3. By assigning a hardcoded comma-separated set: You can initialize a list directly in Set Variable using a list literal — though this is less common. Usually, you start with an empty list and build it up.

Adding Items to a List

Once you have a list variable, use the Add item to list action:

Add item "Contoso Manufacturing" to list: VendorNames
Add item "Northwind Traders"    to list: VendorNames
Add item "Fabrikam Logistics"   to list: VendorNames

After these three actions, %VendorNames[0]% is "Contoso Manufacturing", %VendorNames[1]% is "Northwind Traders", and so on.

You'll typically build lists dynamically — looping through a UI, reading a value on each iteration, and appending:

Loop (For each row in a UI table):
    Get text from element: CurrentVendorLabel → stores in: ScrapedVendor
    Add item %ScrapedVendor% to list: VendorNames
End loop

This pattern — scrape a value, append to list, continue looping — is one of the most common in RPA work.

Reading From a List

Access individual items with bracket notation in expressions:

Set variable: FirstVendor to %VendorNames[0]%
Set variable: LastVendor  to %VendorNames[VendorNames.Count - 1]%

%VendorNames.Count% gives you the total number of items. Using Count - 1 gets you the last item without hardcoding the index.

You can also iterate with a For Each loop:

For each Vendor in VendorNames:
    Log message: "Processing vendor: " + %Vendor%
    [... your processing logic ...]
End for each

The loop variable (Vendor here) takes the value of each item on each iteration. You don't manage the index manually.

Removing and Finding Items

Use Remove item from list to delete by index or by value:

Remove item at index 1 from list: VendorNames        ← removes "Northwind Traders"
Remove item with value "Fabrikam Logistics" from list: VendorNames

To check whether a value exists in a list, use the List contains item action or an inline expression:

If %VendorNames.Contains("Contoso Manufacturing")% then
    [... handle existing vendor ...]
End if

Sorting and Shuffling Lists

The Sort list action sorts alphanumerically (ascending or descending). The Shuffle list action randomizes order — useful for load-balancing tasks across accounts or test data randomization. The Reverse list action flips the order. None of these require you to write sorting logic manually.

---

Data Tables: Structured Two-Dimensional Data

Lists are great for single-column data, but most real-world automation involves rows of records with multiple fields. That's where DataTable comes in.

A DataTable is PAD's version of an in-memory table — rows and columns, where each column has a name and every row is a record. If you've worked with Excel, think of a DataTable as a worksheet range that lives in memory during your flow's execution.

Where Data Tables Come From

You rarely create a DataTable from scratch. They typically come from:

• Read from Excel worksheet — returns a DataTable when you read a range with headers
• Execute SQL statement — returns a DataTable of query results
• Extract data from web page / UI element — web and UI scraping can return a DataTable when the target is a table element
• Read from CSV file — returns a DataTable

The most common source in production flows is Excel. When you use "Read from Excel worksheet" with "Get specified cells" or "Get all values from worksheet," PAD returns a DataTable where column names match the header row.

Reading From Excel Into a DataTable

Here's the action sequence for reading a formatted employee report from Excel:

Launch Excel: EmployeeReport.xlsx → Instance: ExcelInstance
Attach to running Excel  (if already open)
Read from Excel worksheet:
    Excel instance: ExcelInstance
    Retrieve: All available values from worksheet
    First line of range contains column names: Yes
    → Stores in: EmployeeTable

After this action, %EmployeeTable% is a DataTable. In the Variables panel, you can expand it to see its columns and the first few rows of data — invaluable for debugging.

Accessing DataTable Cells

You reference cells using row index and column name:

Set variable: FirstName to %EmployeeTable[0]['FirstName']%
Set variable: Salary    to %EmployeeTable[0]['AnnualSalary']%

Or by column index (zero-based):

Set variable: FirstName to %EmployeeTable[0][0]%

Using column names is strongly preferred — column indices break silently if the source spreadsheet ever adds or reorders columns.

Iterating Through a DataTable

Use For Each with a DataTable, and PAD gives you a CurrentRow variable on each iteration:

For each CurrentRow in EmployeeTable:
    Set variable: EmpName       to %CurrentRow['FullName']%
    Set variable: EmpDepartment to %CurrentRow['Department']%
    Set variable: EmpSalary     to %CurrentRow['AnnualSalary']%
    
    If %EmpSalary% > 150000 then
        Log message: %EmpName% + " exceeds salary threshold"
        [... flag record in legacy system ...]
    End if
End for each

This is the bread and butter of data-driven RPA. You're not hardcoding employee names or counts — the flow adapts to however many rows exist in the spreadsheet. If next month's report has 312 employees instead of 287, the flow handles it without modification.

Filtering a DataTable

PAD has a dedicated Filter data table action that lets you apply conditions:

Filter data table:
    Data table: EmployeeTable
    Filter: Department Equals "Finance" AND AnnualSalary > 80000
    → Stores in: FinanceHighEarners

The filter produces a new DataTable containing only matching rows. The original EmployeeTable is unchanged. You can chain filters — filter once to get a department, filter again to narrow by salary range.

The filter condition syntax in the Filter Data Table dialog uses a visual builder (add rows with column / operator / value), not a text expression. This limits some flexibility but prevents syntax errors.

Sorting a DataTable

Use the Sort data table action:

Sort data table:
    Data table: EmployeeTable
    Column to sort by: AnnualSalary
    Order: Descending
    → Stores in: SortedEmployees

Like filtering, this returns a new DataTable rather than modifying in place.

Adding Rows and Columns

You can build a DataTable from scratch using Create new data table — the designer opens a visual editor where you define column names and types — then populate it row by row:

Create new data table:
    Columns: ProcessedDate (Text), VendorName (Text), InvoiceTotal (Number), Status (Text)
    → Stores in: ProcessingLog

[... inside your processing loop ...]

Add row to data table:
    Data table: ProcessingLog
    Values: %Today%, %VendorName%, %InvoiceTotal%, "Completed"

This pattern is extremely useful for building audit logs, output reports, or staging data before writing to Excel or a database.

---

Type Conversion: Bridging the Gaps

One of the most frustrating sources of errors in PAD flows is implicit type mismatches. The designer doesn't always warn you before runtime, which means you discover the problem when the flow fails.

Text to Number

When you scrape a value from a UI — say, a price field showing "$1,250.00" — PAD stores it as Text. You can't do math on it until you convert:

Replace text:
    Text: %ScrapedPrice%
    Text to find: "$"
    Replace with: ""
    → Stores in: CleanedPrice

Replace text:
    Text: %CleanedPrice%
    Text to find: ","
    Replace with: ""
    → Stores in: CleanedPrice

Convert text to number:
    Text to convert: %CleanedPrice%
    → Stores in: NumericPrice

This is a three-step clean: strip the dollar sign, strip the comma, then convert. Skip any step and the conversion fails.

Number to Text

Going the other direction, use Convert number to text when you need to display a number in a string context:

Convert number to text:
    Number to convert: %InvoiceTotal%
    Decimal places: 2
    → Stores in: FormattedTotal

Set variable: DisplayMessage to "Invoice total: $" + %FormattedTotal%

DateTime Handling

DateTime variables can be tricky because the display format depends on system locale. Use Convert datetime to text with an explicit format string when you need consistent formatting:

Convert datetime to text:
    Datetime: %CurrentDateTime%
    Format to use: Custom
    Custom format: yyyy-MM-dd
    → Stores in: DateString

Using yyyy-MM-dd (ISO 8601) is safest for data that'll end up in spreadsheets or databases, since locale-ambiguous formats like 01/02/2024 can be interpreted differently on different machines.

DataTable to List and Back

Sometimes you need to extract a single column from a DataTable as a List. PAD doesn't have a direct "extract column" action, but you can build it with a loop:

Create new list → ColumnValues

For each CurrentRow in MyDataTable:
    Add item %CurrentRow['TargetColumn']% to list: ColumnValues
End for each

This is useful when you need to pass a set of values to another action that expects a list — like checking whether each item exists in another source.

---

Variable Scope in Subflows

If your flow uses subflows (the equivalent of functions or subroutines), you need to understand scope. In PAD, all variables are global by default. Any variable set in a subflow is visible to the main flow and every other subflow.

This is both convenient and dangerous. Convenient because you don't need complex parameter-passing mechanisms. Dangerous because a subflow can accidentally overwrite a main flow variable if names collide.

The practical pattern for passing data into a subflow is to set a "parameter" variable before calling it:

Set variable: SF_ProcessVendor_VendorName to %CurrentVendorName%
Set variable: SF_ProcessVendor_InvoiceAmt  to %CurrentInvoiceAmount%
Run subflow: ProcessVendor
Set variable: ProcessingResult to %SF_ProcessVendor_Result%

This is manual parameter passing, but it makes the data flow explicit and auditable.

---

Hands-On Exercise: Build an Invoice Processing Data Pipeline

Let's put everything together with a realistic exercise. You'll build a flow that:

1. Reads invoice data from an Excel file into a DataTable
2. Cleans and validates each row
3. Segregates invoices into two lists: approved and flagged
4. Writes a summary DataTable back to Excel

Step 1: Set Up Your Excel Source

Create an Excel file named InvoiceQueue.xlsx with these columns in row 1: InvoiceID | VendorName | InvoiceDate | RawAmount | Currency

Populate 10–15 rows with realistic data. Mix USD and EUR currencies. Include some RawAmount values with currency symbols like $1,250.00 and some without. Include a couple of blank VendorName cells to simulate data quality issues.

Step 2: Create the Main Flow Structure

Open Power Automate Desktop and create a new flow called Invoice_DataPipeline_v1.

Add these actions in sequence:

[Action 1] Launch Excel
    File path: C:\Automation\InvoiceQueue.xlsx
    → Stores instance in: ExcelInstance

[Action 2] Read from Excel worksheet
    Excel instance: %ExcelInstance%
    Retrieve: All available values from worksheet
    First line contains column names: Yes
    → Stores in: InvoiceTable

[Action 3] Create new list → ApprovedInvoices
[Action 4] Create new list → FlaggedInvoices

[Action 5] Create new data table
    Columns: InvoiceID, VendorName, Amount, Currency, Status, ProcessedDate
    → Stores in: SummaryLog

Step 3: Loop and Process

[Action 6] For each CurrentRow in InvoiceTable:

    [Action 7] Set variable: VendorName  to %CurrentRow['VendorName']%
    [Action 8] Set variable: RawAmount   to %CurrentRow['RawAmount']%
    [Action 9] Set variable: InvoiceID   to %CurrentRow['InvoiceID']%
    [Action 10] Set variable: Currency   to %CurrentRow['Currency']%

    [Action 11] If VendorName is empty (VendorName = ""):
        Add item %InvoiceID% to list: FlaggedInvoices
        Add row to data table SummaryLog:
            Values: %InvoiceID%, "UNKNOWN", 0, %Currency%, "Flagged - Missing Vendor", %CurrentDateTime%
        Continue (skip to next iteration)
    End if

    [Action 12] Replace text in RawAmount: "$" → ""  → CleanAmount
    [Action 13] Replace text in CleanAmount: "," → "" → CleanAmount
    [Action 14] Convert text to number: CleanAmount → NumericAmount

    [Action 15] If NumericAmount > 10000:
        Add item %InvoiceID% to list: FlaggedInvoices
        Add row to data table SummaryLog:
            Values: %InvoiceID%, %VendorName%, %NumericAmount%, %Currency%, "Flagged - Exceeds Threshold", %CurrentDateTime%
    Else:
        Add item %InvoiceID% to list: ApprovedInvoices
        Add row to data table SummaryLog:
            Values: %InvoiceID%, %VendorName%, %NumericAmount%, %Currency%, "Approved", %CurrentDateTime%
    End if

End for each

Step 4: Write Results

[Action 16] Write to Excel worksheet
    Excel instance: %ExcelInstance%
    Value to write: %SummaryLog%
    Write mode: On specified cell
    Starting column: A
    Starting row: 1
    Sheet: SummaryLog (create this sheet first or write to an existing one)

[Action 17] Close Excel (save document)

[Action 18] Log message:
    "Processing complete. Approved: " + %ApprovedInvoices.Count% + 
    " | Flagged: " + %FlaggedInvoices.Count%

What You Should See

Run the flow. It reads your invoice data, processes each row, handles dirty currency formatting, catches missing vendor names, separates records by threshold, builds a summary table in memory, and writes the whole thing to Excel in one shot. This is a pattern you'll use — in various forms — in virtually every data-processing RPA flow you build.

---

Common Mistakes & Troubleshooting

"Cannot convert value to type Number"

Cause: You're passing Text to an action that expects a Number, or the text contains non-numeric characters (spaces, currency symbols, commas).

Fix: Always strip formatting characters before converting. Use Replace Text actions to clean the string, then use Convert Text to Number. Use a Try/Catch block around the conversion if your data quality is uncertain.

List Index Out of Range

Cause: Accessing %MyList[5]% when the list has 5 or fewer items (indices 0–4).

Fix: Always check %MyList.Count% > 0 before accessing by index. For the last item, use %MyList[MyList.Count - 1]% rather than a hardcoded index.

DataTable Columns Not Found

Cause: You're referencing a column name that doesn't exactly match the header in the source file. Column names are case-sensitive in PAD.

Fix: After reading from Excel, pause execution and inspect the DataTable in the Variables panel. Expand it and check the exact column names. A trailing space in an Excel header (e.g., "VendorName " instead of "VendorName") is a common culprit. You can use Trim on headers or alias columns as you read them.

Subflow Overwrites Main Flow Variable

Cause: A variable name in a subflow collides with one in the main flow.

Fix: Use a naming prefix convention for subflow-internal variables. Audit your variable names before calling subflows.

"For Each" Modifying a List While Iterating It

Cause: Trying to remove items from a list inside a For Each loop that's iterating over that same list. This causes unpredictable behavior.

Fix: Build a separate list of items to remove during the loop, then remove them after the loop ends. Or build a new filtered list instead of modifying the original.

DataTable Write to Excel Overwrites Headers

Cause: When you write a DataTable to Excel starting at row 1 with "First line contains column names: Yes" enabled on the Read side, PAD includes the column names as the first row in the write output. If you didn't account for that, you overwrite your existing header row or shift data down.

Fix: Either write starting at row 1 and let PAD handle it (the column names become the header row), or write starting at row 2 if you've manually set headers in the destination sheet. Be consistent and test with a small dataset first.

---

Summary & Next Steps

You've covered the complete data management toolkit for Power Automate Desktop:

• Variables are typed, globally scoped, and require explicit conversion when moving between types
• Lists are the right structure for single-column, ordered collections — ideal for accumulating values during loops, tracking IDs, or storing file paths
• DataTables are the right structure for multi-column records — ideal for Excel reads/writes, query results, and building output reports
• Type conversion is always explicit — strip formatting before numeric conversion, use ISO date formats for DateTime, and use column names (not indices) when accessing DataTable cells
• Batch writes are dramatically faster than cell-by-cell writes — build your DataTable in memory, write once at the end

The next natural places to go from here are the more advanced data manipulation patterns you'll need in production. If your flows will involve writing processed data back to Excel or OneDrive, Automating Excel and OneDrive File Processing in Power Automate covers the cloud-side patterns that complement what you've built here. And if you're running these flows on a schedule against large datasets, understanding handling pagination and throttling for large datasets will become important when your data volumes grow.

For flows that need to be production-grade — with proper error handling when a conversion fails or a file doesn't exist — the techniques in Master Error Handling and Retry Patterns in Power Automate will take your data pipeline from "works on my machine" to genuinely reliable unattended automation. The variable and DataTable skills you've built here are the foundation every more advanced RPA technique is built on — everything that follows is just applying them in smarter combinations.