ETL, stacking or appending transposed records from a folder data source by file

[Anonymous]

Hello,

Does anyone know how to stack records that have been transposed during ETL?

I'm starting with this document.  I have hundreds in a folder.  Under data source I select get data and choose excel file.

It coming in like this

After some cleaning it looks like this

Then I transpose the data.  I finish cleaning it to get one completed record.  I could go through the whole process again and append the records, but with hundreds to do I think there is a better solution.

The get data > folder option allows me to bring in all the data at once. (Using 5 records as sample)

Here you can see I have done some cleaning and you can see the records stacked on top of each other.

Here is were my problem starts.  With one record, the next step I do is transpose the data, but since all 5 records are stacked already, I get 160 columns  when it should be only 32 columns.

I can't figure out how to transpose or pivot the data so that each record of two rows remains. I was thinking that I might be able to use the source.data column as an index, but it hasn't worked so far.

The goal is to have the data look like this. 5 records would make it 10 rows and 32 columns

Any ideas?  Greatly appreciated.

[serpiva64 · Accepted solution]

Hi,

you need to create a custom function in power query and then process the files automatically one by one (so avoiding the problem of the incease of the number of columns

If this post isuseful to help you to solve your issue consider giving the post a thumbs up 👍 !