Forum Discussion
Transform employee data
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
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 |
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