Microsoft.ACE.OLEDB.12.0' errorwhile updating report on power bi service

[yamina]

Hello 

I have this message every monday while I try to refresh my report on power bi service. I have already uploaded microsoft .ACE.oled.12.0', but it doesn't solve the problem. To update my report I need to open the file via power bi desktop. 

Erreur de source de données:	DataSource.NotFound: Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site:  Fichiers Sources.
URI du cluster:	WABI-NORTH-EUROPE-I-PRIMARY-redirect.analysis.windows.net
ID d'activité:	963e54dd-e4df-4744-b5e1-3b2e0c430d6f
ID de demande:	5be7d3fe-bcb9-36d2-3685-aa5da22540a6
Heure:	2024-12-23 08:26:37Z

[v-hashadapu]

Hi @yamina , We are closing this thread as we haven't heard from you in a while, according to our follow-up policy. If you have any more questions, please start a new thread on the Microsoft Fabric Community Forum. We will be happy to assist you! Thank you for being part of the community!

[v-hashadapu]

Hi @yamina , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details. Thank you.

[yamina]

hi @v-hashadapu , 

I check all the points. The source is on sharepoint folder which contain excel files. I think I have a problem with the he Data Source Credentials but I can't find the way to set it ( ? Navigate to Settings > Data Source Credentials). I already use power automate to refresh my report on power bi service.  

[v-hashadapu]

Hi @yamina ,
If the issue is with the Data Source Credentials, we can set the data source credentials properly for the SharePoint folder by following the below steps:

1. Ensure that Power BI is set to use your default web browser for authentication.
2. Open PowerBI Service -> Go to Settings -> Select Datasets -> Select the Dataset name -> Edit permissions or Data source Credentials depending on your version.
3. If there are any existing credentials, click on Clear Permissions. Enter the correct credentials for the SharePoint folder. Click Apply to save the changes.
4. Go back to your report and try refreshing the dataset again.

If this helps, please mark it 'Accept as Solution', so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[yamina]

Hi @v-hashadapu

This is what I see when I go to setting in power bi service ,  I don' t find dataset . 

[v-hashadapu]

Hi  @yamina , thank you for reaching out to the Microsoft Fabric Community Forum.

Go to workspaces -> Select your workspace -> Click on three dots (ellipsis) beside the semantic model of your report -> select settings -> Click on Data Source Credentials -> Edit Credentials.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[yamina]

hi @v-hashadapu 

Thank you for you clear explanation.  I 

I manage to update the data source credentials but it seemed to be well configured. Let's see on next monday if the report wil be refresh correctly.  

Have a good weekend. 

[v-hashadapu]

Hi @yamina , Happy to help. Please share the details, if it didn't work.

If it does, Please consider marking it 'Accept as Solution' so others with similar queries may find it easily.

[yamina]

unfortunately, the problem is still presents. 

in power bi service I have this error. 

"Erreur de source de donnéesDataSource.NotFound: Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: . Microsoft.Data.Mashup.ErrorCode = 10478. . The exception was raised by the

In power bi desktop I have this error

I have already uploaded and install  The 64-bit version of the Access Database Engine OLEDB 

I really don't understand. 

[v-hashadapu]

Hi @yamina , thank you for reaching out to the Microsoft Fabric Community Forum.

1. Ensure the URL points to the root of the SharePoint site and does not directly reference a specific file path. Confirm folder structure and file references match the actual SharePoint hierarchy.

Power BI Desktop file -> Transform Data -> Advanced Editor

2. If you're directly connecting to an Excel file, you may need to replace the query logic. Instead of opening the file using Excel.Workbook(), re-import it via the Folder or Web API approach.

Example: Instead of

Source = Excel.Workbook(File.Contents("C:\localpath\filename.xlsx"))

Use

Source = SharePoint.Files("https://company.sharepoint.com/sites/TeamSite", [ApiVersion = 15]),

Filtered = Table.SelectRows(Source, each Text.Contains([Name], "filename.xlsx")),

FileBinary = Filtered{0}[Content],

ExcelData = Excel.Workbook(FileBinary, null, true)

3. Save and publish to Power BI service.
4. Open Power BI Service. Navigate to the dataset and click Refresh Now. Monitor for any errors in the Refresh History section.
   
   

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[yamina]

Hi @v-hashadapu 

I have already check this point also 

But I find a way to refresh the report, by opening the new excel file in the folder and save it. After I open the report in power BI desktop and update the  datas. It works But i need to refresh it by power bi service. 

[v-hashadapu]

Hi @yamina ,

If the path format, data source credentials, and SharePoint Folder Connector are correct, the issue lies elsewhere. Please consider below steps:

