Power Query调用API分页数据无法完整获取的求助

你的API采用top=200和skip=N的分页机制，但之前的递归代码依赖@odata.nextLink字段，而你的API可能并不返回该字段，导致无法自动获取后续页面。以下是手动构造分页参数的解决方案：

完整M代码 #

let
    // 基础配置
    baseUrl = "https://finder.bloodsandbeyond.co.uk/myurl",
    headers = [
        #"x-api-token-name" = "x-api-token",
        #"x-api-token" = "????????????????????????????" // 替换为你的实际API密钥
    ],
    pageSize = 200, // 与API默认每页数据量保持一致
    // 尝试获取总数据量（若API返回总数），无总数则循环到空数据为止
    getTotalCount = Json.Document(Web.Contents(baseUrl, [Headers=headers, Query=[top="1"]])),
    totalRows = try getTotalCount[totalCount] otherwise null,
    // 生成所有需要请求的skip参数列表
    pageList = if totalRows <> null then
        List.Generate(
            () => 0,
            each _ < totalRows,
            each _ + pageSize
        )
    else
        List.Generate(
            () => 0,
            each true,
            each _ + pageSize,
            each let
                response = Json.Document(Web.Contents(baseUrl, [Headers=headers, Query=[top=Text.From(pageSize), skip=Text.From(_)]]))
            in
                if List.Count(response[value]) = 0 then null else _
        ),
    // 过滤无效的skip参数（针对无总数的情况）
    validSkips = List.RemoveNulls(pageList),
    // 批量获取每一页数据
    fetchPages = List.Transform(validSkips, (skip) =>
        let
            urlWithParams = baseUrl & "?top=" & Text.From(pageSize) & "&skip=" & Text.From(skip),
            source = Json.Document(Web.Contents(urlWithParams, [Headers=headers])),
            value = source[value],
            toTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            expandColumns = Table.ExpandRecordColumn(toTable, "Column1", 
                {"id", "lab_id", "assigned_user_id", "lab_invoice_id", "user_invoice_id", "appointment_at", "matched_at", "sample_posted_at", "is_under_18", "is_urgent", "patient_fee", "patient_cost", "collections_fee", "collections_cost", "reference", "status", "status_reason", "name", "address_line_1", "address_line_2", "city", "post_code", "lat", "lng", "phone", "email", "patients", "preferred_datetimes", "notes", "admin_notes", "deleted_at", "created_at", "updated_at", "confirmed_at", "is_issue", "invoice_notes", "issue_type"},
                {"id", "lab_id", "assigned_user_id", "lab_invoice_id", "user_invoice_id", "appointment_at", "matched_at", "sample_posted_at", "is_under_18", "is_urgent", "patient_fee", "patient_cost", "collections_fee", "collections_cost", "reference", "status", "status_reason", "name", "address_line_1", "address_line_2", "city", "post_code", "lat", "lng", "phone", "email", "patients", "preferred_datetimes", "notes", "admin_notes", "deleted_at", "created_at", "updated_at", "confirmed_at", "is_issue", "invoice_notes", "issue_type"}
            )
        in
            expandColumns
    ),
    // 合并所有页面的数据
    combinedData = Table.Combine(fetchPages)
in
    combinedData

关键说明 #

1. 双逻辑适配：
   • 若API返回totalCount（或类似总数字段），代码会先计算总页数，一次性生成所有请求参数。
   • 若API不返回总数，会自动循环请求，直到某一页返回空数据时停止。
2. 参数对齐：确保pageSize与API默认的每页条数（当前为200）完全一致，避免数据遗漏或重复。
3. 权限保持：保留了原始代码中的x-api-token-name和x-api-token头信息，确保API权限验证正常。
4. 权限配置提示：如果Power BI弹出权限错误，需在数据源设置中配置正确的隐私级别，允许跨源请求。

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