File names from folders into rows and columns

temporalnaut 

You can use PowerQuery for this.

Use Data>Get & Transform Data>Get Data>From File>From Folder

Select the root folder that contains all your other folders and click OK, then Transform.

I removed all the columns I didn't need so I was just left with the Folder Path and the Name. I then right-clicked on Folder Path and used Replace Values to remove the absolute path leading up to the root folder on my system, so what remained was just the root folder and any sub-folders:

After this, I use Home>Transform>Group By and configure it like this:

Understandably, trying to sum a text column will give an error:

Use Home>Advanced Editor, I locate the line in the query that describes the sum transformation:

#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Folder Path"}, {{"PipeSeparatedFileList", each List.Sum([Name]), type text}})

Now I change this:

List.Sum([Name])

To this:

Text.Combine([Name],"|")

Then click OK on the Advanced Editor and I have the desired result:

After this, you can use Home>Close&Load to put the data back in the workbook.