View materials in PowerBI

[bmc]

Hi,

I am exploring the connector for PowerBI from a commit from Revit, and can’t figure out to find the same information for “materials”, which I can see in the speckle webviewer(sorry if not the correct nomenclature).

I can find the ‘materialQuantities’ information, on which in this case contains 4 layers. Each one should contain the ‘material’ info as shown below in the red mark-up.

But can’t see the same information in PowerBI, see below. Can only see referenceiD and speckly_type

I have just used the basic rvt sample model and am struggling to see if this is a limitation or I am missing something?

Thanks,

[jonathon (jonathon@stardotbmp.com)]

Hey @bmc, you are not doing anything wrong, per se, but you are seeing the impact of reviewing a data structure that isn’t strictly table based on a table which PowerBI expects.

That record with a referencedId is a by-product of how Speckle stores sub-elements and instances of Revit Types. The material data is held on another row with that id. Essentially the web viewer does some work for you in gathering those data in one place. We are looking to improve our PowerBI connector to have similar functionality. In the meantime, I have demoed two approaches to doing so … both require a bit more detailed knowledge of PowerQuery.

Resolving Instance/Detached Data in PowerBI with Self-Joins:

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…

Resolving detached data with Recursive PowerQuery:

Propagate Rhino Layer Names to Geometries in Power BI

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 h…

You can follow either approach, but we want an option for users such as yourself not to touch a line of PowerQuery but instead see the web data OOTB. Expect development on this in the future.

[bmc]

Thanks jonathon,

The development would be very much helpful.

Meanwhile, I understand the idea by:
Find the referenceID of the material as shown in my screenshot above, then find the actual material information breakdown, containing the same referenceID and merge/correlate both tables.

I am struggling to find the ‘second’ table, on which would containt he referenceID and the material information. Is there any straight forward way to search for this? Or will be by search manually every tree?

Thanks for your support,

[jonathon (jonathon@stardotbmp.com)]

The single table you already have contains all objects with a speckle-id, which is why the self-join method works.

Because every row in the Speckle Data has an id, another object row can be referenced by a referencedId; there is no need to do anything manually as this is what PowerQuery enables OOTB.

The self-join method is closest to a standard database operation.

The recursive query method is generically to do this repeatedly; no manual searching is required.

It is because familiarity with PowerQuery as a language is thin on the ground, even with some strong PowerBI users, that we seek to bridge this knowledge/experience gap.

[bmc]

Got it to work, maybe not the most clean solution, I have used 3 reference queries and 2 merges, in a similar way as the ‘instance’ post example above.