1. Verify that the account used for data refresh in Power BI Service has the necessary permissions on the SharePoint folder. Ensure the account has read access to the folder and files.
2. If an on-premises data gateway is in use, ensure it is properly configured and updated.
3. Confirm that the Excel files in the SharePoint folder have not changed permissions or become locked. Open the files in Excel Online to ensure they can be accessed without issues.
4. Try recreating the connection in Power BI Desktop and publishing it again.
5. Check the M code in the Advanced Editor to ensure there are no hardcoded paths or outdated references.
6. Ensure that Power BI Service and Power BI Desktop are updated to the latest versions. Sometimes updates include fixes for data source issues.
7. If this issue started after a specific Power BI update, it may be worth reporting the issue to Microsoft support, as it could be a bug that was introduced in the latest release.  They might be able to investigate the issue further or provide a solution or workaround. Below is the link to create Microsoft Support ticket:
   How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn
   
   If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
   Thank you.

[yamina]

hi @v-hashadapu 

1 point :  I have other pbi report connected to other files in this folder and it works. 

2 point :  The gateaway is correctly configured

3 point :  I think that the problem is here because when I open the file in excel and save it I can update the report on power bi desktop only. 

4 point Alread cheked

5 point ; How can I identify hardcoded paths or outdated references? 

6 point ; for power BI desktop I worked on the novembre 2024 version. 

thank you for Helpinh me. 

[v-hashadapu]

Hi @yamina ,

Please refer below steps to check the M code in the Advanced Editor for any hardcoded file paths or outdated references to the Excel file.

1. Power BI report -> Transform Data -> Advance Editor
2. You will see the M code that defines how Power BI connects to and transforms your data.
3. If you see hardcoded paths, replace them with dynamic paths using the SharePoint.Files function as shown in the below example:

Shown: Source = Excel.Workbook("C:\path\to\your\file.xlsx")

Replaced: Source = SharePoint.Files("https://company.sharepoint.com/sites/TeamSite", [ApiVersion = 15]),

Filtered = Table.SelectRows(Source, each Text.Contains([Name], "filename.xlsx")),

FileBinary = Filtered{0}[Content],

ExcelData = Excel.Workbook(FileBinary, null, true)

4. Look for any outdated references to columns or tables in the Excel file. If the structure of your Excel file has changed, you may need to update the M code to reflect the changes.
5. click Close & Apply to save the changes.
6. Test the query to ensure it's working correctly.
7. Publish the report to Power BI Service and try refreshing it again.
8. see the example screenshots for your reference.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[yamina]

hi @v-hashadapu 

The same mistake remains this morning

My source is correctly connected to my sharepoint

In pb desktop

Here the last file included in my shrepoint 

But when I open the last file in excel and save it, it works only in power bi desktop

[v-hashadapu]

Hi @yamina , thank you for reaching out to the Microsoft Fabric Community Forum.

Please consider below:

1. If the file is being accessed by another process (e.g., OneDrive sync, SharePoint indexing), Power BI might fail to read it properly. Try disabling OneDrive sync temporarily and see if Power BI Service can refresh the dataset. Check if any scheduled flows or scripts are modifying the file.
2. Older versions of SharePoint API (15) may cause inconsistencies. Try explicitly setting the API version to 14 instead of 15 in Power Query:
   let

    Source = SharePoint.Files("https://yourcompany.sharepoint.com/sites/YourSite", [ApiVersion = 14]),

   FilteredFiles = Table.SelectRows(Source, each Text.Contains([Name], "yourfile.xlsx")),

   FileBinary = FilteredFiles{0}[Content],

   ExcelData = Excel.Workbook(FileBinary, null, true)

in

 ExcelData

3. SharePoint sometimes caches old versions of files, which can interfere with Power BI. Go to SharePoint → Library Settings → Versioning Settings and set "Keep only 1 major version". Delete any old versions of the Excel file manually. Try renaming the file, updating the Power BI connection, and then renaming it back.
4. Power BI Service might be using a cached version of the file instead of fetching the latest. Open Power Query in Power BI Desktop and add a dummy transformation, such as:

Source = Excel.Workbook(File.Contents("yourfile.xlsx"), null, true),

AddedColumn = Table.AddColumn(Source, "Dummy", each 1)

Save, refresh, publish to Power BI Service, and see if the refresh works.

5. Direct SharePoint connections sometimes have inconsistencies. Move the file to OneDrive for Business and use the OneDrive connector in Power BI instead of SharePoint. This method forces Power BI to fetch the latest file version.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[yamina]

HI @v-hashadapu 

Thank you for coming back to me and try to solve my problem. 

As ytou can see I tried to change my sharepoint version to 14 and it doesn't change anything see the error message below 

I also chek in my sharepoint version but i don't know what i have to check

Where  can i add the dummy transformation in power query in the edit file  i suppose but where 

