cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
desreed
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:

 

ReportIDDependencyID
23
24
25
37
312
43
614
1022

 

What I am trying to achieve is table 2:

 

ReportIDDependencyIDs
23;4;5
37;12
43
614
1022

 

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

4 REPLIES 4
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.

desreed
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!).

 

Thanks

EricLott
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..

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

Let me know if you have any questions!

Highlighted
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

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (6,121)