Power Automate Desktop loop through Excel rows

On this Power Automate Desktop, we will see how to loop through Excel rows and insert them into a data table.

For example, we have a below Order tracker Excel; it contains 7 columns, which contains data like below. So, we will create a desktop flow that will loop through each row and insert it into a data table.

The 7 columns in Excel are:

  • Order Number
  • Name
  • How did you hear about us?
  • Delivery method
  • Delivery date
  • Biscuit type
  • Order Quantity
Power automate desktop loop through excel rows

Now let’s create a flow in Power Automate desktop that will loop through Excel rows and insert it into a data table.

How to loop through Excel rows in Power Automate Desktop

Here we will see how to loop through Excel rows in Power Automate Desktop.

Before we start creating a flow, you must have the Excel sheet ready. Then follow the below steps.

Step 1: Open Power Automate Desktop, click on +New flow -> then provide the flow name and click Save.

Microsoft Power automate desktop loop through excel rows

Step 2: Now we will create a data table, expand the Variable section -> expand the Data table section -> drag and drop the Create data table action to the Canvas area. Then provide the below information:

  • New Table: This table currently contains 0 columns and 0 rows, so click on Edit.
MS Power automate desktop loop through excel rows
  • Then by default, 2 columns are there, as we need 7 columns; for this, click on the + button on the top right side.
  • Rename the column by double-clicking on it, to Number, Name, Referral, Delivery Method, Date, Biscuit type, and Order Quantity.
Using Power automate desktop loop through excel rows

Step 3: Now we will Open excel, so, expand the Excel section, drag and drop the Launch Excel action. Then provide the below information:

  • Launch Excel: Select and open the following document’ option.
  • Document path: Select the document’s path by clicking on the ‘Select file’ icon.
loop through excel rows using Power Automate desktop

Step 4: Now we will read the Excel sheet, so from the Excel section, under Action-> drag and drop the ‘Read from Excel Worksheet’ action. Then provide the below information:

  • Excel instance: Select the variable from the dropdown and click on save
  • Retrieve: All available values from the worksheet.
How to loop through excel rows using Power Automate desktop

Step 5: Next, we will loop through each row in Excel, so expand the Loop section -> drag and drop each loop to the flow area. Then provide the below information:

  • Value to iterate: Select the ExcelData variable from the dropdown and click on Save.
How to loop through excel rows using Microsoft Power Automate desktop

Step 6: Now we will insert a row to the data table, so expand the Variable section -> expand the Data table section -> drag and drop the Insert row into the table action in for each loop. Then provide the below information:

  • Data table: Select the DataTable variable from the dropdown and click on save.
  • Into Location: select the ‘End of data table’ option.
  • New values: Select the ‘CurrentItem’ variable from the dropdown and click on save.
How to loop through excel rows using Ms Power Automate desktop

Step 7: Next, we will delete the first row from the data table as it is blank, so, from the Data Table section, drag and drop the Delete row from the data table action to the flow area. Then provide the below information:

  • Data table: Select the DataTable variable from the dropdown and click on Save.
  • Row index: Set as 0.
loop through excel rows using Ms Power Automate desktop

Step 8: As we finish inserting rows into the data table, we will close the Excel, expand the Excel section -> then drag and drop the ‘Close Excel’ action to the flow area. Then provide the below information:

  • Excel instance: Select the ExcelInstance variable from dropdown if it is not selected.
  • Before closing Excel: Choose the ‘Do not save the document’ option.
loop through excel rows in Power Automate desktop

Step 9: Now save the flow and click on the Run button. Once your flow runs successfully. Under the ‘Flow variable section, double-click on the ‘DataTable’ variable. Now you can see the data is inserted into the table from Excel.

How to loop through excel rows in Power Automate desktop

This is how you can loop through the rows in Excel in Power Automate Desktop.

See also  When an item is created in SharePoint list send approval and create item using Power Automate

Conclusion

In this Power Automate Desktop tutorial, we saw an example of how to loop through Excel rows in Power Automate desktop.

You may also like:

>