Concatenation of Year

[PowerBI87]

Hi All,

I currently have an SharePoint list (table 1) with the following:

How would i create flow that can concatenate all years into 1 if the company is the same?

The output I need for list 2 is:

Table 1:
Company	Year
X	2019
X	2020
Y	2017
Y	2018
Y	2019

Table 2:
Company	Year
X	2019,2020
Y	2017,2018,2019

[yashag2255]

Hi @PowerBI87 

There is a couple of ways you can do this. One of which is, initialize an array variable at the top of the flow and then add action to get items. create an apply to each loop to iterate over the items returned and then in the loop add a condition to check if the array contains the company name. If it does do nothing else, add append to array variable action and select the company name.

Outside the loop, add another apply to each loop and this time iterate over the array and add the action filter array where you can provide the list returned from sharepoint to check if the company name is current  item from the array. This way you get filtered distinct companies. 

Now, you can add another variable and from the filtered array, append the year values. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[PowerBI87]

Thanks for your reply.

Not sure if this is the right steps so far. Can you please comment?

also not sure what to do outside of the loop.

[DCReina]

Hey PowerBI87,

You're close. I believe that he meant for you to not nest the two "For Each" actions...you may want to try something like this:

Obviously, you'll have to account for my [...] suggestions since I don't have the values from your tables but you get the idea 🙂

(Please "Like" and "Accept" any helpful responses!)

[PowerBI87]

Thank you. I followed your instructions and came up with this.

However flow is failing when in the initialize variable step. Any idea what's wrong?

[DCReina]

Since the error is indicating that it doesn't like the output for some reason, could you the trigger/actions above the variable? Both the normal view into the cards and then the expanded test view that shows the inputs/outputs?

I'm wondering if you are getting the right values for your lists (i.e. the arrays and not the singular value of the SharePoint list itself (i.e. the object of the list)...thoughts?

[PowerBI87]

"Year of Update" is the field in SFS Notification Template T1 list that the output should go to. ( in picture 4)

[DCReina]

I'm guessing that it's because it's unable to resolve you getting the same list twice? That's the only thing I can think of off the top of my head since all of the steps you have below the "initialize variable" step won't actually run until that action passes.

Thoughts?

[PowerBI87]

I'm not sure how to proceed. Any other ideas?

[DCReina]

I believe that you have to resolve one of those actions...in other words, do you really need both? I'm not sure what you're trying to accomplish with the entire Flow so it's difficult for me to fully understand what you're going for...

Would love to help...just need as much context as you can provide.

[PowerBI87]

Thank you for you efforts in trying to assist me.

I am essentially trying to grab data from one list (List 1), and populate items from list 1 into list 2 and list 3.

I believe I need both triggers.

I need Get item(so I can use those items to populate fields in this step:

i need Get itemS to initialize variable 'List of Items'