Data table(Power Automate Desktop)

07/29/2024

Japanese version.

This section introduces data tables (two-dimensional arrays) that handle data in tabular form.

Power Automate Desktop's Data Table is a feature for storing data in table format. Data tables allow you to easily work with data in table format, consisting of multiple rows and columns.

For example, data from an Excel file or database can be read and stored in a Power Automate Desktop data table.

Data tables are used in conjunction with various Power Automate Desktop functions such as actions and conditional branching. For example, only rows that meet certain conditions can be extracted and passed to another action.

Contents

Tool for Generate

Generate a list/data table with the structure as entered in Excel.

Link

Create

Create new data table action.

Under Variables, select Data Table and then Create new data table.

Click Edit to create the data table.

Sets initial values for data tables.

Double-click on a column name to edit the column name.

Click the "+" to the right to add columns, and the "+" at the bottom to add rows.

Double-clicking a cell sets the value of the item.

Clicking on {x} allows you to specify variables.

You can delete or clear values by right-clicking on the number of rows on the left.

Create data table with 0 rows.

It can be created from Excel worksheet or CSV using the following Actions.

Read from file.

Set variable action.

Depending on the table to be created, it may be more concise and less time-consuming to do it this way.

In some cases, the old flow did not have a create action, so this method of creation is still used.

If you want to make a column name a variable, you must do it this way.

Drag Set Variables from the Variables action to the workspace.

Specify the variable name in Variable and the Data table in Value.

Surround the whole with % meaning formula.

Surround the data table with {}.

Rows are enclosed in [] and separated by commas.

Columns are separated by commas.

Text is enclosed in '.

For example, the following table is specified as %{[1, 'A'], [2, 'B'], [3, 'C']}%.

1A
2B
3C

Column names are specified with ^.

Specifying only columns results in an empty (0 rows) data table.

%{ ^['No', 'Name'], [1, 'A'], [2, 'B'], [3, 'C'] }%

Variables can also be specified for column names.

Operations on already existing data table.

Get item.

Specify %data table[row number][column number]%.

To get the first column of the first row from the data table variable dt, use %dt[0][0]%.

If column names are available, they can also be specified by column name.

Update item.

Use the “Update data table item” action.

Parameters

Data table

Specify the target data table.

Column / Row

Specify the target column/row by a sequential number starting from 0.

If 0 is specified for both, the first row and column are targeted.

New value

Specifies the value after updating.

Search and Replace Items.

Drag Variables, then Find or Replace in data table from Data table onto the workspace.

Set parameters.

ParameterValue
Data tableTarget data table.
Search modeSelect Find or Find and replace.
All matchesOn: All search results
Off: Only the first search result.
Text to findText to look for.
In the case of a Find and replace, the text before the change.
Find using a regular exepressionWhen turned on, searches are performed using regular expressions.
Regular expressions allow for searches such as numeric only or forward matching.
Match caseTurn on to make alphabetic characters case-sensitive.
Cannot be specified when using regular expressions.
Match entire cell contentsIf on, partial match is used; if off, full match is used.
Cannot be specified when using regular expressions.
Text to replace withSpecifies the text after the change.
Specify only when the search mode is Find and replace.
Search byEverywhere: All columns are subject to search/replace.
On column: Specify columns to search/replace by index or column name
Column index or nameSpecified only when the search condition is a column. Specifies the search/replace target.

Add item.

Insert row into data table action

Drag the Insert Row into data Table action onto the workspace.

Set parameters.

ParameterValue
Data tableTarget data table.
Into locationBefore row index: Add before the specified line.
End of data table: Next to last line.
New value(s)Specify the line to be added. Example: %[4, 'D']%

Set variable action.

This method only allows additions at the end (not at the beginning).

We do not recommend using this method, but it was implemented prior to the Insert row into data table action. Therefore, it may remain in past flows.

ParameterValue
VariableTarget data table.
Value%Target data table + Row to add%

Delete row.

Drag Delete row from data table onto the workspace.

Set parameters.

ParameterValue
Data tableTarget data table.
Row indexSpecifies the number of rows to be deleted. The first line is 0.

Delete column.(Conventional Method.)

Drag the Launch Excel Action to the worksheet and select with blank document.

Drag the Write to Excel worksheet action to the workspace and write the contents of the data table in cell A1.

Drag Delete column from Excel worksheet Action onto the workspace to specify the columns to be deleted.

Drag Read from Excel worksheet Action onto the workspace and Select All available values from worksheet.

The data table from which the columns were deleted is set to ExcelData in the Variables produced.

Delete column.(Use Action)

Use the “Delete column from data table” action added in the July 2024 update.

Data table

Specify the target data table.

Specify column with

Select whether to specify the column to be deleted by “Name” or “Index”.

Column Name

Specifies the name of the column to be deleted.

Specify this parameter if you selected “Name” for “Specify column with".

An error will occur if you specify a column name that does not exist.

Column Index

If “Specify column with” is “Index”, specify the position of the column to be deleted as a number.

