How to create a parent child hierarchy using PATH when having data quality issues

[42]

Hello,

I'm new to Power BI and making queries so I hope I can get some help here.

I want to visualize data from Azure DevOps in power bi. To do so I have created an analytics view in DevOps which I then connect to using online services. The data I want to visualize are work items that is ordered hierarchically in Azure DevOps. 

After some searching I found that using the PATH function seems to be the best way to recreate this parent child hierarchy in Power BI. In case anyone has a better suggestion than using PATH, please let me know 🙂 

But when I use the path function i run into problems that seems to be due to poor data quality in the DevOps data. I.e. "parent work items" doesn't exist as own "work items", see below:

Now, since I export all work items from DevOps I find it strange why this error even happen, but after spending a few hours trying to troubleshoot the export view from DevOps I would now rather have a solution to take care of the issue on Power BI side.

So the solution I'm looking for now is that in case a parent work item doesn't exist as its own work item, I would either rather empty the parent work item column or create a new work item just so that I can use the path functionality and then continue to build up the parent child hierarchy in Power BI....

Please help a rookie 🙂

[42 · Accepted solution]

Hello again,

I found the solution myself so in case anyone would be interested for the future, here it is.

I used a two-step approach which is higlighted below:

1. In the Power Query editor I created a new column called Parent Path in to which I copied the  Parent Work Item IDs that had a matching Work Item ID-row. I also ensured that these columns were of the same type so that the path- formula later used in the next step would work.

let
    Source = Excel.Workbook(File.Contents("\\..Data\Desktop\path-powerBI.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Parent Path" = Table.AddColumn(#"Promoted Headers", "Parent Path", each if List.Contains(#"Promoted Headers"[Work Item Id], [Parent Work Item Id]) then [Parent Work Item Id] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Parent Path",{{"Work Item Id", Int64.Type}, {"Title", type text}, {"Parent Work Item Id", Int64.Type}, {"Parent Path", Int64.Type}})
in
    #"Changed Type"

2. The second step was then to in the modeling in Power BI add a column and populate it using the path-formula referencing the original Work Item Id column and the new Parent Path column.

[Greg_Deckler]

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[42]

Here are some example data and snapshots illustrating the problem:

First a one illustrating the issue. The problem is with Work Item ID = 34

Then an example of when it works (I have removed Work Item ID = 34)

And last the example data used:

Work Item Id	Title	Parent Work Item Id
11	A
12	B
13	C
211	A1	11
212	A2	11
221	B1	12
222	B2	12
231	C1	13
232	C2	13
31	D	211
32	E	222
33	F	231
34	G	500
41	H	211
42	I	221
43	J	232

[mvsk1987]

i have a similar data set to the above but in my case its employees and managers. in the data set which is being used by the report not every manager will be presented as an employee as its a subset of data being used from an excel. so removing the row is not an option . how do i handle that ? i am creating the path as i need to use a dynamic RLS for the manager to be able to see his/her direct and indirect reportees and his own record if it exists.

[42 · Accepted solution]

Hello again,

I found the solution myself so in case anyone would be interested for the future, here it is. I used a two-step approach:

1. In the Power Query editor I created a new column called Parent Path in to which I copied the Parent Work Item IDs that had a matching Work Item ID-row. I also ensured that these columns were of the same type so that the path- formula later used in the next step would work.

let

Source = Excel.Workbook(File.Contents("\\..Data\Desktop\path-powerBI.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

#"Parent Path" = Table.AddColumn(#"Promoted Headers", "Parent Path", each if List.Contains(#"Promoted Headers"[Work Item Id], [Parent Work Item Id]) then [Parent Work Item Id] else null),

#"Changed Type" = Table.TransformColumnTypes(#"Parent Path",{{"Work Item Id", Int64.Type}, {"Title", type text}, {"Parent Work Item Id", Int64.Type}, {"Parent Path", Int64.Type}})

in

#"Changed Type"

2. The second step was then to in the modeling in Power BI add a column and populate it using the path-formula

Path = PATH(Sheet1[Work Item Id];Sheet1[Parent Path])

[42 · Accepted solution]

Hello again,

I found the solution myself so in case anyone would be interested for the future, here it is.

I used a two-step approach which is higlighted below:

1. In the Power Query editor I created a new column called Parent Path in to which I copied the  Parent Work Item IDs that had a matching Work Item ID-row. I also ensured that these columns were of the same type so that the path- formula later used in the next step would work.

let
    Source = Excel.Workbook(File.Contents("\\..Data\Desktop\path-powerBI.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Parent Path" = Table.AddColumn(#"Promoted Headers", "Parent Path", each if List.Contains(#"Promoted Headers"[Work Item Id], [Parent Work Item Id]) then [Parent Work Item Id] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Parent Path",{{"Work Item Id", Int64.Type}, {"Title", type text}, {"Parent Work Item Id", Int64.Type}, {"Parent Path", Int64.Type}})
in
    #"Changed Type"

2. The second step was then to in the modeling in Power BI add a column and populate it using the path-formula referencing the original Work Item Id column and the new Parent Path column.