Is it possible to add columns between tables before expanding?

[Rhodes]

Greetings. 

I have 55 csv files that are combined by a query, each of which contains 7 years of 5 min data values. several additional columns are created based on the summation of two or more of these files. To do this i am pivoting all the data then adding the custom column. Then deleting the original 55 column and unpvioting to flatten the query.

The pivoting step is resulting in a long query refresh time and i started wondering if it was possible to pivot the file tables and add columns before expanding the tables representing the data within each file. From the screenshot below, add a column that contains a table with column "MM/dd/yyyy HH:mm:ss" and the summation of column "MGD" from the C-12_36646393.csv and CS11-04_36646393.csv tables. all tables have the same number of timesteps which should sorted the same as well. However, column "MGD" does contain null values.

This could be a deadend but figured i would ask the crowd. Appericate the help.

Steven

[v-frfei-msft]

Hi @Rhodes ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.