Power BI调用API仅返回20条数据，如何获取全量数据？

API默认返回分页数据（每页20条），要获取全量数据需实现分页遍历逻辑，以下针对你的两个数据集分别处理：

一、处理Leads数据集（已包含分页标识） #

你的Leads API返回了next_page_url，支持基于URL的分页，用Power BI递归函数可自动翻页获取全量数据：

替换原Leads的M代码为：

let
    // 递归函数：获取所有分页数据
    GetAllPages = (url as text) as list =>
        let
            CurrentPage = Json.Document(Web.Contents(url, [Headers=[Accept="application/json", Authorization="Bearer 123456789"]])),
            CurrentData = CurrentPage[data][data],
            NextUrl = CurrentPage[data][next_page_url],
            // 有下一页则递归调用，无则返回当前页数据
            Result = if NextUrl <> null then CurrentData & GetAllPages(NextUrl) else CurrentData
        in
            Result,
    // 初始调用第一页API
    AllData = GetAllPages("https://app.website.nl/api/user/v1/account/leads"),
    // 转换为表格并展开字段
    #"转换为表格" = Table.FromList(AllData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展开记录列" = Table.ExpandRecordColumn(#"转换为表格", "Column1", 
        {"id", "business", "gender", "firstname", "lastname", "postcode", "housenumber", "suffix", "streetname", "city", "company_name", "activity", "locked", "status", "created_by", "planned_user_id", "planned_date", "planned_by", "planned_at", "planned_from", "planned_to", "completed_at", "created_at", "updated_at", "planned_to_username", "completed_by_username", "lead_source", "filter_status", "name", "address"},
        {"id", "business", "gender", "firstname", "lastname", "postcode", "housenumber", "suffix", "streetname", "city", "company_name", "activity", "locked", "status", "created_by", "planned_user_id", "planned_date", "planned_by", "planned_at", "planned_from", "planned_to", "completed_at", "created_at", "updated_at", "planned_to_username", "completed_by_username", "lead_source", "filter_status", "name", "address"}),
    // 设置字段类型
    #"设置字段类型" = Table.TransformColumnTypes(#"展开记录列",{{"id", Int64.Type}, {"business", Int64.Type}, {"gender", type text}, {"firstname", type any}, {"lastname", type text}, {"postcode", type text}, {"housenumber", Int64.Type}, {"suffix", type any}, {"streetname", type text}, {"city", type text}, {"company_name", type any}, {"activity", type text}, {"locked", Int64.Type}, {"status", type text}, {"created_by", Int64.Type}, {"planned_user_id", Int64.Type}, {"planned_date", type datetime}, {"planned_by", Int64.Type}, {"planned_at", type datetime}, {"planned_from", type datetime}, {"planned_to", type any}, {"completed_at", type datetime}, {"created_at", type datetime}, {"updated_at", type datetime}, {"planned_to_username", type text}, {"completed_by_username", type text}, {"lead_source", type any}, {"filter_status", type text}, {"name", type text}, {"address", type text}})
in
    #"设置字段类型"

二、处理Sales数据集（需确认分页参数） #

当前Sales API返回的列表仅20条，说明同样存在分页。先确认API支持的分页方式：

• 尝试在浏览器访问https://app.website.nl/api/user/v1/account/sales?per_page=100，看是否返回更多数据
• 查看API文档，确认是否支持page（页码）、cursor（游标）等分页参数

假设API支持page和per_page参数，使用以下递归逻辑获取全量数据：

let
    // 递归函数：按页码分页获取销售数据
    GetAllSalesPages = (page as number) as list =>
        let
            // 构造带分页参数的API URL
            ApiUrl = "https://app.website.nl/api/user/v1/account/sales?page=" & Text.From(page) & "&per_page=100",
            CurrentPage = Json.Document(Web.Contents(ApiUrl, [Headers=[Accept="application/json", Authorization="Bearer 123456789"]])),
            CurrentData = CurrentPage[data]{12}[Value], // 保留原数据提取逻辑
            // 当前页无数据则停止递归
            Result = if List.Count(CurrentData) > 0 then CurrentData & GetAllSalesPages(page + 1) else {}
        in
            Result,
    // 从第1页开始获取
    AllSalesData = GetAllSalesPages(1),
    // 转换为表格并展开字段
    #"转换为表格" = Table.FromList(AllSalesData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展开记录列" = Table.ExpandRecordColumn(#"转换为表格", "Column1", 
        {"id", "user_id", "type", "sub_type", "offer", "offer_accepted", "status", "verification", "verified", "flow_id", "product_id", "incoming_id", "created_at", "finalized_at", "updated_at", "outgoing_at", "offer_answered_at", "verified_at", "valid_till", "cancelled_at", "status_remark", "cancelled_remark", "user_firstname", "user_lastname", "user_email", "organisation_name", "organisation_identifier", "gender", "firstname", "lastname", "business", "company_name", "source", "source_id", "postcode", "housenumber", "streetname", "city", "birthdate", "phone", "email", "iban", "connection_postcode", "connection_housenumber", "connection_streetname", "connection_city", "product_name", "product_identifier", "supplier_name"},
        {"id", "user_id", "type", "sub_type", "offer", "offer_accepted", "status", "verification", "verified", "flow_id", "product_id", "incoming_id", "created_at", "finalized_at", "updated_at", "outgoing_at", "offer_answered_at", "verified_at", "valid_till", "cancelled_at", "status_remark", "cancelled_remark", "user_firstname", "user_lastname", "user_email", "organisation_name", "organisation_identifier", "gender", "firstname", "lastname", "business", "company_name", "source", "source_id", "postcode", "housenumber", "streetname", "city", "birthdate", "phone", "email", "iban", "connection_postcode", "connection_housenumber", "connection_streetname", "connection_city", "product_name", "product_identifier", "supplier_name"})
in
    #"展开记录列"

注意事项 #

1. 权限验证：确保Authorization中的Bearer令牌有效且有权限访问全量数据
2. 分页适配：如果Sales API用游标分页（类似Leads的next_cursor），需修改递归逻辑，传递游标参数而非页码
3. 性能优化：设置合理的per_page值（如100），减少API调用次数，避免触发限流

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