Data Structure for Wide Group Views

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

Prerequisite - Before proceeding with this page, please go through the following pages to ensure a comprehensive understanding of the configurations required:

Let’s explore this with an example of Warehouse Management. 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 create Group ID, Parent ID & Sub Tree ID in Power Query:

We have a Source table with the columns Id, Racks, Section, and City.

We can now extract the Section Column using the "Text After Delimiter" option. The delimiter in this dataset is the hyphen (-)

This will result in a new column named "Text After Delimiter", where the section category is displayed, as shown in the image below:

Let's create a "Warehouse Section" column that will be the group for the nodes. We will merge the columns - City and Category with a hyphen (-) as the separator. Name the column as "Warehouse Section"

We can use this column to create the Subtree ID. If you want to use a different column for the Subtree ID and you already have that column in your dataset, you can skip these steps.

Now, we define a variable that sets the number of vertical racks in each section. Simply copy the code below and paste it into the Advanced Editor after the Merged Columns step.

// Define number of columns/subtree IDs of each section based on Merged column
    NumberOfColumnsByMerged = [
        #"Chicago- A1" = 4,
        #"Chicago- B1" = 3,
        #"Chicago- A2" = 4,
        #"Chicago- B2" = 2,
        #"Berlin- A1" = 3,
        #"Berlin- A2" = 2,
        #"Mumbai- A1" = 4,
        #"Mumbai- A2" = 5,
        #"Mumbai- B1" = 3,
        #"Melbourne- A1" = 3,
        #"Melbourne- B1" = 5
    ]

You can adjust the number of racks here to modify the structure of the sections according to your needs.

We will now create a function to retrieve the number of vertical racks for each section based on the Warehouse Section column. Copy the code below and paste it into the Advanced Editor after the final step:

// Function to get the number of columns/subtree IDs for each Merged value
    GetNumberOfColumns = (warehousesection) => try Record.Field(NumberOfColumnsByMerged, warehousesection) otherwise 5

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

We will now 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
        CurrentCategory = [Warehouse Section],
        Numberofrows = GetNumberOfColumns(CurrentCategory),

        // list of unique Section values
        UniqueSections = List.Distinct(#"Added Index"[Warehouse Section]),
        
        // mapping table from Section values to Sub Tree Id
        SectionMapping = List.Zip({UniqueSections, List.Transform({1..List.Count(UniqueSections)}, each _)}),
        
        // mapping list to a record for lookup
        SectionRecord = Record.FromList(List.Transform(SectionMapping, each _{1}), List.Transform(SectionMapping, each _{0})),
        
        // Lookup the section number from record
        SectionNumber = Record.Field(SectionRecord, [Warehouse Section]),
        
        // Sub Tree ID
        SubtreeID = "G" & Text.From(SectionNumber),

        CurrentSection = [Warehouse Section],
        CurrentIndex = [Index],
        PreviousRows = Table.SelectRows(#"Added Index", each [Warehouse Section] = CurrentSection and [Index] < CurrentIndex),
        RowCountInSection = Table.RowCount(PreviousRows),
        
        // Assign Subtree Id
        IsWithinNumberofrows = RowCountInSection < Numberofrows
    in
        if IsWithinNumberofrows then SubtreeID else null

Make sure to replace the "Warehouse Section" text with the name of your grouping column.

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

Subtree Id must be populated only for the first node of every vertical column (rack, in this case) within a group (section, in this case). Child nodes reside in the group of their respective parent nodes.

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":

llet
    // Get the current row's Section, Index, and Id
    CurrentSection = [Warehouse Section],
    CurrentIndex = [Index],

    // Get the number of rows for the current warehouse section
    NumberofRows = GetNumberOfColumns(CurrentSection),

    // Get the first `NumberofRows` in the current section
    FirstRowsInSection = Table.FirstN(Table.SelectRows(#"Added Subtree Id", each [Warehouse Section] = CurrentSection), NumberofRows),

    // Determine the Parent Id logic
    Pid = 
        if CurrentIndex <= NumberofRows then null // First `NumberofRows` get null
        else 
            // For rows beyond the first `NumberofRows`, find the Parent Id dynamically based on prior rows in the same section
            let
                // Get all rows in the current section before the current index
                PreviousRowsInSection = Table.SelectRows(#"Added Subtree Id", each [Warehouse Section] = CurrentSection and [Index] < CurrentIndex),
                PreviousRowCount = Table.RowCount(PreviousRowsInSection),

                // Assign the Parent Id based on previous rows, ensuring index is valid
                ParentIdRow = if PreviousRowCount > 0 and PreviousRowCount - NumberofRows >= 0 then 
                                PreviousRowsInSection{PreviousRowCount - NumberofRows}[Id] 
                              else 
                                null
            in
                // Return Parent Id if there are previous rows
                if PreviousRowCount > 0 then ParentIdRow else null
in
    Pid

Make sure to replace the "Warehouse Section" text with the name of your grouping column and "Id" text with the unqie identifier column in your data.

This will result in a new column named "Parent Id", displayed in the image below:

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:

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

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. Then, add the Section Names to the Racks column to create a suitable structure for swim lanes, leaving the remaining columns blank.

After entering the data, we will append both queries. Ensure that the structure and column names in the static table match those in the source table.
You can choose to maintain this table in any data source of your choice and connect to the report

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 Groups, as shown in the image below:

Here is an example report file:

This example is inspired by common, real-life dataset structures. Your source data structure may vary and may require additional data transformations to align with this example.

PreviousData Structure for Columnar Group View NextLinks

Last updated 7 days ago