Performance Improvement Help Required

[BBurnett]

I have created a column and set the value dependant on the values within a number of other tables. The problem that I have is that in order to refresh the data in the table (or more soecifically the new column) it takes approx. 3 days for less than 5000 rows which seems somewhat excessive.  Below is the queries that I am using and I would love to get some suggestions on ways to do things better.

Tasks Table - (The table taking the time to refresh)

let
Source = OData.Feed("https://analytics.dev.azure.com/xxx/xxx/_odata/v3.0-preview/WorkItems?$select=WorkItemId,Title,State,StoryPoints,ParentWorkItemId,CompletedWork, AreaSK &$expand=Iteration($select=IterationPath,IterationName,IterationId) &$filter= WorkItemType eq 'Task'", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4]),
Custom1 = Table.ExpandRecordColumn(Source, "Iteration", {"IterationName", "IterationPath", "IterationId"}, {"Iteration.IterationName", "Iteration.IterationPath", "Iteration.IterationId"}),
Custom2 = if List.Count(List.FindText(Table.ColumnNames(Custom1), "WorkItemType" ))= 1 then Custom1 else Table.AddColumn(Custom1, "WorkItemType", each null),
Custom3 = Table.ReplaceValue(Custom2, each [WorkItemType], each if [WorkItemType] is null then LookUpItems([ParentWorkItemId], Bugs, "WorkItemId", "Bug") else [WorkItemType], Replacer.ReplaceValue, {"WorkItemType"}),
Custom4 = Table.ReplaceValue(Custom3, each [WorkItemType], each if [WorkItemType] is null then LookUpItems([ParentWorkItemId], LookUpSupportUserStories, "WorkItemId", "Support") else [WorkItemType], Replacer.ReplaceValue, {"WorkItemType"}),
Custom5 = Table.ReplaceValue(Custom4, each [WorkItemType], each if [WorkItemType] is null then LookUpItems([ParentWorkItemId], LookUpInfraUserStories, "WorkItemId", "Infrastructure") else [WorkItemType], Replacer.ReplaceValue, {"WorkItemType"}),
Custom6 = Table.ReplaceValue(Custom5, each [WorkItemType], each if [WorkItemType] is null then "Feature" else [WorkItemType], Replacer.ReplaceValue, {"WorkItemType"}) 
in
Custom6

 LookUpSupportUserStories (returns instantly)

let
featureList = Table.Column(LookUpSupportFeatures, "WorkItemId"),
userStories = Table.SelectRows(User_Stories, each List.Contains(featureList, [ParentWorkItemId]) )
in
userStories

LookUpSupportFeatures (returns instantly)

let
SupportFeatures = Table.SelectRows(Features, each Text.Contains([Title], "Support Request"))
in
SupportFeatures

The other lookup calls are similar the call for the bugs returns really quickly and isn't a problem the above calls are the problem. Relationships are as follows:

Any help that people can provide would be greatly appreciated. I am not overly skilled at Power Query and teaching myself from a Java background although haven't touched code in a loooong time!

[v-luwang-msft]

Hi  @BBurnett ,

The best practice for power query is mentioned in the official article, you can refer to the official article to optimize against your operation. Wish it is helpful for you!

Best practices when working with Power Query

Best Regards

Lucien