Forum Discussion
How to create a parent child hierarchy using PATH when having data quality issues
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 🙂
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.
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])
5 Replies
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
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 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.
