none
SharePoint Online List- Power BI - slow query update RRS feed

  • Question

  • I am trying to create a dashboard using Power BI desktop app. My data source is a SharePoint Online list. I updated the data setto clean it up. the issue that when I tried to apply changes to the query update the data set it takes forever. I only have about 4000 rows of information and about 25 columns but the data does not update even after 3-4 hours.


    The data keeps on increasing but a very slow speed. Please let me know if there is a better way to update the query and data set.

    Thanks


    Tuesday, July 12, 2016 10:32 PM

Answers

  • Hi there. All those expand operations are going to cause additional requests to be sent to the SharePoint service, which is going to slow things down considerably. My only recommendation at this point is to try to reduce the number of expansion operations you're doing, or to experiment with other ways you might be able to simplify the query.

    Ehren

    Friday, July 15, 2016 6:11 PM
    Owner

All replies

  • Hi there. Is it equally slow if you omit the cleanup steps you mentioned?

    Ehren

    Wednesday, July 13, 2016 10:48 PM
    Owner
  • Which version of PBI Desktop are you using? We fixed a performance bug in this area in the most recent release.
    Thursday, July 14, 2016 2:46 PM
  • I am on  2.36.4434.381 64-bit (June 2016) version. I will try checking if there are any newer updates that I can install. So typically, it shouldn't take this long to update the query, right?
    Thursday, July 14, 2016 3:42 PM
  • Hi. If I omit the cleanup then the query doesn't need to update. 
    Thursday, July 14, 2016 4:43 PM
  • Hi ShamikaR. What I was getting at with my question is: maybe one of the cleanup operations is expensive and thus causing the refresh to run more slowly. If you load the table as-is, what is the performance like? (Versus loading the table using the cleanup logic you mentioned.) If you could paste your formula here (you can copy it from the Advanced Editor in the Query view), that would allow us to see what the cleanup logic involves and help us determine if that could be the issue.

    Ehren

    Thursday, July 14, 2016 5:24 PM
    Owner
  • Hi Ehren, 

    Thanks for your assistance! You are right, when I try yo load the table as-is, it is wayyyyyy faster. 

    All I am trying to do in the query is remove the columns that are not required, get display name from people picker columns and the get lookup column value instead of ID. Here is my Query-


    let
        Source = SharePoint.Tables("https://mysharepointsite/", [ApiVersion = 15]),
        #"b9be90c0-0a13-4c23-b3a7-9e0ca6f4e488" = Source{[Id="b9be90c0-0a13-4c23-b3a7-9e0ca6f4e488"]}[Items],
        #"Renamed Columns" = Table.RenameColumns(#"b9be90c0-0a13-4c23-b3a7-9e0ca6f4e488",{{"form_currentUser_AD_AccountID", "form_currentUser_AD_AccountID.1"}, {"ID", "ID.1"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Test"}),
        #"Expanded ProjectAssistant" = Table.ExpandRecordColumn(#"Removed Columns", "ProjectAssistant", {"Title"}, {"ProjectAssistant.Title"}),
        #"Expanded ProjectManager" = Table.ExpandRecordColumn(#"Expanded ProjectAssistant", "ProjectManager", {"Title"}, {"ProjectManager.Title"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expanded ProjectManager",{"JDERequestCompletedBy", "CopyID_jmsRequestID", "Author", "Editor", "FileSystemObjectType", "form_versionNumber", "form_formID", "form_topTitle", "form_mainTitle", "form_subjectTitle", "form_DisplayFlag_Master", "form_DisplayFlag_CopyRequestIDIn", "form_DisplayFlag_JobInfo", "form_DisplayFlag_GCInfo", "form_DisplayFlag_GCInfo_Bit", "form_DisplayFlag_GCDetailInfo", "form_DisplayFlag_GCDetailInfo_Bi", "form_DisplayFlag_GCNewInfo", "form_DisplayFlag_GCNewInfo_Bit", "form_DisplayFlag_JobSetupInfo", "form_DisplayFlag_PMComboInfo", "form_DisplayFlag_PAComboInfo", "form_DisplayFlag_BusinessSetupIn", "form_DisplayFlag_SalesTaxInfo", "form_DisplayFlag_WageScaleInfo", "form_DisplayFlag_JDEProcessingIn", "form_ReadOnlyFlag_Master", "form_ReadOnlyFlag_CopyRequestIDI", "form_ReadOnlyFlag_JobInfo", "form_ReadOnlyFlag_BusinessSetupI", "form_ReadOnlyFlag_GCInfo", "form_ReadOnlyFlag_GCDetailInfo", "form_ReadOnlyFlag_GCNewInfo", "form_ReadOnlyFlag_JobSetupInfo", "form_ReadOnlyFlag_PMComboInfo", "form_ReadOnlyFlag_PAComboInfo", "form_ReadOnlyFlag_JDEProcessingI", "form_communicationText", "form_currentUser_Claims", "form_currentUser_AD_AccountId", "form_currentUser_AD_AccountStringId", "form_currentUser_AD_AccountID.1", "form_currentUser_AD_AccountText", "form_currentUser_AD_PreferredNam", "form_currentUser_jmsUserIDId", "form_currentUser_jmsUserRoleTypeId", "form_currentUser_jmsBusinessUnitId", "form_currentUser_jmsUserSystemTyId", "JobBillingAddress", "NewGC_Address", "NewGC_City", "NewGC_jmsStateIDId", "NewGC_ZipCode", "NewGC_BusinessPhone", "NewGC_FaxNumber", "NewGC_WebPage", "NewGC_Notes", "GC_Address", "GC_City", "GC_State", "GC_Zip", "GC_BusinessPhone", "GC_FaxNumber", "GC_WebPage", "GC_Notes", "GC_E1SystemNumberFK", "jmsBusinessUnitTypeIDId", "RequestUser_jmsUserIDId", "RequestUser_jmsUserRoleTypeIDId", "RequestUser_jmsBusinessUnitTypeIId", "RequestUser_jmsUserSystemTypeIDId", "RequestUser_jmsTypeIDId", "Salesman_jmsUserIDId", "ProjectManager_jmsUserIDId", "ProjectManagerCombo_jmsUserIDId", "ProjectAssistant_jmsUserIDId"}),
        #"Expanded ProjectAssistantCombo_jmsUserIDId" = Table.ExpandListColumn(#"Removed Columns1", "ProjectAssistantCombo_jmsUserIDId"),
        #"Removed Columns2" = Table.RemoveColumns(#"Expanded ProjectAssistantCombo_jmsUserIDId",{"ProjectAssistantCombo_jmsUserIDId", "Superintendent_jmsUserIDId", "Foreman_jmsUserIDId", "ForemanId", "ForemanStringId", "Print102", "Print103", "Print104", "SalesTaxIncluded", "BillingDate", "BillingDay", "JDERequestCompletedById", "JDERequestCompletedByStringId", "JDERequestCompletedDate", "JDEProcessingAssignEmailFlag", "JobNumberGenerateFlag", "SecuritySetupFlag", "JDEProcessingFlag", "JDEPostProcessingFlag", "CopyID_jmsRequestIDId", "ItemStatus", "ContentTypeId", "AuthorId", "EditorId", "Modified", "form_SubmitTag", "form_ShowButtonCount", "WFjmsReq", "ProjectManagerId", "ProjectManagerStringId", "ProjectAssistantId", "ProjectAssistantStringId", "ArchiveDate", "WFjmsReq0", "BID", "form_DisplayFlag_BIDInfo", "TestId", "ID.1", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "ParentList", "form_currentUser_AD_Account", "form_currentUser_jmsUserID", "form_currentUser_jmsUserRoleType", "form_currentUser_jmsBusinessUnit", "form_currentUser_jmsUserSystemTy", "form_currentUser_jmsTypeID"}),
        #"Expanded jmsTypeID" = Table.ExpandRecordColumn(#"Removed Columns2", "jmsTypeID", {"Title"}, {"jmsTypeID.Title"}),
        #"Removed Columns3" = Table.RemoveColumns(#"Expanded jmsTypeID",{"jmsTypeID.Title"}),
        #"Expanded jmsJobTypeID" = Table.ExpandRecordColumn(#"Removed Columns3", "jmsJobTypeID", {"Title"}, {"jmsJobTypeID.Title"}),
        #"Expanded jmsGeneralContractorID" = Table.ExpandRecordColumn(#"Expanded jmsJobTypeID", "jmsGeneralContractorID", {"Title"}, {"jmsGeneralContractorID.Title"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Expanded jmsGeneralContractorID",{"Id", "ServerRedirectedEmbedUrl", "Title", "form_currentUser_jmsTypeIDId", "jmsTypeIDId", "jmsTypeText", "jmsJobTypeIDId", "JobName", "JobAddress", "jmsGeneralContractorIDId", "NewGC_NewGCFlag", "NewGC_Company", "jmsBusinessUnitTypeText", "jmsProfitCenterIDId", "ForemanText", "jmsWorkTypeIDId", "jmsSectorTypeIDId", "jmsBillingTypeIDId", "ContractAmount", "jmsConstructionTypeIDId", "jmsTaxTypeIDId", "BidGrossMargin", "TargetStartDate", "TargetFinishDate", "BurdenRate", "OCIP", "MWBE", "Bonded", "WageScale", "LEED", "VendorCommission", "BIM", "JobNumber", "jmsJDEStatusTypeIDId", "Created", "owner", "jmsJDEStatusTypeText", "jmsJobTypeID.Title", "jmsGeneralContractorID.Title", "NewGC_jmsStateID", "jmsBusinessUnitTypeID", "jmsProfitCenterID", "RequestUser_jmsUserID", "RequestUser_jmsUserRoleTypeID", "RequestUser_jmsBusinessUnitTypeI", "RequestUser_jmsUserSystemTypeID", "RequestUser_jmsTypeID", "Salesman_jmsUserID", "ProjectManager_jmsUserID", "ProjectManagerCombo_jmsUserID", "ProjectAssistant_jmsUserID", "ProjectAssistantCombo_jmsUserID", "Superintendent_jmsUserID", "Foreman_jmsUserID", "Foreman", "jmsWorkTypeID", "jmsSectorTypeID", "jmsBillingTypeID", "jmsConstructionTypeID", "jmsTaxTypeID", "jmsJDEStatusTypeID", "ProjectManager.Title", "ProjectAssistant.Title"}),
        #"Removed Columns4" = Table.RemoveColumns(#"Reordered Columns",{"MWBE", "Bonded", "WageScale", "LEED", "VendorCommission", "BIM", "jmsJDEStatusTypeIDId"}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns4",{"Id", "ServerRedirectedEmbedUrl", "Title", "form_currentUser_jmsTypeIDId", "jmsTypeIDId", "jmsTypeText", "jmsJobTypeIDId", "JobName", "JobAddress", "jmsGeneralContractorIDId", "NewGC_NewGCFlag", "NewGC_Company", "jmsGeneralContractorID.Title", "jmsBusinessUnitTypeText", "jmsProfitCenterIDId", "ForemanText", "jmsWorkTypeIDId", "jmsSectorTypeIDId", "jmsBillingTypeIDId", "ContractAmount", "jmsConstructionTypeIDId", "jmsTaxTypeIDId", "BidGrossMargin", "TargetStartDate", "TargetFinishDate", "BurdenRate", "OCIP", "JobNumber", "Created", "owner", "jmsJDEStatusTypeText", "jmsJobTypeID.Title", "NewGC_jmsStateID", "jmsBusinessUnitTypeID", "jmsProfitCenterID", "RequestUser_jmsUserID", "RequestUser_jmsUserRoleTypeID", "RequestUser_jmsBusinessUnitTypeI", "RequestUser_jmsUserSystemTypeID", "RequestUser_jmsTypeID", "Salesman_jmsUserID", "ProjectManager_jmsUserID", "ProjectManagerCombo_jmsUserID", "ProjectAssistant_jmsUserID", "ProjectAssistantCombo_jmsUserID", "Superintendent_jmsUserID", "Foreman_jmsUserID", "Foreman", "jmsWorkTypeID", "jmsSectorTypeID", "jmsBillingTypeID", "jmsConstructionTypeID", "jmsTaxTypeID", "jmsJDEStatusTypeID", "ProjectManager.Title", "ProjectAssistant.Title"}),
        #"Merged Columns" = Table.CombineColumns(#"Reordered Columns1",{"NewGC_Company", "jmsGeneralContractorID.Title"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
        #"Renamed Columns1" = Table.RenameColumns(#"Merged Columns",{{"Merged", "GC_Merged"}}),
        #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns1",{"form_currentUser_jmsTypeIDId", "jmsTypeIDId", "jmsGeneralContractorIDId", "NewGC_NewGCFlag", "jmsJobTypeIDId", "ServerRedirectedEmbedUrl", "NewGC_jmsStateID", "jmsBusinessUnitTypeID"}),
        #"Expanded jmsProfitCenterID" = Table.ExpandRecordColumn(#"Removed Columns5", "jmsProfitCenterID", {"TitleName"}, {"jmsProfitCenterID.TitleName"}),
        #"Removed Columns6" = Table.RemoveColumns(#"Expanded jmsProfitCenterID",{"RequestUser_jmsUserID", "RequestUser_jmsUserRoleTypeID", "RequestUser_jmsBusinessUnitTypeI", "RequestUser_jmsUserSystemTypeID", "RequestUser_jmsTypeID"}),
        #"Expanded Salesman_jmsUserID" = Table.ExpandRecordColumn(#"Removed Columns6", "Salesman_jmsUserID", {"FullName"}, {"Salesman_jmsUserID.FullName"}),
        #"Removed Columns7" = Table.RemoveColumns(#"Expanded Salesman_jmsUserID",{"ProjectManager_jmsUserID", "ProjectManagerCombo_jmsUserID", "ProjectAssistant_jmsUserID", "ProjectAssistantCombo_jmsUserID"}),
        #"Expanded Foreman" = Table.ExpandRecordColumn(#"Removed Columns7", "Foreman", {"Title"}, {"Foreman.Title"}),
        #"Reordered Columns2" = Table.ReorderColumns(#"Expanded Foreman",{"Id", "Title", "jmsTypeText", "JobName", "JobAddress", "GC_Merged", "jmsBusinessUnitTypeText", "jmsProfitCenterIDId", "ForemanText", "jmsWorkTypeIDId", "jmsSectorTypeIDId", "jmsBillingTypeIDId", "ContractAmount", "jmsConstructionTypeIDId", "Foreman.Title", "jmsTaxTypeIDId", "BidGrossMargin", "TargetStartDate", "TargetFinishDate", "BurdenRate", "OCIP", "JobNumber", "Created", "owner", "jmsJDEStatusTypeText", "jmsJobTypeID.Title", "jmsProfitCenterID.TitleName", "Salesman_jmsUserID.FullName", "Superintendent_jmsUserID", "Foreman_jmsUserID", "jmsWorkTypeID", "jmsSectorTypeID", "jmsBillingTypeID", "jmsConstructionTypeID", "jmsTaxTypeID", "jmsJDEStatusTypeID", "ProjectManager.Title", "ProjectAssistant.Title"}),
        #"Removed Columns8" = Table.RemoveColumns(#"Reordered Columns2",{"Foreman.Title", "jmsTaxTypeID", "jmsJDEStatusTypeID", "Foreman_jmsUserID"}),
        #"Expanded Superintendent_jmsUserID" = Table.ExpandRecordColumn(#"Removed Columns8", "Superintendent_jmsUserID", {"FullName"}, {"Superintendent_jmsUserID.FullName"}),
        #"Expanded jmsWorkTypeID" = Table.ExpandRecordColumn(#"Expanded Superintendent_jmsUserID", "jmsWorkTypeID", {"TitleName"}, {"jmsWorkTypeID.TitleName"}),
        #"Expanded jmsSectorTypeID" = Table.ExpandRecordColumn(#"Expanded jmsWorkTypeID", "jmsSectorTypeID", {"TitleName"}, {"jmsSectorTypeID.TitleName"}),
        #"Expanded jmsBillingTypeID" = Table.ExpandRecordColumn(#"Expanded jmsSectorTypeID", "jmsBillingTypeID", {"TitleName"}, {"jmsBillingTypeID.TitleName"}),
        #"Expanded jmsConstructionTypeID" = Table.ExpandRecordColumn(#"Expanded jmsBillingTypeID", "jmsConstructionTypeID", {"TitleName"}, {"jmsConstructionTypeID.TitleName"}),
        #"Renamed Columns2" = Table.RenameColumns(#"Expanded jmsConstructionTypeID",{{"jmsTypeText", "JMS Type"}, {"JobName", "Job Name"}, {"JobAddress", "Job Address"}, {"GC_Merged", "GC"}, {"jmsBusinessUnitTypeText", "Business Unit"}}),
        #"Removed Columns9" = Table.RemoveColumns(#"Renamed Columns2",{"jmsProfitCenterIDId"}),
        #"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns9",{"Id", "Title", "JMS Type", "Job Name", "Job Address", "GC", "Business Unit", "jmsProfitCenterID.TitleName", "jmsWorkTypeID.TitleName", "jmsSectorTypeID.TitleName", "jmsJobTypeID.Title", "jmsBillingTypeID.TitleName", "ForemanText", "jmsWorkTypeIDId", "jmsSectorTypeIDId", "jmsBillingTypeIDId", "ContractAmount", "jmsConstructionTypeIDId", "jmsTaxTypeIDId", "BidGrossMargin", "TargetStartDate", "TargetFinishDate", "BurdenRate", "OCIP", "JobNumber", "Created", "owner", "jmsJDEStatusTypeText", "Salesman_jmsUserID.FullName", "Superintendent_jmsUserID.FullName", "jmsConstructionTypeID.TitleName", "ProjectManager.Title", "ProjectAssistant.Title"}),
        #"Removed Columns10" = Table.RemoveColumns(#"Reordered Columns3",{"jmsWorkTypeIDId", "jmsSectorTypeIDId", "jmsBillingTypeIDId"}),
        #"Reordered Columns4" = Table.ReorderColumns(#"Removed Columns10",{"Id", "Title", "JMS Type", "Job Name", "Job Address", "GC", "Business Unit", "jmsProfitCenterID.TitleName", "jmsWorkTypeID.TitleName", "jmsSectorTypeID.TitleName", "jmsJobTypeID.Title", "jmsBillingTypeID.TitleName", "jmsConstructionTypeID.TitleName", "ForemanText", "ContractAmount", "jmsConstructionTypeIDId", "jmsTaxTypeIDId", "BidGrossMargin", "TargetStartDate", "TargetFinishDate", "BurdenRate", "OCIP", "JobNumber", "Created", "owner", "jmsJDEStatusTypeText", "Salesman_jmsUserID.FullName", "Superintendent_jmsUserID.FullName", "ProjectManager.Title", "ProjectAssistant.Title"}),
        #"Removed Columns11" = Table.RemoveColumns(#"Reordered Columns4",{"jmsConstructionTypeIDId", "jmsTaxTypeIDId"}),
        #"Reordered Columns5" = Table.ReorderColumns(#"Removed Columns11",{"Id", "Title", "JMS Type", "Job Name", "Job Address", "GC", "Business Unit", "jmsProfitCenterID.TitleName", "jmsWorkTypeID.TitleName", "jmsSectorTypeID.TitleName", "jmsJobTypeID.Title", "jmsBillingTypeID.TitleName", "jmsConstructionTypeID.TitleName", "ContractAmount", "BidGrossMargin", "TargetStartDate", "TargetFinishDate", "BurdenRate", "OCIP", "JobNumber", "Created", "owner", "jmsJDEStatusTypeText", "Salesman_jmsUserID.FullName", "ProjectManager.Title", "ProjectAssistant.Title", "Superintendent_jmsUserID.FullName", "ForemanText"}),
        #"Removed Columns12" = Table.RemoveColumns(#"Reordered Columns5",{"Job Address"}),
        #"Reordered Columns6" = Table.ReorderColumns(#"Removed Columns12",{"Id", "Title", "Job Name", "GC", "JMS Type", "Business Unit", "jmsProfitCenterID.TitleName", "jmsWorkTypeID.TitleName", "jmsSectorTypeID.TitleName", "jmsJobTypeID.Title", "jmsBillingTypeID.TitleName", "jmsConstructionTypeID.TitleName", "ContractAmount", "BidGrossMargin", "TargetStartDate", "TargetFinishDate", "BurdenRate", "OCIP", "JobNumber", "Created", "owner", "jmsJDEStatusTypeText", "Salesman_jmsUserID.FullName", "ProjectManager.Title", "ProjectAssistant.Title", "Superintendent_jmsUserID.FullName", "ForemanText"}),
        #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns6",{{"jmsProfitCenterID.TitleName", "Profit Center"}, {"jmsWorkTypeID.TitleName", "Work Type"}, {"jmsSectorTypeID.TitleName", "Sector Type"}, {"jmsJobTypeID.Title", "Job Type"}, {"jmsBillingTypeID.TitleName", "Billing  Type"}, {"jmsConstructionTypeID.TitleName", "Construction Type"}, {"ContractAmount", "Amount"}, {"BidGrossMargin", "Bid Gross Margin"}, {"TargetStartDate", "Target Start Date"}, {"TargetFinishDate", "Target Finish Date"}, {"BurdenRate", "Burden Rate"}}),
        #"Reordered Columns7" = Table.ReorderColumns(#"Renamed Columns3",{"Id", "Title", "JobNumber", "Job Name", "GC", "JMS Type", "Business Unit", "Profit Center", "Work Type", "Sector Type", "Job Type", "Billing  Type", "Construction Type", "Amount", "Bid Gross Margin", "Target Start Date", "Target Finish Date", "Burden Rate", "OCIP", "Created", "owner", "jmsJDEStatusTypeText", "Salesman_jmsUserID.FullName", "ProjectManager.Title", "ProjectAssistant.Title", "Superintendent_jmsUserID.FullName", "ForemanText"}),
        #"Renamed Columns4" = Table.RenameColumns(#"Reordered Columns7",{{"JobNumber", "Job Number"}, {"owner", "Owner"}, {"jmsJDEStatusTypeText", "Status"}, {"Salesman_jmsUserID.FullName", "Salesman"}, {"ProjectManager.Title", "PM"}, {"ProjectAssistant.Title", "PA"}, {"Superintendent_jmsUserID.FullName", "Superintendent"}, {"ForemanText", "Foreman"}})
    in
        #"Renamed Columns4"
    

    Thursday, July 14, 2016 7:16 PM
  • Hi there. All those expand operations are going to cause additional requests to be sent to the SharePoint service, which is going to slow things down considerably. My only recommendation at this point is to try to reduce the number of expansion operations you're doing, or to experiment with other ways you might be able to simplify the query.

    Ehren

    Friday, July 15, 2016 6:11 PM
    Owner
  • Hi Shamik,

    I am also agree with Ehren.


    Hope this help.

    Vote - if you find this helpful

    Marked as Answer - if you see this reply being an answer to the question of the thread


    Regards,

    Amjad Khan

    Blog: https://amjadk.com

    Friday, July 15, 2016 6:20 PM
  • Thanks Ehren! After a lot of hours of hit and trial I have come the same conclusion. As a result,  I got rid of the expand operations and I am now using the Lookupvalue function to get the same dataset. 

    Appreciate your help!

    Thanks,

    Shamika

    Friday, July 15, 2016 6:33 PM
  • How did you achieve that? I need to expand multiple column but that is a very expensive operation and I am interested to see how you overcome that.
    Wednesday, September 26, 2018 7:49 PM