Building a sequence based on rows containing 'current' and 'next' columns

[threenub]

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.

[v-yulgu-msft · Accepted 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

[Anonymous]

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.