Forum Discussion
Power BI Only Loading Partial Data from Excel Files on SharePoint/OneDrive for Business
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)/
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?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.
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.
