Need to automate hourly spatial and temporal data manipulation from folder containing CSVs

[Anonymous]

Hello community again! 

I am working with simulation data from a parametric run that I need to filter over each simulation and visualize both with respect to time series as well as “space names” in the building. The first screenshot below shows the data columns that I have wrangled in excel with only “one” simulation results. The simulation outputs from the energy software list each space name as a column header (second image) for each variable I am interested in (which means 200ish of columns per variable which is not ideal working with PBI) so I had to manually do it in excel when I was deadling with only one simulation, essentially write formulas to copy each column in that excel screenshot under each other to create a longer column with more rows and “tag” them with the right space name and date time stamp so that I can filter both on date/time and spaces which is needed for synoptic panel visual (third image).

Now this has worked OK for when I had to do the manual excel clean up for only one simulation. Now I am doing this parametric study where I have 1000s of simulations like this! This is essentially a problem of varying time/space scales and getting the data in the right format for the visuals to be able to filter on simulation ID and date/time.

I have used the import from folder option in PowerBI to import my hourly data from simulation but the problem is my spaces are coming in as individual columns (figure 4) and I need to figure out a way to:

1. Extract space names from the column header names automatically
2. Copy each of those columns with space names (200ish columns * 1000s of simulations = have to automate) back to back to each other to create a long column
3. Create a new column that stores the space names/tags, currently only stored in the column header names, in a new column (similar to what is shown in figure 1) for each simulation ID (Sim_ID) and each time stamp associated with each one of the parametric runs.

I think there may be a solution in “custom column” or similar to that (figure 5) but not sure how!

Figure 1

Figure 2:

Figure 3:

Figure 4

Figure 5

[v-eachen-msft]

Hi @Anonymous ,

I cannot see your figures, you could check if you uploaded the right image type.