Indexing folder content by alphabetical order in Excel

Question

So, I manage to index files from folders with the following formula : =IFERROR(INDEX(E_Standard,ROW()-1),"")

Where "E_Standard" is a Name for =FILES(Standard!$A$2) and in cell "Standard!$A$2" you have my folder path : "N:\\Guitar stuff\\Vids\\Doable\\Standard\\E Standard\\Redone\*".

All of this were fine until now, because for some reasons, the alphabetical order is now not respected (as seen in the screenshot below).

All the other tabs works fine but none of them approach the number of files this one does (114 for the second with the most files, while this one is at 648)

Could it be any reason to why it doesn't list it in alphabetical order anymore ?

• I tried to clean the tab and put the formula again, it didn't change anything. I expected some problem with the auto-refresh

• I tried to reload the folder's content, no effect either, I expected some problem on the first reload of the folder

How I'd like it to be in the end

Answer 1

For Multiple Paths

2024-09-22
Paths could be in a single column or row.

1. In Excel, select the range containing the folder paths and define a name (Formulas > Define Name); in the example I used folderPaths - range containing the paths need
2. Create a blank query: Power Query from menu bar > From Other Sources > Blank Query
3. Click on Advanced Editor and enter code below

let
  fxFileNames = (path) =>
    let
      Source      = Folder.Contents(path), 
      keepNameCol = Table.SelectColumns(Source, {"Name"}), 
      nameList    = Table.ToList(keepNameCol)
    in
      nameList, 

  fxExpandAll = (t as table, optional colNum as number) =>
    let
      colNames = Table.ColumnNames(t), 
      Index    = if colNum = null then 0 else colNum, 
      expand   = Table.ExpandListColumn(t, colNames{Index}), 
      nextCol  = Index + 1, 
      repeat   = if nextCol < List.Count(colNames) then @fxExpandAll(expand, nextCol) else expand
    in
      repeat, 

  paths = Excel.CurrentWorkbook(){[Name = "folderPaths"]}[Content],
  pathsAsCols = if Table.RowCount(paths) > 1 then Table.Transpose(paths) else paths,
  colNames = Table.ColumnNames(pathsAsCols), 
  nameListInCols = Table.TransformColumns(
    pathsAsCols, 
    List.Transform(colNames, (col) => {col, each fxFileNames(_)})
  ), 
  expandAll = fxExpandAll(nameListInCols)

in
  expandAll

4. Rename the query as for example getFileNamesInColumns
5. Click Done and then Close & Load

Please refresh when you update the paths or the range (from Query > Refresh)

I have used Ron's recursive function for expanding columns.

---

Get File Names with Power Query in Excel 2016

1. Install Power Query from Download Microsoft Power Query for Excel from Official Microsoft Download Center
2. In Excel, select the cell containing the folder path and define a name (Formulas > Define Name); in the example I used folderPath
3. Create a blank query: Power Query from menu bar > From Other Sources > Blank Query
4. Click on Advanced Editor and enter code below

let
  path        = Excel.CurrentWorkbook(){[Name = "folderPath"]}[Content]{0}[Column1],
  Source      = Folder.Contents(path),
  keepNameCol = Table.SelectColumns(Source, {"Name"}),
  sortedNames = Table.Sort(keepNameCol, {{"Name", Order.Ascending}})
in
  sortedNames

5. Click Done and then Close & Load

See the whether names are sorted as exepected.

Results from Power Query need to be manually refreshed whenever there's a change (either to the folder path or to the contents of the folder.)