Loop/iterate through every unique ID in table to add single row

[mathieualexh]

Hi, for each of the case that are represented by a unique Case ID (see column CaseIDMaster in the first table), we want to add a new default row whose duration is equal to 30 minutes. In the first table, you will notice multiple lines with the same Case ID, this is because several communications can be associated with the same case during its lifetime and we have not grouped by Case ID because in the output graph we want to display the relative proportion of each communication/duration and not simply the sum total of the duration. I created a copy of the table in which I grouped by Case ID (All Rows) but the different rows now appear as a table in the column.

I tried the following custom function to loop/iterate thought every unique Case ID and create a new row without success, but I don't even know if calling a function is appropriate here... 

(CaseID as text, CommsLogId as text) =>
let
offset = 0,
NewRow =
if offset <= (Table.RowCount(CL_Duration_Duplicate) - 1)
then
{
Table.InsertRows(CL_Duration_Duplicate, offset, {[Id=CommsLogId, CaseIDMaster = CaseID, DurationinMinutes = Duration.TotalMinutes(#duration(0,0,30,0))]}),
offset = offset + 1
}
else
Type.FunctionReturn
in
NewRow

Duplication of CL_Duration tableOriginal CL_Duration table which contains every duration time (each of them in a single row) for each caseFinal graph in dashboard

Thanks for your help.

[Anonymous]

Hi @mathieualexh ,

According to your statement, I think your requirement is complex.

Please share a easy sample file with me and show me the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mathieualexh]

Thank you @Anonymous for your response.

You find attached a full .csv copy of the CL_Duration table. So to recap, the result we want to achieve is to create ONE new row with a duration of 30 minutes for each of the unique cases in the table. The values of the other column Case ID Master will remain the same/unchanged for every new entry. As for the column Id, just input dummy value 00.

-----------------------------------------------------------------------------------------------------------------------
Id Case ID Master Duration in Minutes

15 2022-2023.117 55min

00 2022-2023.117 30min

16 2022-2023.118 15min

17 2022-2023.118 44min

00 2022-2022.118 30min

20 2022-2023.126 30min

21 2022-2023.126 3min

22 2022-2023.126 4min

00 2022-2023.126 30min

23 2022-2023.128 30min

00 2022-2023.128 30min

24 2022-2023.137 60min

00 2022-2023.137 30min
               ... 

-----------------------------------------------------------------------------------------------------------------------

To give you more context, we want to add a 30 minute row to reflect the initial meeting that takes place with clients at the beginning of the case processing, the average length of this meeting is around 30 minutes and we want to automatically add a line for this purpose instead of manually creating a item in our SharePoint Online list (the Id column is the out-of-the-box unique ID given by SP to each item in the source list).