topic Re: Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business in Power Query

Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business

alasharimm — Sat, 14 Sep 2024 15:06:52 GMT

Hello Power BI Community,

I'm encountering two major issues when trying to load data from Excel files stored in OneDrive for Business (via SharePoint Files in Power BI):

Details:

I am using SharePoint.Files API version 15 to connect to Excel files in OneDrive for Business. The files are automatically combined using the File Combination process in Power Query.
New Excel files are added automatically to a specific folder on OneDrive Business. These files have an identical structure (same headers) but contain different data.
Issue 1: Power BI is lagging behind in recognizing newly added Excel files, even though they appear on OneDrive (both in the desktop sync and on the web version).
Issue 2: Once Power BI recognizes the new files, partial data is loaded. Some rows or columns are missing, even though the data is fully present in the Excel files when opened manually.
I’ve tried using Power BI Dataflows as a workaround, but I’m encountering the same issues with delay in file recognition and partial data loading.

What I've Tried:

Using SharePoint.Files and Excel.Workbook to combine files.
Ensuring file structure consistency (no hidden rows, merged cells, etc.).
Manually downloading the files and loading them locally—this works perfectly, but I want to avoid the manual process.
Clearing cache and disabling parallel loading in Power BI options.
Clearing and reapplying global and current file permissions to make sure nothing was wrong with the access.
Disabling any filters in Power Query and checking the steps to ensure nothing is being inadvertently filtered or removed.
Monitoring Power BI's refresh schedule and OneDrive's file sync process.

Additional Information:

The dataset isn’t very large, and this issue only occurs with recently added Excel files. Older files are loading without issues.
I'm not using Dataflows regularly, but I’ve attempted to use them to mitigate the problem, only to encounter the same issue.

Questions:

Why does Power BI lag in recognizing newly added Excel files in OneDrive/SharePoint?
How can I ensure that Power BI always captures new files as soon as they are added?
What could be causing the partial data load from these files, and are there any known issues related to SharePoint or OneDrive APIs in this context?
Are there any best practices or optimizations I can apply to ensure that Power BI can load and combine files efficiently without these delays?

Thank you for any guidance or solutions you can provide!

Re: Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business

lbendlin — Sat, 14 Sep 2024 17:18:14 GMT

1. Not sure it's Power BI that is lagging. Could be SharePoint too. Consider using Sharepoint.Contents , and also consider using Binary.Buffer

2. Use Power Automate to initiate semantic model refresh when a new file is added to the document library. Make sure to debounce.

3. Binary.Buffer might help with that

4. Don't use Excel files. Use CSV or Parquet. Write your own combiner (don't use the default option in Power Query)/

Re: Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business

alasharimm — Sat, 14 Sep 2024 19:12:32 GMT

can you explain what the significance of using sharepoint.contents over sharepoint.files.

can you also explain what binary.buffer is and what parquet is?

Re: Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business

lbendlin — Sat, 14 Sep 2024 20:43:27 GMT

SharePoint.Contents : slightly better performance as it directly addresses a folder rather than enumerating the entire sharepoint document library folder structure

Binary.Buffer: Prevents SharePoint from trying to be cute with sending data chunked. Rather orders Sharepoint to deliver the entire file in one piece

Parquet: Data storage format. Different from CSV (which is organized in rows), Parquet is organized in columns, and carries meta data. It is the foundation for the Direct Lake format used in Microsoft Fabric. Generally CSV and Parquet files ingest very fast, regardless of their size.

Re: Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business

alasharimm — Sat, 14 Sep 2024 20:51:29 GMT

Just to clarify how I’m gathering the data: Due to limited infrastructure in the company, the only way I can collect data is by having the ERP system send me scheduled imports via email in XLSX format. These files are then stored in a OneDrive Business folder through Power Automate, and the rest of the process follows from there.

im new to Parquet first time hearing about it now.. how can i convert to using parquet or in the powerautomate phase convert the xlsx file to csv before storing them in the onedrive folder?

as far as the binary buffer and sharepoint.contents solution i'll get back to you on whether that suffices or not.

Re: Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business

lbendlin — Sun, 15 Sep 2024 00:03:34 GMT

See if you can get CSV extracts from your ERP system instead.