Power Query - Retaining Explorer Column Headings from a 'Music' folder file extraction

New Contributor



I am relatively new to Power Query and seem to be doing OK, but at the moment I have hit a huge hurdle which is stumping me. Any assistance would be hugely appreciated.


Currently, I have hundreds of folders with thousands of individual music album tracks that I would like to extract the file information from into Excel.

At the moment I have set up each folder within Windows Explorer as a music folder to show filename, Track no., Track Title, Contributing Artist, Album, and Length as below...






However, when I run a power query on a particular music folder, I notice that I am unable to retain the specified file explorer column headings and these default to those as shown below...

These revert to Filename, Extension, Date Accessed, Modified, created and Folder Path which I don't need.






Is there anything I can do so that when I run a power query task on a music folder it will retain the full default named columns of each music file within that folder as shown in the first image above and not those that appear in the second image? 

I am wanting to pull together a full list of every music file from every compilation CD that I have in one huge list/table and to be able to automate this task would be absolutely fantastic.


Hopefully, its just an easy fix and I am missing something obvious.


Many Thanks

3 Replies

@Harveyworld PQ doesn't look at the display settings in the Explorer. It just takes the data as it is stored on the disk. You'll need to extract and transform the data you need from what you see when you first connect to a folder. E.g. filter out all files that are not music files, remove all the date columns, split the "song - artist". And the album name is probably the part of the folder path. Not sure if and how you can extract track length. Don't have any music files on my machine so can't check.


Many thanks for your reply - I have to admit that I am somewhat distraught that this cannot be done from reading the file as is within Power Query. I had set up my folders using these default 'music' folder headings from Windows Explorer believing that MS Excel would be compatible with this format. After all its all Microsoft isn't it....  
Given that I have hundreds of folders and thousands of music files which I would like to extract the detailed information in one huge table, does anyone else have any other ideas how to solve this issue with minimal pain? I am only after the listed file information in each of the folders but to go through each one separately would take a very long time.


best response confirmed by Harveyworld (New Contributor)
For anyone else wanting to make a list of their music collection etc from folders within Microsoft then I was recommended to read this blog from Dutch Data Dude...

It certainly worked a treat for me and gave me something to work on.

I hope it helps someone else.