Extracting column data from multiple spreadsheets to a single spreadsheet

Hello,
I am trying to extract data from a specific column in multiple spreadsheets and dump it into a single spreadsheet. I've attached a sample spreadsheet with a sample of the data I've imported using power query along with the output I'm looking for.

Using power query, I can get the desired data but it it is all grouped together in two columns. What I would like is to have the data from each sheet in its own column. I've tried using a pivot table but I have only been able to get counts and summations of the values.

Any help would be greatly appreciated.

Jason

Please try
A1 drag to right

Formula:

Please Login or Register  to view this content.

A2 drag to right and drag down

Formula:

Please Login or Register  to view this content.

Originally Posted by Bo_Ry

Please try
A1 drag to right

Formula:

Please Login or Register  to view this content.

A2 drag to right and drag down

Formula:

Please Login or Register  to view this content.

Thanks for the reply.
I'm looking of something that more automated. I currently have around 625 spreadsheets I need to pull data from but that number will change based on the project. Is there a way to incorporate these steps into the power query or utilize them within a pivot table? Or even using VBA coding?