How to Get Values from Data Table using Power Automate Desktop?

A client has recently contacted us regarding their requirement for a data table in Power Automate Desktop, as they have a data table with invoice processing details. From the data table, they wanted to get the Invoice items whose status value is ‘Pending‘.

However, unfortunately, there is no dynamic content available to retrieve rows from a data table in Power Automate Desktop.

In this tutorial, I will show you how to retrieve values from a data table using Power Automate desktop flow. You’ll learn four different ways to do it:

• Get Data Table Value by using Row Number and Column Number
• Retrieve Data Table Value using Row Number & Column Name
• How to Get Power Automate Data Table Value using Loop
• Get Data Table Values based on Filter Condition in PAD

Get Data Table Value in Power Automate Desktop using Row & Column Index

A data table is similar to an Excel worksheet, storing different types of data in a tabular format, in rows and columns. It is a zero-index variable type, which means that to retrieve values from rows or columns, we need to refer to the indexes starting from 0.

Suppose you have a data table in PAD with invoice processing details, such as Invoice Number, Vendor Name, Amount, Date, Status, etc, with the columns as shown in the screenshot below.

Requirement:

Here, I want to retrieve the values of ‘Status‘ and ‘Payment Method‘ in the first row of the Invoice processing data table, which is located above.

Note:

Keep in mind that, you can retrieve values from the data table item based on the row index and column index, which start from 0.

Follow the steps below to retrieve values from a data table using PAD:

1. On the Power Automate Desktop, click on +New -> select Flow to create a new flow. Then, add the ‘Create new data table’ action from the Actions pane into the workspace. Create a data table according to your requirements. Then click on Save.

From the image below, you can see that the row index starts from ‘0’. To retrieve the first row, you need to specify the row index as ‘0’.

2. From the Actions pane, drag and drop the ‘Set variable‘ action to the workspace. Here, I have renamed the Variable produced.
   • Value: Click on {x} -> Add data table variable by specifying the row and column number as provided below.

Then, click on the Save button to save the flow.

Status Value - %DataTable[0][6]%
Payment Method - %DataTable[0][4]%

Now, save and run the flow.

3. When the flow executes successfully, open the flow variables present on the right side of the workspace and select ‘Set Variable‘.

You can see that the values have been retrieved from the data table and stored in the set variable.

This is how to retrieve values from a data table based on row and column indexes in Power Automate Desktop.

Check out Count Rows & Columns in SpreadSheet by Power Automate Desktop

Retrieve Data Table Value in Power Automate Desktop using Row Index & Column Name

Now, let me show you how to retrieve values from the data table using a row index and column name in Power Automate Desktop.

Suppose we have a data table like the one below with the following values.

Here, I wanted to retrieve the values of Invoice -003, such as Vendor Name, Amount, Payment Method, and Status, from the above data table.

To retrieve this, you can also take the specific column name instead of the column index to access the particular columns in the data table.

1. Following the Create new data table action, add the ‘Set variable‘ action. Provide the parameters mentioned below. Then, click on the Save button to save the action.
   • I have renamed the variable to INV-003 Values.
   • Value: Provide the values given below.

Vendor Name - %DataTable[2]['Vendor Name']%
Amount - %DataTable[2]['Amount']%
Payment Method - %DataTable[2]['Payment Method']%
Status -  %DataTable[2]['Status']%

In the code above, %DataTable[2][‘Vendor Name’]% determines:

• %DataTable% – data table variable
• [2] refers – Row Index
• [‘Vendor Name’] – Column Name

Then, click on the Save icon and execute the flow. After that, open the flow variable to check the output retrieved.

Output:

This is another method for retrieving data table values using a row index and column name from Power Automate Desktop.

Read Send Outlook Email with Attachments using Power Automate Desktop

Get Data Table Value using Loop in Power Automate Desktop

In this section, I will show you how to retrieve data table values using a Loop in Power Automate Desktop.

Suppose, let’s assume you have a Data table like below with the following values, and you wanted to retrieve the ‘Vendor Name‘ column value from the first row item.

Note:

The data table item values can be retrieved based on the row index and column index, which start from 0.

However, we cannot directly retrieve the row index of a data table from any dynamic content. So, use a Set variable action to set the value to 0 and then increase it to 1. This will give the row index.

You can follow the steps below:

1. Below, to create a new data table action, add the “Set variable” action, which represents the row index of a data table. Click on the Save button to save the action.
   • I have renamed the Variable name to RowIndex.
   • Value: Enter the value to 0.

