Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am working on some reports for a workflow system and I am struggling to find a way to build a table that shows the workflow sequence in steps.
I am trying to determine take data in the following format:
WorkflowName CurrentStepName NextStepName
------------ --------------- -------------- MyWorkflow1 Workflow_Step1 Workflow_Step2 MyWorkflow1 Workflow_Step2 Workflow_Step3 MyWorkflow1 Workflow_Step3 Workflow_Step4 MyWorkflow2 Workflow_Step1 Workflow_Step2 MyWorkflow2 Workflow_Step2 Workflow_Step3
and then convert it into this format:
WorkflowName StepNumber StepName ------------ ---------- -------------- MyWorkflow1 1 Workflow_Step1 MyWorkflow1 2 Workflow_Step2 MyWorkflow1 3 Workflow_Step3 MyWorkflow1 4 Workflow_Step4 MyWorkflow2 1 Workflow_Step1 MyWorkflow2 2 Workflow_Step2 MyWorkflow2 3 Workflow_Step3
Any help you could offer would be hugely appreciated.
Solved! Go to Solution.
Hi @threenub,
Add an index column in Query Editor mode first.
Then, in data view, create a calculated table as below.
Table_3 = UNION ( SELECTCOLUMNS ( Table_2, "WorkflowName", Table_2[WorkflowName], "StepNumber", Table_2[Index], "StepName", Table_2[CurrentStepName] ), SELECTCOLUMNS ( FILTER ( Table_2, Table_2[Index] = CALCULATE ( MAX ( Table_2[Index] ), ALLEXCEPT ( Table_2, Table_2[WorkflowName] ) ) ), "WorkflowName", Table_2[WorkflowName], "StepNumber", Table_2[Index], "StepName", Table_2[NextStepName] ) )
Best regards,
Yuliana Gu
Hi @threenub,
Add an index column in Query Editor mode first.
Then, in data view, create a calculated table as below.
Table_3 = UNION ( SELECTCOLUMNS ( Table_2, "WorkflowName", Table_2[WorkflowName], "StepNumber", Table_2[Index], "StepName", Table_2[CurrentStepName] ), SELECTCOLUMNS ( FILTER ( Table_2, Table_2[Index] = CALCULATE ( MAX ( Table_2[Index] ), ALLEXCEPT ( Table_2, Table_2[WorkflowName] ) ) ), "WorkflowName", Table_2[WorkflowName], "StepNumber", Table_2[Index], "StepName", Table_2[NextStepName] ) )
Best regards,
Yuliana Gu
I don't have Power BI open, so I can't test this M code, but I think it will work:
let Source = <table> ,Group = Table.Group( Source ,{"WorkflowName"} ,{{"StepName", each List.Distinct( {_[CurrentStepName] ,_[NextStepName]} ), type list}} ) ,Expand = Table.ExpandListColumn(Group, "StepName") ,Add_StepNumber = Table.AddColumn(Expand, "StepNumber", each Number.From( Text.AfterDelimiter([StepName], "Workflow_Step") ), Int64.Type) in Add_StepNumber
The magic really happens in the 2nd step...Group. The idea is you group by the WorkflowName column, and you obtain a distinct list of all of the step names from the other 2 columns. Once you have that 2 column table, you expand out the list into separate rows. Then you add an additional column that extracts the # from the StepName.
Let me know if this works. If it doesn't, please post the error message that you get and I'll try and help troubleshoot.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |