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.
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:
Resolving detached data with Recursive PowerQuery:
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.
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?
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.
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.
hi @bmc,
can you explain what you did with screenshots?
@jonathon i’m not sure if the data structure changed from last updates on PBI connector, but i still can’t split the Material quantities array and can’t recognise the ID material of that geometry (and relative material name, where is it stored in the Speckle schema?)
That’s a front-end limitation. Your object structure is still the same. I suggest using our recently introduced Raw Data Explorer to understand the data structure or Power Query for this purpose.
