Forum Discussion

threenub's avatar
threenub
Regular Visitor
7 years ago
Solved

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

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.

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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