Devops flatten workitems hierarchy

Question

Hi gurus,&nbsp;I have following devops odata script:https://analytics.dev.azure.com/xxx/yyy/_odata/v3.0-preview/WorkItems?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$select=WorkItemId&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;$expand=Links($select=TargetWorkItemId,LinkTypeName)&nbsp;This returns table into power bi:WorkItemId&nbsp; &nbsp; &nbsp; &nbsp;TargetWorkItemId&nbsp; &nbsp; &nbsp; LinkTypeName1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Related&nbsp;&nbsp;&nbsp;Which is best way to create following table&nbsp;WorkItemId&nbsp; &nbsp; &nbsp; &nbsp;TargetWorkID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LinkTypeName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Level1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Related&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Child&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;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 · Answer

Hi&nbsp;suojis&nbsp;- 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 · Answer

ImkeF&nbsp;edhans&nbsp;HotChilli&nbsp;

edhans · Answer

Can you explain your data a bit more&nbsp;suojis&nbsp;?&nbsp;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 QuicklyHow to provide sample data in the Power BI Forum

suojis · Answer

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

anonymous · Answer

Hi&nbsp;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)=&gt;
    let
        search=
            if output &lt;&gt; 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 &lt;&gt; null 
            then Table.Combine({search,output}) 
            else search,
        result = 
            if Table.RowCount(search) &gt; 0
            then FindDenpency(source, search{0}[WorkItemId], search{0}[TargetWorkItemId], merged)
            else merged
    in
        result
in
    FindDenpencylet
    FindReference=(source as table, target as number, optional item as list, optional output as table)=&gt;
    let
        search = 
            if item &lt;&gt; 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 &lt;&gt; null 
            then Table.Combine({search,output}) 
            else search,
        result = 
            if Table.RowCount(search) &gt;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

Forum Discussion

Devops flatten workitems hierarchy

5 Replies

Related content

Power Query - flatten Parent-child hierarchy

Flatten Parent Child Hierarchy

PowerBi Azure DevOps OData WorkItem hierarchy

Flattening of parent-child hierarchy in power query.

Flattened Hierarchy - How to hidden blanks