Forum Discussion
Error refresh model semantic imposible excel in sharepoint
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"
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-sgandrathiCommunity Support
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.