Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
threenub
Regular Visitor

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.

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @threenub,

 

Add an index column in Query Editor mode first.

1.PNG

 

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]
    )
)

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

Hi @threenub,

 

Add an index column in Query Editor mode first.

1.PNG

 

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]
    )
)

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.