体现公司的层级关系

shufeabb

架构调整.zip (6.05 KB, 下载次数: 21)

2024-6-25 10:25 上传

点击文件名下载附件

wdx223

pxy0409

只会菜单操作,多次合并查询处理,次数需要人为判断:
let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    合并的查询 = Table.NestedJoin(源, {"Manager"}, 表1, {"Staff"}, "表1", JoinKind.LeftOuter),
    #"展开的“表1”" = Table.ExpandTableColumn(合并的查询, "表1", {"Manager"}, {"Manager.1"}),
    合并的查询1 = Table.NestedJoin(#"展开的“表1”", {"Manager.1"}, 表1, {"Staff"}, "表1", JoinKind.LeftOuter),
    #"展开的“表1”1" = Table.ExpandTableColumn(合并的查询1, "表1", {"Manager"}, {"Manager.2"}),
    合并的查询2 = Table.NestedJoin(#"展开的“表1”1", {"Manager.2"}, 表1, {"Staff"}, "表1", JoinKind.LeftOuter),
    #"展开的“表1”2" = Table.ExpandTableColumn(合并的查询2, "表1", {"Manager"}, {"Manager.3"}),
    筛选的行 = Table.SelectRows(#"展开的“表1”2", each ([Manager.2] = "GM")),
    删除的列 = Table.RemoveColumns(筛选的行,{"Manager.3"}),
    重排序的列 = Table.ReorderColumns(删除的列,{"Manager.2", "Manager.1", "Manager", "Staff"})
in
    重排序的列

pxy0409

wdx223 发表于 2024-6-25 12:48

综合性太高,完全看不懂......

ThxAlot

全员都参与的层级关系，

1. let
   
2.     fx_Upstream = (empl as text, lvl as list) as list =>
   
3.         let pos = List.PositionOf(Stf, empl, Occurrence.First, Comparer.OrdinalIgnoreCase)
   
4.         in if pos>-1 then @fx_Upstream(Mgr{pos}, {empl} & lvl) else {empl} & lvl,
   
5. 
   
6.     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXL3VYrViVZyAjIdwSxnIMsJzHKBi7nCxdzgYu5AlguY5QFkuSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Staff = _t, Manager = _t]),
   
7.     Stf = Source[Staff],
   
8.     Mgr = Source[Manager],
   
9.     #"Added Column" = Table.AddColumn(Source, "Lvl", each fx_Upstream([Manager], {[Staff]})),
   
10.     Lvl = Table.Combine(List.Transform(#"Added Column"[Lvl], each Table.FromRows({_})))
    
11. in
    
12.     Lvl

复制代码

cnmlgb9998

wdx223 发表于 2024-6-25 12:48

太强大了。我就是不会递归。。。。。。。。。。。。。。。。。