Forum Discussion

pszpecht's avatar
pszpecht
New Member
1 year ago

Skip source paths that do not exist

I need to create a daily overview of the files for a given month. 

I have a table with various source paths from which the files need to be pulled (I do not need the data which is in the files, but the data about the files themselves). There might be a situation that a path does not exist and the data does not get loaded, unless I remove the invalid path from the table. 

I tried several ways to approach it but it does not work. I always get the error "[DataSource.NotFound] File or Folder: we coulnd' find the folder: xxx"

 

Here is the latest code I tried and I run out of ideas on how to handle it. I'd appreciate support from you a lot.

 

 

let
FolderPathsTable = FolderPathsTable,
BufferedFolderPathsTable = Table.Buffer(FolderPathsTable),
CheckFolderExists = (folderPath as text) as logical =>
try Folder.Files(folderPath) <> null otherwise false,
#"Filtered Rows" = Table.SelectRows(BufferedFolderPathsTable, each Text.Contains([FolderPath], "xxx -")),
ExistingsPaths = Table.SelectRows(#"Filtered Rows", each CheckFolderExists([FolderPath]) = true),
GetFiles = Table.AddColumn(ExistingsPaths, "Files", each Folder.Files([FolderPath])),
CombineFiles = Table.Combine(GetFiles[Files]),
#"Removed Other Columns" = Table.SelectColumns(CombineFiles,{"Name", "Folder Path"})
in
#"Removed Other Columns" 

Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi pszpecht - Update the CheckFolderExists function to handle exceptions for missing folders more gracefully.

    please find the attache pq editor code.

    let
    // Reference the table containing folder paths
    FolderPathsTable = FolderPathsTable,
    BufferedFolderPathsTable = Table.Buffer(FolderPathsTable),

    // Function to check if a folder exists
    CheckFolderExists = (folderPath as text) as logical =>
    try
    Folder.Files(folderPath) <> null
    otherwise
    false,

    // Filter rows to keep only those paths containing "xxx -"
    FilteredRows = Table.SelectRows(BufferedFolderPathsTable, each Text.Contains([FolderPath], "xxx -")),

    // Keep only rows where the folder exists
    ExistingPaths = Table.SelectRows(FilteredRows, each CheckFolderExists([FolderPath]) = true),

    // Add a column to retrieve file metadata for each valid path
    GetFiles = Table.AddColumn(ExistingPaths, "Files", each try Folder.Files([FolderPath]) otherwise null),

    // Remove rows where the "Files" column is null (for invalid paths)
    ValidFiles = Table.SelectRows(GetFiles, each [Files] <> null),

    // Combine file data from all valid folders
    CombineFiles = Table.Combine(ValidFiles[Files]),

    // Select relevant columns for the output
    RemovedOtherColumns = Table.SelectColumns(CombineFiles, {"Name", "Folder Path"})
    in
    RemovedOtherColumns

     

    Modified one.

    Hope this helps.

    • pszpecht's avatar
      pszpecht
      New Member
      1 year ago

      rajendraongole1 I'm impressed by this quick turnaround and really great explanation of the code (thank you!), however, sadly I still get the DataSource.NotFound error.

      Maybe there should be a different approach taken completetly?