WilliamLam.com

  • About
    • About
    • Privacy
  • VMware Cloud
  • Tanzu
    • Application Modernization
    • Tanzu services
    • Tanzu Community Edition
    • Tanzu Kubernetes Grid
    • vSphere with Tanzu
  • Home Lab
  • Nested Virtualization
  • 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

  • Free Linux & Windows Syslog Alternatives to depercated vi-logger in vMA 5
  • Hiking Trails
  • How to mount a cdrom using vsish on ESXi
  • Supermicro 2021 Homelab Group Buy
  • PowerShell for PhotonOS on Raspberry Pi 3

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

Author

William Lam is a Senior Staff Solution Architect working in the VMware Cloud team within the Cloud Infrastructure Business Group (CIBG) at VMware. He focuses on Cloud Native technologies, Automation, Integration and Operation for the VMware Cloud based Software Defined Datacenters (SDDC)

Connect

  • Email
  • GitHub
  • LinkedIn
  • RSS
  • Twitter
  • Vimeo

Recent

  • Changing the default HTTP(s) Reverse Proxy Ports on ESXi 8.0 03/22/2023
  • Quick Tip - How to download ESXi ISO image for all releases including patch updates? 03/15/2023
  • SSD with multiple NVMe namespaces for VMware Homelab 03/14/2023
  • Is my vSphere Cluster managed by vSphere Lifecycle Manager (vLCM) as a Desired Image or Baseline? 03/10/2023
  • Interesting VMware Homelab Kits for 2023 03/08/2023

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 © 2023

 

Loading Comments...