By now you should be familiar with making Web Queries in Power BI that allow you to access the latest information in 3D Repo. When you apply additional steps in Power BI to translate JSON response from 3D Repo, you will notice that some steps use fixed column names for translating raw data into a readable data table.

This can cause issues when you perform the same API request for other models that contain different column headers, as a result, you have to remap all of the steps. You can automate this process by adding a pivot table step. This is an ideal solution for reusing the same query across multiple formats, such as RVT, IFC, DGN and many more.

In this example, we will use a GET Request that will retrieve all the BIM data stored in a model in 3D Repo. Since various file formats structure their data differently, this particular API call will return different key-value names, based on your model. Here is what the Get all metadata API call looks like in our API Docs:

We will start our process right after we converted our source into a table. If you're not sure how to get there, have a read through Power BI to 3D Repo article. If you ever get lost, here is a sample template file that already contains all of the steps shown in this article:

Select a column containing Record information and click on the expand icon. Ensure you select _id and metadata then click OK as shown below:

In the main ribbon select the Add Column tab and click on the Custom Column icon. A new window will appear called Custom Column. Give your column a new name, in this example it's Data. You will need to provide the following formula in the Custom column formula section:

This formula will copy & convert your metadata record to a table. When done click OK to add a custom column.

Once you have your data column expand it as shown in the image below. Data Name and Value columns will appear. Delete all other columns and leave _id, Name and Value columns only.

Now is the final and crucial step of this exercise called the Pivot column. Select the Name column and open the Transform tab in the main panel. Whilst the Name column is actively selected click on the Pivot Column option. A new window will appear called Pivot Column and you will need to do the following:

Having taken all of the steps above you were able to quickly convert JSON data into a readable data table. Now you can use this template on multiple models in 3D Repo. The next time you change your Model ID parameter value, your Power BI table will still work regardless of the model's file format differences.