Forum Discussion

hakunamatatata1's avatar
hakunamatatata1
Regular Visitor
4 years ago
Solved

Making a hierachy chain

Hello! I got my data from a SP list that looks like this:   EmailEmployee   EmployeeName   ManagerEmail    ManagerName    Date [email protected]            A                          [email protected]            ...
  • Vijay_A_Verma's avatar
    4 years ago

    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"