Error refresh model semantic imposible excel in sharepoint

[risraspakas]

• Error del origen de datosDataSource.NotFound: Excel Workbook: El proveedor 'Microsoft.ACE.OLEDB.12.0' no está registrado en el equipo local. Es posible que se requiera la versión de 64 bits del proveedor OLEDB del motor de base de datos de Access para leer este tipo de archivo. Para descargar el software cliente, visite el siguiente sitio: https://go.microsoft.com/fwlink/?LinkID=285987.. Microsoft.Data.Mashup.ErrorCode = 10478. . La excepción fue provocada por la interfaz IDbCommand. Tabla: CurvasCarga 2024.
• URI de clústerWABI-WEST-EUROPE-B-PRIMARY-redirect.analysis.windows.net

[risras · Accepted solution]

Creo que ya he encontrado la solución , pero repito algun cambio en el servicio ha habido, la pongo para el que pueda ayudar es cambiar la forma de combinar los archivos de la carpeta de sharepoint haciendo un combine files sin la función externa

let
Origen = SharePoint.Files("https://kuredusoftware-my.sharepoint.com/personal/cesar_kuredusoftware_com", [ApiVersion = 15]),
ArchivosFiltrados = Table.SelectRows(Origen, each ([Folder Path] = "https://kuredusoftware-my.sharepoint.com/personal/cesar_kuredusoftware_com/Documents/TELEMEDIDA/2024...")),

// Transformar cada archivo
Transformados = Table.AddColumn(ArchivosFiltrados, "Datos", each
let
Binario = [Content],
Excel = Excel.Workbook(Binario, null, true),
HojaCurva = Table.SelectRows(Excel, each [Name] = "Consumos diarios"){0}[Data]
in
HojaCurva
),
#"Se expandió Datos" = Table.ExpandTableColumn(Transformados, "Datos", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
#"Otras columnas quitadas" = Table.SelectColumns(#"Se expandió Datos",{"Name", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
#"Encabezados promovidos" = Table.PromoteHeaders(#"Otras columnas quitadas", [PromoteAllScalars=true]),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"CASER STA LEONOR_506_202505041937.xls", type text}, {"Fecha", type text}, {"P1", type text}, {"P2", type text}, {"P3", type text}, {"P4", type text}, {"P5", type text}, {"P6", type text}, {"TOTAL", type text}}),
#"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "Fecha Nueva", each if Value.Is([Fecha], type number) then Date.From(Number.From([Fecha])) else [Fecha]),
#"Columnas quitadas" = Table.RemoveColumns(#"Personalizada agregada",{"Fecha"}),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas quitadas",{{"Fecha Nueva", "Fecha"}}),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Columnas con nombre cambiado",{{"Fecha", type date}}),
#"Filas filtradas" = Table.SelectRows(#"Tipo cambiado2", each ([TOTAL] <> "TOTAL"))
in
#"Filas filtradas"

[v-sgandrathi]

Hi @risras,
Thank you for the detailed follow-up, and great job identifying and resolving the issue!

You're absolutely right, the issue stemmed from how Power Query’s auto-generated functions (like Transform File) internally use File.Contents, which isn't supported in the Power BI Service. These used to work under certain conditions but may now fail due to stricter platform behavior or recent service-side updates.

By restructuring the transformation logic and combining files manually (without relying on the external function), you've ensured that all file handling is done via web-compatible methods (SharePoint.Files and Excel.Workbook), which is the correct and supported approach for Power BI Service.

Your solution is spot on and yes, a Power BI Pro license does support this method, and you shouldn’t face any query folding limitations with this setup, especially since SharePoint and Excel sources are usually read-only in folding scenarios.

Thanks again for sharing your fix, it's valuable for others facing the same issue.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum.

[risraspakas]

Sorry, that's not the problem, because this Excel is in SharePoint, it's not a local Excel, I can't install anything in the Power BI service.

[v-sgandrathi]

Hi @risraspakas,

When you connect to a SharePoint-hosted Excel file using "Get Data > Excel" in Power BI Desktop, it's treated as a local file. This works locally but fails in the Power BI Service, which cannot access local paths. As a result, it throws a misleading 'Microsoft.ACE.OLEDB.12.0' error. The real issue is that the connection isn’t web-based, the service requires a web-accessible source like SharePoint Folder or Web connector.

To enable Power BI Service to refresh data from an Excel file on SharePoint, you must connect using a web-based method, either the Web connector with the file's direct URL or the SharePoint Folder connector. These methods create Power Query (M) code that references SharePoint URLs, allowing the service to access the file via HTTPS. After publishing to Power BI Service, update the dataset’s credentials using OAuth2 with your Microsoft account. This setup ensures secure access and enables both manual and scheduled refreshes.

Thanks for reaching out! If this answer was helpful, please consider marking it as Accepted Solution and giving a Kudos, it helps the community!

Thank you.

[risraspakas]

La conexión es a Sharepoint

