Stair, revit Power BI

[Xavier_Steverlynck]

Thank you for seeking help! Our friendly community is here to assist you.

To assist you better, please consider providing:

• Objective: geting data from stair from revit
• Issue: Cant get into a Record List

[teocomi (Matteo Cominetti)]

Hey @Xavier_Steverlynck welcome to our community!

What parameters are missing, could you please clarify? Is this for a hackathon submission or a separate project?

Thanks!

[Xavier_Steverlynck]

Hi Mateo, I am new in this area, but the stairs is a headache , I would like to get the parameters that is under materialQuantities that gives me a LIst not a record

[gokermu (Mucahit Bilal GOKER)]

Hey @Xavier_Steverlynck ,

Welcome to the community! Feel free to Introduce yourself to the community if you want to!

Please take a look at this thread:

How to extract value from Power BI list?

How can I extract information from a “list” from the “materialQuantities” parameter [image]

[Xavier_Steverlynck]

The list has to record as fallows

[Xavier_Steverlynck]

I would like to get into those records

[Xavier_Steverlynck]

The List contains to records of informations that has Data that I need

[jonathon]

@xavier, I don’t have access to a model with stair and material quantities right now. Can you share a Speckle link to a model with just the stair object you are screenshotting?

[AlanRynne]

Hi Xavier!

Could you explain in a bit more detail what is the expected result? Do you want each materialQuantity to be extracted into it’s own column? or just convert the list into a record?

There are several ways to extract lists of data but it really depends on what you expect to do with it

[Xavier_Steverlynck]

I will make one to share

[AlanRynne]

Small followup:

If the issue you’re having is that you’re not seeing the material data inside each materialQuantity record (like so👇🏼)

These objects represent a different element in the speckle database, you will find a row with the same Object ID in the original table that get’s returned by our Data Connector.

Jonathon has written several replies in the past about this, I recommend reading this reply (and the links it contains) to get a better idea of how this is done.

View materials in PowerBI

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

Hope this helps!

[Xavier_Steverlynck]

[jonathon]

Perfect; in the meantime, I have dug one out of the archive.

The trick here is that the Stair materialQuantities materials are detached objects. We have a few examples here on the forum that explain how but in short,

1. Make one query that filters to all the Stairs, (expand data.category)
   = Table.SelectRows(#"Expanded data", each Text.Contains([data.category], "Stairs"))

2. Expand the materialQuantities first as a List-> Rows and then Records to properties, you’ll only need the material.referenceId

   e.g. = Table.ExpandRecordColumn(#"Expanded data.materialQuantities1", "data.materialQuantities.material", {"referencedId"}, {"data.materialQuantities.material.referencedId"})

3. a second query that filters to all the Materials (expand data.category)
   = Table.SelectRows(#"Expanded data", each Text.Contains([speckle_type], "Objects.Other.Material:Objects.Other.Revit.RevitMaterial"))

4. Expand the data prop of the Materials as far as the material.parameters Record
   = Table.ExpandRecordColumn(#"Filtered Rows", "data - Copy", {"parameters"}, {"material.parameters"})

5. Add another source by Merge as New
   

   = Table.NestedJoin(Stairs, {"data.materialQuantities.material.referencedId"}, Materials, {"Object ID"}, "Materials", JoinKind.LeftOuter)

   At this point you have a data table that jins the relevant materials to each stair in the dataset

6. Use our namespace function Speckle.Revit.Parameters.ToNameValueRecord([Materials.material.parameters])) to make a new custom column

7. You can now expand that custom column record to reveal whichever of the material properties you wish
   

   

You can now use this merged data source to reveal material properties in your dashboard. My model has no value for the KEYNOTE_PARAM but the principle is complete.

I have included the dashboard I made for you; you’ll need to change the single source URL.

Stair Material Properties.pbix (1.8 MB)

[Xavier_Steverlynck]

Thanks, I will work with it

[Xavier_Steverlynck]

I have worked with your example in the dashboard that I am making and worked all well thanks you very much for your help.
ReporteBoulevard.pbix (1.4 MB)

Thanks again