Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
suojis
New Member

Devops flatten workitems hierarchy

Hi gurus,

 

I have following devops odata script:

            $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?
5 REPLIES 5
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Sorry about text data. Here is my data in tables.
Original
WorkItemId TargetWorkItemIdLinkTypeName
12Child
13Child
24Child
31Related

Goal
WorkItemIdTargetWorkIDLinkTypeNameLevel
12Child1
13Child1
14Child2
24Child1
31Related1
32Child2
34Child3
 
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 

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"

90.gif
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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. 👍



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@ImkeF @edhans @HotChilli 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.