Power Automate to List and Update Online Excel Table Rows

To keep you valued readers engaged and updated with the latest offerings of Microsoft and the IT solutions that my organization provides, the marketing team here works with great zeal to identify readership interests and preference on the regularly published blogs.

One evident task in this effort is to keep a track of number of views, comments for each published blog. This may not at start look like an arduous task, but considering the volume over a period, the number does look daunting.

Fetching some update like this might not qualify for some sophisticated coding effort, hence, I tried to create a Power Automate solution for my team, and only they could confirm if that helped. However, my entire journey in this attempt has been quite refreshing with discovery of new actions to Power Automate.

Introduction

The text above was an ode to my team. My actual blog work starts here.

Unlike my other Flows, this one is short and crisp.

For sake of simplicity, and to stay uptight, I am enumerating only the main actions of the entire flow.

• List rows in an online excel table
• Update data in the online excel table against each corresponding row fetched from #1

Between #1 and #2, I have initialized and set values in some variables that I need to use in #2, however that is something quite specific to my need, so I have voluntarily skipped from focusing on that.

Get Set Go

The below screen shot displays my online excel table with 3 columns, namely ‘BlogURL’, ‘Number of Views’ and ‘Number of Comments’.

The trigger of your flow can be anything, in my case it is a recurring activity with a set periodic recurrence, hence I directly jump to the main actions of the flow.

List rows present in a table

I have used an O365 excel file, hence the Location, Document Library and File fields point to my file.

Table is the range of cells identified for any activity.

To help flow choose the Table, I opened the excel file in a desktop app and selected my range of column headers and clicked on Home > Format as Table to identify the table.

This action shows up a new menu option Table Design which allows to name the table.

Note: The identification of Table in excel must be performed prior to creating the Flow.

You may also like : Power Automate Conditional Substring Pattern Filtration of Excel Tabular Data

Update a Row

The UI that comes up on this action, prompts me to enter the file location details (since it may not be the same file where update might be required). After pointing out the file location details and the Table (similar to that done in the previous step), Flow intelligently shows options for Key Column based on the selected table; this is for identification of the unique row where update should be done.

All available columns of the table are listed in this action for any update to be made to them.

The Key Value can be any text that you think uniquely identifies the excel table row. In my case, I am using the BlogURL from the fetched excel table, which shows up in my Dynamic Content.

Flow is quite intelligent to understand that the value in BlogURL are multiple (since it is fetched from the entire excel table), hence automatically this action gets encapsulated inside Apply to each block, to run as a loop for each BlogURL column value fetched from the excel table.

I have initialized and set a few variables that I am using to update my excel row data for the columns I need to update, rest column values can be left blank if no update is to be made.

On the Finish Line

This blog may seem to be quite rushed since I omitted a major chunk of initializing and setting variables values, as I wanted to stay focused to the actual actions.

What may be important, is to outline that while I am updating values in the same table, the Update a Row action is not limited to the excel table from which rows were listed in step #1. The update action can be performed on any other online excel table by pointing to the appropriate online O365 Excel Location and Table details and selecting the desired Key Column and Key Value to update respective column(s) data for row(s) matching the key value.

Finally, as I pen down this blog, I leave my Flow to run to fill in all the missing details of the excel table as in the image below.