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"
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"
