Showing results for 
Search instead for 
Did you mean: 
Level: Powered On

Help with concat and for all

Hi everyone,


Really hoping someone can help with this - I've spent all day and can't quite get there.  The scenario is this:


I'm building an app to manage all of the BI reporting we do.  I have a big list of reports, and some reports are dependent on others (e.g. a report for senior managers may take data from several operational reports).  I have managed to create a collection (Table 1) which is basically a stripped down list of dependencies a report has with other reports.  What I am trying to produce is table 2, which is 1 record per report (instead of 1 row per dependency), but with the dependencies stored as text with a ";" as a separator.


Here is table 1:




What I am trying to achieve is table 2:




I can get the collection to have one row per report and then have the dependency IDs stored in a table within that collection.  I can also do this for just 1 report record and get a text output.  What I can't figure out is a way of applying some code that looks at table 1 (the length of which will vary) and create table 2.  I'm sure it has something to do with ForAll and concat, but just can't figure it out.  I'm expecting both table 1 and table 2 to be collections by the way as I'll only need them temporarily in the app whilst the user is editing the dependencies recorded.


Really hoping someone can help - I'd be really grateful if someone can help crack this for me.  If you could, it would unlock a whole new set of functionality for my users.


Many thanks in advance

Super User
Super User

Re: Help with concat and for all

I'm not 100% sure...I will do some testing on my end but I do know you will most likely have to use GroupBy in there somewhere to achieve this.

Level: Powered On

Re: Help with concat and for all

Thank you for looking into this.  I did try GroupBy - I think that was the solution that gave me the results as a table within the collection.  I then tried to ungroup and concat that but got tied up in knots and couldn't get it to work at all.

If you can figure it out, I'd be really grateful (and impressed!).



Level 10

Re: Help with concat and for all

@rebeccas  @desreed 

I've got this working. There may be some ways to tweak it. There are a couple weird workarounds..

AddColumns(AddColumns(testReportData,"LookupReportID","LOOK"&ReportID),"DependencyIDs", Concat(Filter(testReportData,"LOOK"&ReportID=LookupReportID),Text(DependencyID),";")),

Let me know if you have any questions!

Super User
Super User

Re: Help with concat and for all

Okay...bare with me if I didn't explain this well. And this is another way to achieve a similar result but it might not work for what you need. It was just something I had already used so I had quick notes on it.


For the first collection use group by so it is just listing the first ReportID once:


ClearCollect(CollName, GroupBy(DataSource,"ReportID","grpRptID"))


Then you can have a gallery that the source is CollName


Then you can have a second gallery that is either off to the side or a subgallery (u might use autoheight for first gallery if using this one)


Second Gallery would be - Filter(DataSource, ReportID=Gallery1.Selected.ReportID)


So when they select an item from the first gallery they see a second open with a list showing all the DependencyID that are related.


If you do the subgallery (which will show the items without making a selection so might be the better option) ….the gallery will need to be inside the first gallery and then the items could be =Filter(DataSource, ReportID=ThisItem.ReportID)


This is going to have a list inside each row of your gallery that shows all the related DependencyID for each ReportID


Either would work. It will list them out on top of each other instead of the ";" as a separator but you could do a vertical gallery to get the same look. 


The added benefit is that you could allow them to select either ReportID to see more details or DependencyID to allow for viewing of more information (if you have more you would want to show).


I'll look at a few ideas more along the lines of what you were asking but this was something I have already used a few times I listed above so figured I would mention it.

Helpful resources

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.


Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!


Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020


Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.


Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,692)