Data table(Power Automate Desktop)
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.
- 1. Tool for Generate
- 2. Create
- 3. Operations on already existing data table.
- 3.1. Get item.
- 3.2. Update item.
- 3.3. Search and Replace Items.
- 3.4. Add item.
- 3.5. Delete row.
- 3.6. Delete column.(Conventional Method.)
- 3.7. Delete column.(Use Action)
- 3.8. Get number of rows.
- 3.9. Get column names. Columns and ColumnHeadersRow.
- 3.10. Get number of columns.
- 3.11. Check if empty
- 3.12. Retrieve data table column into list.
- 3.13. Loop (For each)
- 3.14. Add Column
- 3.15. Delete rows where all columns are empty
- 3.16. Delete duplicate rows
- 3.17. Delete all rows
- 3.18. Row Reordering
- 3.19. Row Filter
- 3.20. Merge
- 3.21. Join
- 3.22. Convert text to Data table
- 3.23. Convert Data table to text
- 4. For those who want to learn Power Automate Desktop effectively
- 5. Links
Tool for Generate
Generate a list/data table with the structure as entered in Excel.
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']}%.
| 1 | A |
| 2 | B |
| 3 | C |
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.
| Parameter | Value |
|---|---|
| Data table | Target data table. |
| Search mode | Select Find or Find and replace. |
| All matches | On: All search results Off: Only the first search result. |
| Text to find | Text to look for. In the case of a Find and replace, the text before the change. |
| Find using a regular exepression | When turned on, searches are performed using regular expressions. Regular expressions allow for searches such as numeric only or forward matching. |
| Match case | Turn on to make alphabetic characters case-sensitive. Cannot be specified when using regular expressions. |
| Match entire cell contents | If on, partial match is used; if off, full match is used. Cannot be specified when using regular expressions. |
| Text to replace with | Specifies the text after the change. Specify only when the search mode is Find and replace. |
| Search by | Everywhere: All columns are subject to search/replace. On column: Specify columns to search/replace by index or column name |
| Column index or name | Specified 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.
| Parameter | Value |
|---|---|
| Data table | Target data table. |
| Into location | Before 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.
| Parameter | Value |
|---|---|
| Variable | Target data table. |
| Value | %Target data table + Row to add% |
Delete row.
Drag Delete row from data table onto the workspace.
Set parameters.
| Parameter | Value |
|---|---|
| Data table | Target data table. |
| Row index | Specifies 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 ｗorksheet 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.
| Select | Location |
|---|---|
| End of data table | Last tail. |
| Before column index | Specify 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.
| Column1 | Column2 |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| Column1 | Column3 |
|---|---|
| 4 | d |
| Column1 | Column2 | Column3 |
|---|---|---|
| 1 | A | |
| 2 | B | |
| 3 | C | |
| 4 | d |
Ignore extra columns
Ignore columns that do not exist in the first data table. Header deficits are commonly present columns.
| Column1 | Column2 |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| Column1 | Column3 |
|---|---|
| 4 | d |
| Column1 | Column2 |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 |
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 table | List of sales and purchases (transaction data) |
| Second data table | List 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.
| Code | Name | Price |
|---|---|---|
| 1 | A | 400 |
| 2 | B | 500 |
| 3 | C | 600 |
| Code | Qty |
|---|---|
| 1 | 10 |
| 3 | 5 |
| 4 | 7 |
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.
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".
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.
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.
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.