Microsoft Power Automate is a pretty nifty service that allow users to automate various business processes using Microsoft Office 365 as well as supporting other 3rd party integrations. For a particular project I have been working on, I have been trying to figure out the best way to access a specific row of information from an Excel workbook such as the example below.
For anyone that does not have a scripting or programming background, Power Automate is relatively easy to use for basic process automation. However, if you have some scripting background, then the tool can feel a bit clunky, especially since each action must be manually created, one at a time and there are no bulk operations or even the ability to quickly duplicate an action for editing.
For my initial prototype, I had used multiple Excel Get Row action to fetch a specific row from my Excel workbook to be able to reference it later in my automation. Not only was this not optimal but I also ran into a number of connection issues since I had up to 40 of these actions. I had searched online and various Power Automate forums for better solution which would allow me to read the Excel data once and then reference it when I needed. From what I could find, most examples focused on a specific row within the document or required looping through the table which did not help me since I needed to be able to access a particular row on-demand.
Without any luck searching online, I decided to reach out to some of our internal O365 resources and even this had them stumped. They decided to open a support request with the Power Automate support team and after initially taking a look at the request, they too were not sure about the best way to accomplish this. They were going to recommend using a Sharepoint list but I fear the same challenge would arise as I still was not sure how to access a specific row of information at any given moment within my automation.
The support Engineer took one more look this week and finally found a solution that would allow me to use the Excel List rows present in a table action once and using a specific expression to then access a particular row without needing to loop through the table.
Given how difficult this was to figure out, I figure I share the expression in case it helps someone else in the future.
Using the following expression, where "List_rows_present_in_a_table" is the name of your action and X is the name of a specific column that you wish to access:
@body('List_rows_present_in_a_table')?['Value']?[0]?['X']
Not only will this allow me to simplify and reduce the complexity of my Power Automate Flow, but it should also remove the connection issue I was seeing since each run was taking up to a minute plus to retrieve all the initial set of rows within my Excel workbook.
Thanks for the comment!