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.sharepoint.com/sites/Resource", [ApiVersion = 15]),
Folder1 = Table.SelectRows(Source, each Text.EndsWith([Folder Path], "Assessment/"))
in
Folder1


 

The issue is that there are files sitting outside of the folder (in the main SharePoint site) that are not used in the report all together whose name contain the '#' symbol. This causes a blank error to generate which then causes report refresh failure. I have considered a few options:

  1. Rename filenames with '#' to something else - not suitable because there are 100s of users working accross the organisation and people end up adding new files with the symbol almost daily.
  2. Connect to the file path directly - not suitable because my report uses all files in a few specific folders within the SharePoint site (so I have to first connect to the main SharePoint site first, which ends up bringing a list of filenames containing '#' symbol).
  3. Add "Remove Errors" to the 'Binary content' column of the code - this drastically slows down data import and report refresh (slows it down so much that a regular 5 minute refresh ends up being over an hour long, or otherwise failing all together)

Plese let me know if there's any way to work-around this error related to filenames having '#' symbol. 

Need Help

4 Replies

  • christinepayton's avatar
    christinepayton
    Most Valuable Professional
    1 year ago

    I just want to point out here that SharePoint really doesn't like the # symbol in filenames either. It causes all kinds of problems, so you might want to recommend that users not do that if it's part of their standard practice right now. 

    • ADTRE's avatar
      ADTRE
      Frequent Visitor
      1 year ago

      These things are frequently advised to staff, but people end up forgetting regularly.

  • 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.