Hi @KrishR ,
I got a solution for you! This is not the best flow design (it is a little long) and you probably will be able to improve it in the future, but it can be a nice start.
Table and List overview
This is the Excel Table that I'm using in this example:
The goal is to populate this List:
Step 1 - Get data from Excel:
Use the 'List rows present in a table' from Excel connector to get all records from your issue table:
Step 2 - Initialize variables:
You will need to use 3 variables during the process: a string, an integer and an array. Initialize them right after the 'List rows present in a table' action, assigning '1' as value to the integer variable (the other 2 variables may be initialized without values):
The variables will be used for:
String - Build the "Issue Description" record to insert into SP List
Integer - Count how many issues each department have and include the "X." before each issue description
Array - As we have one record per department, this variable will store the departments that were already inserted into the List.
Step 3 - Loop table records
Finally, we will access each table record to extract its data. Use an 'Apply to each' loop passing the 'value' from Excel action as input:
Step 4 - Identify department
Inside your loop, use a 'Filter array' action passing the array variable as input. In the conditions, include the expression item() (highlighted in yellow) and compare it to the department of the current loop iteration. The idea here is to check if we already inserted into SharePoint the department from the current Excel iterated record.
After the 'Filter array' action, add a condition to identify if its length (highlighted in green, expression: length(body('Filter_array')) is equals to zero.
The length will be equal to zero only for those departments that was not inserted into the SP List yet. So, if we already inserted the department in the List, we can skip to the next iteration.
To replicate it in Power Automate, let the "If no" block from your conditional blank. In the "If yes" block, append the department from the current loop iteration to your array variable - so in the future iterations of the same department, the flow will ignore it.
Step 5 - Capturing all issues from department
After append the department into array variable, you will add another 'Filter array' action (highlighted in green), but at this time you will be filtering the 'List rows present in a table' value. As a filter condition, set the 'Department' to be equal to the expression item()['Department'] (highlighted in yellow):
It will generate an array containing only the records from your Excel table that matches to the current read department.
Step 6 - Looping each issue from department
Now you will use another 'Apply to each' loop, but at this time you will be using your Excel table filtered array, from the previous step, as input. Inside this loop, you will have only 2 actions: (1) append a text to your string variable and (2) increment your integer variable:
In the 'Append to string variable' action, we are building a dynamic text where we are first including a number (highlighted in yellow, expression: string(variables('counter'))), then including a single dot "." (highlighted in pink), then including the issue itself (highlighted in green, expression: item()['Issue Description']) and finally letting a blank space at the end (highlighted in blue).
This expression will generate a text like "1. Network connection " and connect it to "2. Access issue ", and so on. The counter is used to capture the "1", "2" and so on; that's why you need to increment it in each loop iteration.
Step 7 - Insert record into SP List:
Finally, let's insert your record into SP List! Out of the internal 'Apply to each loop', add a 'Create item' action and pass the Department as department and your string variable as 'Issue description':
Note: in my List, the logical name of the 'Department' column is 'Title'.
Step 8 - Clear integer and string variables:
As a final step, still inside the "If yes" block, you need to clear the integer and string variables, so they can be properly used for the next department. Use two 'Set variable' actions, assigning the expression null to the string variable and '1' to integer variable:
Output:
The flow runs properly:
After running the flow, this is the output in the List:
Let me know if it works for you or if you need any additional help!
-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.
