The Preview window should now only displays the files and folders in the selected folder.īOOM! That was pretty easy too! Listing file attributes = Folder.Contents("C:\Users\marks\.\Documents") ![]() Folder.Contents only looks at individual folders. The M code formula for the Source step is: = Folder.Files("C:\Users\marks\.\Documents")Īll we need to do is change Folder.Files to Folder.Contents, and we’re done. While working through the previous example, you may have noticed the output includes files in the selected folder AND subfolders.īut what if we only want the files from the folder? With a small change to the M code, we can achieve this too.īy default, Power Query users the Folder.Files function. List files in a folder, but not Subfolders The Preview window now shows only a list of the files and folders.Ĭlick Home > Close and Load to push the data into Excel.īOOM! How easy was that! We didn’t need to write a single line of code □. With the Full File Path column selected, click Home > Remove Columns (drop-down) > Remove Other Columns. Make the new column name Full File Path,Ī new column is created with the complete file path.The selection order is important as this determines how the text is combined. Select the Folder Path column, hold the Ctrl key, then click the Name column. If we just want a single list of files in the folder, we need to combine the Name and Folder Path columns. The Power Query Editor opens and shows the file attributes: Take a look this is already starting to reveal some useful information.Ĭlick Transform Data to open the Power Query Editor. In Excel, click Data > Get Data > From File > From Folder.ĭepending on how many files are in the folder and subfolders, this may take some time.Ī preview list of documents opens. Let’s start by looking at how to list the files in a folder using Power Query. List files in a folder (and sub-folders) with Power Query While some of these attributes are available using other solutions, they are not as easy to obtain or as comprehensive as Power Query. Power Query also has the added advantage of easily obtaining other file and folder attributes – file size, date created, date modified, etc. In comparison, Power Query has an easy-to-use, point-and-click user interface there is no coding or complex techniques required. Microsoft no longer supports Excel 4 Macros, as VBA replaced them in 1995. Excel 4 Macros – These are automation techniques originally found in Excel in the 1980s and 1990s. ![]() ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |