WilliamLam.com

  • About
    • About
    • Privacy
  • VMware Cloud Foundation
  • VKS
  • Homelab
    • Resources
    • Nested Virtualization
  • VMware Nostalgia
  • Apple
You are here: Home / Automation / Quick Tip - Microsoft Power Automate expression to access specific row from Excel workbook

Quick Tip - Microsoft Power Automate expression to access specific row from Excel workbook

05.13.2021 by William Lam // Leave a Comment

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.

More from my site

  • Tips & Tricks for deploying VMware Cloud Foundation (VCF) using Holodeck
  • Automated ESXi Installation to USB using Kickstart
  • Automating Intel Sighting remediation using PowerCLI (SSH not required)
  • Does the ESXi Mac Learn dvFilter work with Nested ESXi on NSX VXLAN's?
  • Slick Jonsbo D31 computer case with embedded LCD screen for homelab

Categories // Automation Tags // Excel, Microsoft Power Automate

Thanks for the comment!Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Search

Thank Author

Author

William is Distinguished Platform Engineering Architect in the VMware Cloud Foundation (VCF) Division at Broadcom. His primary focus is helping customers and partners build, run and operate a modern Private Cloud using the VMware Cloud Foundation (VCF) platform.

Connect

  • Bluesky
  • Email
  • GitHub
  • LinkedIn
  • Mastodon
  • Reddit
  • RSS
  • Twitter
  • Vimeo

Recent

  • VMware Flings is now available in Free Downloads of Broadcom Support Portal (BSP) 05/19/2025
  • VMUG Connect 2025 - Minimal VMware Cloud Foundation (VCF) 5.x in a Box  05/15/2025
  • Programmatically accessing the Broadcom Compatibility Guide (BCG) 05/06/2025
  • Quick Tip - Validating Broadcom Download Token  05/01/2025
  • Supported chipsets for the USB Network Native Driver for ESXi Fling 04/23/2025

Advertisment

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

Copyright WilliamLam.com © 2025

 

Loading Comments...