Making a hierachy chain

Question

Hello! I got my data from a SP list that looks like this:&nbsp;EmailEmployee&nbsp; &nbsp;EmployeeName&nbsp; &nbsp;ManagerEmail&nbsp; &nbsp; ManagerName&nbsp; &nbsp; Datea@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; c@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xx/xx/xxxb@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; c@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;xx/xx/xxxc@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; d@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;xx/xx/xxxd@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;e@lala.eu&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xx/xx/xxx&nbsp;What I want to get is chain management, something like this but I don't know how many levels of management are there.&nbsp;Employee Name&nbsp; ManagerNameLV1&nbsp; ManagerNameLv2&nbsp; ManagerNameLV3&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-Is there a way to obtain that in PowerBI? Even if I don't know exactly how many levels of management there are? I can say maximum 5 leves, at least for a solution that is not dynamic.&nbsp;Does anyone have an idea? Thanks a lot for your time

vijay_a_verma · Accepted Answer

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (This for 5 levels)let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSnTIScxJ1EstVdJRcgTiZCS+MxBXVOiDUYVSrE60UhKSrBNB1eiyKUh8FwzV6LKpSHxXVNWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmailEmployee = _t, EmployeeName = _t, ManagerEmail = _t, ManagerName = _t, Date = _t]),
    Custom1 = Table.FromList(List.Distinct(Source[EmployeeName]&amp;Source[ManagerName]),null,{"Employee Name"}),
    #"Added Custom" = Table.AddColumn(Custom1, "ManagerNameLv1", each try Source{[EmployeeName=[Employee Name]]}[ManagerName] otherwise null, type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ManagerNameLv2", each try Source{[EmployeeName=[ManagerNameLv1]]}[ManagerName] otherwise null, type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ManagerNameLv3", each try Source{[EmployeeName=[ManagerNameLv2]]}[ManagerName] otherwise null, type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "ManagerNameLv4", each try Source{[EmployeeName=[ManagerNameLv3]]}[ManagerName] otherwise null, type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "ManagerNameLv5", each try Source{[EmployeeName=[ManagerNameLv4]]}[ManagerName] otherwise null, type text),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom4", {"Employee Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"&nbsp;

Forum Discussion

Making a hierachy chain

2 Replies

Related content

Supply Chain Analytics Template | DataFlip

Date Hierachy

strategy for org hierachies long term

Problem with customData on chained/composite datasets on embedded viewing

Certificate chain refresh error