Power Query中'RecursiveExpand'未被识别的原因排查

报错原因 #

你遇到的RecursiveExpand未识别错误，根源是函数内部的语法错误导致函数无法正确定义：

• Table.ColumnsOfType(table, {type list})返回的是列名字符串数组（比如{"fields.comment.comments"}），但代码里错误使用current[Name]去访问不存在的Name属性，这会让函数定义失败，Power Query自然无法识别这个函数名。

修复后的代码 #

let
    Source = 100,
    JiraIDPerPage = 100,
    GetJson = (maxR, startA) =>
        let 
            Json = Json.Document(
                Web.Contents(
                    #"URI_BASE (2)",
                    [
                    RelativePath = #"URI_PATH (2)" & #"URI_RESOURCE (2)",
                    Content = Json.FromValue(
                        [
                            jql = "project in (" & #"JIRA_PROJECT_KEYS (2)" & ")",
                            fields = {"*all"},
                            maxResults = maxR,
                            startAt = startA
                        ]
                    ),
                    Headers =
                        [
                        Authorization = "Basic " & Binary.ToText(Text.ToBinary(#"USERNAME_TOKEN (2)"), 0),
                        Accept = "application/json",
                        #"Content-Type"="application/json"
                        ]
                    ]
                )
            )
        in  Json,
    GetJiraIDCount = () =>
        let maxResultss  = 0,
            startAtt = 0,
            Json  = GetJson(maxResultss, startAtt),
            Count = Json[#"total"]
        in  Count,
    GetPage = (Index) =>
        let Top   = 100,
            Skip  = Index * 100,
            Json  = GetJson(Top, Skip),
            Value = Json[#"issues"]
        in  Value,
    JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
    PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages = List.Transform(PageIndices, each GetPage(_)),
    JiraID = List.Union(Pages),
    Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"expand", "id", "self", "key", "fields"}, {"expand", "id", "self", "key", "fields"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"expand", "id", "self"}),
    Custom1 = let
    // 递归展开列表列的函数
    RecursiveExpand = (table as table) =>
    let
        // 获取所有列表类型的列名
        listColumns = Table.ColumnsOfType(table, {type list}),
        // 逐个展开列表列
        expandedTable = List.Accumulate(listColumns, table, (state, current) => Table.ExpandListColumn(state, current)),
        // 检查是否还有未展开的列表列
        remainingListColumns = Table.ColumnsOfType(expandedTable, {type list}),
        // 递归调用直到没有列表列
        result = if List.IsEmpty(remainingListColumns) then expandedTable else RecursiveExpand(expandedTable)
    in
        result,
    // 启动递归展开
    expandedTable = RecursiveExpand(#"Removed Columns")
in
    expandedTable
in
    Custom1

关键修正点 #

• 将Table.ExpandListColumn(state, current[Name])改为Table.ExpandListColumn(state, current)，直接使用Table.ColumnsOfType返回的列名字符串。

可选扩展：同时展开记录列 #

如果需要完全展开JIRA返回的嵌套记录（比如fields里的子字段），可以修改递归函数，同时处理列表和记录列：

// 扩展版：递归展开所有列表和记录列
RecursiveExpandAll = (table as table) =>
let
    // 获取列表列和记录列
    listColumns = Table.ColumnsOfType(table, {type list}),
    recordColumns = Table.ColumnsOfType(table, {type record}),
    // 先展开所有列表列
    expandedLists = List.Accumulate(listColumns, table, (state, current) => Table.ExpandListColumn(state, current)),
    // 再展开所有记录列（取所有字段）
    expandedRecords = List.Accumulate(recordColumns, expandedLists, (state, current) => 
        let
            cols = Record.FieldNames(Table.FirstValue(state[current]))
        in
            Table.ExpandRecordColumn(state, current, cols, List.Transform(cols, each current & "." & _))),
    // 检查是否还有嵌套的列表或记录列
    remainingLists = Table.ColumnsOfType(expandedRecords, {type list}),
    remainingRecords = Table.ColumnsOfType(expandedRecords, {type record}),
    // 递归调用直到没有嵌套结构
    result = if List.IsEmpty(remainingLists) and List.IsEmpty(remainingRecords) then expandedRecords else RecursiveExpandAll(expandedRecords)
in
    result

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