Forum Discussion
Solved
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 ar...
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
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"
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