Merge SharePoint lists in collection

[Jackson_Dhorty]

Hi all,

I have two list which contains data with different records but both have same unique Id in common. I want to merge both with some conditions.

1st list records as below: 

f_Id	approval_Status	date
RT-001	Approved	1/21/2021
RT-002	Approved	1/27/2021
RT-003	Approved	1/27/2021
RT-004	In Progress	1/28/2021

2nd list records as below:

f_Id	item_Id	return_status	description	date
RT-001	IT-001	Yes	.....	10/20/2021
RT-001	IT-002	No	.....	10/22/2021
RT-002	IT-003	Yes	.....	10/25/2021
RT-003	IT-004	No	......	10/25/2021

So, here between these two lists,  f_Id is unique in both lists.

 I need to do the following which I am stuck on:

1- From list 2, I need to filter only those items that has return status No

2- Next, filter only approved records from list 1 and merge them both with same unique Id i.e f_Id.

Something like this:

f_Id	item_Id	return_status
RT-001	IT-002	No
RT-003	IT-004	No

Need your kind help.

Thanks

[WarrenBelz · Accepted solution]

Hi @Jackson_Dhorty ,

This would be easier if your matching columns did not have the same name

Filter(
   AddColumns(
      RenameColums(
         List2,
         "f_id",
         "fid"
      ),
      "ApprovalStatus",
      LookUp(
         List1,
         f_id = fid
      ).approval_status
   ),
   ApprovalStatus = "Approved" && return_status = "No"
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @Jackson_Dhorty ,m

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Jackson_Dhorty]

Thanks @WarrenBelz, your are awesome. It solved my problem.

Thanks gain.