Data Structure for Columnar Group View

This document explains the process to dynamically convert a standard data structure to the data structure required to plot a columnar group view in the xViz Performance Flow visual

Let’s explore this with an example. We have a dataset with specific IDs, but we don't have the Parent ID, Group ID, and Subtree ID included. However, we can easily create these columns in Power Query using a few transformation steps.

Steps to dynamically convert your data for the Swimlane view:

1. We have a Source table with the columns Id, Title, Subtitle, and Group Name. The Group Name column represents the groups that we used for grouping to create swim lanes.

1. We sort the data by "Group Name" column to ensure that unique group names are arranged in the correct sequence.

1. Add an Index column starting from 1 - Add an index column | Power Query

1. Next, we will add the "Subtree Id" column - Add a custom column | Power Query Create a Custom Column - paste the code below as its definition and name the column as "Subtree Id":

let

        // Create a list of unique Group Name values
        UniqueGroupName = List.Distinct(#"Sorted Rows"[Group Name]),

        // Create a mapping table from Group Name values to numbers
        GroupNameMapping = List.Zip({UniqueGroupName, List.Transform({1..List.Count(UniqueGroupName)}, each _)}),

        // Convert mapping to a record 
        GroupNameRecord = Record.FromList(List.Transform(GroupNameMapping, each _{1}), List.Transform(GroupNameMapping, each _{0})),

        // Lookup the Group Name number from the record
        GroupNameNumber = Record.Field(GroupNameRecord, [Group Name]),

        // Subtree ID
        SubtreeID = "G" & Text.From(GroupNameNumber),

        // if the current row is the first in its Group Name
        CurrentGroupName = [Group Name],
        CurrentIndex = [Index],
        PreviousRows = Table.SelectRows(#"Added Index", each [Group Name] = CurrentGroupName and [Index] < CurrentIndex),
        IsFirst = Table.RowCount(PreviousRows) = 0
    in
        if IsFirst then SubtreeID else null

This will result in a new column named "Subtree Id" is displayed in the image below:

1. Then, we will add the "Parent Id" column - Add a custom column | Power Query Create a Custom Column - paste the code below as its definition and name the column as "PId":

let
            // Get the Group Name and Index
            CurrentGroupName = [Group Name],
            CurrentIndex = [Index],
            // Filter previous rows with same Group Name
            PreviousRows = Table.SelectRows(#"Added Resident Id", each [Group Name] = CurrentGroupName and [Index] < CurrentIndex),
            // If there are previous rows, get the ID of the most recent one
            PreviousID = if Table.RowCount(PreviousRows) > 0 then PreviousRows{Table.RowCount(PreviousRows) - 1}[Id] else null
        in
            PreviousID

This will result in a new column named "PId" is displayed in the image below:

1. Now, we will add the "Group Id" column- Add a custom column | Power Query Create a Custom Column. Then enter double quotes in the custom formula tab to create a blank column for now. Name this column as "Group Id":

This will result in a blank column named "Group Id" is displayed in the image below:

1. We will now perform basic transformations, such as changing data types and reordering columns, as shown in the image below:

1. Now, we will create a static table to define the groups in the visual.

   1. Click on "Enter Data" in the Home tab of Power Query Editor and input the data - Enter Data Directly | Power BI

   2. Enter the same values in both the Group Id and Id columns which we got in Subtree Id during transformation.

   3. Now, add the Group Names to the Title column to create a suitable structure for swim lanes, leaving the remaining columns blank.

1. Finally, we will append the Static Groups table to the Source table - Append Queries | Power Query

Our data structure is now ready!

Finally, populate the data columns in the visual to display the data in swim lanes, as shown in the image below:

Sample Report File - Columnar Group View

Refer to the documentation link below for details on the group structure, which allows multiple nodes to be displayed side by side within a single group - Data Structure for Wide Group Views