Help me merge data in a specific way

New Contributor

So i have two excel files, one with the list of files in folders and second with list of the same folders.


My goal is to merge this two data so that each folder has list of the files in horizontal cell like this


What i've tried: IF formula with CONCATENATE, creating a bot with macro recorder, googling exact solution. no luck in any of them (except for a bot, i was somewhat close to making it work but the data is 11,000 rows and it will make a mistake the way i had created it)

2 Replies
best response confirmed by giokinkla (New Contributor)

@giokinkla Attached a simplified example of how I would do this with PowerQuery. The example uses two small tables on the same sheet, rather than two separate files, but the principle is the same. If you are new to PQ, it will take some time to get used to, but it's worth learning, especially when you intend to perform such tasks frequently on large data sets.

Screenshot 2021-12-15 at 08.47.48.png

The link below would be a good starting point. 

@Riny_van_Eekelen Thanks! for now it's too complicated for me to understand it but the directions are great! Thanks again