Improve File Import from SharePoint in Power BI and Power Query

When you use the UI to import files from SharePoint, you’ll end up with the Sharepoint.Files function. This function can become fairly or super slow when you use it on large SharePoint sites. This is due to the fact, that it will retrieve metadata for ALL files that lie on the site. Meaning: The root site whose URL you have to enter as the function argument. So I’ve developed a better way for File import from SharePoint.

Alternative

A faster alternative is the function SharePoint.Contents. This function will read much less metadata and that seems to make it faster. But it comes with a different navigation experience: It basically only allows to select files from one folder.

Therefore I’ve created 2 functions that overcome those limitations.

SharePoint.GetAllFilesInFolder and Sharepoint.GetFile

Sharepoint.GetAllFilesInFolder allows to enter a folder path and will trigger the import of all the files from that folder including all subfolders. So you don’t have to navigate to all of them individually or create multiple queries for them.

The functions reference a parameter or query named “RootPath”. This needs to exist or being created in your file. Unfortunately it cannot be passed as an ordinary function parameter, as it would cause refresh problems in the service. The root path is basically the name of your SP site, generally everything before the 5th slash (“/”) in the URL. To get the syntax right, you should retrieve the folder name from the details-section like so:

Improve File Import from SharePoint in Power BI and Power Query

Get URL for SharePoint files and folders

The second function Sharepoint.GetFile is a convenient function if you want to import one specific file. It allows you to simply enter the full path to the desired file. No need for manual navigation. Also, this can speed up the load process as well, as navigation steps that are manually created, can slow down the import as well.

Function Code

Get all files from folder

Get single file

How to use

 

Considerations

Both functions use the fast SharePoint.Contents function (it took my queries to run half as slow than before) and use some dynamic logic to retrieve the data. But as a side effect, the formula firewall seems to kick in sooner. So there might be some query redesign necessary if you’re going to use this function on existing models.

These are a pretty new function and I haven’t tested it extensively yet. But the potential performance gain will probably strongly depend on the size of the SharePoint sites you’re running them on.

I’d be curious to hear your experiences with these functions.

Enjoy & stay queryious 😉

Comments (7) Write a comment

  1. “Unfortunately it [the root path] cannot be passed as an ordinary function parameter, as it would cause refresh problems in the service.”

    Do you have enough information to explain that? That’s hard to grasp.

    Reply

    • Hi Frank,
      unfortunately I have zero information about this apart from my own experience when trying to publish (and I’ve tried it a couple of times :))
      /Imke

      Reply

  2. Hallo Imke! Tks for sharing! Did you had some experience like this?

    An error occurred in the ‘’ query. DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
    OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
    OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
    OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)
    Details:
    DataSourceKind=SharePoint
    DataSourcePath=https://gusta.sharepoint.com/sites/MaxHelpBI2/Shared%20Documents/DadosParquet
    SPRequestGuid=c5a68e9f-1047-0000-723c-982e6e3a9ab1, c5a68e9f-c04a-0000-5ecf-fd9d000ca298, c5a68e9f-804d-0000-7aa3-b1c94e710ee7

    Reply

    • @Gustavo

      Did you use “https://gusta.sharepoint.com/sites/MaxHelpBI2” explicitly as as the RootPath?

      Reply

  3. Hi Imke,

    Thank you! Good work as always!

    I am having problems with using the Sharepoint.GetAllFilesInFolder.pq function on Sharepoint subsites.

    Using the following example of a main site (https://xyz.sharepoint.com/sites/MainSite), lets assume the files are located on a subsite (https://xyz.sharepoint.com/sites/MainSite/SubSite).

    RootPath = https://xyz.sharepoint.com/sites/MainSite/SubSite

    StaticRoot = SubSite Contents
    ExtractRoot = MAIN SITE path (fourth slash) (generates error) (https://xyz.sharepoint.com/sites/MainSite).
    NonRootFolders = “SubSite” & folders
    SubfoldersList = {“SubSite”,…}
    GetRootContent = SubSite Contents
    NavigateIn = error (Cant find “SubSite” folder – not present in subsite……..).

    For ExtractRoot would to following work?:

    Text.BeforeDelimiter(StaticRoot[Folder Path]{0},”/”,{0, RelativePosition.FromEnd})

    Gets Folder from StaticRoot and removes last slash.
    Don’t need fnUriUnescapeString
    StaticRoot may need Table.Buffer – Not sure…….

    instead of:
    fnUriUnescapeString(Text.BeforeDelimiter(FullPath, “/”, 4))?

    I assume the same would work for single file function.

    Reply

    • Thank you!
      Sorry that you’ve ran into trouble with the function and thanks for sharing your workaround.
      Just keep in mind that we still need this UriKUnescapeString-function for the NonRootFolders – step in case the subfolders have spaces in them (they don’t get autocorrected like the name of the site).
      StaticRoot wouldn’t benefit from a buffer here, as it will only be evaluated once as it is.
      /Imke

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz