xViz
ProductPricingCommunity
  • Welcome to xViz Docs!
  • Project Management
    • Project Management - xViz Gantt Chart | Introduction
    • Hierarchy and Additional Columns
    • 🔸Milestones
      • Milestone Formatting
    • Planned Bars
      • Planned Bars Formatting
    • Connectors
      • Primary & Secondary Connectors
      • Multiple Connectors
      • Connectors Formatting
    • Progress
      • Tracking the progress
      • Progress Bar Display
      • Progress Base
      • Bar Formatting
    • Timeline Configurations
      • Timeline Start/End
      • Timeline Range
      • Header Levels
      • Zoom Levels
      • Zoom Options
      • Fiscal Year Support
      • Timeline Format
      • Language Support for Timeline
    • ✨Alerting Techniques
      • Steps to enable Conditional Formatting
      • Conditional Formatting
    • Reference Lines and Reference Range
      • Introduction
      • Static Approach
      • Data Driven Approach
    • Resource Gantt
    • ➡️Other Features
      • Data Colors
        • Row Background Color
        • Bar Border Color
        • Bar Selection Color
        • Bar Fill Color
        • Connectors
      • Duration Data field
      • Display Duration
      • Display Totals
      • Hide Blanks
      • Date Formats
      • DaysOff
        • Weekday
        • Custom Date
      • Setting Up Drill-through
      • Configure External URLs
      • Splitter Position
      • Text Customization
      • Row Numbering
      • Indentation
      • Sorting
    • Parent Level Calculations
      • Aggregation Types
    • Tooltip
    • Release Notes
    • ❓FAQs
  • Performance Management
    • Performance Flow - Introduction
      • Installing from AppSource
      • Get Started
    • Use Cases
      • Organization Performance
      • Microsoft Tenant Data Analytics
        • Organization Chart using Microsoft Graph API
      • Process Flow
      • Sales Performance Analysis
      • Cost Center Analysis
      • KPI Tree
      • Warehouse Management
      • Supply Chain Network
    • Configuring the chart structure
      • Hierarchy structure
      • Grouping / Swim Lanes
        • Data Structure for Columnar Group View
        • Data Structure for Wide Group Views
      • Invisible Group
    • 🪪Display Data in the Cards
      • Title, Subtitle
      • 👨‍💼Images
        • Converting HTTPs link images/icons to Base 64 in Power BI
        • Handling long Base 64 URLs in Power BI
      • 🔢Displaying KPIs
        • Number Formatting
      • 🔻Displaying Variance
      • Sparkline Trends
      • 🔗Hyperlinks
    • Card Formatting
      • Data-Driven Color
      • Templates
      • Customize Template
        • Insert additional data
        • Alignment
        • Wrap Text
        • Styling
        • Margin & Split
        • Modify (Delete / Cope-Paste)
        • Import/Export
    • Links
    • 🗒️Notes & Annotation
    • 🚦Conditional Formatting
    • Layout & Orientation
    • ✨Search
    • ✨Viewing Subtrees
      • Cross Filtering with Subtrees
    • 🔐✨User Context - RLS Experience
    • ✨Filter Context
    • 🎨Formatting Features
      • Formatting Font & Colors
      • Connectors
      • Expand Collapse Button
      • Dimensions
    • Toolbar Ribbon Configuration
    • Simulation
    • ❓FAQs
      • Circular Dependency message
    • Tips & Tricks
      • Bookmark
      • Report Page Tooltip
      • Field Parameters
      • Drill Through
    • Release Notes
Powered by GitBook
On this page
  • Steps to dynamically convert your data for the Swimlane view:
  • Sample Report File - Columnar Group View

Was this helpful?

  1. Performance Management
  2. Configuring the chart structure
  3. Grouping / Swim Lanes

Data Structure for Columnar Group View

PreviousGrouping / Swim LanesNextData Structure for Wide Group Views

Last updated 5 months ago

Was this helpful?

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

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

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.

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.

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.

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

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:

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

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:

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. Enter the same values in both the Group Id and Id columns which we got in Subtree Id during transformation.

    2. 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

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.

Sample Report File - Columnar Group View

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

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 using a few transformation steps.

Add an Index column starting from 1 -

Next, we will add the "Subtree Id" column - Create a Custom Column - paste the code below as its definition and name the column as "Subtree Id":

Then, we will add the "Parent Id" column - Create a Custom Column - paste the code below as its definition and name the column as "PId":

Now, we will add the "Group Id" column- 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":

Click on "Enter Data" in the Home tab of Power Query Editor and input the data -

Finally, we will append the Static Groups table to the Source table -

Power Query
Add an index column | Power Query
Add a custom column | Power Query
Add a custom column | Power Query
Add a custom column | Power Query
Enter Data Directly | Power BI
Append Queries | Power Query
Data Structure
Swim Lanes
2MB
xViz Performance Flow - Transformation for Swim Lanes Structure.pbix
Image 1: Column Group (Swim lane) View in xViz Performance Flow | Power BI
Image 2: Source data structure
Image 3: Data sorted by the Group Name column
Image 4: Index column added
Image 5: Subtree Id column added
Image 6: Added Parent ID column
Image 7: Group Id column definition
Image 8: Blank Group ID column added
Image 9: Basic Transformations
Image 10: Static Table of Groups
Image 11: Appending Static Table to the Source Table
Image 12: Data Structure suitable for Swim Lanes
Image 13: Column Group (Swim lane) View in xViz Performance Flow | Power BI