In this blog I will use the For Each loop in Power Automate Desktop.

From this point I will refer to Power Automate Desktop as PAD.

What is a For Each Loop?

The For each loop iterates over items such as Lists, Datatable rows, folders or files, storing the current item in a variable.

While the item is stored PAD can perform a variety of actions on it.

Unlike the Simple loop and Loop condition, For each loops don't iterate a fixed number of times or while a condition is true.

For each loops iterate over all the items you specify, processing each in turn until no items are left.

Below is the outline of what I'll do.

• Open a connection to a SQL Server database.

• Execute a SQL command to extract the top 10 highest earning films from a SQL database table.

• Close the SQL connection.

• Loop over the resulting Datatable variable, writing details of each film into a text file.

• Make changes to the SQL command, without changing any of the flow and still execute successfully.

Creating a new flow

Create a new flow with a suitable name.

The newly created flow.

Opening a SQL connection

You can find a detailed blog on using the 3 available SQL actions in PAD by clicking here.

To begin, I'll use the Open SQL connection action.

The completed action will connect to the Movies_02 database with a variable name of SQL_Movies.

Execute a SQL statement

Now I need to add the Execute SQL statement action with a SQL query to extract my required data.

The Execute SQL statement action will run the entered query and store the results in a variable named Films.

Testing the connection

I would always recommend running the flow now to check that the Open SQL connection and Execute SQL statement actions return some results.

Viewing the variables shows that some data has been returned.

You can see details of the data you've retrieved as shown below:

Clicking the 3 vertical dots next to the variable will allow me to click View to see what is stored.

A sample of the data returned by the query.

Close the SQL connection

Once the data has been stored in the variable I can now add the Close SQL connection action.

This action simply closes the connection to the database.

The For Each loop

The For each loop can be found in the Loops section of the actions pane or you can search for it.

The For each loop showing the corresponding tooltip.

When you add a For each loop action, you have to choose which collection of items you want to loop over.

The Value to iterate property sets the collection of items to loop over.

I want to loop over the Datatable stored in the variable named Films.

I've chosen Films using the variable picker {x}.

Each item from the Datatable will be stored in a variable with a default name of CurrentItem. I've renamed the variable to Film in the example below:

Its a common convention to use plural for the collection of items and singular for each item.

Writing the results to a text file

As each item passes through the loop I want to write certain details about each film to a text file. I can use the Write text to file action to do this:

The Write text to file action ready to be filled in.

Use the File path property to set the destination for the output.

You can use a variable that may store the file path and name or use the browse icon.

Clicking the browse icon will prompt you to choose a location and enter a file name for the created text file.

After choosing the location and entering a name for the file, you must add the file extension. It is .txt in this case.

Now I need to add the text to be written to the file for each film.

For each film I want a sentence to say, for example 'Film has made $123456 and won 5 Oscars'

To reference a particular column of the Datatable you can use the column number inside square brackets. The first column is numbered 0.

The above text string will reference the stated column numbers for each film as it passes through the loop, writing each to the file.

Rather than using numbers to reference the columns, you can use their names:

An alternative method is to use the column names from the Datatable. The name must be enclosed in square brackets and single quotes.

The above text string will reference the stated column names for each film as it passes through the loop, writing each to the file.

For my example I will use the number method.

You can choose whether the new text added to the file will appear on a new line or added to the end of the line. I'd like each film to appear on a separate line.

You can choose whether each film will be added to the file as a new line or appended to the previous films details.

You can choose whether the new text added to the file will replace the existing content or not. In this case I'd like to overwrite the old data.

If the file you are trying to write to already exists, you can choose what action to take. Either append the new data or overwrite the existing data.

Once you have set the options you can save the action.

The completed action. This action will write the stated text string to the chosen file, append each film as a new line and append to any existing data in the file.

Testing the flow

Its time to test the flow.

These are the films I viewed in the Datatable earlier so I can confirm the flow has worked.

A passing note that films that make lots of money don't generally win Oscars!

As mentioned previously, For each loops process each item from a collection of items.

Editing the SQL statement

You can change the SQL statement in the Execute SQL statement action to return a different set of films.

I've changed the top number to 20 and the flow should still work with no further editing required.

Here are the results of running the flow with the new SQL statement:

Now the text file contains 20 films instead of 10.

How about changing the query to show the top 20 films by length rather than income?

This will produce the top 20 longest films.

The resulting file has a different group of films now. Despite various changes to the data extracted, the flow still works.

If you enjoyed this blog, please view the others in this series.