powerautomate
Working with Flow control, Variables, Expressions and Loops
In this lab, you will create a cloud flow using a Power Automate to work with Flow control, Variables, Expressions and Loops.
Learning objectives: Flow control, expressions, variables, using Date/Time Duration: 50 minutes.Scenario: We have a list of offices in an Excel sheet. Create a Flow that will send a report describing this list of offices, including the biggest office.Prerequisites- Each student must have a dedicated excel sheet Offices.xlsx. This list was will be imported diring the first task of this lab.Duration- 60 minutes
Task 1
a. Create an Excel workbook and a scheduled flow. The Offices.xlsx excel is available in the resource section of this lab. Upload this spreadsheet in your onedrive for business.
b. This document contains the list of offices of Contoso Corp. Each office has a limited number of seats.Every month a report describing the list of offices and the total number of seats is sent to the management (in this case the management is…yourself). The e-mail should look like this:
c. Create a flow to generate this e-mail report.
- Create a New flow > Scheduled cloud flow
- Use the following screenshot to name the flow, and set the flow frequency.
d. Click Create. The following flow will be generated:
e. The first challenge will be to define the Total capacity. Use the following steps to create a variable that will contain that value.
- Select New Step and select Initialize variable:
f. Rename this action to Initialize variable Total capacity, set the variable name Total Capacity, and select Integer as the type with an initial Value of 0:
Task 2
Extend the flow to loop through all offices: In this task, you will make it loop through all offices, retrieve their capacity, and increment the Global Capacity variable to calculate the total capacity.
a. To retrieve the list of offices.
- Select New step > Excel Online > List rows present in a table:
b. Set the Excel action’s properties as per the following screenshot:
c. Click Show Advanced option and type capacity DESC in the Order By field:
d. Add the Apply to each action (it expects a list of values), using the ”Add a dynamic value” to select the value property from the List rows present in a table action.
e. Calculate the current office capacity using a variable and an expression.
- In the Apply to each action, click Add an action > Increment variable:
- In the Name drop-down list, select Total capacity, and rename the action:
- Click inside the Value text box, and click the Expression tab to add an expression
- Type the following expression: and don’t forget the click Save: int(item()?[‘capacity’])
Note : The item() expression retrieves the current record information in the current loop, and [‘Capacity’] provides the field name to retrieve. Item()[‘Capacity’] returns a string. The question mark ? makes your code more robust by avoiding it crashing if there is no such field (here ‘capacity’) in the record.
Note : The information coming from Excel is a string, and we need to transfer it as a number to increment it to the variable Total capacity. To transform a string to an integer (), we use the int() function.
Note : There are many other expressions available in Power Automate, and we encourage you to read the documentation related to expressions after doing the labs. You can start from the following web page. The list (reference) of all functions can be found here
f. To test the flow, without waiting one month before it starts, Save the flow and use the Test button to manually start the flow on demand (in test mode). This is convenient for testing and debugging purposes.
- Click Test.
- Wait until you get the message: Your Flow ran successfully.
- To check the Total Capacity value, you can examine the value of Total capacity for each step. For example, in our case, we will check its value once it has completed the loop 11 times: so, type 11 In the Show textbox
- Click Increment variable – Total Capacity to display a value of 1607 (if you use the values in the Excel workbook as defined at the beginning of the lab.
g. Define 2 new variables below the variable, Total capacity and before the Apply to each loop, add two new variables named: • Biggest office (type string) • Biggest capacity (type integer)
h. Add a Condition (from the Control connector) in the Apply to each action. The goal is to compare two numbers and select the larger one. To do so we need to transform our capacity values into integers.
- On the left side of the condition, click Choose a value and click on expression. As we already did it before, type int(items()?[‘Capacity’])
- Select the comparison operator is greater than:
- In the Choose a value textbox, select the Dynamic value – Variables – Biggest Capacity
- Now, in the left If yes branch, add a new action Variables – Set variable for our Biggest Capacity variable.
- Rename the action Set variable – Biggest Capacity:
- click Choose a value and click on expression type int(item()?[‘capacity’])
i. In the same left branch of the condition, add another set variable action and select the variable Bigger Office and assign it a value of city. Click on the Dynamic value button to retrieve city.
Or, as an alternative, you can create an expression with item()?[‘city’]
j. Save and test the flow to determine which city has the bigger capacity (Toronto in our case). You can debug the flow or add a notification (or send an e-mail to yourself).
Task 3
a. Next, let’s send an e-mail by adding an Office 365 Outlook – Send an e-mail (V2) action after the Apply to each:
- Find the action by typing Outlook
- In the Connectors list click Office 365 Outlook:
- Select the action Office 365 Outlook – Send an e-mail (V2)
b. Fill-in the Send an e-mail action with the following values:
- In the To field provide your e-mail address
- In the Subject, type “Office Capacity Report.”
- In the body type the following text:
The biggest office is: Biggest Office (from dynamic content) Its capacity is: custom expression - string (dynamic content for biggest capacity) The total capacity is: custom expression - string (dynamic content for total capacity)
c. Save and Test your flow.
Task 4
In the following steps, we will display the list of offices, so we will have to define a list formatting logic and create an HTML table based on this logic.
a. Let’s define the list formatting logic. Before the Send an e-mail action, add a Data Operations – Create HTM table action:
- From: value (dynamic content from List of items)
- Columns: Custom
- Header values: City and Capacity
- Value: Dynamic content for City and Capacity
b. Go back to the Send an e-mail action and update the Body text box to include the Create HTML Output value:
- Type The Total list of offices is : and add the output of The create HTML table action.
c. Save and Test your flow.
Task 5 - Exercise
Let’s extend our flow by adding a new column named Size in our report: the logic is that if the quantity is smaller than 100, we would like to see “Small”, otherwise we will see “Big”.
The report must look like:
