Power Automate Desktop: Data Table Explained

PA

Power Automate Desktop - Data Table

Concise reference - what it is, where to use, when to use, limitations

1. What it is

A Data Table is an in-memory tabular structure with named columns and rows, implemented on the .NET DataTable model. Use it to hold structured records inside a flow for manipulation, iteration, and transformation before exporting or persisting.

2. Where you can use it

• Read Excel or CSV data and process rows without opening Excel UI
• Aggregate results from web scraping or API responses into a structured form
• Prepare datasets for export to SharePoint, databases, or files
• Combine multiple sources - append, merge, or join data before output
• Drive decision logic by iterating rows and applying conditional operations

3. When to use it

1. When automation must process multiple records in memory during runtime
2. When you need temporary structured storage between extraction and output steps
3. When you will loop, filter, sort, or transform tabular data programmatically
4. When preparing batched writes to Excel, databases, or downstream systems

4. Practical patterns

• Create table with columns then Add Data Row inside a loop to build results
• Use Get Row or For Each Row to iterate and apply business rules or transformations
• Export Data Table to CSV or Excel at end of flow for persistence or reporting
• Use Data Table as an intermediate cache when joining data from multiple sources

5. Limitations

• All contents reside in memory - large tables increase RAM use and slow execution
• No built-in query language - filtering requires loops, conditions, or expression workarounds
• Columns are loosely typed - manual coercion may be required to avoid runtime type issues
• Not persistent - Data Table state is lost when the flow ends unless exported
• Complex nested objects are not supported directly - flatten or serialize before storing

6. Quick best practices

• Limit rows kept in memory - stream to files or databases for very large datasets
• Define column names explicitly to avoid runtime key errors
• Validate and coerce data types as you add rows to prevent downstream failures
• Use descriptive variable names for Data Tables to clarify purpose in complex flows

Power Automate Desktop Data Table Operations

 
Variables.CreateNewDatatable InputTable: { ^['First Name', 'Middle Name', 'Last Name', 'Address'], [$'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteEmptyRowsFromDataTable DataTable: DataTable
/# It will create empty row to remove it you can add remove empty row or copy that code in notepad
it will look like below
Variables.CreateNewDatatable InputTable: { ^['First Name', 'Middle Name', 'Last Name', 'Address'] } DataTable=> DataTable
============
from here remove that square bracket
Variables.CreateNewDatatable InputTable: { ^['First Name', 'Middle Name', 'Last Name', 'Address'] } DataTable=> DataTable
and paste this in designer
#/
DISABLE Variables.CreateNewDatatable InputTable: { ^['First Name', 'Middle Name', 'Last Name', 'Address'] } DataTable=> DataTable
# insert record
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Sun', 'S', 'Shetty', 'Test Address']
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Test', '', 'Singh', 'Test Address1']
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Test2', 'M', 'Ravat', 'Test Address2']
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Test2', 'M', 'Ravat', 'Test Address2']
# update data table item
Variables.ModifyDataTableItem DataTable: DataTable ColumnNameOrIndex: $'''Address''' RowIndex: 0 Value: $'''updated address'''
# Delete duplicate rows
Variables.DeleteDuplicateRowsFromDataTable DataTable: DataTable
# find rows based on search value
Variables.FindOrReplaceInDataTable.FindItemInDataTableEverywhere DataTable: DataTable AllMatches: True ValueToFind: $'''Test''' MatchCase: False MatchEntireCellContents: False DataTableMatches=> DataTableMatches
# find and replace values
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableEverywhere DataTable: DataTable AllMatches: True ValueToFind: $'''Test''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''Replaced''' DataTableMatches=> DataTableReplaced
# sort based on column
Variables.SortDataTable.SortWithColumnName DataTable: DataTable ColumnName: $'''First Name''' Order: Variables.SortDirection.Ascending
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Test', '', 'Singh', 'Test Address1']
# delete row based on row index
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 3
/# add new column at the end of datatable
and delete it using delete operation#/
Variables.AddColumnToDataTable.AppendColumnToDataTable DataTable: DataTable ColumnName: $'''Test Column'''
Variables.DeleteColumnFromDataTable.DeleteColumnWithName DataTable: DataTable ColumnName: $'''Test Column'''
# filter datatable
Variables.FilterDataTable DataTable: DataTable FilterParameters: { ^['RULE', 'COLUMN', 'OPERATOR', 'VALUE'], [$'''''', $'''First Name''', $'''Contains''', $'''Sun'''] } MatchCase: True FilteredDataTable=> FilteredDataTable
/# merge two datatable
merge mode you can select based on your requirement#/
Variables.MergeDataTables FirstDataTable: DataTable SecondDataTable: FilteredDataTable MergeMode: Variables.MergeMode.AddExtraColumns
# create new data table
Variables.CreateNewDatatable InputTable: { ^['First Name', 'Middle Name', 'Last Name', 'Address', 'PhoneNo', 'PinCode'] } DataTable=> DataTable2
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable2 RowToAdd: ['akash', '', '', 'test', 9878987656, 768976]
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable2 RowToAdd: ['Sun', '', '', 'test', 9878982345, 345976]
# join table
Variables.JoinDataTable FirstDataTable: DataTable2 SecondDataTable: DataTable JoinOperation: Variables.JoinOperationType.Inner JoinParameters: { ^['COLUMN 1', 'OPERATOR', 'COLUMN 2'], [$'''First Name''', $'''Equal''', $'''First Name'''] } JoinedDataTable=> JoinedDataTable
# convert datatable to text
Variables.ConvertDataTableToCSV.DataTableToCSVCustomSeparator DataTable: DataTable IncludeColumnNames: True CustomColumnsSeparator: $''',''' CSVText=> CSVText
# convert text to data table
Variables.GenerateDataTableFromCSV.ReadPredefinedSeparator CSVText: CSVText TrimFields: True FirstLineContainsColumnNames: True ReadFieldsAsText: False ColumnsSeparator: Variables.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable

Copy and paste the above code into your Power Automate Desktop designer to test all variable operations.