Erstellen einer Mitarbeiter-Manager-Beziehungstabelle

Question

Ich habe einen Tisch wie unter:

Originaltabelle :

+---------------+--------------+--------------+
| 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            |
+---------------+--------------+--------------+

Ich möchte allen Mitarbeiterebenen eine ID sowie eine Spalte hinzufügen, in der die Manager-ID für jeden Mitarbeiter wie folgt angegeben ist:

Gewünschte Mitarbeitertabelle :

+----+----------+------------+
| 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        |            |
+----+----------+------------+

Mein Hauptziel dabei ist das Hinzufügen einer Pfadspalte, um einen Pfad für jede Mitarbeiter-Manager-Beziehung zu erstellen, damit ich die Sicherheit auf Zeilenebene hinzufügen kann:

Die Pfadfunktion, die ich verwenden würde, ist:

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

damit mein Beistelltisch so aussehen würde:

+----+----------+------------+---------+
| 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      |
+----+----------+------------+---------+

Ich habe gerade eine harte Zeit , meine Umwandlung oiginal Tabelle in das Format der gewünschten Mitarbeiter Tabelle

Answer 1

Sie können im Power Query Editor einige Transformationen durchführen , um die erforderliche Ausgabe zu erzielen. Nehmen wir an, Ihr Tabellenname ist Mitarbeiter mit der folgenden Struktur:

Jetzt ist die Transformation etwas lang, aber versuchen Sie Schritt für Schritt zu verstehen. Wenn Sie verstehen können, werden die Dinge einfach sein. Duplizieren Sie Ihre Tabelle Employee und ersetzen Sie sie durch den folgenden Code aus dem 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"

Hier ist die endgültige Ausgabe Ihrer neuen Tabelle.

Answer 2

Dieser ist etwas kürzer und erfordert nur ein paar Selbstzusammenführungen

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"