Transform employee data

[Truelearner]

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 

[mahoneypat]

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,
Pat

[Truelearner]

pat  , 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) .

[mahoneypat]

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

[Truelearner]

Hey pat ,

I am glad you have taken time to look into my query but i find that there are few discripencies  in the below table i highlighted them, i basically dont need the rows hilighted as they are mentioned about the employee not there manager and when it comes to CEO he dont have any manager so he should have a record for that 

empno	ename	job	mgr	Name	Role
1	JOHNSON	ADMIN	9	JACKSON	CEO
1	JOHNSON	ADMIN	1	JOHNSON	ADMIN
1	JOHNSON	ADMIN	6	GARFIELD	MANAGER
2	HARDING	MANAGER	2	HARDING	MANAGER
2	HARDING	MANAGER	9	JACKSON	CEO
3	TAFT	SALES I	3	TAFT	SALES I
3	TAFT	SALES I	2	HARDING	MANAGER
3	TAFT	SALES I	9	JACKSON	CEO
4	HOOVER	SALES I	2	HARDING	MANAGER
4	HOOVER	SALES I	4	HOOVER	SALES I
4	HOOVER	SALES I	9	JACKSON	CEO
5	LINCOLN	TECH	5	LINCOLN	TECH
5	LINCOLN	TECH	9	JACKSON	CEO
5	LINCOLN	TECH	6	GARFIELD	MANAGER
6	GARFIELD	MANAGER	6	GARFIELD	MANAGER
6	GARFIELD	MANAGER	9	JACKSON	CEO
7	POLK	TECH	9	JACKSON	CEO
7	POLK	TECH	6	GARFIELD	MANAGER
7	POLK	TECH	7	POLK	TECH
8	GRANT	ENGINEER	8	GRANT	ENGINEER

[mahoneypat · Accepted solution]

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