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