Loop / recursive / DAX / PowerQuery / Same Table loop

[vyask]

Hi, I am new to Power BI and I need help with creating a loop using DAX or Power Query.

Problem Statement:
I have only 1 table (which shows unidirectional links between workitems) with 4 columns.

SourceWorkItemId	ParentWorkItemId	SourceWorkItemType	ParentWorkItemType	CapID (OUTPUT)
11	22	Story1	Feature1	66
22	33	Feature1	Epic1	66
44	55	Theme1	MBI1	0
33	44	Epic1	Theme1	0
55	66	MBI1	Cap1	0
111	222	Story2	Cap1	222
1111	2222	Story3	Cap2	2222
1	66	Story4	Cap1	66
77	2222	Epic2	Cap2	0

The WorkItemType hierarchy is not fixed. It can be Story -> Feature -> Cap or it can be Story -> Cap or anything else.

The maximum depth (in this order) is: Story -> Feature -> Epic -> Theme -> MBI -> Cap.

I need to show CapID against Stories and Features that are directly or indirectly linked to that Cap by adding 1 new column in the existing table.

I tried to do this using a python script which works fine in python but it times-out in Power BI and gives 1800000ms error, probably because of the table size.

Code Example with comments:

Cap = []                                                   // created a blank list
for indices,row in df.iterrows():                          // started a for loop    
     if row['SourceWorkItemType'] in ['Story','Feature']:  // if SourceType is Story/Feature
          if row['ParentWorkItemType'] == 'Cap':           // if current ParentType is Cap               
               Cap.append(row['ParentWorkItemId'])          // append current CapID to list Cap
          else:                                            // if ParentType is not Cap               
               for indices,row_1 in df.iterrows():         // nested for loop
                    if row['ParentWorkItemId'] == row_1['SourceWorkItemId']: // if ParentID is found in another row's SourceID 
                         if row_1['ParentWorkItemType'] != 'Cap':  // if ParentType is not Cap
                              row['ParentWorkItemId'] = row_1['ParentWorkItemId'] // then search new ParentID in another row's sourceID
                         else:                                // else if current Parent is Cap                                    
                              Cap.append(row_1['ParentWorkItemId'])  // add the current ParentID to Cap list         
     else:                  // if SourceType is not Story/Feature or if CapID is not found
          Cap.append(0)               // append 0 to the Cap list
print(Cap)                          // add Cap list as the 5th column

Request you to help me with this. Thanks in advance! 🙂