Revit Instances in Power BI

[jonathon]

Sorry for the delay @salpbes, we’ve been busy getting lots of things done.

@salpbes

I can’t get Type and Family information for “Objects.Other.Revit.RevitInstance” elements. This type of element seems doesn’t have relationship connections with Types and Families but they have with Categories.

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 that down into steps. (I will remove columns for brevity per step)

1. The source stream - No magic here, just the base query to a URL.
   

   
   
   Name that query Speckle Source

2. Without doing any other transforms, use the Speckle Source as a Reference
   

   
   
   What that allows is for a single Speckle Query to be used for all the subsequent steps

3. Name that new query Instance Data and duplicate the data column

4. Expand the data - copy to reveal first the definition and then again to expose the referenceId
   

   

5. I have filtered the data.referenceId column to omit blank rows.

6. Add another Reference and name that query Definitions

7. Duplicate the data and expand it to expose cataegory, family, and type
   

   

8. I have filtered the data.type column to omit blanks rows.

9. Make a Reference of the Instance Data query and Choose Merge Queries. Merge Definitions using referenceId and Object ID
   

   

10. A Left Outer Join will keep all the rows from Instance Data and add the Definition row that it points to (If I hadn’t omitted blank rows, an Inner Only Join would retain only those matches that can be made)

11. You should now see the following
    

    

12. If you and Instance Data to show level.name and Definition Data to reveal the speckle_type, family, type and category
    

    

You can use this Merged Query as the data source for your visuals.

For your example use-case, you may want not to follow this exactly, but just make the Definitions query and Left Outer Join it to the Speckle Source.

From there, a few Custom Columns, all in the form of

= if [data.category] <> null then [data.category] else [Definitions.data.category]

Once you get the hang of self-joins, then you can also work with Collections<>Elements for deeper grouped insights or grouping strategies.

Essentially, then your dashboard and visual can refer to this Join Query rather than the raw data source from Speckle