How to create an array from an Excel table using Power Automate?

by

In this Power Automate article, we will learn how to create an array from an Excel table using Power Automate.

Create an array from an Excel table using Power Automate

Before creating the flow, we need to create the Excel file. Here is an example I have taken an Excel file having columns as:

  • EmpId
  • EmpName
  • EmpSalary
  • Date of Joining
  • Status
  • Last Date, etc.

Once you create the excel table, make sure to select everything (Header columns & Data) and then Format as Table from the Home tab. You can see below how the excel looks like:

create an array from an Excel table using Power Automate

Once you excel file is ready, upload it to a SharePoint document library.

Now, let us start creating the flow.

Step:1

Login to Power Automate and create an instant cloud flow and then enter the flow name and choose the trigger as Manually trigger a flow and click on the Create button.

create array from excel table in power automate

Step:2

Select New step and choose the initialize variable action, Choose the variable type as Array as shown below: This variable we are going to use to store values after reading the Excel file.

How to create an array from an Excel table using Power Automate

Step:3

Add a new step and choose the List rows present in a table, And provide the below-required information.

  • Location: Select the location of the SharePoint site where the Excel file is presented
  • Document library: Select the document library where the Excel file that you have uploaded above.
  • File: select a file from the options
  • Table: Select the table. Here the table name was Table1 in the Excel file.
power automate create array from excel table

Step:4

  • Select the +New step and choose the select data operation to map the key-value pairs. In the Form field, pass the values of the previous action to loop through the rows presented in the Excel sheet.
  • In the Map field, map the related values like below: For the date column type values in the Excel sheet and pass the below expression:
  • Because when we execute the flow for the date field it displays the random integer value in the compose action as highlighted below:
create an array from an Excel table using Microsoft flow
  • To overcome this we have to format the date field value by using the below expressions so that it displays the date value in the dd-MM-yyyy format.

Date of Joining column(Date type)

addDays('1899-12-30', int(item()?['Date of Joining']), 'dd-MM-yyyy')

Last Date column(Date type)

addDays('1899-12-30', int(item()?['Last Date']), 'dd-MM-yyyy')
create array from excel table power automate

Step:5

Add a set variable action, In the Name section, select the variable that we have at step-2, and in the value section pass the Outputs of the select action.

create array from excel table flow

Step:6

To check the output value, add a compose action and pass the variable name which stores the array of values.

create an array from an Excel table using the Power Automate

Step:7

Now our flow is ready. Then Save -> Test and Run the flow by selecting the run flow option. Once the flow ran successfully like below:

power automate create array from excel table

We can see the expected output, which displays the Excel values in an array format in the outputs of compose action.

power automate create array from excel table example

This is how to create an array from an Excel table using Power Automate.

Conclusion

In Power Automate, we can easily create an array from an excel file. In this tutorial, step by step I have explained, how to create an array from an excel file in Power Automate.

You may also like:

>