Creating an employee manager relationship table

Question

I've a table as under:

Original table:

+---------------+--------------+--------------+
| Employee Name | Manager Lvl1 | Manager Lvl2 |
+---------------+--------------+--------------+
| A             | L            | Y            |
| B             | M            | Y            |
| C             | L            | Y            |
| D             | M            | Y            |
| E             | N            | Z            |
| F             | N            | Z            |
| G             | O            | Z            |
+---------------+--------------+--------------+

I want to add an id to all employee levels and also a column that specifies the manager ID for each employee as under:

Desired Employee Table:

+----+----------+------------+
| ID | Employee | Manager ID |
+----+----------+------------+
|  1 | A        |          8 |
|  2 | B        |          9 |
|  3 | C        |          8 |
|  4 | D        |          9 |
|  5 | E        |         10 |
|  6 | F        |         10 |
|  7 | G        |         11 |
|  8 | L        |         12 |
|  9 | M        |         12 |
| 10 | N        |         13 |
| 11 | O        |         13 |
| 12 | Y        |            |
| 13 | Z        |            |
+----+----------+------------+

My main aim in doing this is to add a path column to create a path of each employee manager relation so that I can add row level security:

The path function that I'd be using is:

EmployeePath= Employee[ID], Employee[Manager ID])

so that my end table would look like this:

+----+----------+------------+---------+
| ID | Employee | Manager ID |  Path   |
+----+----------+------------+---------+
|  1 | A        |          8 | 12|8|1  |
|  2 | B        |          9 | 12|8|2  |
|  3 | C        |          8 | 12|8|3  |
|  4 | D        |          9 | 12|9|4  |
|  5 | E        |         10 | 13|10|5 |
|  6 | F        |         10 | 13|10|6 |
|  7 | G        |         11 | 13|11|7 |
|  8 | L        |         12 | 12|8    |
|  9 | M        |         12 | 12|9    |
| 10 | N        |         13 | 13|10   |
| 11 | O        |         13 | 13|11   |
| 12 | Y        |            | 12      |
| 13 | Z        |            | 13      |
+----+----------+------------+---------+

I'm just having a hard time converting my oiginal table to the format of the desired employee table

Answer 1

This one is a bit shorter, requires just a few self merges

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source,{"Manager ID"},Source,{"ID"},"Changed Type",JoinKind.LeftOuter),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Manager ID"}, {"Manager ID.1"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Changed Type",{"Manager ID.1"},#"Expanded Changed Type",{"ID"},"Expanded Changed Type",JoinKind.LeftOuter),
#"Expanded Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Changed Type", {"Manager ID"}, {"Manager ID.2"}),
#"Added Custom" = Table.AddColumn(#"Expanded Expanded Changed Type", "Custom", each if [Manager ID.2]=null then 

if  [Manager ID] = null then Text.From([ID]) else if
[Manager ID.1] = null then Text.From([Manager ID])&" | "&Text.From([ID])
else Text.From([Manager ID.1])&" | " &Text.From([Manager ID])&" | "&Text.From([ID])
else Text.From([Manager ID.2]) & "|" & Text.From([Manager ID.1]) & " | " & Text.From([Manager ID]) & " | " & Text.From([ID])),

#"Sorted Rows" = Table.Sort(#"Added Custom",{{"ID", Order.Ascending}})
in #"Sorted Rows"

Answer 2

You can perform some transformation in Power Query Editor to achieve the required output. Lets say your table name is Employee with this below structure-

Now, the transformation is bit long, but try to understand step by step. If you can understand, things will be easy. Duplicate your table Employee and replace with the below code from Advance Editor-

