Forum Discussion

mathieualexh's avatar
mathieualexh
Frequent Visitor
4 years ago

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

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.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      mathieualexh
      Frequent Visitor
      4 years ago

      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).