如何在Power BI的Power Query中为分层资产表添加根资产列

针对你的层级资产表需求，这里提供两种在Power Query中实现添加RootAsset列的方法：

方法一：递归函数遍历层级（适合中小数据量） #

这种方法通过递归函数向上遍历父节点，直到找到父编码为0的根资产：

1. 进入Power Query编辑器
   打开Power BI，找到WS_ASSET_1表，点击转换数据进入Power Query编辑器。

2. 创建递归自定义函数

   • 点击主页→新建源→空白查询，将查询命名为GetRootAsset。
   • 在公式栏粘贴以下M代码（如果你的编码是数值类型，把text改为number，"0"改为0）：

     (currentCode as text, assetTable as table) as text =>
     let
         parentCode = Table.SelectRows(assetTable, each [_ms_code] = currentCode){0}[_ms_prt_code],
         rootCode = if parentCode = "0" then currentCode else @GetRootAsset(parentCode, assetTable)
     in
         rootCode
     

3. 为原表添加根资产列

   • 切换回WS_ASSET_1表，点击添加列→自定义列。
   • 在自定义列公式中输入：

     GetRootAsset([_ms_code], WS_ASSET_1)
     

   • 将新生成的列重命名为RootAsset。

4. 验证并加载
   检查RootAsset列的结果是否符合预期，确认无误后点击关闭并应用。

方法二：迭代循环构建映射表（适合大数据量） #

如果资产数据量较大，递归可能存在性能瓶颈，可使用循环迭代预先构建所有资产的根节点映射：

1. 进入Power Query编辑器，打开WS_ASSET_1表的高级编辑器，替换原有代码为以下内容（注意根据编码类型调整"0"为0）：

   let
       Source = WS_ASSET_1,
       // 提取编码映射关系
       AssetMapping = Table.SelectColumns(Source, {"_ms_code", "_ms_prt_code"}),
       // 初始化根节点列
       InitialMapping = Table.AddColumn(AssetMapping, "CurrentRoot", each [_ms_code]),
       // 循环更新根节点，直到所有节点的根不再变化
       UpdateLoop = List.Generate(
           () => InitialMapping,
           (prev) => List.AnyTrue(Table.AddColumn(prev, "Check", each [_ms_prt_code] <> "0" and [CurrentRoot] <> Table.SelectRows(prev, each [_ms_code] = [_ms_prt_code]){0}[CurrentRoot])[Check]),
           (prev) => Table.ReplaceValue(prev, each [CurrentRoot], each if [_ms_prt_code] = "0" then [CurrentRoot] else Table.SelectRows(prev, each [_ms_code] = [_ms_prt_code]){0}[CurrentRoot], Replacer.ReplaceValue, {"CurrentRoot"}),
           (x) => x
       ),
       // 获取最终映射表
       FinalMapping = List.Last(UpdateLoop),
       CleanMapping = Table.RemoveColumns(FinalMapping, {"_ms_prt_code"}),
       // 与原表合并添加根资产列
       Result = Table.NestedJoin(Source, {"_ms_code"}, CleanMapping, {"_ms_code"}, "RootMapping", JoinKind.LeftOuter),
       ResultWithRoot = Table.ExpandTableColumn(Result, "RootMapping", {"CurrentRoot"}, {"RootAsset"})
   in
       ResultWithRoot
   

2. 点击完成，查看生成的RootAsset列，确认无误后点击关闭并应用。

内容的提问来源于stack exchange，提问作者Stefano Ferrario