If 0 is specified, the column is deleted at the beginning.

If a number equal to the number of columns in the “Data Table” is specified, the column at the end is deleted.

If a negative number or a number exceeding the number of columns in the “Data table” is specified, an error occurs.

Get number of rows.

Get the number of rows with RowsCount.

%Data table.RowsCount%

Get column names. Columns and ColumnHeadersRow.

Columns obtains a list of column names in List format.

%Data table.Columns%

ColumnHeadersRow to get a list of column names in the form of data rows.

%Data table.ColumnHeadersRow%

Columns and ColumnHeadersRow behave similarly.

When looping with For Each, both loop over the number of columns and can be added as data rows to another data table.

The behavior is different when used to Write to Excel worksheet with Columns, the data is written vertically (columns), whereas with ColumnHeadersRow, the data is written horizontally (rows).

Get number of columns.

Get the number of columns with Columns.Count.

%Data table.Columns.Count%

Check if empty

Check the data table with IsEmpty.

%Data table.IsEmpty%

Retrieve data table column into list.

Drag Retrieve data table column into list action from Variables onto the workspace.

Specify the data table variable in Data Table and the column to retrieve in Column Name or Index.

The beginning of the column is 0.

If you do not change the name of the variables produced, the acquisition column will be set to ColumnAsList.

Loop (For each)

Drag For each onto the workspace and
Specify a data table variable in the Value to iterate field.

If you do not change the name of the Store into, CurrentItem will contain the row of data.
Specifying %CurrentItem[column number]% will retrieve the value of that row.

Add Column

Use the “Insert column into data table” action added in the July 2024 update.

Data table

Specify the target data table.

Into location

Select where to insert the new column.

SelectLocation
End of data tableLast tail.
Before column indexSpecify the position with the additional parameter “Column index”.

Column name

Specify a column name for the new column.

Specifying a column name that already exists will result in an error.

Column index

When “Before column index” is selected for “Into location”, specify the position of the additional column as a number.

If 0 is specified, the column will be added at the beginning.

If you specify the same number as the number of columns in the “Data table”, the column will be added at the end. It is the same as selecting “End of data table” for “Into location”.

If a negative number or a number that exceeds the number of columns in the “Data table” is specified, an error will occur.

Delete rows where all columns are empty

Use the “Delete empty rows from data table” action added in the July 2024 update.

This action automatically detects and deletes empty rows in the data table.

Empty Definition

  • Only completely empty cells are recognized as “empty”.
  • Cells containing single-byte spaces are not considered empty and such rows will not be deleted.

Deletion Scope

  • If there are multiple empty rows, all of them will be deleted.
  • If all rows in a data table are empty, the result is a data table with zero rows.

If you want to consider half-width spaces as empty as well, use “ Trim text” or similar to eliminate them first.

Delete duplicate rows

Use the “Delete duplicate rows from data table” action added in the July 2024 update.
This action automatically detects and removes duplicate rows in the data table.
Rows that have the same values in all columns are considered duplicates. Even if there are three or more identical rows, the result will be one row.

Delete all rows

Use the “Clear data table” action added in the July 2024 update.

This action deletes all rows and converts them to a data table with zero rows.

Only rows are deleted, column definitions remain.

Row Reordering

Use the “Sort data table” action added in the July 2024 update.

Data table

Specify the target data table.

Specify column with

Select whether to specify the columns on which to base the sorting, by “Name” or “Index”.

Column name

Specifies the name of the column on which to base the sorting.

Specify this parameter if you selected “Name” for “Specify column with“.

An error will occur if you specify a column name that does not exist.

Column index

If “Specify column with” is “Index”, specify the position of the column on which to base the sorting by a numerical value.
If 0 is specified, it is the first column.
If a number equal to the number of columns in the “Data table” is specified, it is the last column.
If a negative number or a number exceeding the number of columns in the “Data table” is specified, an error occurs.

Order

Select whether to sort the criteria columns in ascending or descending order.

Row Filter

Use the “Filter data table” action added in the July 2024 update.

Data table

Specify the target data table.

Filters to apply

Click “Edit” to set filtering criteria.

Rule

Click the + in the lower left corner to select whether the relationship is “Or” or “And” when the condition is increased.

If both “And” and “Or” exist, “And” takes precedence.

Column or Index

Specify the target column of the condition by one of the following:

  • Sequential number starting from 0
  • Column name
Operator

Selects the logical operator of the condition:

  • equal to (=)
  • Not equal to (<>)
  • Greater than (>)
  • Greater than or equal to (>=)
  • Less than (<)
  • Less than or equal to (<=)
  • Contains
  • Does not contain
  • Is empty
  • Not empty
  • Start with
  • Does not start with
  • End with
  • Does not end with
Value

Specifies the value of the condition.

Filter Result

The data table in the generated variable (FilteredDataTable) is the filter result.

Merge

Use the “Merge data table” action added in the July 2024 update.

This action adds a row from the second data table to the end of the first data table.

