Skip source paths that do not exist

[pszpecht]

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" 

[rajendraongole1]

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]

@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?