Forum Discussion
hakunamatatata1
4 years agoRegular Visitor
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] ...
- 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"
Vijay_A_Verma
4 years agoMost Valuable Professional
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"