Cataloguing files and folders in Power Query

Introduction

This is the first of three articles about an add-in for Microsoft Excel called “Power Query”.

Among other features, it allows files and folders to be cataloged from within Microsoft Excel. (It also does other things, which are perhaps what it is more used for.)

This article shows the process where files can be listed. It walks through each step that you need to take.

The second article discusses the pros and, sadly, the many significant disadvantages with the cataloging process and the results in Power Query.

The third article compares this add-in with Filecats Standard for Microsoft Excel, which also allows cataloging of files and folders in Microsoft Excel, and looks how the cons in Power Query can be rectified by using Filecats Standard.

Starting your catalog in Power Query

First of all, you need to download Power Query, as it is not installed by default in Excel. As of the time of writing this article, this is the link for downloading Power Query.

It should be noted at this stage that Power Query is not available with all versions of Excel. This is discussed in my second article, Pros and Cons of using Power Query.

Having downloaded it and installed it, a new toolbar called “Power Query” is installed. To start a catalog, click on it, and go to Import – From File – From Folder.

 

Note that the above also says “Import metadata and links about files in a folder” – I will refer to that in my second article. This article answers the question “what is metadata?“.

Selecting the folder

A very simple dialog box will then appear, asking you what base folder you wish to catalog. You can “Browse…” for this folder on your computer.

Please note that all files in that folder, and all subfolders and their files will eventually be cataloged, which may take a time.

First stage results

A new dialog box will appear, which will show the first 100 files and folders. This gives you a look and feel about what results you will receive.

 

Here you can choose to remove (i.e. hide) any columns that you do not wish to be catalogued, or you can do other things such as splitting columns or grouping rows. However, most of these buttons, whilst useful for queries that you may receive from other sources such as database queries or SQL Server procedures, are not that useful in the context of listing files and folders.

Exporting results to Excel

Finally, to exit Power Query and to export your catalog to Excel, click on the “Close & Load” button. Excel should then show your files and folders, almost identical to the Query Editor shown above.

So far, so good

The above how you can catalog your files and folders directly in Excel.

It may be surprising that a Google Search for “cataloging files in power query” fails to give any relevant results.

Unfortunately, there are some negatives associated with the above process, and the results that you get. The next article in this series discusses some of the pluses and minus associated with Power Query cataloging.