cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ajwou
Helper II
Helper II

Best way to split SP list rows based on column single/duplicate values to different SP lists

Hello, 

 

I have a list of projects in an SP list. Some projects are listed multiple times as there might be several departments working on the project. 

I would like to take the projects that are occurring on the list several times and create a record of that project in another list A (even without the departments)  and take the single department projects and create items in different list B. 

Ajwou_0-1631263309282.png

 

The flow I was playing with looks like this:

Ajwou_1-1631263349368.png

 

However, I am not sure what 'condition I would have to have there to say IF the Project ID is on the list more than once create record here (list A), if only once then create record there (list B)

 

1 ACCEPTED SOLUTION

Accepted Solutions

@Ajwou  so first you will have to get all unique projects and then loop through those unique project list, inside the loop get unique department of each project and if length of list of unique department is 1, then project id has one department and if length of list of unique department is greater tan 1, then project id has more departments.

 

See below detailed steps:

 

1. Add Select action after the Get Items action, select value from dynamic content of Get Items in From parameter. Map Project ID with project ID column as shown below

annajhaveri_0-1631351846196.png

2. Add Compose action and use following expression in compose action. This will give you list of unique project IDs.

union(body('Select'),body('Select'))

annajhaveri_2-1631351977441.png

 

 

3. Now to loop through unique project ids, add apply to each action and use output of Compose action

annajhaveri_1-1631351950186.png

 

4. Now under Apply to each action, add compose action and configure following expression

items('Apply_to_each_5')?['ProjectID']

annajhaveri_3-1631352000176.png

 

5. Now add filter array action, in from parameter select value from dynamic contents of Get Items. in filter configure it as shown below, on right side of filter use output of compose action we added in step 4.

annajhaveri_4-1631352041164.png

 

6. Now add Select action and in from parameter use output of filter array action from dynamic content. In map parameter configure as shown below, on right side of map parameter use item()?['Department'] expression

annajhaveri_5-1631352184697.png

 

7. Add compose action with following expression

union(body('Select_2'),body('Select_2'))

 

annajhaveri_6-1631352229251.png

 

 

8. Now add condition action to check length of unique department list of the project

annajhaveri_7-1631352286059.png

 

length(outputs('Compose_9'))

 

In the yes branch of condition, then as per your logic go to list A, in no branch go to list B

 

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

10 REPLIES 10
annajhaveri
Super User
Super User

@Ajwou  you want to get values of both Project ID and Departments and see if both of these values are repeating?

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.
Ajwou
Helper II
Helper II

Hello, 

I want to take the projects (project IDs) with only 1 department and send them to one list and then take the projects that have 2 or more departments into another list only as 1 record (the department would not be relevant at that stage). So if project ID has only 1 row then it would go to list B and if it has 2 or more rows then it would be send(created) in list A. Essentially testing if the project ID has 1 or more records and then recreating these records in different lists based on the condition result. 

@Ajwou  so first you will have to get all unique projects and then loop through those unique project list, inside the loop get unique department of each project and if length of list of unique department is 1, then project id has one department and if length of list of unique department is greater tan 1, then project id has more departments.

 

See below detailed steps:

 

1. Add Select action after the Get Items action, select value from dynamic content of Get Items in From parameter. Map Project ID with project ID column as shown below

annajhaveri_0-1631351846196.png

2. Add Compose action and use following expression in compose action. This will give you list of unique project IDs.

union(body('Select'),body('Select'))

annajhaveri_2-1631351977441.png

 

 

3. Now to loop through unique project ids, add apply to each action and use output of Compose action

annajhaveri_1-1631351950186.png

 

4. Now under Apply to each action, add compose action and configure following expression

items('Apply_to_each_5')?['ProjectID']

annajhaveri_3-1631352000176.png

 

5. Now add filter array action, in from parameter select value from dynamic contents of Get Items. in filter configure it as shown below, on right side of filter use output of compose action we added in step 4.

annajhaveri_4-1631352041164.png

 

6. Now add Select action and in from parameter use output of filter array action from dynamic content. In map parameter configure as shown below, on right side of map parameter use item()?['Department'] expression

annajhaveri_5-1631352184697.png

 

7. Add compose action with following expression

union(body('Select_2'),body('Select_2'))

 

annajhaveri_6-1631352229251.png

 

 

8. Now add condition action to check length of unique department list of the project

annajhaveri_7-1631352286059.png

 

length(outputs('Compose_9'))

 

In the yes branch of condition, then as per your logic go to list A, in no branch go to list B

 

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

Ajwou
Helper II
Helper II

@annajhaveri Thank you for putting the details together. Just to clarify, would all the steps from Step 4 be inside the loop (step 3)?

 

When I included all steps from 4 in the Apply to each, I get the original list items in the list A (5x) and all projects from the original list in list B.

@Ajwou  the steps should be as following screenshot

 

annajhaveri_0-1631532216284.png

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

@annajhaveri - Thank you for your help. I have tried that option and that sent the data to separate lists so that's brill. However the output will be Departments only (I assume that's because only departments have been mapped).

Ajwou_0-1631693078743.png

Please would there be a way to create the records based on the current splitting, however include features from each column so they can be assigned to relevant columns (as in the initial list)

See my current Condition flow below (I am looking for adding Project ID/Name/Accepted/Price/Hours to each item)

Ajwou_1-1631693420899.png

 

@Ajwou  so if your list has same data for all same project IDs then you can use below expression to get the data, you need to provide your column name in place of ProjectName, Title

body('Filter_array')?[0]?['ProjectName']
body('Filter_array')?[0]?['Title']

 

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

@annajhaveri  - yes, that worked perfectly. Thank you very much for your help!

Ajwou
Helper II
Helper II

@annajhaveri  - apologies for opening this again, but I was wondering, as you know the current flow well, whether there would be an easy way to include condition in the item creation stage in the flow saying 'if the record already exists in the new lists, then don't create one'.  

@Ajwou  you will need a value in the list to identify if the record already exists in the list or not? Do you already have that value in that list, if yes, then before adding a record, add Get Items action to query the list using that value (you can specify that in  filter query parameter of Get Items e.g. columnname eq 'value'), then you can check length of items returned by get items to determine if the record already exists or not.  

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,959)