时间戳

首页 > 解决方案 > 创建员工经理关系表

powerbi - 创建员工经理关系表

问题描述

我有一张桌子如下：

原表：

+---------------+--------------+--------------+
| 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 的列，如下所示：

所需员工表：

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

我这样做的主要目的是添加一个路径列来创建每个员工经理关系的路径，以便我可以添加行级安全性：

我将使用的路径功能是：

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

所以我的茶几看起来像这样：

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

我只是很难将我的原始表转换为所需员工表的格式

标签： powerbidaxpowerquerypowerbi-desktop

解决方案

这个有点短，只需要几个自我合并

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"

推荐阅读