PowerQuery循环拉取API全量数据：实现每50条批量获取直至完成

原代码仅拉取了起始索引为1的单批次数据，要实现全量拉取，需利用API返回的totalCount和nextBatchStartIndex进行分页循环请求。以下是改造后的完整M代码：

let
    // 1. 定义获取单批次数据的函数
    GetBatch = (startIndex as number) =>
        let
            postData = Json.FromValue([fStartIndex=startIndex]),
            Source = Web.Contents("https://XXX.appiancloud.com/suite/webapi/XXX", 
                [Content = postData, 
                 Headers=[
                     #"Content-Type"="application/json", 
                     #"Appian-API-Key"="xxxxxxxxxxxxyyyyyyyyyyyyyyyzzzzzzzzzzzzzzz"
                 ]
                ]),
            ImportedJSON = Json.Document(Source),
            ConvertedToTable = Table.FromRecords({ImportedJSON}),
            ParsedJSON = Table.TransformColumns(ConvertedToTable,{{"recommendations", Json.Document}}),
            ExpandedRecommendations = Table.ExpandListColumn(ParsedJSON, "recommendations"),
            ChangedType = Table.TransformColumnTypes(ExpandedRecommendations,{
                {"totalCount", Int64.Type}, 
                {"nextBatchStartIndex", Int64.Type}, 
                {"recommendations", type any}
            }),
            ExpandedDetails = Table.ExpandRecordColumn(ChangedType, "recommendations", 
                {"updatedDateFormatDate", "createdDateFormatDate", "recommendationSource", "iosRecommendationId", "secondaryDepartmentEmail", "sourceLink", "meetingDate", "delegateToDate", "delegateFromDate", "delegatee", "dateOfissuanceText", "updatedDate", "updatedBy", "createdDate", "createdBy", "gbReport", "status", "customField5", "customField4", "customField3", "customField2", "customField1", "generalComment", "recommendationBODeadline", "flmComment", "flmApprovalDecision", "flmName", "initialManagementResponse", "crossReferencing", "riskLevel", "priorityLevel", "recommendationDeadline", "externaltargetAudience", "secondaryDepartments", "associateBusinessOwner", "businessOwnerName", "businessOwnerDepartment", "division", "acceptanceComment", "whoAcceptance", "openEndedProcess", "theme3", "theme2", "theme1", "category", "recommendation", "agendaItemName", "agendaItem", "sourceReportNo", "dateOfissuance", "meeting", "portal", "indexNo", "recommendationId"},
                {"updatedDateFormatDate", "createdDateFormatDate", "recommendationSource", "iosRecommendationId", "secondaryDepartmentEmail", "sourceLink", "meetingDate", "delegateToDate", "delegateFromDate", "delegatee", "dateOfissuanceText", "updatedDate", "updatedBy", "createdDate", "createdBy", "gbReport", "status", "customField5", "customField4", "customField3", "customField2", "customField1", "generalComment", "recommendationBODeadline", "flmComment", "flmApprovalDecision", "flmName", "initialManagementResponse", "crossReferencing", "riskLevel", "priorityLevel", "recommendationDeadline", "externaltargetAudience", "secondaryDepartments", "associateBusinessOwner", "businessOwnerName", "businessOwnerDepartment", "division", "acceptanceComment", "whoAcceptance", "openEndedProcess", "theme3", "theme2", "theme1", "category", "recommendation", "agendaItemName", "agendaItem", "sourceReportNo", "dateOfissuance", "meeting", "portal", "indexNo", "recommendationId"}
            )
        in
            ExpandedDetails,

    // 2. 获取第一批次数据，提取总记录数和下一批次索引
    FirstBatch = GetBatch(1),
    TotalCount = FirstBatch{0}[totalCount],
    // 生成所有需要请求的起始索引列表
    IndexList = List.Generate(
        () => [CurrentIndex = 1, IsFinished = false],
        each [IsFinished] = false,
        each [
            CurrentIndex = FirstBatch{0}[nextBatchStartIndex],
            IsFinished = [CurrentIndex] > TotalCount
        ],
        each [CurrentIndex]
    ),

    // 3. 批量调用函数获取所有批次数据并合并
    AllBatches = List.Combine(List.Transform(IndexList, each GetBatch(_))),

    // 4. 清理数据（移除分页字段）
    CleanedData = Table.RemoveColumns(AllBatches,{"totalCount", "nextBatchStartIndex"})
in
    CleanedData

关键改造说明： #

• 封装单批次请求为函数：将原有的单次请求逻辑封装成GetBatch函数，接收起始索引参数，返回该批次的原始数据（保留分页字段用于后续判断）。
• 生成索引列表：通过List.Generate循环生成所有需要请求的起始索引，直到下一批次索引超过总记录数。
• 合并所有批次：用List.Transform遍历索引列表调用函数，再通过List.Combine合并所有批次的结果。
• 清理数据：最后移除分页相关的totalCount和nextBatchStartIndex字段，得到全量数据。

注意事项： #

• 确保API返回的nextBatchStartIndex逻辑正确（通常是当前起始索引+每页条数，这里API已经直接返回，无需计算）。
• 如果API有请求频率限制，可在函数中添加延迟（需用Function.InvokeAfter实现）。

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