First data table / Second data table

Adds a second theta table to the end of the first data table.

No new variables are created. The contents of the first data table are changed.

Merge Mode

Select the rule for when the two data tables have different column configurations.

Add extra columns

Leave both columns. Here is an example of the execution result. The red in the header is the column that exists in common.

Column1Column2
1A
2B
3C
First data table (before action)
Column1Column3
4d
Second data table
Column1Column2Column3
1A
2B
3C
4d
First data table (after action)
Ignore extra columns

Ignore columns that do not exist in the first data table. Header deficits are commonly present columns.

Column1Column2
1A
2B
3C
First data table (before action)
Column1Column3
4d
Second data table
Column1Column2
1A
2B
3C
4
First data table (after action)
Error on extra columns

Exit with an error if the data table has a different column structure.

Join

Use the “Join data tables” action added in the July 2024 update.
This action joins two data tables horizontally.
For example, if you have a list of products (master data) and a list of sales and purchases (transaction data), the main use of this action is to merge their information.

Set up the join rule

Add the information from the second data table to the right of the first data table.

In most cases, specify the following.

First data tableList of sales and purchases (transaction data)
Second data tableList of products (master data)

The result of the join is stored in %JoinedDataTable% in “Variables produced”.

Join the following data tables as columns.

The red markers in the headers are columns that exist in common. Red markers in the data are data that exist in common.

CodeNamePrice
1A400
2B500
3C600
First data table %Transaction%
CodeQty
110
35
47
Second data table %Product%

Click on Edit under “Join rules” to display the settings window.

Specify the column information common to the two data tables in the “Column Name or Index” fields on the left and right.
In this example, we specify “Code” for the product code. The column name can be different.
When specifying by index, use a sequential number with 0 at the beginning.

Example of specifying by Index.
Example of specifying by Column Name.

For “Conditional,” choose “Equal to (=).” The other options are less frequently used and more esoteric (in many cases it is safer to adopt a method other than join).

The “Join operation” is selected from the following

  • Inner
  • Left
  • Full
Join operation: Inner

Joins the rows specified in the join rule, leaving only the data common to the columns specified in the join rule.

In this example, rows with “Code” of 1 and 3 are joined, while other rows are excluded.

Ideally, the information should be organized so that there are no problems "Inner".

Results of Inner %JoinedDataTable%
Join operation: Left

Retains all information in the “First data table”.

This is used when there may be missing information in the list of products (master data), but you still want to keep the rows of transactions.

Results of Left %JoinedDataTable%
Join operation: Full

I leave all the information in both data tables.
This is used when there may be missing information in both and you want to keep it.

Results of Full %JoinedDataTable%

Convert text to Data table

Use the “Read from CSV Text variable” action added in the July 2024 update.

This action converts CSV (comma separated text), TSV (tab separated text), and fixed length text into data tables.

The conversion result is stored in the Variables produced %CSVTable%.

CSV Text

Specifies the text to be converted from.

Trim fields

Turn ON to remove white space before and after data.

Most often used for fixed length text.

Removes not only half-width spaces, but also full-width spaces and tab characters.

First line contains column names

Turn ON if the first row is not data.

If ON, the first row is the column name.

Get CSV fields as text

When OFF, columns that look like numbers or dates are automatically detected and captured as numbers or dates.

When ON, all columns are treated as text without auto-detection.

In most cases, leave OFF.

Column Separator

Select the delimiter.

Predefined

In most cases, select “Column Separator” from “Predefined”.

  • System default(often a comma)
  • Comma
  • Semicolon
  • Tab
Custom

Specify if the delimiter does not exist in the “Predefined” list.
Although infrequent, | (pipe delimiter) and others are applicable.

Fixed Column Widths

Select this option for fixed length text with no delimiter.
Specify the number of column-width characters separated by any of comma/space/tab/newline.

Convert Data table to text

The “Convert data table to text” action was added in the July 2024 update.
This action converts a data table to CSV (comma separated text) or TSV (tab separated text).

The conversion result is stored in the Variables produced %CSVText%.

Data table

Specify the target data table.

Include column names

If ON, a header row is generated on the first line.

Use custom columns separator

If OFF, select from the default delimiters.

If ON, enter a delimiter.

In most cases, turn it OFF.

Then select “ Separator”.

  • System's default value (often a comma)
  • Comma
  • Semicolon
  • Tab

Select ON if it does not exist in “ Separator”.

Although it is used infrequently, it is applicable to the | (pipe delimiter) and so on.

For those who want to learn Power Automate Desktop effectively


The information on this site is now available in an easy-to-read e-book format.

Or Kindle Unlimited (unlimited reading).

You willl discover how to about basic operations.

By the end of this book, you will be equipped with the knowledge you need to use Power Automate Desktop to streamline your workflow.

Table of Contents.

Update History.

Links

Power Automate Desktop Introduction.

Examples of Use

How to convert URL parameters (GET parameters) into Data table.

How to rank a list of numbers.

How to get the deviation.