Getting schedule information from Revit to power bi

[pranav_vr]

Hello,

I am doing my dissertation on revit integration with power bi for cost management / cost estimation / cost control. My objective is to bring the Revit model to power bi using speckle and create a dashboard for the cost management in power bi. I have watched Speckle videos on Youtube, however, I have no clue on how to retrieve schedule information in power query after Revit model is brought into power bi. Appreciate any suggestions on this or alternate ideas to create cost management dashboard in power bi.

Regards,
Pranav

[gokermu (Mucahit Bilal GOKER)]

Hey @pranav_vr ,

You can directly send Schedules from Revit.

Sending | Speckle Docs

[pranav_vr]

Thanks Bilal. Appreciate your help.

I am able to send the schedules from Revit to Power BI, however, could you guide me on how to extract the schedule information in the power Query when loaded into power BI. It has only default columns like Model URL, speckle type, data etc. Any recorded videos will be much helpful.

Also, instead of schedule / quantities, is it possible to send the material takeoff from revit to power BI?

Thanks in advance.

[gokermu (Mucahit Bilal GOKER)]

Hey @pranav_vr ,

Have you checked our Power BI playlist? We cover how to work with received data in Power BI and show how to extract relevant information. Take a look and let me know if it helps:

https://www.youtube.com/playlist?list=PLlI5Dyt2HaEsZHG2WJ75WIM0Brx6VHT2S

[pranav_vr]

Thank you very much, I have gone through all the videos, and it was very helpful. I exported material take off and was able to import to Power BI. However, all the data from the material take off is stored in the form of list. Is there any function to call and expand this list so that the actual values including the columns

, rows and headers appear in the form of table? I have attached the snip.

[gokermu (Mucahit Bilal GOKER)]

Hi @pranav_vr,

You can receive Revit schedules in Excel and then import the Excel data into Power BI. If you don’t want to do that and receive directly in Power BI, we currently do not have a built-in function to directly convert Revit schedules to Power BI tables. Nevertheless, we appreciate your suggestion and will consider it.

Meanwhile, I have created a simple function that you can use. You can also create your own custom functions in Power Query and call them. Please refer to the following guide for more information on creating custom functions:

Create and invoke a custom function - Microsoft Support

Once you create a blank query, open Advanced Editor and replace it all with the below code:

(DataTable as record) =>
let
    DataTableList = DataTable[data],
    #"DataTableList to Table" = Table.FromList(DataTableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RemovedTableName = Table.RemoveFirstN(#"DataTableList to Table", 1),
    ColumnHeaders = RemovedTableName[Column1]{0},
    RemovedHeaders = Table.RemoveFirstN(RemovedTableName, 1),
    #"Extracted Values" = Table.TransformColumns(RemovedHeaders, {"Column1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), ColumnHeaders)
in
    #"Split Column by Delimiter"

Then feel free to name the function whatever you want. I named mine “DataTableToPbiTable”.

This function expects the Record object in DataTable speckle type row.

Once you give that object to it, it will spit out the schedule as a table.

I hope this helps.