Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.