Skip to main content

Notifications

Power Automate Fundamentals # 41: Working with List Rows Present in Excel Table OneDrive in Power Automate

Power Automate Fundamentals # 41: Working with List Rows Present in Excel Table OneDrive in Power Automate

 

Introduction:

In Power Automate, during certain scenarios we must traverse all the records in excel file table and based on few conditions content in excel to be updated. List Rows action present under Excel Online(Business) Connector in power automate can be used.

As an example scenario of updating eligibility of Employees based on Age explained here.

 

Step 1:

Login to the required Power Apps environment using URL make.powerapps.com by providing username and password and click on Flows on the left-hand side as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 1.jpg

 

Step 2:

After Step 1, Click on New Flow and select instant cloud flow and provide the trigger as Manually trigger a flow and click on Create as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 2.jpg

 

Step 3:

After Step 2, name the flow as Working With List Rows Present in Excel Table OneDrive and take List rows present in a table action under Excel Online(Business)  as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 3.jpg

 

 

Step 4:

After Step 3, name step   as List rows present in a table [ Employee Table] provide the input values

Location : OneDrive for Business

Document Library: OneDrive

File : ExcelWorkBooks/Employee.xlsx

Table : Table1

as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 4.jpg

 

 

 

Step 5:

After Step 4, take action Apply to each and then under Select an output from previous steps select value from List rows present in a table [ Employee Table]

as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 5.jpg

 

Step 6:

After Step 5, inside Apply to each Step, add an action as condition and inside condition provide the following values  

First Value : float(items('Apply_to_each')?['Age'])

Condition : is greater than or equal to

Value to compare : 18

as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 6.jpg

 

Step 7:

After Step 6, under if yes block , select action update a row under Excel Online(Business)  and provide below values

 

Location : OneDrive for Business

Document Library: OneDrive

File : ExcelWorkBooks/Employee.xlsx

Table : Table1

Key Column : Sno

Key Value : Sno – selected from [items('Apply_to_each')?[ Sno]]

Date - @{triggerOutputs()['headers']['x-ms-user-timestamp']}

Comments: Eligible for Vaccination

as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 7.jpg

 

Step 8:

After Step 7, make sure in Employees Excel File under table1, columns are  

as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 8.jpg

 

 

Step 9:

After Step 8, now save and manually test the flow post providing the connections for Dataverse and observe that values in spread sheet gets populated as shown in the below figure.

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 9.jpg

 

And observe excel file gets filled with values only for the Employees whose age was greater than equal to 18 years as shown in the below figure

Working With List Rows Present in Excel Table OneDrive in Power Automate Figure 10.jpg

 

Note:

  1. Make sure to save and run the flow whenever you try expressions.
  2. Make sure to under Step 6 condition as the value in excel table is an object cannot be compared with an integer value, so that’s why float function was used on Age object which will convert from string to float value then only flow can easily compare between numbers otherwise we get exception.
  3. Make sure to use proper columns in spread sheet are used in flow

Conclusion: In this way one can iterate through list of records present in excel table OneDrive and based on condition updates rows and for bulk files this is an efficient way so as to reduce huge manual work.

Comments

*This post is locked for comments