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"

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

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
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.
Click on "Enter Data" in the Home tab of Power Query Editor and input the data - Enter Data Directly | Power BI
Enter the same values in both the Group Id and Id columns which we got in Subtree Id during transformation.
Then, add the Section Names to the Racks column to create a suitable structure for swim lanes, leaving the remaining columns blank.

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:

Download the sample 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.
Last updated
Was this helpful?