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.


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

func =
// fnGetAllFilesInSharepointFolder
(FullPath as text) =>
// Helper function
fnUriUnescapeString =
(data as text) as text =>
ToList = List.Buffer(Text.ToList(data)),
Accumulate = List.Accumulate(
[Bytes = {}],
(state, current) =>
HexString = state[HexString]?,
NextHexString = HexString & current,
NextState =
if HexString <> null then
if Text.Length(NextHexString) = 2 then
= state[Bytes]
& Binary.ToList(Binary.FromText(NextHexString, BinaryEncoding.Hex))
[HexString = NextHexString, Bytes = state[Bytes]]
else if current = "%" then
[HexString = "", Bytes = state[Bytes]]
[Bytes = state[Bytes] & {Character.ToNumber(current)}]
FromBinary = Text.FromBinary(Binary.FromList(Accumulate[Bytes]))
StaticRoot = SharePoint.Contents(RootPath, [ApiVersion = 15]),
ExtractRoot = fnUriUnescapeString(Text.BeforeDelimiter(FullPath, "/", 4)),
NonRootFolders = fnUriUnescapeString(Text.AfterDelimiter(FullPath, ExtractRoot)),
SubfoldersList = List.Buffer(List.Select(Text.Split(NonRootFolders, "/"), each _ <> null and _ <> "")),
GetRootContent = StaticRoot,
NavigateIn = Table.Buffer(List.Accumulate(
(state, current) => state{[Name = current]}[Content]
ListGenerate = List.Generate(
() => [
SelectFurtherExpansion = Table.RemoveColumns(
each Type.Is(Value.Type([Content]), type table)
each ([ExpandFurther.1] = true)
Result = Table.ExpandTableColumn(
{"Content", "Name", "Extension"},
{"Content", "Name.1", "Extension"}
Counter = 1,
NextIteration = true
each [NextIteration],
each [
SelectFurtherExpansion = Table.SelectRows(
"ExpandFurther." & Text.From([Counter] + 1),
each Type.Is(Value.Type([Content]), type table)
(x) => (Record.Field(x, "ExpandFurther." & Text.From([Counter] + 1)) = true)
RemoveExtension = Table.RemoveColumns(SelectFurtherExpansion, {"Extension"}),
Result = Table.ExpandTableColumn(
{"Content", "Name", "Extension"},
{"Content", "Name." & Text.From([Counter] + 1), "Extension"}
Counter = [Counter] + 1,
NextIteration = try Table.RowCount([Result]) > 0 otherwise false
each [Result]
Combine = Table.Combine(ListGenerate),
FilesInRoot = Table.SelectRows(NavigateIn, each Type.Is(Value.Type([Content]), type binary)),
FullResults = FilesInRoot & Combine,
#"Filtered Rows" = Table.SelectRows(
each ([Extension] <> "" and [Extension] <> null)
AddNameFields = Table.AddColumn(
#"Filtered Rows",
each List.Select(
List.Select(Record.FieldNames(_), (x) => Text.Contains(x, "Name"))
(y) => y <> null
AddFileName = Table.AddColumn(AddNameFields, "FileName", each List.Last([NameFields])),
AddSubFolder = Table.AddColumn(
each Text.Combine(List.RemoveLastN([NameFields], 1), "/")
#"Removed Columns" = Table.RemoveColumns(AddSubFolder, {"NameFields"})
#"Removed Columns",
documentation = [
Documentation.Name = " Sharepoint.GetAllFilesInFolder ",
= " Imports all files from a SharePoint folder, inclusive subfolders. ",
= " Imports all files from a SharePoint folder, inclusive subfolders. !! Root path to SP file has to be hardcoded in the function code itself !! ",
Documentation.Category = " Accessing Data Functions ",
Documentation.Source = ", see: . ",
Documentation.Version = " 1.2: 30-Mar-2021-ImprovedSpeed ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ", Code = " ", Result = " "]}
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Get single file

  func = 
    // fnGetSharepointFile
      Source = (FullPath as text) => 
          // Helper function
          fnUriUnescapeString = 
(data as text) as text => 
              ToList = List.Buffer(Text.ToList(data)),
              Accumulate = List.Accumulate(
                  [Bytes = {}], 
                  (state, current) => 
                      HexString = state[HexString]?,
                      NextHexString = HexString & current,
                      NextState = 
                        if HexString <> null then 
                          if Text.Length(NextHexString) = 2 then 
                                = state[Bytes]
                                  & Binary.ToList(
                                    Binary.FromText(NextHexString, BinaryEncoding.Hex)
                            [HexString = NextHexString, Bytes = state[Bytes]]
                        else if current = "%" then 
                          [HexString = "", Bytes = state[Bytes]]
                          [Bytes = state[Bytes] & {Character.ToNumber(current)}]
              FromBinary = Text.FromBinary(Binary.FromList(Accumulate[Bytes]))
          StaticRoot = SharePoint.Contents(RootPath, [ApiVersion = 15]),
          ExtractRoot = fnUriUnescapeString(Text.BeforeDelimiter(FullPath, "/", 4)),
          FileName = fnUriUnescapeString(
              Text.AfterDelimiter(FullPath, "/", {0, RelativePosition.FromEnd})
          NonRootFolders = fnUriUnescapeString(
                  Text.AfterDelimiter(FullPath, ExtractRoot), 
                  {0, RelativePosition.FromEnd}
          SubfoldersList = List.Buffer(List.Select(Text.Split(NonRootFolders, "/"), each _ <> null and _ <> "")),
          NavigateIn = List.Accumulate(
              (state, current) => state{[Name = current]}[Content]
          #"Filtered Rows" = Table.SelectRows(NavigateIn, each ([Name] = FileName))[Content]{0}
          #"Filtered Rows"
  documentation = [
    Documentation.Name = " Sharepoint.GetFile ", 
    Documentation.Description = " Convenient way to get SP file by entering full URL. ", 
      = " Convenient way to get SP file by entering full URL. !! Root path to SP file has to be hardcoded in the function code itself !! ", 
    Documentation.Category = " Accessing Data Functions ", 
    Documentation.Source = ", see: . ", 
    Documentation.Version = " 1.2: 30-Mar-2021-ImprovedSpeed ", 
    Documentation.Author = " Imke Feldmann ", 
    Documentation.Examples = {[Description = " ", Code = " ", Result = " "]}
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

How to use



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 (20) 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.


    • 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 :))


  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)
    SPRequestGuid=c5a68e9f-1047-0000-723c-982e6e3a9ab1, c5a68e9f-c04a-0000-5ecf-fd9d000ca298, c5a68e9f-804d-0000-7aa3-b1c94e710ee7


    • @Gustavo

      Did you use “” explicitly as as the RootPath?


  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 (, lets assume the files are located on a subsite (

    RootPath =

    StaticRoot = SubSite Contents
    ExtractRoot = MAIN SITE path (fourth slash) (generates error) (
    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.


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


  4. Hello Imke,
    I just tried the GetSharePointFile for the single file out and already now it feels much faster for me.
    Two questions:
    a) I would need to adjust the RootPath Parameter for each file coming from a different source, right?
    I. e. if I pull files from 4 different sharepoint sites, I would need to have four different values in that Parameter, so changing it between the queries?

    b) forgive me if this is a totally stupid question, but in this second function, why can’t I reuse the FullPath with a formula to get the base instead of having to rely on the RootPath parameter?
    I.e. for our sharepoint site I would write

    StaticRoot = SharePoint.Contents(Text.Start(FullPath, Text.PositionOf(FullPath, “/”,5){4}+1), [ApiVersion = 15]),

    instead of

    StaticRoot = SharePoint.Contents(RootPath, [ApiVersion = 15]),

    What am I missing here?


    • And you see I am new, so I didn’t know the Before.Delimiter function, as that can be part extracting the RootPath could be writen as Text.BeforeDelimiter(Fullpath, “/”, 4)


    • Hello Ralph,
      a) yes, that’s correct
      b) Yes, you can hardcode your specific path in the function itself, that’s not a problem.


      • Hi Imke,
        thanks for the fast reply.
        for b) – If I can do that – i. e. replace the RootPath parameter in line 42 with the FullPath (parameter passed to the function), why would I still need the RootPath?
        Wouldn’t it be enough to switch the line 42 and 43 and replace the parameter RootPath with ExtractRoot to then have
        ExtractRoot = fnUriUnescapeString(Text.BeforeDelimiter(FullPath, “/”, 4)),
        StaticRoot = SharePoint.Contents(ExtractRoot, [ApiVersion = 15]),

        Why would this not work? [and sorry again if I am being dense here]


  5. Hi Imke,
    I can’t begin to tell you how much this helped! We have what we are now guessing are large SharePoint sites (not sure how to quantify that in relation to others), and this took not only refreshes but simply working in the file WAY DOWN in time. Been searching for a solution many times previously and found this the other day.
    One file, that I used the Folder connector above, we would literally walk away from while it did a refresh (25 minutes or so) and it is now under 4.
    Another file (also folder connector) that was 2+ minutes is now 40 seconds.
    And finally, one that I used the single file connector above, that varied between 4-10 minutes is now literally seconds (this last one is simple easy spreadsheets with just combing 9 tabs, so not complex at all, but if the query had to sort through thousands of SharePoint files first…).



  6. God bless you! Finally found a reliable working solution here to extract from Sharepoint (or Sh@tpoint) to PowerBI Service without the crazy click-fest of repeated failed refresh attempts. We have 30,000 tiny files of 4KB CSV data that the default Sharepoint Folder just could no longer handle reliably at all. About 6 months ago, it could load 7500 files after a few attempts, but now can’t even handle 3000 tiny files. What a waste of 100+ hours troubleshooting this … MS innovation? … yeah, right!


  7. Hi Imke,

    Back again 🙂

    Just a small suggestion to the GetAllFiles function – to replace the “Date created” and “Date modified” columns as it burrows down the tree – just like what you have done for “Extension” column.. At the moment, the function will only show the dates of the top level parent node instead of the dates from the child objects. I just modified your script in the “ListGenerate” step to get the desired dates.

    Thanks again for this awesome solution.
    Woop woop!


  8. Works very well. Thank you for sharing this with us.

    Keep your good work up.



  9. Works nice! One question though: If I want to add more columns ( such as Author etc) where should I add these in the code?
    thank you !


  10. Dear Imke, great content! Thank you.
    I got the same error you got when you clicked to expand the table: “Formula.Firewall: Query ‘Invoked Function’ (step ‘Expanded Table Column1’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
    How to overcome this issue?


      • I got the same error. Delete the last step in the Invoked Function and than in the previous step expand the table. It worked.


        • Thanks for posting this. Never had this problem, but good to know an alternative!


Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: