Power BI

OK, so I was starting to gather data to debate this issue with you, but decided to ask CoPilot first.  I'm amazed I can ask such a generic question of CoPilot and get such a targeted answer.  The suggested code uses the SharePoint.Files connector AND allows the specific filespec without tedius filtering.  So, I'm humbled.

Q: In Power Query, if I open an excel file in a SharePoint document library like this: Excel.Workbook(Web.Contents("https://xxx.sharepoint.com/sites/yyy/zzz/myFile.xlsx"), null, true), will it eventually lead to errors?

A: When you use

Excel.Workbook(Web.Contents("https://xxx.sharepoint.com/sites/yyy/zzz/myFile.xlsx"), null, true)

you’re effectively telling Power Query to pull the binary content of the file directly using a generic web call, then parse it as an Excel workbook. Initially, that might work fine, but over time there are several potential pitfalls:

1. Caching & Stale Data: Power Query’s Web.Contents function implements caching based on the URL and any query parameters. If the file is updated on SharePoint but the URL remains the same, Power Query might still return a cached version. This isn’t just a matter of stale data—it can cause errors if the cached version doesn’t match the file’s current structure.

2. Authentication & Permissions: SharePoint has its own nuances around authentication and authorization. The dedicated SharePoint connectors (like SharePoint.Files or SharePoint.Contents) handle these matters much more gracefully. When you bypass these connectors, you might run into intermittent authentication issues—especially if your organizational policies or tokens change.

3. Structural Changes & Metadata: The Excel.Workbook function expects a certain format. If the file is modified (even slightly) on SharePoint—say, a sheet is renamed or headers change—the direct binary parsing might start throwing errors. With the dedicated SharePoint connectors, you generally get more metadata and context that helps Power Query adapt to changes.

4. Refresh and Query Folding Issues: Using Web.Contents in this way may interfere with some query folding capabilities and scheduled refreshes. This could lead to inconsistencies or errors later, particularly if the file’s source or permissions are updated.

Best Practice Advice: For files stored in SharePoint, it’s wiser to use the built-in SharePoint connectors. For instance, using something like:

let
    Source = SharePoint.Files("https://xxx.sharepoint.com/sites/yyy", [ApiVersion = 15]),
    MyFile = Source{[Name="myFile.xlsx"]}[Content],
    Workbook = Excel.Workbook(MyFile, null, true)
in
    Workbook

This method leverages the SharePoint.Files connector, which is designed to handle the quirks of SharePoint access, such as authentication, caching updates, and file metadata. It makes the data source more robust to changes over time.

Conclusion: Yes, while your approach may initially work, it can eventually lead to errors due to caching, authentication, and file change issues. Switching to a dedicated SharePoint connector method is more reliable, especially if you expect the file to change or if you need consistent refresh behavior.