cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Manc_Gurl
Helper III
Helper III

Formatting the contents of an Array Variable

I have built a flow that grabs the text from a SharePoint multi line text column that has append changes to text turned on (column name Comments).  Part of the flow appends the text from the Comments column to an array variable and later writes it into a row in an excel table along with other information about the current item in the loop.  Some list items have multiple comments, so more than one comment appears in my variable. The problem is that the contents of the array variable look ugly when copied into the excel table.  Is there an easy way to format it?  Ideally I'd like each comment in the array variable to be on a new line but on a single excel row/cell.

 

The append to array action looks like this in my flow

Manc_Gurl_0-1624967099511.png

 

However, when it appears in the spreadsheet it looks like this:-

Manc_Gurl_1-1624967232444.png

I'd like to show the comments like so within a row in excel table:-

Editor:  users email - Comments:  bla bla bla

Editor:  users email - Comments:  bla bla bla

 

I hope the above makes sense.  TIA

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User III
Super User III

Yes, you can do something like this:

FormatArray.png

Explanation:

 

The array is used at the input to a select action. The select action is put into text mode, by pressing the little button on the right hand side of the map field. Then this expression is used within the field:

concat('Editor: ', item()['Editor'], ' - Comments: ', item()['Comments'])

So the output of the select action is a new array containing a single string for each entry, and looks like this:

[
  "Editor: Paulie78 - Comments: What a lad",
  "Editor: damobird365 - Comments: He's an alright bloke",
  "Editor: tom_riha - Comments: Loves sending SharePoint API requests"
]

The final compose action called join uses this expression:

join(body('Select'), decodeUriComponent('%0A'))

Which joins the contents of the array from select with a newline character and produces this output as a string:

Editor: Paulie78 - Comments: What a lad
Editor: damobird365 - Comments: He's an alright bloke
Editor: tom_riha - Comments: Loves sending SharePoint API requests

I am not 100% sure that will give you a new line in Excel, you might need to change:

decodeUriComponent('%0A') to decodeUriComponent('%0D%0A')

 

That should do what you want. @DamoBird365 @tom_riha 

 

View solution in original post

3 REPLIES 3
PrasadAthalye
Super User
Super User

Use Parse JSON action and provide output of Comments JSON to it. Then Use Editor and Comments Property.

 

You may have to wrap it in loop since its a collection. If a loop is added, declare a string variable outside the loop and use append to string variable to build a string. Finally use it outside loop in excel action.

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
Paulie78
Super User III
Super User III

Yes, you can do something like this:

FormatArray.png

Explanation:

 

The array is used at the input to a select action. The select action is put into text mode, by pressing the little button on the right hand side of the map field. Then this expression is used within the field:

concat('Editor: ', item()['Editor'], ' - Comments: ', item()['Comments'])

So the output of the select action is a new array containing a single string for each entry, and looks like this:

[
  "Editor: Paulie78 - Comments: What a lad",
  "Editor: damobird365 - Comments: He's an alright bloke",
  "Editor: tom_riha - Comments: Loves sending SharePoint API requests"
]

The final compose action called join uses this expression:

join(body('Select'), decodeUriComponent('%0A'))

Which joins the contents of the array from select with a newline character and produces this output as a string:

Editor: Paulie78 - Comments: What a lad
Editor: damobird365 - Comments: He's an alright bloke
Editor: tom_riha - Comments: Loves sending SharePoint API requests

I am not 100% sure that will give you a new line in Excel, you might need to change:

decodeUriComponent('%0A') to decodeUriComponent('%0D%0A')

 

That should do what you want. @DamoBird365 @tom_riha 

 

View solution in original post

@Paulie78 This is perfect, thank you so much for the detailed explanation, it did exactly what I needed.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (2,366)