cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dchilders85
Frequent Visitor

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

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.

 

 

dchilders85_0-1640533273447.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User
Dual Super User

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.



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

View solution in original post

2 REPLIES 2
Pstork1
Dual Super User
Dual Super User

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
dchilders85
Frequent Visitor

Perfect, thank you!

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (2,784)