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

[ManchevB]

Hello,

I have a PBI report, where it is connected to a couple of Stored Procedures, a few Excel files sitting on SharePoint locations and one connection with a semantic model.

I am able to refresh that in Desktop, however when I publish that in a workspace, I get the following error:

I understand that using .xlsb files in tha scenario might be problematic, but the same files are used in another PBI report which refreshes in a workspace just fine. I tried some troubleshooting myself and downloaded Acess Database x64 (to match the version of my PBI), did the installation, refreshed my computer but still got the same error. 

The problem is that I cannot convert the .xlsb files to .xlsx as this is a requirement in our company and I need to make it work using .xlsb files + scheduled refresh

Any help here would be greatly appreciated

[v-sshirivolu]

Hi @ManchevB ,
Thanks for reaching out to the Microsoft fabric community forum.

 

If you're working with .xlsb files in Power BI and facing issues with refreshes, there are a few ways :

Option 1 is to use a gateway with on-premises data. This is necessary if you must stick with .xlsb  files. You’ll need to install and configure an On-Premises Data Gateway on a machine that has the Microsoft.ACE.OLEDB.12.0 provider installed. Make sure both the gateway and the Access Database Engine are installed in 64-bit. Once set up, you can use the gateway to enable scheduled refreshes in the Power BI Service.

Option 2, which is also Microsoft’s recommended approach, is to convert the .xlsb file to a supported format like .xlsx. While the user might mention they can’t convert the file, it’s worth considering that Power BI supports .xlsx files natively, especially when using connectors like SharePoint or Web.Contents. If manual conversion is not feasible, you could automate this step using Power Automate or a scheduled Excel macro on a shared machine.

Option 3 is more of a workaround for .xlsb  files stored on SharePoint. You can try loading the file through the SharePoint Folder connector and then use Power Query to parse the binary content. However, this may still cause issues during refreshes in the Power BI Service. A more reliable workaround would be to sync the SharePoint location locally using OneDrive, automatically convert the .xlsb to .xlsx, and then point Power BI to the .xlsx file using the gateway.

Each approach has its pros and cons, but converting to .xlsx and automating that process usually results in the most reliable and maintainable solution.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[ManchevB]

Do you think that if I use a on premise gateway and stick with the original xlsb it will work? 

[v-sshirivolu]

Hi @ManchevB ,
Yes, it might work if you're using a local file path, proper drivers, and the gateway is configured correctly but it's still a fragile and unsupported setup. If long-term reliability is a concern, converting to .xlsx and automating that step is still the safer route.

Using an on-premises gateway with the original .xlsb file can work, but only under specific conditions.

To make it work reliably:

1. The gateway machine must have the correct driver – specifically, the 64-bit version of the Microsoft Access Database Engine ( which includes Microsoft.ACE>OLEDB.12.0).

2. The path to the .xlsb file must be accessible from the gateway machine ( usually a shared network path like \\Server\Folder\File.xlsb ).

3. Your Power BI Desktop file must be set up to use that same path so it matches during refresh.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[ManchevB]

Thank you, this is very helpful?

Do you think using Power Automate, can help me automate that solution? - as this report will be used as an engine for distributing engineer info.

[v-sshirivolu]

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.

[v-sshirivolu]

Hi @ManchevB ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution and give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

[burakkaragoz]

Hi @ManchevB ,

This won’t work in Power BI Service because the ACE OLEDB driver needed for .xlsb files isn’t available in the cloud. That’s why it works in Desktop but fails after publishing.
There’s no way to add this driver to Power BI Service. If converting to .xlsx isn’t an option for your company, you’re basically stuck—scheduled refresh with .xlsb just isn’t supported.

If you want, you can try automating .xlsb to .xlsx conversion before refresh, but there’s no native support for .xlsb in Service.

[andrewsommer]

The issue stems from using .xlsb files hosted on SharePoint Online. While Power BI Desktop can handle these files locally (because it uses your machine's drivers), the Power BI Service cannot natively refresh them without a gateway and the appropriate OLEDB driver installed on the gateway machine 

Even if another report works with the same .xlsb files, differences in:

• Gateway configuration
• Data source credentials
• File access permissions
• Query structure (e.g., use of Web.Contents or SharePoint.Files)

...can cause one report to fail while another succeeds.

You either need to make sure your gateway has the correct drivers or you could look into switching to use SharePoint API with Web.Contents. 

Please mark this post as a solution if it helps you. Appreciate Kudos.