Hello,
Need help !!!
the raw data is as follows, with the example and sample attachment
- No headers, data is in form of rows (each record is in one row)
- "First row as Headers" is not applicable here because the columns are dynamic
I have tried multiple ways to reshape the data (pivot, unpivot, transpose, groupby, lookup, merge) but no success
unable to attach the txt/csv/xlsx/zip, so here is the same raw data to refer where you can see dynamic columns (procedureid/groupid are not in same column for one record its in column I for next record its in column K)
| DateTime | 2022-08-22T08:07:39 | Type | ProcedureWasSelected | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | ProcedureId | 130053 | ProcedureTitle | 130053 | - | - | - | - | - | - | - |
| DateTime | 2022-08-22T08:07:39 | Type | DecisionWasMade | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | DecisionId | PreviousProduct | DecisionTitle | Codice prodotto lavorato precedentemente: | DecisionType | Assortment | DecisionOriginalValue | 130594 | DecisionModifiedValue | 130594 | - |
| DateTime | 2022-08-22T08:07:39 | Type | DecisionWasMade | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | DecisionId | ExpDeb | DecisionTitle | Livello di competenza: | DecisionType | Assortment | DecisionOriginalValue | Expert | DecisionModifiedValue | Expert | - |
| DateTime | 2022-08-22T08:07:39 | Type | ExecutionStarted | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | ProcedureId | 130053 | ProcedureTitle | 130053 | JobId | bb8cc2ea-8d94-44be-a969-e73cced777a2 | - | - | - | - | - |
| DateTime | 2022-08-22T08:14:53 | Type | ExecutionJoining | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | UserDeviceKind | HoloLens2 | ProcedureId | 130053 | ProcedureTitle | 130053 | JobId | bb8cc2ea-8d94-44be-a969-e73cced777a2 | - | - | - |
| DateTime | 2022-08-22T08:15:49 | Type | ExecutionExitedGroupNode | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | UserDeviceKind | HoloLens2 | GroupNodeId | Root | GroupNodeHeader | (empty) | GroupNodeCompletionStatus | Incomplete | ProcedureId | 130053 | ProcedureTitle |
| DateTime | 2022-08-22T08:15:49 | Type | ExecutionEnteredGroupNode | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | UserDeviceKind | HoloLens2 | GroupNodeId | Blisteratrice_Smontaggio_LO | GroupNodeHeader | Blisteratrice Smontaggio LO | GroupNodeCompletionStatus | Incomplete | ProcedureId | 130053 | ProcedureTitle |
| DateTime | 2022-08-22T08:15:50 | Type | ExecutionExitedGroupNode | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | UserDeviceKind | HoloLens2 | GroupNodeId | Blisteratrice_Smontaggio_LO | GroupNodeHeader | Blisteratrice Smontaggio LO | GroupNodeCompletionStatus | Incomplete | ProcedureId | 130053 | ProcedureTitle |
| DateTime | 2022-08-22T08:15:50 | Type | ExecutionEnteredTaskNode | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | UserDeviceKind | HoloLens2 | TaskNodeId | Blisteratrice_Smontaggio_LO_1 | TaskNodeHeader | Impostare modalità CF | SpotIds | Impostazione_Cambio_Formato | SpotTitles | Impostare modalità CF | WorkflowPhaseId |
| DateTime | 2022-08-22T08:15:57 | Type | ExecutionTaskNodeWasEdited | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | UserDeviceKind | HoloLens2 | TaskId | Blisteratrice_Smontaggio_LO_1 | TaskHeader | Impostare modalità CF | SpotIds | Impostazione_Cambio_Formato | SpotTitles | Impostare modalità CF | WorkflowTransitionId |
| DateTime | 2022-08-22T08:15:57 | Type | ExecutionExitedTaskNode | UserId | de904e1b-387f-4774-82f7-c2b0431035a8 | UserFullName | UserName1 | UserDeviceKind | HoloLens2 | TaskNodeId | Blisteratrice_Smontaggio_LO_1 | TaskNodeHeader | Impostare modalità CF | SpotIds | Impostazione_Cambio_Formato | SpotTitles | Impostare modalità CF | WorkflowPhaseId |
the requirement is to get the time value of each procedure, group and task below is the use case
Hi @Anonymous ,
Sorry I'm not very clear about your expected result. Here's the original data:
You want to get the time value of each procedure, which represent each procedure, would you like to explain more.
Best Regards,
Community Support Team _ kalyj
Yes sure, I need help in reshaping the data to get procedure, group and task time.
The raw data is like one record per row (header,value,header,value,header,value...)
in the below screenshot ProcedureId can be noticed in column E, G, M, S.... The same is with GroupId and TaskId (generated in dynamic columns in a row)
For a Task: there can be multiple tasks with the same flags/identifiers, following types are considered
- ExecutionEnteredTaskNode
- ExecutionTaskNodeWasEdited
- ExecutionExitedTaskNode
calculation logic is = ((ExecutionTaskNodeWasEdited with phase Skip - ExecutionEnteredTaskNode)+(ExecutionTaskNodeWasEdited with phase Skip - ExecutionEnteredTaskNode))
For a Group: there can be multiple groups with the same flags/identifiers, following types are considered
- ExecutionEnteredGroupNode
- ExecutionExitedGroupNode
calculation logic is = ((ExecutionEnteredGroupNode with CompletionStatus=Complete - The first ExecutionEnteredGroupNode after ExecutionResumed with CompletionStatus=Complete)+ (TerminalShutdown-The first ExecutionExitedGroupNode with CompletionStatus=Incomplete followed by ExecutionEnteredTaskNode))
For a Procedure: it includes both task and group time along with
- TerminalShutDown
- ExecutionResumed
calculation logic is = (TerminalShutDown-The first ExecutionEnteredTaskNode)+(TerminalShutDown-ExecutionResumed)+(TerminalShutDown-ExecutionResumed)....+(The last ExecutionExitedTaskNode-ExecutionResumed)
Merged columns in the screenshot show the task, group and procedure data occururances. Please let me know if this explains
Helpful resources
Power BI Dataviz World Championships
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
Power BI Monthly Update - December 2025
Check out the December 2025 Power BI Holiday Recap!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
