Problem: I have exported some quantities from Revit and I want to combine a list of files together into one single file.

I also want to extract only five columns and put them in the same order.

Let’s start reading all the excel files into one single list:

Then let’s extract the five columns, remove the titles and clean the list from null values:

The python script works as the GetItemAtIndex node:

Finally let’s Write the data to an Excel File:

In order to write our data to a single Excel file we need to count the number of rows in each original Excel file to find the start row at which those data will be written. i.e. the first file has 193 rows and it will start from row 0, the second file has 59 rows which must start at row 193, the third file has 118 rows which must start at row 193+59=252 and so on. The Python Script add each value to the sum of its predecessors:

To achieve the same result with Dynamo we need to use List.Scan:

I also like to store my variables in a function that I can recall further down in the script without drawing a spaghetti line all the way through

Once you have all your quantities combined into one single file you can import that into Power BI to visualise it.

The dynamo file can be downloaded from here Combine Excel