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 ...
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

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.