Question about how to collect sub-arrays from Apply to Each steps within Master Array

[dchilders85]

Hi all,

First, let me thank everyone in the community for their awesome help, both in helping me and others. You guys are awesome.

Second, I am very very new to Power Automate, and am more or less learning as I go. If my question or flow below suggests there's a better way to do things, I am very much willing to learn and change direction. I'm used to using R, Python (Pandas), and Alteryx, but Power Automate is new to me.

Here is the larger overview of what I'm trying to do. Every day I receive an email with a text file attachment that contains client payment information, that I want to automatically download, parse/cleanse/calculate, and add into an excel sheet on our sharepoint. I have gotten to the point where Power Automate opens the text file and reads the data in as a long string, and I am splitting the multi-line text file into an array by splitting on line breaks, and then using standard identifiers in the lines containing payment data to filter out all the garbage that made it into my initial array.

I am now in the cleansing /parsing /calculating stage. My flow is using the array I described above, and doing parsing/cleanse work during Apply to Each. The end of my Apply to Each results in an array of [Payment Amount, Borrower Number, Borrower Name]. This will represent a single row on the ultimate excel file. See the picture below for this stage of the flow.

What I don't understand is how to collect the array at each step of the Apply to Each into a Master Array for further transportation to an excel file. Earlier in my flow initialize a variable of type array "PaymentData" and attempt to use "Append to Array Variable" to collect the sub arrays from the Apply to Each into the PaymentData master array. It returns an error that I can't append arrays within an array.

My next step after this will be to send to an excel file, to each borrower payment line will be a row in excel. It will append to an existing excel file that already contains the monthly data, so it will be keeping a running ledger for use in payment reconciliations with the bank.

Any thoughts and insight are very much appreciated. If you are also telling me I'm just dumb and need to do some proper studying and learning on Power Automate, I will understand.

Thank you again.

[Pstork1 · Accepted solution]

I think you would be better served by removing the compose 5 step and just appending the comma delimited string to the array instead of creating a new array.  Even better would be to reassemble the comma delimited string as a JSON record to be appended to the array instead.  Use this with the formulas you have in Compose 3

{
    "Payment Amount": <<Payment formula>>,
    "Borrower Number": <<Borrower number formula>>, 
    "Borrower Name": <<Borrower Name formula>>
}

When you are all done you will have a JSON array in your master array that you can run an Apply to Each on to add each record as a row in the Excel table.

[dchilders85]

Perfect, thank you!