My report take to much time to refresh

[CarlosFlores119]

I have a report connects to a sharepoint library but takes 15 min to refresh 17 MB some know why takes to much time?

[Greg_Deckler]

Are you able to post your M code?

[Divilover]

 @Greg_Deckler Where's M code?

Thanks

[CarlosFlores119]

Hello smoupre , yes is enable the M code

[KHorseman]

@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.

[CarlosFlores119]

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 

[Greg_Deckler]

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. 

[CarlosFlores119]

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 

[Greg_Deckler · Accepted 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.

[CarlosFlores119]

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 

[CarlosFlores119]

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

[KHorseman]

@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).