Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

powerquery m transfrom and reshape data (powerquery, transformation, data cleaning, data reshaping)

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)

DateTime2022-08-22T08:07:39TypeProcedureWasSelectedUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1ProcedureId130053ProcedureTitle130053-------
DateTime2022-08-22T08:07:39TypeDecisionWasMadeUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1DecisionIdPreviousProductDecisionTitleCodice prodotto lavorato precedentemente:DecisionTypeAssortmentDecisionOriginalValue130594DecisionModifiedValue130594-
DateTime2022-08-22T08:07:39TypeDecisionWasMadeUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1DecisionIdExpDebDecisionTitleLivello di competenza:DecisionTypeAssortmentDecisionOriginalValueExpertDecisionModifiedValueExpert-
DateTime2022-08-22T08:07:39TypeExecutionStartedUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1ProcedureId130053ProcedureTitle130053JobIdbb8cc2ea-8d94-44be-a969-e73cced777a2-----
DateTime2022-08-22T08:14:53TypeExecutionJoiningUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1UserDeviceKindHoloLens2ProcedureId130053ProcedureTitle130053JobIdbb8cc2ea-8d94-44be-a969-e73cced777a2---
DateTime2022-08-22T08:15:49TypeExecutionExitedGroupNodeUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1UserDeviceKindHoloLens2GroupNodeIdRootGroupNodeHeader(empty)GroupNodeCompletionStatusIncompleteProcedureId130053ProcedureTitle
DateTime2022-08-22T08:15:49TypeExecutionEnteredGroupNodeUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1UserDeviceKindHoloLens2GroupNodeIdBlisteratrice_Smontaggio_LOGroupNodeHeaderBlisteratrice Smontaggio LOGroupNodeCompletionStatusIncompleteProcedureId130053ProcedureTitle
DateTime2022-08-22T08:15:50TypeExecutionExitedGroupNodeUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1UserDeviceKindHoloLens2GroupNodeIdBlisteratrice_Smontaggio_LOGroupNodeHeaderBlisteratrice Smontaggio LOGroupNodeCompletionStatusIncompleteProcedureId130053ProcedureTitle
DateTime2022-08-22T08:15:50TypeExecutionEnteredTaskNodeUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1UserDeviceKindHoloLens2TaskNodeIdBlisteratrice_Smontaggio_LO_1TaskNodeHeaderImpostare modalità CFSpotIdsImpostazione_Cambio_FormatoSpotTitlesImpostare modalità CFWorkflowPhaseId
DateTime2022-08-22T08:15:57TypeExecutionTaskNodeWasEditedUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1UserDeviceKindHoloLens2TaskIdBlisteratrice_Smontaggio_LO_1TaskHeaderImpostare modalità CFSpotIdsImpostazione_Cambio_FormatoSpotTitlesImpostare modalità CFWorkflowTransitionId
DateTime2022-08-22T08:15:57TypeExecutionExitedTaskNodeUserIdde904e1b-387f-4774-82f7-c2b0431035a8UserFullNameUserName1UserDeviceKindHoloLens2TaskNodeIdBlisteratrice_Smontaggio_LO_1TaskNodeHeaderImpostare modalità CFSpotIdsImpostazione_Cambio_FormatoSpotTitlesImpostare modalità CFWorkflowPhaseId


the requirement is to get the time value of each procedure, group and task below is the use case

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    any help here please

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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