Forum Discussion
Transform employee data
i have data like this
| empno | ename | job | mgr | hiredate | sal | comm | dept |
| 1 | JOHNSON | ADMIN | 6 | 12/17/1990 | 18000 | NULL | 4 |
| 2 | HARDING | MANAGER | 9 | 2/2/1998 | 52000 | 300 | 3 |
| 3 | TAFT | SALES I | 2 | 1/2/1996 | 25000 | 500 | 3 |
| 4 | HOOVER | SALES I | 2 | 4/2/1990 | 27000 | NULL | 3 |
| 5 | LINCOLN | TECH | 6 | 6/23/1994 | 22500 | 1400 | 4 |
| 6 | GARFIELD | MANAGER | 9 | 5/1/1993 | 54000 | NULL | 4 |
| 7 | POLK | TECH | 6 | 9/22/1997 | 25000 | NULL | 4 |
| 8 | GRANT | ENGINEER | 10 | 3/30/1997 | 32000 | NULL | 2 |
| 9 | JACKSON | CEO | 0 | 1/1/1990 | 75000 | NULL | 4 |
| 10 | FILLMORE | MANAGER | 9 | 8/9/1994 | 56000 | NULL | 2 |
| 11 | ADAMS | ENGINEER | 10 | 3/15/1996 | 34000 | NULL | 2 |
| 12 | WASHINGTON | ADMIN | 6 | 4/16/1998 | 18000 | NULL | 4 |
| 13 | MONROE | ENGINEER | 10 | 12/3/2000 | 30000 | NULL | 2 |
| 14 | ROOSEVELT | CPA | 9 | 10/12/1995 | 35000 | NULL | 1 |
but i want data like below and i want to it to be done in Power Query
| empno | ename | Level | Manager ID | ManagerName | Manager Designation |
| 1 | JOHNSON | 1 | 6 | GARFIELD | MANAGER |
| 1 | JOHNSON | 2 | 9 | JACKSON | CEO |
| 3 | TAFT | 1 | 2 | HARDING | MANAGER |
| 3 | TAFT | 2 | 10 | FILLMORE | MANAGER |
| 3 | TAFT | 3 | 9 | JACKSON | CEO |
@mgwena @cham @amitchandak @Greg_Deckler @Mariusz v-yingjl v-juanli-msft
This version gets rid of those highlight rows and brings back the CEO row. If you want, you can add steps to replace the null values for the CEO's manager info.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVLLjoMwDPyVFedKwXlAOUY0QNqQVIF2DxX//xtrJ0Vb1BwSDxKT8Yz9elVQnaprmPwSPCJ9mS3VBg9wBi2Drqvp41zXVP3DOSyy2k6viiOadLxYPyKatdejiYg6PJxx4p4RKp65It+JKhCtelixLNqZ5ccSh4QyjxrgKvPUB0+SZAjPpHNkysykf3l76DZTFSJnfR8cOVxNP72NNowLYtLbnGc1kKlkn/TTqONgjbt8GVUMiEyGlCyE1CK6B3c7anaMp27bD58HGgU3Ru0pIeNH601ShBQFE/VOFvxA5olMfV11f8tD7U3AO7nKvRJsS6Lp9cE6N4dovpyeWbfHpJqCKkDaID0vxZ5B7YMVssSmGf7qZcJtWr+WUTJo9n0q7iLQAObgYzAFddxlwf7XsKBOrmIIi3kaR5n3d/22DZh1mhUtkDjGBtW2/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [empno = _t, ename = _t, job = _t, mgr = _t, hiredate = _t, sal = _t, comm = _t, dept = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"empno", Int64.Type}, {"ename", type text}, {"job", type text}, {"mgr", Int64.Type}, {"hiredate", type date}, {"sal", Int64.Type}, {"comm", type text}, {"dept", Int64.Type}}),
RemovedOtherColumns = Table.SelectColumns(#"Changed Type",{"empno", "ename", "job", "mgr"}),
#"Added Custom" = Table.AddColumn(RemovedOtherColumns, "Path", each let //Define your columns here
mytable=RemovedOtherColumns,p="mgr",c="empno"
in
let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
],
each [y])
in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Path", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Path.1", "Path.2", "Path.3", "Path.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Path.1", Int64.Type}, {"Path.2", Int64.Type}, {"Path.3", Int64.Type}, {"Path.4", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"empno", "ename", "job", "mgr"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "PathID"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"PathID"}, RemovedOtherColumns, {"empno"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"empno", "ename", "job"}, {"empno.1", "ename.1", "job.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Removed Columns",{{"job.1", "Role"}, {"ename.1", "Name"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"mgr", "PathID"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"empno", Order.Ascending}}),
#"Renamed Columns2" = Table.RenameColumns(#"Sorted Rows",{{"empno.1", "mgr"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each ([mgr] <> [empno])),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name] <> null or [job] = "CEO"))
in
#"Filtered Rows1"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
5 Replies
You can just merge your table with itself to get the manager info for each employee. Try this M code in a blank query with (it has your sample data in the Source step). See image too.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVLLjoMwDPyVFedKwXlAOUY0QNqQVIF2DxX//xtrJ0Vb1BwSDxKT8Yz9elVQnaprmPwSPCJ9mS3VBg9wBi2Drqvp41zXVP3DOSyy2k6viiOadLxYPyKatdejiYg6PJxx4p4RKp65It+JKhCtelixLNqZ5ccSh4QyjxrgKvPUB0+SZAjPpHNkysykf3l76DZTFSJnfR8cOVxNP72NNowLYtLbnGc1kKlkn/TTqONgjbt8GVUMiEyGlCyE1CK6B3c7anaMp27bD58HGgU3Ru0pIeNH601ShBQFE/VOFvxA5olMfV11f8tD7U3AO7nKvRJsS6Lp9cE6N4dovpyeWbfHpJqCKkDaID0vxZ5B7YMVssSmGf7qZcJtWr+WUTJo9n0q7iLQAObgYzAFddxlwf7XsKBOrmIIi3kaR5n3d/22DZh1mhUtkDjGBtW2/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [empno = _t, ename = _t, job = _t, mgr = _t, hiredate = _t, sal = _t, comm = _t, dept = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"empno", Int64.Type}, {"ename", type text}, {"job", type text}, {"mgr", Int64.Type}, {"hiredate", type date}, {"sal", Int64.Type}, {"comm", type text}, {"dept", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"empno", "ename", "job", "mgr"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"mgr"}, #"Removed Other Columns", {"empno"}, "Removed Other Columns", JoinKind.LeftOuter),
#"Expanded Removed Other Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Other Columns", {"ename", "job"}, {"ename.1", "job.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Removed Other Columns",{{"ename.1", "Manager"}, {"job.1", "Manager Designation"}})
in
#"Renamed Columns"If this works for you, please mark it as the solution. Kudos are also appreciated. Please let me know if not.
Regards,
Patpat , thanks for taking time to look into my query
it is no the outputi am looking for , if you look at the same output Johnson should have two line items one for his immediate manager and other is manager's -manager (CEO) .
My bad. I should have read your post more closely. Please try this M code instead. It uses the M equivalent of the PATH() DAX function found here - PATH Function in M
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVLLjoMwDPyVFedKwXlAOUY0QNqQVIF2DxX//xtrJ0Vb1BwSDxKT8Yz9elVQnaprmPwSPCJ9mS3VBg9wBi2Drqvp41zXVP3DOSyy2k6viiOadLxYPyKatdejiYg6PJxx4p4RKp65It+JKhCtelixLNqZ5ccSh4QyjxrgKvPUB0+SZAjPpHNkysykf3l76DZTFSJnfR8cOVxNP72NNowLYtLbnGc1kKlkn/TTqONgjbt8GVUMiEyGlCyE1CK6B3c7anaMp27bD58HGgU3Ru0pIeNH601ShBQFE/VOFvxA5olMfV11f8tD7U3AO7nKvRJsS6Lp9cE6N4dovpyeWbfHpJqCKkDaID0vxZ5B7YMVssSmGf7qZcJtWr+WUTJo9n0q7iLQAObgYzAFddxlwf7XsKBOrmIIi3kaR5n3d/22DZh1mhUtkDjGBtW2/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [empno = _t, ename = _t, job = _t, mgr = _t, hiredate = _t, sal = _t, comm = _t, dept = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"empno", Int64.Type}, {"ename", type text}, {"job", type text}, {"mgr", Int64.Type}, {"hiredate", type date}, {"sal", Int64.Type}, {"comm", type text}, {"dept", Int64.Type}}),
RemovedOtherColumns = Table.SelectColumns(#"Changed Type",{"empno", "ename", "job", "mgr"}),
#"Added Custom" = Table.AddColumn(RemovedOtherColumns, "Path", each let //Define your columns here
mytable=RemovedOtherColumns,p="mgr",c="empno"
in
let mylist={Record.Field(_,c)} & List.Generate(()=>[x=0,y=Record.Field(_,p),w=1],each [w] > 0,each [z=[y], x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,c)=z),p),y=x{0},w=List.Count(x)
],
each [y])
in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Path", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Path.1", "Path.2", "Path.3", "Path.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Path.1", Int64.Type}, {"Path.2", Int64.Type}, {"Path.3", Int64.Type}, {"Path.4", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"empno", "ename", "job", "mgr"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "PathID"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([PathID] <> 0)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"PathID"}, RemovedOtherColumns, {"empno"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"empno", "ename", "job"}, {"empno.1", "ename.1", "job.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Removed Columns",{{"job.1", "Role"}, {"ename.1", "Name"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"mgr", "PathID"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"empno", Order.Ascending}}),
#"Renamed Columns2" = Table.RenameColumns(#"Sorted Rows",{{"empno.1", "mgr"}})
in
#"Renamed Columns2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
