Revit Instances in Power BI

Help
,
1

Hi guys,
I’m using the newest Power BI visual and connector with Revit 2024 and working on Autodesk’s new Snowdon Tower Project Sample for a couple of weeks.
I have a little issue on my side.

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. I know we have relationship Category >> Family >> Type in Revit, but it seems I don’t have it now in Speckle. Maybe this is related to new updates. When I go to “Transform data” in Power BI I have empty cells for “Objects.Other.Revit.RevitInstance” elements. Please see the video and image. I don’t remember I have this issue before. Any help is much appreciated.

Speckle_Revit_Instance_Issue_01
Speckle_Revit_Instance_Issue_012017×891 238 KB

2 Likes
2

Hey @salpbes ,

We haven’t looked into how to handle Revit Instances in Power BI just yet. I’ll look into it and get back to you.

1 Like
3

I’m on it! :speckle: :heart:

2 Likes
4

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

    image
    image1975×358 60.5 KB

    Name that query Speckle Source

  2. Without doing any other transforms, use the Speckle Source as a Reference
    image
    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

    image
    image2005×473 82.1 KB

  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

    image
    image2772×574 108 KB

  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

    image
    image1012×913 50.2 KB

  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

    image
    image1483×349 46 KB

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

    image
    image2896×243 50.7 KB

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]

image
image1920×846 264 KB

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

2 Likes
5

I guess we can simplify this by adding a new function under the Speckle Namespace in Power Query. WDYT @AlanRynne ?

6

This might make sense to do so. Data from Definition to Instance could be similar to the need to aggregate data from Collection to Element, as we were discussing viz. Navisworks