Extracting Categories from Revit Data in Power BI

Video Tutorial

Structure of the Received Data

When working with the received Revit data in Power BI, it's essential to understand its underlying structure. Inside the Power Query Editor, we can explore the structure by previewing individual elements, such as Revit walls, and observe the key-value pairs within the records. Specifically, we'll focus on the "category" key, which contains the category information that we aim to extract.

Method 1: Expanding Data Column

One approach to extract the category information is by expanding the existing data column. By clicking on the expand button in the Power Query Editor, we can select the specific column(s) we're interested in, such as the "category" column.

However, it's important to note that this method has limitations, as it replaces the original data column, potentially excluding other valuable information. Therefore, we recommend going with the second or third approach to preserve the original data.

Method 2: Adding a new Category Column

In this method, we create a new column specifically for the category information from the received data. By adding a custom column, we can define the name of the new column, such as "Category". or it’s translation in your language.

1. Click on the “Add Column” tab in the Power Query editor.
2. Select “Custom Column” from the options.
3. In the "Custom Column" dialog, provide a name for the new column, such as "Category."
4. In the formula field, use the lookup functionality to extract the category information from the "data" column. This can be achieved by, [data][category].
5. Click “OK” to create the new column.

Method 3: Using Speckle Types

An alternative approach to get the specific category of elements is by using the “speckle_type” key. There are corresponding schemas for some fundamental building elements, like Wall, Floor, Room etc. However, it's important to note that not all Revit categories have a direct mapping to a "Speckle Type," particularly when dealing with component instance families such as generic models or furniture. In that case, previous method is recommended as it covers that case as well.

Conclusion

In this tutorial, we explored three different methods to extract category information from the received Revit data. Each method has its own advantages and limitations. Depending on specific case, you can use each.

If you have any questions, feel free to ask them at speckle.community.

Try this tutorial, let us know what you think!