cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tianaranjo
Level 8

Join two collections

I have been stuck on something that should be fairly straightforward.

I have two collections and I would like to update one with values from the other.  

Collection1 (name will only be in this list 1 time)

User: Smith, Joe, dept: Operations

User: Smith, Betty, dept: Finance

User: Jones, Rachel, dept: Shipping

 

Collection2 (name may appear more than once)

Title: Smith, Joe, dept: ""

Title: Smith, Betty, dept: ""

Title: Jones, Rachel, dept: ""

Title: Smith Betty, dept: ""

Title: Jones, Rachel, dept: ""

 

The only related fields are User:Title

I cannot get, for the life of me, get the second collection dept to update with the correct values from Collection 1.  

Any thoughts?
@RezaDorrani @wyotim @RandyHayes 

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Join two collections

Hey @tianaranjo 

 

Can you try the below expression:

ForAll(RenameColumns(Collection1,"User","User1","dept","dept1"),UpdateIf(Collection2,Title = User1,{dept:dept1}))

 

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!

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Join two collections

@tianaranjo 

You might consider a ForAll in this case.  Something similar to this should do the trick:

ForAll(Collection1,
   UpdateIf(Collection2, Title=User, {dept: dept})
   )

Since you have duplicates, it will hit those as well with the UpdateIf.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Dual Super User
Dual Super User

Re: Join two collections

Hey @tianaranjo 

 

Can you try the below expression:

ForAll(RenameColumns(Collection1,"User","User1","dept","dept1"),UpdateIf(Collection2,Title = User1,{dept:dept1}))

 

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!

View solution in original post

tianaranjo
Level 8

Re: Join two collections

@RandyHayes Firstly, thank you for the quick response. It is greatly appreciated. This 'almost' worked. The dept column in Collection2 was updated; however, the dept was the same for everybody. 

I tried @yashag2255 and that worked.  Again, THANKS!

tianaranjo
Level 8

Re: Join two collections

@yashag2255 This worked great; can you provide a quick explanation of what you did here? Thanks.

Dual Super User
Dual Super User

Re: Join two collections

Hey @tianaranjo 

 

Here, we are looping over the first collection to update the values in collection2. UpdateIf function updates all the records in the collection matching the criteria with the provided set of values.

Eg. The loop goes like, get the first item in Collection1 and updates all the records in the Collection2 which has the same name as the selected first record of Collection1 and updates its department value. RenameColumn is used to uniquely identify the column as both the collections had same name.

 

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!

Super User
Super User

Re: Join two collections

@tianaranjo 

Yes, in some cases you need to disguise the column name.  Luckily you can do that with a rename columns.

So, more like this:

ForAll(
   RenameColumns(Collection1, "dept", "col1Dept"),
      UpdateIf(Collection2, Title=User, {dept: col1Dept})
   )

See if that does it for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Join two collections

Hey all, a little late to the party but what about using disambiguation rather than renaming? Something like:

ForAll(
    Collection1,
    UpdateIf(Collection2, Title = User, {dept: Collection1[@dept]})
)

 

@tianaranjo I know this is solved but I thought I would offer another take on it.  😁

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,790)