Results 1 to 2 of 2

Thread: Best SharePoint Connector for Speed with OnSite SP 2019

  1. #1
    Seeker rogersp188's Avatar
    Join Date
    Mar 2019
    Denver, CO
    Excel Version
    2016 x64

    Question Best SharePoint Connector for Speed with OnSite SP 2019

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I'm building a complex PowerQuery model with roughly 65+ queries. The majority source CSV or XLSX files on mapped network drives. However I also have to connect to our SharePoint and find the most recent version of an excel file from a specific library.

    I'm using the native SharePoint.Files() connector however this seems to be a massive bottleneck in my model. Unfortunately I need data from 8 different worksheets in this one file. Initially I had built a single query to connect, filter the library contents to the correct file name and file type that is the most recent created and then expand the workbook via Excel.Workbook. From here I have a new query for each worksheet picking up from this connection query, then drilling into the appropriate worksheet and formatting. Finally I reference one of the 8 queries (that is a complete list of all items on all worksheets) and merge query in the appropriate columns from all the various sheets. (I know an append may be an option but the structure of all 8 sheets is not identical).

    This seems to be a huge bottleneck. However after reading about how PowerQuery/PBI/Excel works with cache I've modified the queries to not route through a central connection query but rather included those steps in each of the individual queries.

    I've dabbled with Table.Buffer() and Fast Data Load as well as even realized I could use OData.Feed to use the _API directory to connect to SharePoint.

    Ultimately the question is, does anyone have any advice or suggestions on which combination of options I have would be the fastest? Should I stick with what I have or perhaps has anyone proven that the OData method is faster than the native SharePoint connectors? Does someone have any guidance on the best place to implement a Table.Buffer() ?

    (And I have considered mapping the Library to my system however that introduces potential for issues if my peers using this don't have the Library mapped, nearly none of them will)

    Finally, there are 174 files in this Library and on average the file I'm selecting is 2,400kb.

    Code examples:

    // SP-Files_ExcelFilesOnly
        Source = SharePoint.Files("https://sharepoint/sites/Investments/"),
        #"Filter SP site for Excel files" = Table.SelectRows(Source, each ([Extension] = ".xls" or [Extension] = ".xlsm" or [Extension] = ".xlsx")),
        #"Filter Excel files for ASU" = Table.SelectRows(#"Filter SP site for Excel files", each Text.StartsWith([Name], "ASU")),
        #"Filter ASU for Most Recent" = Table.SelectRows(#"Filter Excel files for ASU", let latest = List.Max(#"Filter Excel Files for ASU"[Date created]) in each [Date created] = latest)
        #"Filter ASU for Most Recent"

    and via OData
    // ASU via OData
    // an example of how to use the generic OData Feed connector type to query sharepoint via the _api directory
        Source = OData.Feed("https://sharepoint/sites/Investments/_api/"),
        #"files_function () as table" = Source{[Name="files",Signature="function () as table"]}[Data]
        #"files_function () as table"

  2. #2
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Excel Version
    Excel 2016 ProPlus
    Can't be of much help but Chris Webb's blog might be,

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts