创建员工经理关系表 [英] Creating an employee manager relationship 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 |
+---------------+--------------+--------------+
我想为所有员工级别添加一个ID，并且还要为每位员工指定经理ID，如下所示：
I want to add an id to all employee levels and also a column that specifies the manager ID for each employee as under:
所需雇员表：
+----+----------+------------+
| 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 |
+----+----------+------------+---------+
我很难将我的原始表转换为所需员工的格式表
推荐答案
您可以在 Power Query Editor 中执行一些转换，以获取所需的输出。假设您的表名是 Employee ，具有以下结构-
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-
现在，转换有点长，但是请尝试逐步了解。如果您能够理解，事情将会很容易。复制您的表 Employee ，并用 Advance Editor -
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"
这是新表的最终输出-
这篇关于创建员工经理关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
