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
Last updated
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
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. 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.
Reference links have been provided next to each step for your convenience, allowing you to explore more about that specific transformation if you are encountering it for the first time.
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.
We sort the data by "Group Name" column to ensure that unique group names are arranged in the correct sequence.
Sort the Group Name column according to your requirements for preferred group order. This will ensure that the Subtree Id and Group Id are ordered according to the sorted column.
Add an Index column starting from 1 - Add an index column | Power Query
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":
Make sure to replace the "Group Name" text in the above code with the name of the column you used for grouping.
This will result in a new column named "Subtree 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":
Make sure to replace the "Group Name" text in the above code with the name of the column you used for grouping.
This will result in a new column named "PId" is 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.
Now, add the Group Names to the Title 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 swim lanes, as shown in the image below:
The transformation code blocks are suited to create simple groups and linear structures of nodes within groups as displayed in the last image.
If the parent node is in a group, the child node will automatically be included in the same group and displayed below the parent node. To position nodes side by side within a group, skip adding Parent ID but provide the Subtree ID for both nodes.
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.
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