let
    Source = employee,
        
    L0 =    
    Table.Distinct(         
        Table.FromList(
            Table.Column(Source,"Employee Name"),
            Splitter.SplitByNothing(), null, null, ExtraValues.Error
        )
    ),
    
    L1 =    
    Table.Distinct(         
        Table.FromList(
            Table.Column(Source,"Manager Lvl1"),
            Splitter.SplitByNothing(), null, null, ExtraValues.Error
        )                
    ),
    
    L2 =
    Table.Distinct(       
        Table.FromList(
            Table.Column(Source,"Manager Lvl2"),
            Splitter.SplitByNothing(), null, null, ExtraValues.Error
        )
    ),
    
    L3 =
    Table.Distinct(         
        Table.FromList(
            Table.Column(Source,"Manager Lvl3"),
            Splitter.SplitByNothing(), null, null, ExtraValues.Error
        )
    ),

    
    combine_table = 
    Table.Combine({
        L0,L1,L2,L3
    }),
    #"Added Index" = Table.AddIndexColumn(combine_table, "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Column1"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Column1"}, employee, {"Employee Name"}, "employee", JoinKind.LeftOuter),
    #"Expanded employee" = Table.ExpandTableColumn(#"Merged Queries", "employee", {"Manager Lvl1"}, {"employee.Manager Lvl1"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded employee", {"Column1"}, employee, {"Manager Lvl1"}, "employee", JoinKind.LeftOuter),
    #"Expanded employee1" = Table.ExpandTableColumn(#"Merged Queries1", "employee", {"Manager Lvl2"}, {"employee.Manager Lvl2"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded employee1", {"Column1"}, employee, {"Manager Lvl2"}, "employee", JoinKind.LeftOuter),
    #"Expanded employee2" = Table.ExpandTableColumn(#"Merged Queries2", "employee", {"Manager Lvl3"}, {"employee.Manager Lvl3"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded employee2",{"employee.Manager Lvl1", "employee.Manager Lvl2", "employee.Manager Lvl3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns"),
    #"Merged Queries3" = Table.NestedJoin(#"Removed Duplicates", {"Merged"}, employee, {"Manager Lvl1"}, "employee", JoinKind.LeftOuter),
    #"Expanded employee3" = Table.ExpandTableColumn(#"Merged Queries3", "employee", {"Manager Lvl2"}, {"employee.Manager Lvl2"}),
    #"Merged Queries4" = Table.NestedJoin(#"Expanded employee3", {"Merged"}, employee, {"Manager Lvl2"}, "employee", JoinKind.LeftOuter),
    #"Expanded employee4" = Table.ExpandTableColumn(#"Merged Queries4", "employee", {"Manager Lvl3"}, {"employee.Manager Lvl3"}),
    #"Merged Columns1" = Table.CombineColumns(#"Expanded employee4",{"employee.Manager Lvl2", "employee.Manager Lvl3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1"),
    #"Removed Duplicates1" = Table.Distinct(#"Merged Columns1"),
    #"Merged Queries5" = Table.NestedJoin(#"Removed Duplicates1", {"Merged.1"}, employee, {"Manager Lvl2"}, "employee", JoinKind.LeftOuter),
    #"Expanded employee5" = Table.ExpandTableColumn(#"Merged Queries5", "employee", {"Manager Lvl3"}, {"employee.Manager Lvl3"}),
    #"Removed Duplicates2" = Table.Distinct(#"Expanded employee5"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates2",{{"Merged", "M1"}, {"Merged.1", "M2"}, {"employee.Manager Lvl3", "M3"}}),
    #"Merged Queries6" = Table.NestedJoin(#"Renamed Columns", {"M1"}, #"Renamed Columns", {"Column1"}, "Renamed Columns", JoinKind.LeftOuter),
    #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries6", "Renamed Columns", {"Index"}, {"Renamed Columns.Index"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Renamed Columns",{{"Renamed Columns.Index", "M1_ID"}}),
    #"Merged Queries7" = Table.NestedJoin(#"Renamed Columns1", {"M2"}, #"Renamed Columns1", {"Column1"}, "Renamed Columns1", JoinKind.LeftOuter),
    #"Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries7", "Renamed Columns1", {"Index"}, {"Renamed Columns1.Index"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Renamed Columns1",{{"Renamed Columns1.Index", "M2_ID"}}),
    #"Merged Queries8" = Table.NestedJoin(#"Renamed Columns2", {"M3"}, #"Renamed Columns2", {"Column1"}, "Renamed Columns2", JoinKind.LeftOuter),
    #"Expanded Renamed Columns2" = Table.ExpandTableColumn(#"Merged Queries8", "Renamed Columns2", {"Index"}, {"Renamed Columns2.Index"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Renamed Columns2",{{"Renamed Columns2.Index", "M3_ID"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns3",{"Index", "Column1", "M1", "M1_ID", "M2", "M2_ID", "M3", "M3_ID"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "Index", "Index - Copy"),
    #"Renamed Columns4" = Table.RenameColumns(#"Duplicated Column",{{"Index - Copy", "M0_ID"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns4", "M1_ID", "M1_ID - Copy"),
    #"Renamed Columns5" = Table.RenameColumns(#"Duplicated Column1",{{"M1_ID - Copy", "Manager ID"}}),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns5", {{"M1_ID", type text}, {"M2_ID", type text}, {"M3_ID", type text}, {"M0_ID", type text}}, "en-US"),{"M1_ID", "M2_ID", "M3_ID", "M0_ID"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns2","||","|",Replacer.ReplaceText,{"Merged"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","||","|",Replacer.ReplaceText,{"Merged"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"M2", "M3", "M1"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns",{"Index", "Column1", "Manager ID", "Merged"})
in
    #"Reordered Columns2"

Here is the final output of your new table-