let
Origen = SharePoint.Files("https://kuredusoftware-my.sharepoint.com/personal/cesar_kuredusoftware_com", [ApiVersion = 15]),
#"Filas filtradas10" = Table.SelectRows(Origen, each ([Folder Path] = "https://kuredusoftware-my.sharepoint.com/personal/cesar_kuredusoftware_com/Documents/TELEMEDIDA/2023...")),
#"Archivos ocultos filtrados1" = Table.SelectRows(#"Filas filtradas10", each [Attributes]?[Hidden]? <> true),
#"Invocar función personalizada1" = Table.AddColumn(#"Archivos ocultos filtrados1", "Transformar archivo (2)", each #"Transformar archivo (2)"([Content])),
#"Columnas con nombre cambiado1" = Table.RenameColumns(#"Invocar función personalizada1", {"Name", "Source.Name"}),
#"Otras columnas quitadas1" = Table.SelectColumns(#"Columnas con nombre cambiado1", {"Source.Name", "Transformar archivo (2)"}),
#"Columna de tabla expandida1" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo (2)", Table.ColumnNames(#"Transformar archivo (2)"(#"Archivo de ejemplo (2)"))),
#"Personalizada agregada" = Table.AddColumn(#"Columna de tabla expandida1", "Fecha Nueva", each if Value.Is([Fecha], type number) then Date.From(Number.From([Fecha])) else [Fecha]),
#"Columnas quitadas" = Table.RemoveColumns(#"Personalizada agregada",{"Fecha"}),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas quitadas",{{"Fecha Nueva", "Fecha"}}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas con nombre cambiado",{{"Fecha", type date}})
in
#"Tipo cambiado"

Siempre había funcionado es un problema del servicio allí no ouedo instalar nada

[v-sgandrathi]

Hi @risraspakas,

Thank you fo rthe Follow-up!

To ensure compatibility with the Power BI Service:

1. All queries must use web-compatible connectors, such as SharePoint.Files or Web.Contents, for HTTPS-based access.
2. Check sample file transformation functions, especially those auto-generated from folder sources, to ensure they do not reference File.Contents(), even indirectly.
3. After publishing to the Power BI Service, verify that your data source credentials are correctly configured. Use OAuth2 authentication with your Microsoft account for secure access to SharePoint.

No drivers or components need to be installed on the Power BI Service side, as all data interactions must occur through supported web-based connectors.

By following these guidelines, the refresh operation should complete successfully in the Power BI Service without needing the Microsoft.ACE.OLEDB.12.0 provider.

Thanks for reaching out! If this answer was helpful, please consider marking it as Accepted Solution and giving a Kudos, it helps the community!

Thank you.

[risras]

La transformacion de power query que genera este error que antes no generaba se compone de la siguiente

CurvasCarga 2024

let
Origen = SharePoint.Files("https://kuredusoftware-my.sharepoint.com/personal/cesar_kuredusoftware_com", [ApiVersion = 15]),
#"Filas filtradas10" = Table.SelectRows(Origen, each ([Folder Path] = "https://kuredusoftware-my.sharepoint.com/personal/cesar_kuredusoftware_com/Documents/TELEMEDIDA/2024...")),
#"Archivos ocultos filtrados1" = Table.SelectRows(#"Filas filtradas10", each [Attributes]?[Hidden]? <> true),
#"Invocar función personalizada1" = Table.AddColumn(#"Archivos ocultos filtrados1", "Transformar archivo (3)", each #"Transformar archivo (3)"([Content])),
#"Columnas con nombre cambiado1" = Table.RenameColumns(#"Invocar función personalizada1", {"Name", "Source.Name"}),
#"Otras columnas quitadas1" = Table.SelectColumns(#"Columnas con nombre cambiado1", {"Source.Name", "Transformar archivo (3)"}),
#"Columna de tabla expandida1" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo (3)", Table.ColumnNames(#"Transformar archivo (3)"(#"Archivo de ejemplo (3)"))),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columna de tabla expandida1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Column3] <> null)),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Filas filtradas",{{"Source.Name", "Fichero"}, {"Column1", "Periodo"}, {"Column2", "Fecha"}, {"Column3", "kWActiva"}, {"Column4", "KVAr Reactiva"}}),
#"Filas filtradas1" = Table.SelectRows(#"Columnas con nombre cambiado", each ([Periodo] <> "Periodo")),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Filas filtradas1",{{"Fecha", type datetime}, {"kWActiva", Int64.Type}, {"KVAr Reactiva", Int64.Type}})
in
#"Tipo cambiado1"

Y Transformar archivo (3)

(source as binary) =>
let
Origen = Excel.Workbook(source, null, true),
Curva1 = Origen{[Name="Curva"]}[Data]
in
Curva1

H acambiado algo en el servicio porque antes si podía realizar la carga , hay alguna opcion de modificar el powewr query de Transformar archivo (3) para solucionarlo a parte de modificarlo todo creando un dataflow que creo que funcionaría lo que no sé es si podría con mi licencia Pro por el plegado de consultas?

[v-sgandrathi]

Hi @risraspakas,

The error you're encountering in Power BI occurs because the required OLEDB provider, Microsoft.ACE.OLEDB.12.0, is not installed on your system. This provider is essential for Power BI to connect to certain Excel files, especially if you're using the 64-bit version of Power BI.
To resolve the issue, you need to install the 64-bit version of the Microsoft Access Database Engine 2010 Redistributable, which contains the necessary OLEDB driver. You can download it from the official Microsoft site. During installation, if you have a 32-bit version of Microsoft Office already installed, you might encounter a conflict. In that case, you should open the Command Prompt as Administrator and install the file using the command AccessDatabaseEngine_x64.exe /quiet to bypass the conflict silently. Once installed, restart Power BI and attempt to load your Excel file again. This should resolve the “provider not registered” error and allow Power BI to successfully connect to and import data from the Excel file.

Thanks for reaching out! If this answer was helpful, please consider marking it as Accepted Solution and giving a Kudos, it helps the community!

Thank you.