Enable Power Query to Get Data from Excel workbook stored in SharePoint Online document library
Excel Power Query does not provide an Excel workbook stored in SharePoint Online (SPO) document libraries as a data source. In response to Community discussion, Microsoft suggested a work-around to sync the document library that stores the data source (Excel workbook) with OneDrive for Business, then Get Data from the ODFB copy of the data source. That works, but not for our use case.
Our use case: We are a very small company with only 1 power user who is proficient with Excel power tools. We want that user to develop the Excel workbooks (that use power tools) that would be available to all users. We also want those workbooks to serve as "mini-apps" that pull data from other sources (e.g., Access databases, Excel workbooks). That has worked well to date when the data sources are stored on our on-premise file server. But, we are moving content from on-premise file server to SPO document libraries, and Power Query cannot use Excel workbooks stored there as data sources.
Example: Our bookkeeper exports QuickBooks reports to Excel files (data sources). We have a separate Excel workbook ("mini-app") that extracts & transforms the data from the data source workbook. We have other users besides our bookkeeper who open the mini-app, refresh, & use the output data. It works fine when the data source is on the file server, but not when stored on SPO. If our bookkeeper were the only mini-app user, then the suggested work-around would be OK (not ideal). The work-around does not work for other mini-app users.
Sergei Baklan commented
Instead of built-in function you may write your own to pick-up the object from Excel file with siteURL, filename and objectName parameters, that's not necessary to repeat all steps every time.
I guess the issue here is since Sharepoint Online operates with files IDs, not with file names directly, and we have to map somehow one on another.
Anyway, glad to help.
Jim Fitch commented
I tried your suggestion, and it works, so thank you ... greatly appreciated!!!
I still think that this is harder than it needs to be. This requires multiple applied steps whereas the "Get Data" step to source data from an Excel workbook stored on a shared drive is very straightforward. Microsoft generally encourages organizations to store shared files in SharePoint (or Teams Files, which also is SharePoint), so the pre-loaded steps (i.e., Get Data menu items) to do that ought to be as easy for SharePoint-stored files as it is for file shares.
That said, my original suggestion is invalid. I'll close it down. Thanks again.
Sergei Baklan commented
Jim, Power Query works fine with Excel files on Sharepoint Online sites. You may google for technical details, in general the query is like
Sharepoint.Files(<root site URL>)
Filtrer file by name
Get binary contend
Get object from file (table or sheet)