Introduction
In Power Automate, during certain scenarios, we must traverse all the records in the excel file table and based on a 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.
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.
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.
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.
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.
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.
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.
Step 8:
After Step 7, make sure in Employees Excel File under table1, columns are
as shown in the below figure.
Step 9:
After Step 8, now save and manually test the flow post providing the connections for Dataverse and observe that values in spreadsheet gets populated as shown in the below figure.
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
Note:
- Make sure to save and run the flow whenever you try expressions.
- 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 an exception.
- Make sure to use proper columns in spreadsheet 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.
Join the conversation! Your thoughts help the community grow.