Forum Discussion

ManchevB's avatar
ManchevB
Helper II
1 year ago
Solved

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

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 re...
Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    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's avatar
v-sshirivolu
Community Support
1 year ago

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's avatar
    ManchevB
    Helper II
    1 year ago

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

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      1 year ago

      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's avatar
        ManchevB
        Helper II
        1 year ago

        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.