2. To get all the items present in a data table, drag and drop a “For each” loop into the workspace. This iterates over the list of items in a data table, allowing the execution of a block of actions inside the loop to be repeated.
   • Value to iterate: Click on {x} -> Select DataTable variable.
   • Store into: It produces CurrentItem variable.

Click on Save.

3. Inside the loop, add an “If condition” action that will check “Row Index is equal to 0”. This retrieves the item value if the specified condition in the statement is met.
   • First operand: Select the CurrentItem variable from the For Each loop. Then, within brackets, enter the column index.
   • Operator: Select the operator based on your requirement.
   • Second operand: Enter the value. In my case, I was taking 0.

Save the action to the workspace by clicking on the Save button.

4. Inside the if condition, add the “Set Variable” action in the workspace. Set the parameters below.
   • I have renamed the variable produced.
   • Value: Click on {x} -> %CurrentItem[‘Vendor Name’]%

5. Below the if condition, add the ‘Increase variable‘ action to increase the Index. This will increment the row index each time the for-each loop runs. Set the parameters and click on Save.
   • Variable name: Select Row Index variable.
   • Increase by: Enter the value by 1.

6. Now save the flow. You can see the complete flow in the screenshot below.

7. When the flow executes successfully, the ‘Vendor Name‘ variable is present under Flow variables. The image displays the specific column value [Vendor Name] from the first row of the data table.

Check out Export and Import Power Automate Desktop Flows

Retrieve Data Table Values based on Filter Condition in Power Automate Desktop

Here, let me show you how to get the values from the data table based on the filter condition in PAD.

To illustrate this, I will take the same data table with the ‘Invoice Processing Details’ used in the above examples. The highlighted items in the image below have a status of ‘Pending‘.

From the above-mentioned data table, I would like to retrieve the Item values whose ‘Status‘ is ‘Pending‘.

Follow the steps below:

1. After the ‘Create new data table’ action, drag and drop the ‘Set variable‘ action to configure the row index of the data table. Then, click on the Save button.
   • I have renamed the variable produced to ‘Row Index‘.
   • Value: Enter the value as ‘0’, as the row index begins with 0.

2. Next to that, add a ‘For each‘ loop into the workspace that iterates over all the list items in a data table.
   • Value to iterate: Click on {x} -> Select DataTable variable.

This action will produce ‘CurrentItem‘ variable. Tap on the Save button.

3. Inside, for each loop, add an ‘If‘ condition action that will filter the items with ‘Status = Pending‘. Set the parameters below:
   • First operand: Select the CurrentItem variable from the For Each loop. Then, within brackets, enter the column index of the Status column.
   • Operator: Select the operator based on your requirement.
   • Second operand: Enter the value. In my case, I was taking ‘Pending‘.

Save the action to the workspace by clicking on the Save button.

4. Inside the if condition, add ‘Set Variable‘ action to set the values of retrieved items from the data table.
   • I have renamed the variable to ‘StatusPending‘.
   • Value: Insert for each loop variable with the column names that you want to display.

Click on the Save button.

%CurrentItem['Vendor Name']% - %CurrentItem['Payment Method']% - %CurrentItem['Status']%

5. Below the if condition, inside the For each loop, add ‘Increase variable‘ action to increase the row index each time the loop runs.
   • Variable name: Select Row Index variable.
   • Increase by: Enter the value by 1.

6. After that, drag and drop ‘Append line to text‘ variable action that appends remaining filtered item values to the set variable. Then, click the Save button.
   • Original text: Select the set variable from the previous step.
   • Line to append: Provide the current item variable with column names.

%CurrentItem['Vendor Name']% - %CurrentItem['Payment Method']% - %CurrentItem['Status']%

The flow is ready now. See the complete flow in the image below. Save and run the flow manually.

After flow execution, open ‘InvoicePendingValues‘ variable output. You can see that the data table item values have been retrieved based on the filter condition.

This way, you can retrieve the item values from the data table based on a filter condition using Power Automate Desktop.

From this tutorial, you have learnt four different ways to retrieve values from data table items in Power Automate Desktop. I hope you found this article helpful. If you have any comments or feedback, please share them in the comments section.

You may like the following Power Automate Desktop tutorials

• Concatenate Strings Using Power Automate Desktop
• Add Excel Table into HTML Email Body using Power Automate Desktop
• Update Data Table Items using Power Automate Desktop
• Power Automate Desktop Text Actions

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.