Forum Discussion
Hierarchy Levels
Hi, I have a data that looks like this with thousand of rows and I need to know how many levels does each employee has to get to Person Z. In this example, the answer for Joon should be 4 as there are 4 persons to reach Person Z. Kook should be 1.
| Employee | Level 1 | Level 2 | Level 3 | Level 4 | Level 5 | Level 6 | Level 7 | Level 8 |
| Joon | Person A | Person B | Person H | Person Y | Person Z | |||
| Kook | Person F | Person Z |
Thank you in advance
Put following formula in a custom column
= List.PositionOf(Record.ToList(_),"Person Z")-1See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPz1PSUQpILSrOz1NwRDCdEEwPBDMSwYwCMqEoVidayTs/Pxsh6YZVHUJDLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Level 1" = _t, #"Level 2" = _t, #"Level 3" = _t, #"Level 4" = _t, #"Level 5" = _t, #"Level 6" = _t, #"Level 7" = _t, #"Level 8" = _t]), #"Added Custom" = Table.AddColumn(Source, "Level", each List.PositionOf(Record.ToList(_),"Person Z")-1, Int64.Type) in #"Added Custom"Hmm it seems to count the second Person Z. I just changed the formula to -2 instead of -1. I appreciate your quick assistance.
= List.PositionOf(Record.ToList(_),"Person Z")-1
4 Replies
Put following formula in a custom column
= List.PositionOf(Record.ToList(_),"Person Z")-1See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPz1PSUQpILSrOz1NwRDCdEEwPBDMSwYwCMqEoVidayTs/Pxsh6YZVHUJDLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Level 1" = _t, #"Level 2" = _t, #"Level 3" = _t, #"Level 4" = _t, #"Level 5" = _t, #"Level 6" = _t, #"Level 7" = _t, #"Level 8" = _t]), #"Added Custom" = Table.AddColumn(Source, "Level", each List.PositionOf(Record.ToList(_),"Person Z")-1, Int64.Type) in #"Added Custom"Hi. Sorry I forgot to mention that there are instances where in Person Z is in two columns like this. What formula should I add so that only the first Person Z is counted. Hence Kook should be 1 instead of 2.
Employee Level 1 Level 2 Level 3 Level 4 ...until Level 20 Kook Person F Person Z Person Z
Thank you for your help!It will take the first Person Z only not any other.
Hmm it seems to count the second Person Z. I just changed the formula to -2 instead of -1. I appreciate your quick assistance.
= List.PositionOf(Record.ToList(_),"Person Z")-1