Devops flatten workitems hierarchy

[suojis]

Hi gurus,

I have following devops odata script:

https://analytics.dev.azure.com/xxx/yyy/_odata/v3.0-preview/WorkItems?

            $select=WorkItemId

            &$expand=Links($select=TargetWorkItemId,LinkTypeName)

This returns table into power bi:

WorkItemId       TargetWorkItemId      LinkTypeName

1                        2                                 Child

1                        3                                 Child

2                        4                                 Child

3                        1                                 Related

Which is best way to create following table

WorkItemId       TargetWorkID          LinkTypeName          Level

1                        2                                 Child                        1

1                        3                                 Child                        1

1                        4                                 Child                        2

2                        4                                 Child                        1

3                        1                                 Related                    1

3                        2                                 Child                        2

3                        4                                 Child                        3

So I need flatten recursive table for all master items. There are loops in structure, so level depth must define in table creation script or recursive loop must stop when master item or structure item comes second time in loop.

Any hints?

[edhans]

Can you explain your data a bit more @suojis ? 

I don't understand row 3. Why is there a relatinship between 1 and 4?

Also, if you provide additional data, please use the table option so there aren't a few hundred spaces and char(160) to get rid of. There is more about sharing data to the forum at the links below.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[suojis]

Sorry about text data. Here is my data in tables.
Original

WorkItemId	TargetWorkItemId	LinkTypeName
1	2	Child
1	3	Child
2	4	Child
3	1	Related

Goal

WorkItemId	TargetWorkID	LinkTypeName	Level
1	2	Child	1
1	3	Child	1
1	4	Child	2
2	4	Child	1
3	1	Related	1
3	2	Child	2
3	4	Child	3

So row 3 shows workitemid 1 second hierarchy level. If I want to show all hierarchy levels (all parts and subparts), then I just filter column workitemid. Thanks for reply and hope this helps. @edhans 

[v-shex-msft]

Hi @suojis,

I write two custom functions to analytics raw table records and extract the corresponding records, you can try it if helps. (notice: these formulas still required additional processing to check relationship levels and replace node fields values)

Recursive functions:

let
    FindDenpency=(source as table, current as number, target as number, optional output as table)=>
    let
        search=
            if output <> null 
            then Table.SelectRows(source, each [LinkTypeName]="Child" and [TargetWorkItemId]=current)
            else Table.SelectRows(source, each [LinkTypeName]="Child" and [WorkItemId]=current and [TargetWorkItemId]=target),
        merged= 
            if output <> null 
            then Table.Combine({search,output}) 
            else search,
        result = 
            if Table.RowCount(search) > 0
            then FindDenpency(source, search{0}[WorkItemId], search{0}[TargetWorkItemId], merged)
            else merged
    in
        result
in
    FindDenpency

let
    FindReference=(source as table, target as number, optional item as list, optional output as table)=>
    let
        search = 
            if item <> null
            then Table.SelectRows(source, each [LinkTypeName]="Child" and List.Contains(item, [WorkItemId]))
            else Table.SelectRows(source,each [LinkTypeName]="Child" and [WorkItemId] = target),
        merged= 
            if output <> null 
            then Table.Combine({search,output}) 
            else search,
        result = 
            if Table.RowCount(search) >0 
            then FindReference(source,target, search[TargetWorkItemId],merged) 
            else merged        
    in
        result
in
    FindReference

Test table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYueMzJwUpVgdiIgxighI3gRFBCQPUheUmpNYkgoUiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, TargetWorkItemId = _t, LinkTypeName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"TargetWorkItemId", Int64.Type}, {"LinkTypeName", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Relationship", each 
        if [LinkTypeName]="Child" 
        then FindDenpency(#"Changed Type",[WorkItemId],[TargetWorkItemId])
        else FindReference(#"Changed Type",[TargetWorkItemId]))
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[edhans]

Hi @suojis - if someone else jumps in that is great. I am slammed and I need to take a step back and look at this, so I haven't abandoned it, but this is not a quick fix that I can see yet. I will look at more earnestly tonight or tomorrow when I am past my deadline today with work. 👍

[Greg_Deckler]

@ImkeF @edhans @HotChilli