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])
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.
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])
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.
