Filtering data in power BI recieved from Revit by category & family & type

[hesham.bendary01 (Hesham Bendary)]

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.

the 3D visual follows the category slicer but does not follow neither the type nor family slicers except for (blanks)

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.

speckle link: Speckle
attached Power BI file.
STA-STR-Speckle.pbix

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.

[hesham.bendary01 (Hesham Bendary)]

@gokermu looking for your assist!

[gokermu (Mucahit Bilal GOKER)]

Hey @hesham.bendary01 ,

I’m looking into it now. Will get back to you once I figure out what’s going on.

[gokermu (Mucahit Bilal GOKER)]

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.

Revit Instances in Power BI

Sorry for the delay @salpbes, we’ve been busy getting lots of things done. What you are seeing is a consequence of the development of Revit Instance support in Speckle. The Instance and the Definition do carry different data on each. There is no way to carry instance data on the definition object. What we have chosen to do, is keep the Definition data where is sourced rather than propagate to the Instance. That being said, to get the data you want is a straightforward Self Join. I’ll break t…

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:

Create and invoke a custom function - Microsoft Support

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

Then you can directly call this on your raw data:

It seemed to work once we fixed the instances:

I hope this helps!

Here’s the Power BI file: instancesFixes_STA-STR-Speckle.pbix (1.8 MB)