Forum Discussion

ADTRE's avatar
ADTRE
Frequent Visitor
1 year ago

Filenames containing '#' on SharePoint site

Hi. I am connecting to all files in multiple folders accross a single SharePoint site for my Power-BI report using PowerQuery. Part of the code is: let Source = SharePoint.Files("https://NAME.sha...
Need Help
aduguid's avatar
aduguid
Memorable Member
1 year ago

You could exclude these based on the name

 

let
    // Connect to SharePoint folder
    Source = SharePoint.Files("https://NAME.sharepoint.com/sites/Resource", [ApiVersion = 15]),

    // Filter to include only files from the desired folder
    Folder1 = Table.SelectRows(Source, each Text.EndsWith([Folder Path], "Assessment/")),

    // Exclude files with "#" in the file name
    FilteredFiles = Table.SelectRows(Folder1, each not Text.Contains([Name], "#"))
in
    FilteredFiles
ADTRE's avatar
ADTRE
Frequent Visitor
1 year ago

The files that I need to import don't have any '#' in their names. It is the larger extent of the entire SharePoint site where users keep adding filenames with '#' (or '$' or '%'). 


So, just the first instance of the query ( Source = SharePoint.Files("https://NAME.sharepoint.com/sites/Resource", [ApiVersion = 15]) ) ends up with an error because of wrong symbols in file names. And these files are not part of my report, but just their existence in the SharePoint site results in error. Currently I manually rename these files each morning and that refreshes the report fine.