Complex PBI report with microsoft.ace.oledb.12.0 error

Hi ManchevB ,
Yes, Power Automate can absolutely help in this scenario, especially if converting .xlsb to .xlsx is a viable workaround.

Since Power BI Service doesn't support .xlsb refresh directly, you can automate the conversion and upload of the file to SharePoint or OneDrive in .xlsx format using Power Automate, which can then be used reliably in Power BI with scheduled refresh.

1. Trigger: Use a trigger like:

   • “When a file is created or modified” in a local shared folder (via gateway/Power Automate Desktop), or

   • “Recurrence” trigger to run on schedule.

2. Convert the .xlsb to .xlsx:

   • Use Power Automate Desktop to open the .xlsb in Excel and save it as .xlsx.

     • Actions:

       • “Launch Excel”

       • “Open Workbook” (point to .xlsb)

       • “Save As” (choose .xlsx )

       • “Close Excel”

3. Upload/Replace the .xlsx file to SharePoint or OneDrive, where Power BI expects the file.

   • Use “Create file” or “Update file” actions in SharePoint/OneDrive connector.

4. Configure Power BI Report to point to the uploaded .xlsx file in SharePoint/OneDrive.

   • Use “Web” or “SharePoint Folder” connector in Power BI.

   • Schedule refresh in Power BI Service as normal.

• The gateway machine running Power Automate Desktop needs Excel installed.

• This works well for headless automation scenarios (e.g., overnight updates).

This approach gives you an automated pipeline to transform .xlsb - .xlsx - refresh Power BI - distribute engineer data - without needing manual intervention or relying on unsupported data sources in the Power BI Service.