Performing Inner Join on Two Collections

[ztb97]

Hello all, I am currently following along to Power Apps - Joining Collections - SQL (Inner) Join - YouTube in order to perform an inner join on two collections I have in my PowerApp.

The two collections I am attempting to perform this on are the resourcesColl collection and the orderIds collection. In the resourcesColl collection, there is an "Id" column which matches/maps to the "WorkCenterID" column in the orderIds collection.

Here is my formula/code:

I am getting two errors:

1) The function 'Ungroup' has some invalid arguments

2) A column named 'CreatedBy' already exists (I am pretty sure this one is just being thrown because of the 1st error. I am not actually creating a column called 'CreatedBy' in this code)

Where am I going wrong?

Let me know if you need any further information.

Regards,

Zachary

[v-bofeng-msft · Accepted solution]

Hi @ztb97 ,

I assume there are two tables:

ClearCollect(resourcesColl,{Id:"1",Column1:"1"},{Id:"2",Column1:"2"});
ClearCollect(orderIds,{WorkCenterID:"2",Column2:"1"},{WorkCenterID:"3",Column2:"2"});

You could use this formula to do the inner join:

ClearCollect(nameWithPoID,
   ForAll(
       Filter(resourcesColl,Id in orderIds.WorkCenterID) As TableA,
       Patch(LookUp(resourcesColl,Id=TableA.Id),LookUp(orderIds,WorkCenterID=TableA.Id))
   )
)

Best Regards,
Bof

[ztb97]

Great, that creates a new collection but only populates the "Id" and "WorkCenterID" columns in the nameWithPoID collection. How would I get this to populate two more columns in the collection? The column names I also wish populated are "Name" from resourcesColl and "ProcessOrderID" from orderIds collection