Navigating Sharepoint Folders Containing Apostrophes

Hey, LeverPowerCLiff ,

This is kinda big oopsie from the SharePoint site; There's no native clicking way to bypass this. But for you, it's an opportunity to learn a different way how to get your data (perhaps even faster) than the standard way.

 

The error already hints at what SharePoint does in the background, and you can do it just yourself. I kinda wrote a blog about this, so for full context, you can check it here: https://www.vojtechsima.com/post/fix-slow-refreshes-faster-way-to-load-sharepoint-files-in-power-bi

 

But to resolve your issues immediately, you can do this:

let
    // config here
    tenantUrl = "https://daatlers.sharepoint.com",
    siteUrlPath = "sites/Experiments",
    targetFolderServerRelativeUrl = "/" & siteUrlPath & "/Shared Documents/Vojtech's Folder",
    targetFolderServerRelativeUrlQuoteEscaped = Text.Replace(targetFolderServerRelativeUrl, "'", "''"),

    getListOfFiles = 
    Json.Document(
        Web.Contents(tenantUrl, [
            RelativePath = siteUrlPath & "/_api/web/GetFolderByServerRelativeUrl('" & targetFolderServerRelativeUrlQuoteEscaped & "')/Files",
            Headers = [Accept="application/json;odata=nometadata"]
            ]
        )
    ),

 

I entered my own information, so it's clearer for you. This will output a list of records where you have all the files in your SharePoint for the given site and folder. You can also see that I added escaping of the quote character.

 

Then you get a list of files, and for each you get their 'downloadable' path, and that's kinda it, then you can transform them or do anything you need to do with clicking. The example 'extractor' could look like this:

 

    extractServerRelativeUrlAndBuffer = List.Transform(getListOfFiles[value], each [ServerRelativeUrl]),

    retrieveContentForFile = (serverRelativeUrl as text) =>
        let 
            binaryContent = Web.Contents(tenantUrl, [
                RelativePath =  siteUrlPath & "/_api/web/GetFileByServerRelativeUrl('" & Text.Replace(serverRelativeUrl, "'", "''") & "')/$value"
                ]
            ),
            // transformations here
            processedData = binaryContent
        in 
            processedData,

    combineAllFiles = List.Transform(extractServerRelativeUrlAndBuffer, each retrieveContentForFile(_))
in
    combineAllFiles

 

With this approach, you first enter the string to the target folder, you want to access, so you don't go folder by folder, but you directly locate the folder and then you get list of files with their metadata, here you can expand it to table and modify the list with clicking, or if you want everything, just extract the download link and then get the binaries, and then you can treat it as any other file or set of files.

 

Lemme know what you think.