rogersp188
New member
- Joined
- Mar 3, 2019
- Messages
- 6
- Reaction score
- 0
- Points
- 0
- Location
- Denver, CO
- Excel Version(s)
- 2016 x64
All,
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:
and via OData
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:
Code:
// SP-Files_ExcelFilesOnly
let
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)
in
#"Filter ASU for Most Recent"and via OData
Code:
// ASU via OData
// an example of how to use the generic OData Feed connector type to query sharepoint via the _api directory
let
Source = OData.Feed("https://sharepoint/sites/Investments/_api/"),
#"files_function () as table" = Source{[Name="files",Signature="function () as table"]}[Data]
in
#"files_function () as table"