Handle when Folder.Files returns folders that do not exist

380 views Asked by At

I want Power Query to search the entire C: drive to find all files of a particular extension, such as *.pdf. When I try to filter the file extension in the second step I'm getting this error:

DataSource.NotFound: File or Folder: We couldn't find the folder 'C:\Program Files (x86)\Microsoft Office\root\vfs\Common AppData\Application Data'.
Details:
    C:\Program Files (x86)\Microsoft Office\root\vfs\Common AppData

The problem is, this folder doesn't exist. Why is Power Query returning a folder path that is invalid? The folders being returned by PowerQuery seem to be links to the folders in the C:\ProgramData folder.

The following M script (limited to the problem area) returns 999+ rows on the the first step. If I run dir /s /ah from the same folder from the command prompt, it only returns 20 files.

let
    Source = Folder.Files("C:\Program Files (x86)\Microsoft Office\root\vfs\Common AppData"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".pdf"))
in
    #"Filtered Rows"

My end goal is use Excel Power Query to find all files of a particular extension on the entire C: drive and return their filename, path, and size. Note that dir c:\*.pdf /b /s > list.txt will not return file sizes.

I have Windows 10 64 bit and Office 365 32-bit installed.

0

There are 0 answers