power query refresh time from SharePoint really slow

gagvik001

New Member
Joined
Jun 10, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
How do I reduce refresh time for power query which loads data from SharePoint. I have disabled background refresh and data preview download in background. Still it's slow. Any solutions?

I am loading data from am a online share point online List to excel via power query(only by creating connection). When i refresh the connection or pivot tables related to this connection, it takes 30-35 minutes and i can't use any other excel file meanwhile. Also this time is increasing day by day since my sharepoint list entries are also increasing.

structure-share.----.com/sites/audit_Sharepoint - Audit_Sharepoint(List)

Connection String-Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Audits Sharepoint;Extended Properties=""

Approximately 2000+rows Please go through the image- Query Loading Time is really slow Loading.PNG



Below is the query-

Below is the query from Source = SharePoint.Tables("https://share.-----.com/sites/Audit_SharePoint", [ApiVersion = 15]), #"f4033f85-c251-4d37-bed7-7a2dd4854336" = Source{[Id="f4033f85-c251-4d37-bed7-7a2dd4854336"]}[Items], #"Renamed Columns" = Table.RenameColumns(#"f4033f85-c251-4d37-bed7-7a2dd4854336",{{"ID", "ID.1"}}), #"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Renamed Columns", "FieldValuesAsText", {"Title", "login", "L4x0020_Manager_x0020_Login", "L5_x0020_Manager_x0020_Login", "L6_x0020_Manager_x0020_Login", "ManagerU", "Defect_name", "NC_x002f_VC", "Queue", "Scenario", "Order_Type", "Justified", "Primary_x0020_defect_x0020_area", "Primary_x0020_defect_x0020_area", "Secondaryx0020_defect_x0020_are", "Secondary_x0020_defect_x0020_are0", "Comments", "Causes_x0028_Fishbone_x0029", "Auditorx0020_Login", "Second_x0020_Investigator_x0020", "Secondx0020_Investigator_x0020_0", "Audit_x0020_Type", "Manager", "ID", "Modified", "Created", "Order", "FileRef", "Last_x0020_Modified", "Created_x0020_Date"}, {"FieldValuesAsText.Title", "FieldValuesAsText.login", "FieldValuesAsText.L4_x0020_Manager_x0020_Login", "FieldValuesAsText.L5_x0020_Manager_x0020_Login", "FieldValuesAsText.L6_x0020_Manager_x0020_Login", "FieldValuesAsText.ManagerU", "FieldValuesAsText.Defect_name", "FieldValuesAsText.NC_x002f_VC", "FieldValuesAsText.Queue", "FieldValuesAsText.Scenario", "FieldValuesAsText.Order_Type", "FieldValuesAsText.Justified", "FieldValuesAsText.Primary_x0020_defect_x0020_area", "FieldValuesAsText.Primary_x0020_defect_x0020_area", "FieldValuesAsText.Secondaryx0020_defect_x0020_are", "FieldValuesAsText.Secondary_x0020_defect_x0020_are0", "FieldValuesAsText.Comments", "FieldValuesAsText.Causes_x0028_Fishbone_x0029", "FieldValuesAsText.Auditorx0020_Login", "FieldValuesAsText.Second_x0020_Investigator_x0020", "FieldValuesAsText.Secondx0020_Investigator_x0020_0", "FieldValuesAsText.Audit_x0020_Type", "FieldValuesAsText.Manager", "FieldValuesAsText.ID", "FieldValuesAsText.Modified", "FieldValuesAsText.Created", "FieldValuesAsText.Order", "FieldValuesAsText.FileRef", "FieldValuesAsText.Last_x0020_Modified", "FieldValuesAsText.Created_x0020_Date"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded FieldValuesAsText",{{"Title", "Unique_ID(OID/CID)"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"FieldValuesAsText.Title"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"FieldValuesAsText.login", "login_ID(Being Audited)"}, {"FieldValuesAsText.L4_x0020_Manager_x0020_Login", "L4_Manager"}, {"FieldValuesAsText.L5_x0020_Manager_x0020_Login", "L5_Manager"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"FileSystemObjectType", "loginId", "L4 Manager LoginId", "L5 Manager LoginId", "L6 Manager LoginId", "ManagerU", "Order_TypeId", "Auditor LoginId", "Second Investigator 0Id", "entity_type", "action", "function", "location", "Manager", "AuthorId", "EditorId", "ID.1", "ODataUIVersionString", "Attachments", "GUID", "ContentTypeId"}), #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"FieldValuesAsText.L6_x0020_Manager_x0020_Login", "L6_Manager"}}), #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns3",{"FieldValuesAsText.ManagerU"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns2",{{"FieldValuesAsText.Defect_name", type date}, {"Action_date", type date}, {"Defect Receive_date", type date}, {"Defect_Received_Week", Int64.Type}}), #"Renamed Columns4" = Table.RenameColumns(#"Changed Type",{{"Defect_Received_Week", "Actioned_Week"}, {"FieldValuesAsText.Auditor_x0020_Login", "Auditor_Login"}}), #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns4",{"QueueId", "ScenarioId"}), #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns3",{{"FieldValuesAsText.Queue", "Queue."}, {"FieldValuesAsText.Scenario", "Scenario."}, {"FieldValuesAsText.Justified", "Justified."}, {"FieldValuesAsText.Order_Type", "Order Type"}, {"Primary defect area", "Primary defect area Reason"}, {"Secondary defect are", "Secondary defect area Reason"}, {"Secondary defect are0", "Secondary defect area"}, {"Second Investigator ", "Second Investigator miss"}, {"FieldValuesAsText.Secondx0020_Investigator_x0020_0", "Second Investigator Login"}}), #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns5",{"FieldValuesAsText.Audit_x0020_Type", "FieldValuesAsText.Manager", "FieldValuesAsText.FileRef", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText.Defect_name", "FieldValuesAsText.NC_x002f_VC", "FieldValuesAsText.Primary_x0020_defect_x0020_area", "FieldValuesAsText.Primary_x0020_defect_x0020_area", "FieldValuesAsText.Secondaryx0020_defect_x0020_are", "FieldValuesAsText.Secondary_x0020_defect_x0020_are0", "FieldValuesAsText.Comments", "FieldValuesAsText.Causes_x0028_Fishbone_x0029", "FieldValuesAsText.Order", "FieldValuesAsText.Lastx0020_Modified", "FieldValuesAsText.Created_x0020_Date", "FieldValuesAsText.Second_x0020_Investigator_x0020", "FieldValuesAsText.ID", "FieldValuesAsText.Modified", "FieldValuesAsText.Created"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns4",{{"Received_week", Int64.Type}}) in #"Changed Type1"
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Watch MrExcel Video

Forum statistics

Threads
1,113,868
Messages
5,544,774
Members
410,633
Latest member
ecronic
Top