Unanswered

Merging Multiple collections pulled from Table

(0) Share

Report

Posted on by Berns

I am trying to merge multiple collections that are pulled from a table in the dataverse. This means all the collections have owner, CreationDate, etc.... There are a lot of articles that look simalar to this out there

ClearCollect(MergedDrivers,
 Ungroup(
 DropColumns(
 AddColumns(
 ThisEventDrivers,
 'TempColumn',
 Filter(
 ThisEventDrivers,
 DriverGUID=AllDriversToEvents[@DriverGUID]
 )
 ),
 DriverGUID
 ),
 TempColumn
 )
);

However, because all these come from a table, I always get an error that the TempColumn has an Column called Owner. So if I at the Drop columns, I have DriverGUID, Owner that works for Owner, but I would have to do that for each and every column, and there is a lot. So I have 2 questions of possibilities
1. When creating a collection based a Table, how can I only add specific columns?

2. How can I drop all columns except the few I need that are not duplicates?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

AhmedSalih 6,678 Moderator on at

Like (0)

Report
Hello, @Berns, the answer to both of your questions is to use the ShowColumns function instead to retrieve only the columns that you need in the collection:

ShowColumns( Table, ColumnName1 [, ColumnName2, ... ] )

Table - Required. Table to operate on.

ColumnName(s) - Required. Names of the columns to include.

If my reply helped you, please give a 👍 If it solved your issue, please give a 👍 & accept it as the Solution to help other community members find it more.

Visit my Blog: ahmedsalih.blog

Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

Was this reply helpful? Yes No
Berns 15 on at

Like (0)

Report

The only problem is it still returns system tables, which is causing me a massive headache when trying to follow this article: https://www.matthewdevaney.com/powerapps-collections-cookbook/join-one-columns-from-another-collection/

Was this reply helpful? Yes No
Berns 15 on at

Like (0)

Report

I posted this earlier, but I think I need to do a new thread since I understand more of what I need to ask.

I have four collections:
I was told once that I should always pull data into a collection so I don't have to keep going to the data table. So I am trying to learn this right.

AllDrivers (pulled from a Dataverse table called Drivers)
Drivers,DriverGUID,Email,Name,'Number of Passengers','Phone Number','Vehicle Type'

AllDestinations (pulled from Dataverse table Destinations)
Destinations,DestinationGUID,Name,Address,URL,Notes

AllEvents (pulled from Dataverse table Events)
Events,DestinationGUID,EventGUID,'Start Date','End Date','Number of Scouts','Map URL',Notes,Stops

AllDriversToEvents (pulled from Dataverse table DriversToEvents)
DriversToEvents,DriverGUID,EventGUID,DriverDirection

Here is how I get them:
ClearCollect(AllDrivers,ShowColumns(Drivers,DriverGUID,Email,Name,'Number of Passengers','Phone Number','Vehicle Type'));
ClearCollect(AllDestinations,ShowColumns(Destinations,DestinationGUID,Name,Address,URL,Notes));
ClearCollect(AllEvents,ShowColumns(Events,DestinationGUID,EventGUID,'Start Date','End Date','Number of Scouts','Map URL',Notes,Stops));
ClearCollect(AllDriversToEvents,ShowColumns(DriversToEvents,DriverGUID,EventGUID,DriverDirection));
I want to create a collection of Driver Details, But the DriverGUID is not stored in the AllEvents Collection (If I did that I would end up with a very large repetitive table since I could have 10 drivers per event). So I store the correlation of Events to Drivers in the AllDriversToEvents collection. What should happen is the following flow:
I have a gallery bound to AllEvents
1. Click on Name (I get the name as follows:
LookUp(AllDestinations,ThisItem.DestinationGUID = DestinationGUID,Name))
2. Get the EventGUID (Already in the AllEvents collection)
3. Find all the DriverGUID for that EventGUID (I have this figured out, This also gets me the DriverDirection). I put this into a Gallery called DriverGUIDtoEvent That is also uses Lookup:
LookUp(AllDriversToEvents,ThisItem.DriverGUID = DriverGUID)

So now I have all the GUIDs and Direction.
I cannot figure out how to combine the AllDriverstoEvents and the AllDrivers Details. I've seen this post (and other;s like it) https://www.matthewdevaney.com/powerapps-collections-cookbook/join-all-columns-from-another-collection/
However, this doesn't work when pulling from a table due to all the system tables, even if I Drop All the system columns. I have spent the last 5 days trying to figure this one part out, and I am just at a loss, totally confused. In simple terms I just want to do something like this
Foreach (GUID in DriverGUIDtoEvent) {
Collect (DriverDetails, {
Name:LookUp(Drivers, DriverGUID = GUID, Name),
Email:LookUp(Drivers, DriverGUID = GUID, Email),
...
}
);

Then the last step is once I have this in a collection, populate a gallery with all of this data.

Was this reply helpful? Yes No
mmbr1606 14,605 Super User 2025 Season 2 on at

Like (0)

Report

hey @Berns

is the old topic still running or close? having multiple places with the same topic can be a bit confusing.

thanks

Was this reply helpful? Yes No