Making a hierachy chain

[hakunamatatata1]

Hello! I got my data from a SP list that looks like this:

EmailEmployee   EmployeeName   ManagerEmail    ManagerName    Date

a@lala.eu            A                          c@lala.eu            C                          xx/xx/xxx

b@lala.eu            B                          c@lala.eu            C                          xx/xx/xxx

c@lala.eu            C                          d@lala.eu            D                         xx/xx/xxx

d@lala.eu           D                           e@lala.eu            E                          xx/xx/xxx

What I want to get is chain management, something like this but I don't know how many levels of management are there.

Employee Name  ManagerNameLV1  ManagerNameLv2  ManagerNameLV3 

A                           C                             D                             E

B                           C                             D                             E

C                           D                             E                             -

D                           E                              -                             -

E                            -                              -                             -

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. Does anyone have an idea? Thanks a lot for your time

[Vijay_A_Verma · Accepted solution]

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]&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"

[jennratten]

Hello - yes, this can be done in Power BI.  It should be done with DAX rather than Power Query.  Here is a really great overview of the process that includes a lot of examples.

https://www.daxpatterns.com/parent-child-hierarchies/