Data Tables in Power Automate Desktop

KT Sessions November 15, 2024

 Data Tables in Power Automate Desktop

When automating processes in Power Automate Desktop (PAD), you often need to handle structured data in the form of tables. Whether it's data coming from an Excel file, a CSV, or even from a web scraping process, Data Tables are a powerful feature in PAD that lets you manage and manipulate rows and columns of data with ease.



What is a Data Table in Power Automate Desktop?

A Data Table is a structured collection of data, similar to a table in a spreadsheet or a database. It is made up of rows and columns, where each row represents a record, and each column represents a field or property of that record.

In Power Automate Desktop, Data Tables are used to:

  • Store multiple rows of data.
  • Manipulate data (e.g., sort, filter, modify, delete).
  • Loop through rows to process each record individually.

You can think of a Data Table as a temporary "spreadsheet" in your flow, which you can use to hold data that you need to work with.

Common Use Cases for Data Tables

Some common scenarios where you’ll use Data Tables in PAD include:

  • Processing Excel Data: When reading data from an Excel sheet, it’s often stored in a Data Table.
  • Web Scraping: Extracted data from web pages might be saved as a Data Table for further manipulation.
  • CSV Files: If you're working with CSV files, you can import them as Data Tables for easier handling.
  • Databases: Data pulled from databases can be stored in a Data Table for further processing.

Key Actions to Work with Data Tables in PAD

Here are some key actions in Power Automate Desktop for working with Data Tables:

  1. Create Data Table: To create an empty Data Table.
  2. Add Row to Data Table: Add a new row of data to an existing Data Table.
  3. Get Column from Data Table: Extract a specific column from a Data Table.
  4. Set Column Value: Update the value of a specific cell in a Data Table.
  5. Remove Row from Data Table: Delete a row from the Data Table.
  6. Sort Data Table: Sort rows in a Data Table based on the values in one or more columns.
  7. Filter Data Table: Extract rows based on certain conditions.

Example: Using Data Tables to Process an Excel Sheet

Let's create a practical example where we read data from an Excel file, store it in a Data Table, and process it row by row. We’ll simulate a scenario where we have an Excel file containing a list of employees, and we need to filter out employees from a specific department and update their status.

Step 1: Create and Read the Data Table from Excel

  1. Launch Excel: Use the Launch Excel action to open Excel. Make sure the “Visible” property is set to True so you can see the process.

  2. Read Data into a Data Table: Use the Read from Excel action to read the contents of a sheet into a Data Table. Choose the range or entire sheet you want to read.

Example:

plaintext
Read data from range A1:F10 into Data Table “EmployeeData”

Here, the EmployeeData Data Table will store the Excel rows (e.g., Employee Name, Department, Status, etc.).

Step 2: Loop Through the Data Table

Once the data is loaded into the Data Table, we need to loop through each row to process the data.

  1. For Each Row in Data Table: Use the For Each loop to iterate through each row of the Data Table. Each iteration gives you access to the columns in the current row.

  2. Check Condition: Inside the loop, you can check if an employee belongs to a specific department, say “HR”.

Example:

plaintext
For Each Row in “EmployeeData”:
    If “Department” = "HR":
        Update Status to "Active"

Here, we're checking the "Department" column and updating the "Status" column for all HR employees to "Active".

Step 3: Set the Column Value

Use the Set Data Table Column Value action to update the value of a specific column for the current row.

plaintext
Set Column Value “Status” = "Active" for current row

This updates the status for each HR employee.

Step 4: Save the Updated Data to Excel (Optional)

Once the data is updated, you might want to write it back to an Excel file.

  1. Write Data Table to Excel: Use the Write to Excel action to save the updated Data Table to a new or existing Excel file.
plaintext
Write “EmployeeData” to Excel file “UpdatedEmployeeList.xlsx”

This writes the processed data back to an Excel sheet, saving the updates you made.

Common Operations with Data Tables

Let’s dive a little deeper into some common operations you can perform with Data Tables.

1. Filtering Data Tables

Sometimes you may want to extract only specific rows based on a condition. Power Automate Desktop provides the Filter Data Table action for this.

Example: Let’s say you only want to keep employees in the “IT” department.

plaintext
Filter “EmployeeData” where Department = "IT"

This filters the original Data Table to only include employees from the IT department.

2. Sorting Data Tables

You can sort the rows in a Data Table based on a specific column, such as sorting employees by name or age.

Example: Sorting by the "Name" column alphabetically:

plaintext
Sort “EmployeeData” by column “Name” in ascending order

This sorts the rows in the Data Table alphabetically by employee name.

3. Removing Rows

If you want to delete specific rows from a Data Table (for example, remove employees who have left the company), you can use the Remove Row from Data Table action.

Example: Remove all employees whose status is "Inactive":

plaintext
Remove rows where "Status" = "Inactive"

4. Getting a Column from a Data Table

To work with a specific column in a Data Table (e.g., extracting a list of all employee names), you can use the Get Column from Data Table action.

Example: Extract all names from the "EmployeeData" Data Table:

plaintext
Get column “Name” from “EmployeeData”

This gives you a list of all employee names in the Data Table.

Conclusion

Data Tables in Power Automate Desktop are a great way to handle structured data, whether it’s coming from an Excel file, a CSV, or even scraped from a website. By using the various actions available in PAD, you can easily create, filter, sort, and manipulate data tables as part of your automation flows.

Whether you’re processing employee records, analyzing customer data, or managing inventory, Data Tables help you efficiently manage and process large sets of structured data.

Now that you’ve learned how to work with Data Tables in PAD, try using them in your next automation project and see how they can simplify your workflows!

KT Sessions

Posted by KT Sessions