Power Query中基于条件控制合并流程以生成文件夹树的问题

需求说明 #

需要在Power Query中完成两项任务：

• 生成文件夹树并定位目标文件夹：从根文件夹开始，基于OBJECT_ID = PARENT_ID合并对象列表扩展子对象，每次合并后检查是否找到目标OBJECT_ID，找到则筛选该行，未找到则重复合并直至目标出现；
• 获取目标文件夹下所有对象：找到目标文件夹后继续合并，每次合并后对比前后行数，若行数不再变化则停止流程，否则继续合并。

当前已有代码 #

FolderTree 查询中的重复合并片段 #

//This is the repeating merge

    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns2", {"OBJECT_ID"}, AllFolders, {"PARENT_ID"}, "AllFolders", JoinKind.LeftOuter),
    #"Expanded AllFolders" = Table.ExpandTableColumn(#"Merged Queries", "AllFolders", {"OBJECT_NAME", "OBJECT_ID"}, {"OBJECT_NAME.1", "OBJECT_ID.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllFolders", "Path.1", each [Path]&"\"&[OBJECT_NAME.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Path.1", "OBJECT_ID.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"OBJECT_ID.1", "OBJECT_ID"}, {"Path.1", "Path"}}),

// This is where I need to do a check.

TargetFolderCheck 查询代码 #

let
    Source = FolderTree,
    #"Filtered Rows" = Table.SelectRows(Source, each ([OBJECT_ID] = _TargetFolder)),
    #"Counted Rows" = Table.RowCount(#"Filtered Rows")
in
    #"Counted Rows"

问题卡点 #

需要实现逻辑：若TargetFolderCheck统计值为0，则重复FolderTree中的合并操作；若不为0，则筛选目标文件夹行，之后继续合并直至行数不再增加。现有if语句资料多为条件生成值场景，不知道如何应用到递归合并流程中。

解决方案 #

在Power Query中实现这类递归逻辑，需要通过自定义函数+递归调用来完成，以下是具体实现步骤：

1. 封装合并逻辑为自定义函数 #

将重复的合并步骤封装成可复用的函数，接收当前文件夹表作为参数：

let
    MergeFolderLevels = (currentTable as table) as table =>
    let
        #"Merged Queries" = Table.NestedJoin(currentTable, {"OBJECT_ID"}, AllFolders, {"PARENT_ID"}, "AllFolders", JoinKind.LeftOuter),
        #"Expanded AllFolders" = Table.ExpandTableColumn(#"Merged Queries", "AllFolders", {"OBJECT_NAME", "OBJECT_ID"}, {"OBJECT_NAME.1", "OBJECT_ID.1"}),
        #"Added Custom" = Table.AddColumn(#"Expanded AllFolders", "Path.1", each [Path]&"\"&[OBJECT_NAME.1]),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Path.1", "OBJECT_ID.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"OBJECT_ID.1", "OBJECT_ID"}, {"Path.1", "Path"}}),
        // 移除无内容的空行，避免无效递归
        #"Removed Nulls" = Table.SelectRows(#"Renamed Columns", each not List.IsEmpty(Record.FieldValues(_)))
    in
        #"Removed Nulls"
in
    MergeFolderLevels

2. 实现定位目标文件夹的递归函数 #

创建函数递归合并，直到找到目标文件夹：

let
    FindTargetFolder = (startTable as table) as table =>
    let
        // 检查当前表是否包含目标OBJECT_ID
        hasTarget = List.Contains(startTable[OBJECT_ID], _TargetFolder),
        // 条件分支：找到则筛选目标行，未找到则合并后继续递归
        result = if hasTarget then
            Table.SelectRows(startTable, each [OBJECT_ID] = _TargetFolder)
        else
            let
                mergedTable = MergeFolderLevels(startTable)
            in
                FindTargetFolder(mergedTable)
    in
        result
in
    FindTargetFolder

3. 实现获取所有子对象的递归函数 #

找到目标文件夹后，递归合并直到行数不再变化：

let
    GetAllChildObjects = (currentTable as table) as table =>
    let
        mergedTable = MergeFolderLevels(currentTable),
        // 对比合并前后的行数
        rowCountBefore = Table.RowCount(currentTable),
        rowCountAfter = Table.RowCount(mergedTable),
        // 行数不变则停止递归，否则继续
        result = if rowCountAfter = rowCountBefore then
            currentTable
        else
            GetAllChildObjects(mergedTable)
    in
        result
in
    GetAllChildObjects

4. 整合流程到FolderTree查询 #

修改FolderTree查询，调用上述函数完成完整流程：

let
    // 替换为你的根文件夹初始数据（需包含Path和OBJECT_ID列）
    Source = Table.FromRecords({[Path = "C:\Root", OBJECT_ID = "ROOT_ID"]}),
    // 第一步：定位目标文件夹
    TargetFolderTable = FindTargetFolder(Source),
    // 第二步：获取目标文件夹下所有子对象
    AllChildObjects = GetAllChildObjects(TargetFolderTable)
in
    AllChildObjects

关键注意事项 #

• 确保_TargetFolder是已定义的全局变量或查询参数；
• 初始根文件夹表必须包含Path（根路径）和OBJECT_ID（根文件夹ID）两列；
• 移除空行的操作可以减少无效递归，提升整体效率。

内容的提问来源于stack exchange，提问作者Paul Robbins