Answered

Create an array for each Excel table column

(0) Share

Report

Posted on by Sakura

151

Hi all

I need to loop columns in a table of an Excel Online file stored on Sharepoint.
Each column needs to be saved to an array. Then that array needs to be appended to an array of arrays.

For example

Fruit	Vegetables	Animals
Apple	Lettuce	Cow
Pear	Carrot	Dog
Banana	Broccoli	Cat

Result should be:

[
 [Apple, Pear, Banana],
 [Lettuce, Carrot, Broccoli], 
 [Cow, Dog, Cat]
]

It seems to be impossible unless I initialize an array variable for each column or do it in Office Script. I want to avoid the latter due to the Run Script daily call limitations. Is there a more dynamic solution within Power Automate someone has come up with?

All responses (2)

Answers (1)

Sakura 151 on at

Like (0)

Report

Re: Create an array for each Excel table column

Dear @ekarim2020 thank you so much for this thorough walkthrough! This works nicely. It is still an issue when the number of columns can change per file, but it's already a good help.
Verified answer

Ellis Karim 11,120 Super User 2025 Season 1 on at

Like (2)

Report

Re: Create an array for each Excel table column

Here is one approach to the problem. Starting with the following Excel file:
This is the flow which reads the Excel file:
Note the commas as used in the Compose Array action:
Then finally looping through the array and reading the items:

Ellis

____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.