Hierarchy Levels

[Honne2021]

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

[Vijay_A_Verma · Accepted solution]

Put following formula in a custom column

= List.PositionOf(Record.ToList(_),"Person Z")-1

See 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"

[Vijay_A_Verma · Accepted solution]

Put following formula in a custom column

= List.PositionOf(Record.ToList(_),"Person Z")-1

See 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"

[Vijay_A_Verma]

It will take the first Person Z only not any other. 

[Honne2021 · Accepted solution]

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

[Honne2021]

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!