Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a report connects to a sharepoint library but takes 15 min to refresh 17 MB some know why takes to much time?
Solved! Go to Solution.
OK, couple things that I would try. First, I would remove the two red lines below, they seem to be non-essential unless I am missing something, it looks like you create a "Custom" field and then remove it without ever using it.
let Origen = SharePoint.Tables("https://mysite.sharepoint.com", [ApiVersion = 15]), #"Columnas quitadas" = Table.RemoveColumns(#"08daa923-e677-46c5-aa52-e33d1054ed7d",{"ContentTypeId", "Id", "FileSystemObjectType", "ID", "Title"}), #"Filas filtradas" = Table.SelectRows(#"Columnas quitadas", each true), #"Se expandió Usuario_Responsable" = Table.ExpandRecordColumn(#"Filas filtradas", "Usuario_Responsable", {"FirstName"}, {"Usuario_Responsable.FirstName"}), #"Se expandió Cierre" = Table.ExpandRecordColumn(#"Se expandió Usuario_Responsable", "Cierre", {"Description"}, {"Cierre.Description"}), #"Personalizada agregada" = Table.AddColumn(#"Se expandió Cierre", "Custom", each DateTime.LocalNow()-[Created]),#"Día del año insertado" = Table.AddColumn(#"Personalizada agregada", "DayOfYear", each Date.DayOfYear([Created]), type number), #"Columnas quitadas1" = Table.RemoveColumns(#"Día del año insertado",{"Custom"}),#"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas quitadas1",{{"Fecha Hora", type datetime}}), #"Fecha extraída" = Table.TransformColumns(#"Tipo cambiado",{{"Fecha Hora", DateTime.Date}}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Fecha extraída",{{"Fecha Hora", type datetime}, {"DIAS ABIERTOS", Int64.Type}, {"Fecha Txt", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Tipo cambiado1",{{"Fecha Txt", "Fecha de Creacion"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"FirstUniqueAncestorSecurableObject", "Procesos", "Cancelado", "Cerrado", "Resuelto", "Notificacion_resuelto", "Asignacion", "RoleAssignments", "AttachmentFiles", "ContentType", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "ParentList", "Author", "Editor", "Reasignacion", "ServerRedirectedEmbedUrl", "GetDlpPolicyTip", "OData__UIVersionString", "GUID", "Cambio Responsable", "Nuevo Responsable", "Usuario_ResponsableId", "Usuario_ResponsableStringId", "OData__CopySource", "CheckoutUserId", "Folder", "EditorId", "AuthorId", "CheckoutUser", "Correo Responsable", "Cambio Status", "Finalizado", "Display Name", "Solicitante", "Correo", "Correo Creado Por", "NResponsableId", "NResponsableStringId", "Total Fecha", "Contador Dias", "DayOfYear", "NResponsable"}) in #"Removed Columns"
The second thing that I would try would be to create a new query like this one:
let Origen = SharePoint.Tables("https://mysite.sharepoint.com", [ApiVersion = 15]), #"Columnas quitadas" = Table.RemoveColumns(#"08daa923-e677-46c5-aa52-e33d1054ed7d",{"ContentTypeId", "Id", "FileSystemObjectType", "ID", "Title"}), #"Removed Columns" = Table.RemoveColumns(#"Columnas quitados",{"FirstUniqueAncestorSecurableObject", "Procesos", "Cancelado", "Cerrado", "Resuelto", "Notificacion_resuelto", "Asignacion", "RoleAssignments", "AttachmentFiles", "ContentType", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "ParentList", "Author", "Editor", "Reasignacion", "ServerRedirectedEmbedUrl", "GetDlpPolicyTip", "OData__UIVersionString", "GUID", "Cambio Responsable", "Nuevo Responsable", "Usuario_ResponsableId", "Usuario_ResponsableStringId", "OData__CopySource", "CheckoutUserId", "Folder", "EditorId", "AuthorId", "CheckoutUser", "Correo Responsable", "Cambio Status", "Finalizado", "Display Name", "Solicitante", "Correo", "Correo Creado Por", "NResponsableId", "NResponsableStringId", "Total Fecha", "Contador Dias", "DayOfYear", "NResponsable"}) in #"Removed Columns"
Basically, this verion strips out everything except accessing the SharePoint list and removing a bunch of columns. If this version runs at the same speed as the original, then you can conclude that it is not inefficient "M" code but rather probably the SharePoint Server or local machine or some type of network latency that is to blame. Let me know and we can continue troubleshooting.
Are you able to post your M code?
Hello smoupre , yes is enable the M code
@CarlosFlores119 he is asking you to post your code for us to look at. It's hard to troubleshoot and improve code if you can't see it.
Proud to be a Super User!
Hello KHorseman but it is not a problem code, i mean the main problem is the time that it takes to refresh 17 MB
Because for examle if you have a report connected to a resourse that have 2 GB of information , it will take about 40 min to refresh the report?
Thanks
The problem can absolutely be the M code (Power Query code). For example, if you are doing a selection filter and within that selection filter you have something like:
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= List.Min(SALES[DATE]) and [DATE] <= List.Max(SALES[DATE]))
Versus doing it this way:
LMinSD = List.Min(SalesDate), LMaxSD = List.Max(SalesDate), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= LMinSD and [DATE] <= LMaxSD)
In this example, the second way improved performance by over 4x faster.
Hello @Greg_Deckler here is my M code
let
Origen = SharePoint.Tables("https://mysite.sharepoint.com", [ApiVersion = 15]),
#"Columnas quitadas" = Table.RemoveColumns(#"08daa923-e677-46c5-aa52-e33d1054ed7d",{"ContentTypeId", "Id", "FileSystemObjectType", "ID", "Title"}),
#"Filas filtradas" = Table.SelectRows(#"Columnas quitadas", each true),
#"Se expandió Usuario_Responsable" = Table.ExpandRecordColumn(#"Filas filtradas", "Usuario_Responsable", {"FirstName"}, {"Usuario_Responsable.FirstName"}),
#"Se expandió Cierre" = Table.ExpandRecordColumn(#"Se expandió Usuario_Responsable", "Cierre", {"Description"}, {"Cierre.Description"}),
#"Personalizada agregada" = Table.AddColumn(#"Se expandió Cierre", "Custom", each DateTime.LocalNow()-[Created]),
#"Día del año insertado" = Table.AddColumn(#"Personalizada agregada", "DayOfYear", each Date.DayOfYear([Created]), type number),
#"Columnas quitadas1" = Table.RemoveColumns(#"Día del año insertado",{"Custom"}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas quitadas1",{{"Fecha Hora", type datetime}}),
#"Fecha extraída" = Table.TransformColumns(#"Tipo cambiado",{{"Fecha Hora", DateTime.Date}}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Fecha extraída",{{"Fecha Hora", type datetime}, {"DIAS ABIERTOS", Int64.Type}, {"Fecha Txt", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Tipo cambiado1",{{"Fecha Txt", "Fecha de Creacion"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"FirstUniqueAncestorSecurableObject", "Procesos", "Cancelado", "Cerrado", "Resuelto", "Notificacion_resuelto", "Asignacion", "RoleAssignments", "AttachmentFiles", "ContentType", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "ParentList", "Author", "Editor", "Reasignacion", "ServerRedirectedEmbedUrl", "GetDlpPolicyTip", "OData__UIVersionString", "GUID", "Cambio Responsable", "Nuevo Responsable", "Usuario_ResponsableId", "Usuario_ResponsableStringId", "OData__CopySource", "CheckoutUserId", "Folder", "EditorId", "AuthorId", "CheckoutUser", "Correo Responsable", "Cambio Status", "Finalizado", "Display Name", "Solicitante", "Correo", "Correo Creado Por", "NResponsableId", "NResponsableStringId", "Total Fecha", "Contador Dias", "DayOfYear", "NResponsable"})
in
#"Removed Columns"
I hope you can help me
Thanks
OK, couple things that I would try. First, I would remove the two red lines below, they seem to be non-essential unless I am missing something, it looks like you create a "Custom" field and then remove it without ever using it.
let Origen = SharePoint.Tables("https://mysite.sharepoint.com", [ApiVersion = 15]), #"Columnas quitadas" = Table.RemoveColumns(#"08daa923-e677-46c5-aa52-e33d1054ed7d",{"ContentTypeId", "Id", "FileSystemObjectType", "ID", "Title"}), #"Filas filtradas" = Table.SelectRows(#"Columnas quitadas", each true), #"Se expandió Usuario_Responsable" = Table.ExpandRecordColumn(#"Filas filtradas", "Usuario_Responsable", {"FirstName"}, {"Usuario_Responsable.FirstName"}), #"Se expandió Cierre" = Table.ExpandRecordColumn(#"Se expandió Usuario_Responsable", "Cierre", {"Description"}, {"Cierre.Description"}), #"Personalizada agregada" = Table.AddColumn(#"Se expandió Cierre", "Custom", each DateTime.LocalNow()-[Created]),#"Día del año insertado" = Table.AddColumn(#"Personalizada agregada", "DayOfYear", each Date.DayOfYear([Created]), type number), #"Columnas quitadas1" = Table.RemoveColumns(#"Día del año insertado",{"Custom"}),#"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas quitadas1",{{"Fecha Hora", type datetime}}), #"Fecha extraída" = Table.TransformColumns(#"Tipo cambiado",{{"Fecha Hora", DateTime.Date}}), #"Tipo cambiado1" = Table.TransformColumnTypes(#"Fecha extraída",{{"Fecha Hora", type datetime}, {"DIAS ABIERTOS", Int64.Type}, {"Fecha Txt", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Tipo cambiado1",{{"Fecha Txt", "Fecha de Creacion"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"FirstUniqueAncestorSecurableObject", "Procesos", "Cancelado", "Cerrado", "Resuelto", "Notificacion_resuelto", "Asignacion", "RoleAssignments", "AttachmentFiles", "ContentType", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "ParentList", "Author", "Editor", "Reasignacion", "ServerRedirectedEmbedUrl", "GetDlpPolicyTip", "OData__UIVersionString", "GUID", "Cambio Responsable", "Nuevo Responsable", "Usuario_ResponsableId", "Usuario_ResponsableStringId", "OData__CopySource", "CheckoutUserId", "Folder", "EditorId", "AuthorId", "CheckoutUser", "Correo Responsable", "Cambio Status", "Finalizado", "Display Name", "Solicitante", "Correo", "Correo Creado Por", "NResponsableId", "NResponsableStringId", "Total Fecha", "Contador Dias", "DayOfYear", "NResponsable"}) in #"Removed Columns"
The second thing that I would try would be to create a new query like this one:
let Origen = SharePoint.Tables("https://mysite.sharepoint.com", [ApiVersion = 15]), #"Columnas quitadas" = Table.RemoveColumns(#"08daa923-e677-46c5-aa52-e33d1054ed7d",{"ContentTypeId", "Id", "FileSystemObjectType", "ID", "Title"}), #"Removed Columns" = Table.RemoveColumns(#"Columnas quitados",{"FirstUniqueAncestorSecurableObject", "Procesos", "Cancelado", "Cerrado", "Resuelto", "Notificacion_resuelto", "Asignacion", "RoleAssignments", "AttachmentFiles", "ContentType", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "ParentList", "Author", "Editor", "Reasignacion", "ServerRedirectedEmbedUrl", "GetDlpPolicyTip", "OData__UIVersionString", "GUID", "Cambio Responsable", "Nuevo Responsable", "Usuario_ResponsableId", "Usuario_ResponsableStringId", "OData__CopySource", "CheckoutUserId", "Folder", "EditorId", "AuthorId", "CheckoutUser", "Correo Responsable", "Cambio Status", "Finalizado", "Display Name", "Solicitante", "Correo", "Correo Creado Por", "NResponsableId", "NResponsableStringId", "Total Fecha", "Contador Dias", "DayOfYear", "NResponsable"}) in #"Removed Columns"
Basically, this verion strips out everything except accessing the SharePoint list and removing a bunch of columns. If this version runs at the same speed as the original, then you can conclude that it is not inefficient "M" code but rather probably the SharePoint Server or local machine or some type of network latency that is to blame. Let me know and we can continue troubleshooting.
Hello @Greg_Deckler it helps me too much to undertand how PWBI works and yes my report take less than a minute to refresh
Thank you so much for your help
Hello @Greg_Deckler i will try the steps and i let you know if it works
Thanks for you time to review my problem
We are in contact
Thanks again
@CarlosFlores119 how do you know the problem isn't in the code? Some transformations, custom column formulas and other query steps can add a huge amount to the refresh time. It's hard to give any advice on how to make your query run faster if we don't know anything about your query.
@Divilover go to Edit Queries, then hit the Advanced Editor button there to get a text window with your query's M code. Obviously don't post anything business sensitive from it (like if there's something in the Source step that includes some confidential file location info or such).
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
59 |