Forum Discussion

ManchevB's avatar
ManchevB
Icon for Helper II rankHelper 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
  • Anonymous's avatar
    Anonymous
    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.

     

ManchevB's avatar
ManchevB
Icon for Helper II rankHelper II
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
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
    Icon for Helper II rankHelper 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.