let

    Source = SharePoint.Files("https://groupexxxxx.sharepoint.com/sites/PerformanceSAV/", [ApiVersion = 14]),

    #"Lignes filtrées" = Table.SelectRows(Source, each ([Folder Path] = "https://groupexxxxx.sharepoint.com/sites/PerformanceSAV/Shared Documents/Fichiers sources/1. IAD/20. Suivi des RAC/Fichiers Sources/")),

    #"Fichiers masqués filtrés1" = Table.SelectRows(#"Lignes filtrées", each [Attributes]?[Hidden]? <> true),

    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier (3)", each #"Transformer le fichier (3)"([Content])),

    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),

    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier (3)"}),

    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier (3)", Table.ColumnNames(#"Transformer le fichier (3)"(#"Exemple de fichier (3)"))),

    #"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"id_stat_motif", Int64.Type}, {"code_societe", type text}, {"code_lieu", type text}, {"prefixe_dossier", Int64.Type}, {"num_dossier", Int64.Type}, {"type_rappel", type text}, {"contenu_dde_appel", type text}, {"id_motif", Int64.Type}, {"motif_rac", type text}, {"date_appel", type date}, {"heure_appel", type time}, {"contenu_reponse_sav", type text}, {"date_reponse", type date}, {"heure_reponse", type time}, {"delai", type text}, {"id_user", Int64.Type}, {"nom", Int64.Type}}),

    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Personnalisé", each [code_societe]&Text.PadStart([code_lieu],3,"0")),

    #"Lignes filtrées1" = Table.SelectRows(#"Personnalisée ajoutée", each true),

    #"Colonnes renommées" = Table.RenameColumns(#"Lignes filtrées1",{{"Personnalisé", "Code SAV"}}),

    #"Premiers caractères insérés" = Table.AddColumn(#"Colonnes renommées", "Premiers caractères", each Text.Start(Text.From([heure_appel], "fr-FR"), 2), type text),

    #"Colonnes renommées2" = Table.RenameColumns(#"Premiers caractères insérés",{{"Premiers caractères", "heure appel"}}),

    #"Duplication de la colonne" = Table.DuplicateColumn(#"Colonnes renommées2", "date_appel", "date_appel - Copier"),

    #"Semaine de l'année calculée" = Table.TransformColumns(#"Duplication de la colonne",{{"date_appel - Copier", Date.WeekOfYear, Int64.Type}}),

    #"Colonnes renommées3" = Table.RenameColumns(#"Semaine de l'année calculée",{{"date_appel - Copier", "Semaine"}})

in

    #"Colonnes renommées3"

thank you for helping me

[v-hashadapu · Accepted solution]

Hi @yamina , thank you for reaching out to the Microsoft Fabric Community Forum.

Since you've already verified your SharePoint connection, credentials, API version, and M code logic, the key takeaway is that Power BI Service struggles to read the file until you manually open and save it in Excel. This suggests that SharePoint might be caching an older version or locking the file.

The best next step is to report this issue to Microsoft Support, as it may be a bug or a backend issue with how Power BI Service interacts with SharePoint. You can provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution. They might be able to identify something specific about your admin account setup or provide a solution that isn't immediately obvious. 

Below is the link to create Microsoft Support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @yamina ,Thank you for reaching out to Microsoft Fabric Community Forum.

1. The error ‘The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file’ seems like the issue might be related to the bit-version mismatch of the Access Database Engine OLEDB provider.  Ensure that the correct version of the Access Database Engine OLEDB provider is installed. You need the 64-bit version if you're using a 64-bit version of Power BI. After installing the correct version, restart the Power BI service to ensure the changes take effect.
2. ‘DataSource.NotFound: Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.’ This error suggests that Power BI Service is unable to find the Microsoft.ACE.OLEDB.12.0 provider on the machine where the Power BI Service is running. The required Access Database Engine (ACE) provider is either missing or not properly registered for Power BI Service to use during the data refresh process.
3. If refreshing the report works on the local machine (via Power BI Desktop), but fails on Power BI Service, it indicates that the ACE provider is installed on the local machine, but not on the Power BI Service. Unfortunately, Power BI Service (cloud) does not support installing custom data connectors like ACE OLEDB. This is a limitation of the cloud environment.
4. To resolve this issue in Power BI Service, the Excel file should be accessible in a supported format. If the Excel file is stored locally or uses the ACE OLEDB connection, it might help to Upload the file to OneDrive or SharePoint and link to it from Power BI Service. Alternatively, using a different connector like Excel from OneDrive for Business or Excel Online could be a better solution.
5. If Import Mode is being used and the error occurs during data refresh, switching to DirectQuery (if applicable) for the data source could help. This may bypass issues related to local provider installations and allow Power BI Service to query the data in real-time without needing the ACE.OLEDB provider.
6. If Power Query in Power BI Desktop is being used to connect to the Excel file, it might help to change the connection type or method to a more reliable connector for the cloud. If the Excel files are stored in SharePoint or OneDrive, using the SharePoint Folder connector or OneDrive connector can help establish a more reliable connection.
7. It is important to ensure that the Data Source Credentials are correctly configured in Power BI Service. For Excel files or external connections, updating or re-entering credentials might resolve the issue. Navigate to Settings > Data Source Credentials in Power BI Service and verify that the credentials are set correctly for the Excel file source.
8. If this issue occurs frequently and requires automation for data refresh, using Power Automate (formerly Microsoft Flow) could be an effective solution. With Power Automate, the refresh of Power BI reports can be automated, which may help overcome limitations related to manual refresh.

If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not, please share the details.