Different ways of getting Excel data using Power Automate

Introduction

In this blog post we will see 3 different (1 slow and 2 fast) ways of getting data from a table in an excel file using Power Automate. We will start by looking at

  1. then we will look at using a combination of simple actions and Pieter’s method which takes lesser time to run and
  2. finally we will use a couple of HTTP actions along with Pieter’s method which will take only a couple of seconds to run.

Credits

Pieter Veenstra and John Liu write amazing blogs on Power Automate. The content in their blogs is brilliant and helps a lot in creating fast Power Automate flows. We will be using the different techniques mentioned by them in this post.

Data that will be used

Our ultimate aim is to transform large excel data into JSON. Say for example we have the excel file below that has a table named tblWorkwith 1500 rows of data (20k rows later), which shows 3 jobs — Painting, Gardening and Plumbing that are completed in different houses.

Method 1 — Avoid if possible

A simple way to get the data is using the “List rows present in a table” action. We pass in the required details to this action i.e. the location of the excel file and the table (tblWork) in which the data is present and we will get the rows as JSON.

Time to execute

This is a simple method and works well with small data however, if we have large data then the time this takes to execute is very long.

Method 2

Let’s take a look at reducing the time taken for the ‘Apply to each row’ action. The main reason it takes longer is because that action uses variables inside it. As John Liu explains, Power Automate needs to obtain the lock for the variables, set the variable values and then release the lock when some task needs to be performed on the variables. Hence we need a way of avoiding the variables.

The flow

  1. then select the House number, Street and City related to that row.

Method 3

In the final method we will see how to reduce the time taken for the “List rows present in a table” action. We can use this method when the data we are dealing with is large and/or execution time needs to be smaller. Also this method uses the premium HTTP action with user credentials.

The flow

  1. the value of “Gardening” is represented by data at the 4th index
  2. the value of “Plumbing” is represented by data at the 5th index

Execution time

With these actions the time now gets reduced to 1 second to read the table rows. That is, only 1 second to read about 1500 rows of data!

Conclusion

We can try and avoid using for each loops with variables inside them, whenever possible. Instead we can use the filter and select actions which are fast and help us in getting the required data in a few seconds. Combining those with Pieter’s method help us making our flows really fast.

SharePoint developer at Content+Cloud.