Propagate Rhino Layer Names to Geometries in Power BI

This tutorial on propagating Rhino layer names to geometries in Power BI using the power of Power Query! You’re in the right place if you’ve ever worked with Rhino geometries and wanted to display their corresponding layer names in Power BI. In this tutorial, we will build upon the concept of self-joins, which was covered in my previous answer related to BlockInstances and BlockDefintions, to achieve this seamless integration.


To follow along with this tutorial, I’ll assume you have:

  1. Power BI Desktop (or Power BI Service if you’re familiar with Power Query there).
  2. Installed the Speckle Data Connector for Power BI.
  3. Access to a Speckle server, where the geometries and layer information are stored.

This example model has 4 Elements where the layering strategy references the materials used. The nature of this layer path information will be used to cross-reference to a Carbon Dictionary for materials*. It could be cost or anything else.

*A real users use-case

Previous example: Self-Joins

In my previous post, I introduced the ability to perform “self-joins” in Power Query. Self-joins allow us to combine data from the same table based on a common key used to cross-reference Block definition data with Block Instance data. If you haven’t read the previous post, check it out, as it provides some foundation for the steps in this tutorial.

What’s different is this time, we’ll explore the secret sauce of programmers - recursion.

Step-by-step Guide

Open a PowerBI dashboard and start by adding a new Blank Query:


Fetching Data from Speckle Server

We’ll connect to the Speckle server using the Specle PowerBI Data Connector.

    // Get Speckle Data
    Source = Speckle.GetByUrl("")

Expanding Geometry and Layer Information:

We’ll expand the data column to access the geometries and their corresponding layer information.

    // We'll reserve the original data for later
    #"Clone Data" = Table.DuplicateColumn(Source, "data", "original data"),
    // Expand the data column for the mapping of referenced IDs to objects
    #"Expanded Properties" = Table.ExpandRecordColumn(
        #"Clone Data",
        {"name", "elements", "collectionType", "displayValue", "definition", "geometry"},
    #"Expanded Block Geometry Rows" = Table.ExpandListColumn(#"Expanded Properties", "geometry"),
    #"Expanded Geometry Reference ID" = Table.ExpandRecordColumn(
        #"Expanded Block Geometry Rows", "geometry", {"referencedId"}, {"geometry.referencedId"}
    #"Expanded Block Definition" = Table.ExpandRecordColumn(
        #"Expanded Geometry Reference ID", "definition", {"referencedId"}, {"definition.referencedId"}
    #"Expanded Layer Children Rows" = Table.ExpandListColumn(#"Expanded Block Definition", "elements"),
    #"Expanded Children Reference ID" = Table.ExpandRecordColumn(
        #"Expanded Layer Children Rows", "elements", {"referencedId"}, {"elements.referencedId"}
    #"Expanded DisplayValue Rows" = Table.ExpandListColumn(#"Expanded Children Reference ID", "displayValue"),
    #"Expanded DisplayValue Reference ID" = Table.ExpandRecordColumn(
        #"Expanded DisplayValue Rows", "displayValue", {"referencedId"}, {"displayValue.referencedId"}
    #"Removed Stream/Commit Columns" = Table.RemoveColumns(
        #"Expanded DisplayValue Reference ID", {"Stream URL", "URL Type", "Commit Object ID"}
    SpeckleReferences = #"Removed Stream/Commit Columns"

Adding Referenced IDs

Blocks, Geometries, and Layers all reference their “contents” using a referencedId to their objectIds as a function of them being detached. Expanding each and then coalescing in to a single Referenced ID column for ease later.

    Source = #"Speckle References",

        // Add a column that rationalises the referenced IDs
    AddReferencedId = Table.AddColumn(
        #"Speckle References",
        "Referenced ID",
            if [elements.referencedId] <> null then
            else if [definition.referencedId] <> null then
            else if [geometry.referencedId] <> null then
            else if [displayValue.referencedId] <> null then
    // Remove the multiple referencedId columns
    ReferenceIds = Table.RemoveColumns(
        {"elements.referencedId", "definition.referencedId", "geometry.referencedId", "displayValue.referencedId"}

The Power of Recursion

Brace yourself for the secret sauce! We’ll craft a recursive function that iteratively traverses each geometry’s hierarchy of layer names. In this example, we’ll include a table lookup - which in some ways is like the self-joins process.

// A helper function to get parent node's name if that parent is a layer
GetParentName = (
    currentTable as table, 
    currentName as nullable text, 
    objectId as text
) as nullable text =>
        // Get parent node
        parentRecord =
            if objectId = null then
                Table.SelectRows(currentTable, each [Referenced ID] = objectId){0} ?,
        // Only add layer names to breadcrumb
        parentNameToUse =
            if parentRecord = null then
            // Blocks also have names, we only want the layer names
            else if parentRecord[collectionType] <> "layer" then
        // Add current name to list of ancestor names
        parentPrefix =
            if parentRecord = null then
                {@GetParentName(currentTable, parentNameToUse, parentRecord[Object ID])},
        ancestorNames = if currentName = null then parentPrefix else {currentName} & parentPrefix,
        // Concatenate ancestor names in reverse order
        newName = if List.Count(ancestorNames) = 0 then null else Text.Combine(
            List.Reverse(ancestorNames), "."

Generating the Layer Path

With the recursive query in place, we’ll create a new column, “Layer Path,” to represent the hierarchy of layer names for each geometry.

    // Add breadcrumb column
    PathSource = #"Resolve References",

    GetParentName = ...

    // Apply the function on each row
    AddBreadcrumbColumn = Table.AddColumn(
        "Layer Path",
            if [Referenced ID] = null then
                    PathSource, if [name] = null then null else if [collectionType] <> "layer" then null else [name], [
                        Object ID
        type text

Finalizing the Query

From here, we could expand the original data to whichever other properties we have applied to our Rhino objects. The rows could be filtered to only the Objects.Geometry.Mesh if we use the Speckle Viewer Visual.

Power BI Connector Plans

In the future, we aim to include more namespaced functions that encapsulate uses like described here or more complex use cases like propagating full property sets from collections to elements (Rhino doesn’t support Layers having custom properties). These streamlined functions will empower you to achieve remarkable results with a single Query call.

Screenshot 2023-08-03 014839

'Til next time. :slight_smile:


For anyone interested, the complete PowerQuery is attached:
rhino-layer-breadcrumbing.m (4.9 KB)

1 Like