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.
Prerequisites
To follow along with this tutorial, I’ll assume you have:
- Power BI Desktop (or Power BI Service if you’re familiar with Power Query there).
- Installed the Speckle Data Connector for Power BI.
- 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.
let
// Get Speckle Data
Source = Speckle.GetByUrl("https://latest.speckle.dev/streams/bf8f36b57b/commits/2c9bf02011")
in
Source
Expanding Geometry and Layer Information:
We’ll expand the data column to access the geometries and their corresponding layer information.
let
// 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",
"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"
in
SpeckleReferences
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.
let
Source = #"Speckle References",
// Add a column that rationalises the referenced IDs
AddReferencedId = Table.AddColumn(
#"Speckle References",
"Referenced ID",
each
if [elements.referencedId] <> null then
[elements.referencedId]
else if [definition.referencedId] <> null then
[definition.referencedId]
else if [geometry.referencedId] <> null then
[geometry.referencedId]
else if [displayValue.referencedId] <> null then
[displayValue.referencedId]
else
null
),
// Remove the multiple referencedId columns
ReferenceIds = Table.RemoveColumns(
AddReferencedId,
{"elements.referencedId", "definition.referencedId", "geometry.referencedId", "displayValue.referencedId"}
)
in
#"ReferenceIds"
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 =>
let
// Get parent node
parentRecord =
if objectId = null then
null
else
Table.SelectRows(currentTable, each [Referenced ID] = objectId){0} ?,
// Only add layer names to breadcrumb
parentNameToUse =
if parentRecord = null then
null
// Blocks also have names, we only want the layer names
else if parentRecord[collectionType] <> "layer" then
null
else
parentRecord[name],
// Add current name to list of ancestor names
parentPrefix =
if parentRecord = null then
{}
else
{@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), "."
)
in
newName,
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.
let
// Add breadcrumb column
PathSource = #"Resolve References",
GetParentName = ...
// Apply the function on each row
AddBreadcrumbColumn = Table.AddColumn(
PathSource,
"Layer Path",
each
if [Referenced ID] = null then
GetParentName(
PathSource, if [name] = null then null else if [collectionType] <> "layer" then null else [name], [
Object ID
]
)
else
"",
type text
)
in
AddBreadcrumbColumn
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.
'Til next time. 