hello dears,
my ultimate goal is to manipulate speckle 3D visual view in Power BI based on slicers that rely on data such as family name, type name, category, and instant shared parameters such as “planned date” and “actual date” which all are received from the Revit model.
I dove into the power query of the received data in Power BI and I realized the structural foundation pile family received in 3 rows one of them of Speckle_type:
Objects.Other.Revit.RevitInstance
and the remaining two:
Objects.BuiltElements.Revit.RevitElementType:Objects.BuiltElements.Revit.RevitSymbolElementType
each of them has values I need to combine them, similarly as I select an instance in Revit and all information related to one element.
Software Versions:
Revit 2022.1
Revit connector: 2.19-RC
Power BI: Version: 2.122.746.0 64-bit (October 2023)
Power BI connector: 2.19-RC
using Speakle.xyz as stream server.
Ok, i think i know what’s going on. You have lots of instances in your model and those instances don’t directly have the “category”, “type”, “parameters”, “zone” fields you were referencing too. You can learn more about what instances are in this blog.
@jonathon shared a workaround for this problem using Power BI.
We need to relate these instances back to their reference elements/types. I converted @jonathon 's suggestion into a Power BI function. Please refer to the following guide for more information on creating custom functions:
Once you create a blank query, open Advanced Editor and replace it all with the below code:
let
CreateRevitInstanceTable = (SourceTable as table) as table =>
let
// Step 1: Identify RevitInstance Objects
RevitInstanceRows = Table.SelectRows(SourceTable, each [speckle_type] = "Objects.Other.Revit.RevitInstance"),
NonRevitInstanceRows = Table.SelectRows(SourceTable, each [speckle_type] <> "Objects.Other.Revit.RevitInstance"),
// Step 2: Extract Reference IDs
RevitInstanceWithReferences = Table.AddColumn(RevitInstanceRows, "ReferencedId", each Record.Field([data][definition], "referencedId")),
// Step 3: Select only necessary columns for the join to avoid duplicates
SourceForJoin = Table.SelectColumns(SourceTable, {"Object ID", "data"}),
SourceForJoinRenamed = Table.RenameColumns(SourceForJoin, {{"Object ID", "ReferencedObjectId"}, {"data", "ReferencedData"}}),
// Step 4: Join with the referenced rows
ReferencedRows = Table.NestedJoin(RevitInstanceWithReferences, {"ReferencedId"}, SourceForJoinRenamed, {"ReferencedObjectId"}, "JoinedTable", JoinKind.LeftOuter),
// Step 5: Expand the joined data
ExpandedTable = Table.ExpandTableColumn(ReferencedRows, "JoinedTable", {"ReferencedData"}, {"ReferencedData"}),
// Step 7: Remove temporary columns
CleanedTable = Table.RemoveColumns(ExpandedTable, {"ReferencedId", "data"}),
// Rename column
RenamedColumns = Table.RenameColumns(CleanedTable, {"ReferencedData", "data"}),
// Final Table
InstancesFixed = Table.Combine({NonRevitInstanceRows, RenamedColumns})
in
InstancesFixed
in
CreateRevitInstanceTable
Then feel free to name the function whatever you want. I named mine “CreateRevitInstanceTable ”.