Forum Discussion
Hierarchy Levels
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"
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