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
Last updated
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
Last updated
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.
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.
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:
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"
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":
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.
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.
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:
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.