Power BI M Query中Clarizen API分页Limit参数失效问题求助

问题背景 #

刚接触API及POST请求、分页功能，已在Power BI M Query中配置Clarizen API拉取工时表数据，但遇到分页瓶颈：明明存在更多数据，却仅能获取5000条记录，尝试将SQL语句中的LIMIT值调至8000仍无效。

当前使用的M Query代码 #

let
    // Endpoint URL
    RequestUrl = "https://api2.clarizen.com/v2.0/services/data/query",

    // Request Headers as a record
    RequestHeaders = [
        #"Authorization" = "Session " & Login,
        #"Content-Type" = "application/json"
    ],

    // Request Body as binary
    RequestBody = Text.ToBinary("{
         ""q"": ""SELECT CreatedBy, CreatedOn, LastUpdatedBy, FROM timesheet 
         WHERE C_AdjustedReportedDate >= '09/01/2023' AND State = '/State/Approved'AND Duration > 0 LIMIT 8000 ""
    }"),

    // Send POST request
    ResponseMessage = Web.Contents(RequestUrl, [
        Headers = RequestHeaders,
        Content = RequestBody
    ]),

    // Parse JSON response
    ResponseJson = Json.Document(ResponseMessage),
    // Parse JSON response
    entities = ResponseJson[entities]
in
    entities

Clarizen API分页规则 #

根据官方文档说明：

查询返回的分页信息中，如果paging.hasMore为true，需将该分页对象原样传入下一次相同的查询API请求，以获取下一页数据。

分页对象字段说明：

• from：整数，从第几条记录开始获取
• limit：整数，每页获取的记录数
• hasMore：布尔值，标识是否还有更多数据待获取

API已不再使用PageNumber和PageSize参数，改为From和Limit。遵循最佳实践的话，直接复用返回的paging对象即可。

解决方案：循环分页请求合并全量数据 #

问题根源是当前代码只请求了单页数据，且Clarizen API对单页返回的最大记录数有上限（即使设置LIMIT 8000，API仍会返回其允许的最大数量，比如5000）。需要通过循环请求，每次带上上一页返回的paging对象，直到hasMore为false，最后合并所有页的数据。

完整M Query代码 #

let
    // 基础配置
    RequestUrl = "https://api2.clarizen.com/v2.0/services/data/query",
    RequestHeaders = [
        #"Authorization" = "Session " & Login,
        #"Content-Type" = "application/json"
    ],
    // 初始查询语句（移除LIMIT，分页由paging参数控制）
    BaseQuery = "SELECT CreatedBy, CreatedOn, LastUpdatedBy FROM timesheet WHERE C_AdjustedReportedDate >= '09/01/2023' AND State = '/State/Approved' AND Duration > 0",
    
    // 定义递归函数处理分页
    FetchPage = (optional paging as nullable record) =>
        let
            // 构造请求体：如果有paging参数则带上，否则只传基础查询
            RequestBody = if paging <> null then
                Text.ToBinary(Json.FromValue([
                    q = BaseQuery,
                    paging = paging
                ]))
            else
                Text.ToBinary(Json.FromValue([q = BaseQuery])),
            // 发送请求
            Response = Web.Contents(RequestUrl, [Headers = RequestHeaders, Content = RequestBody]),
            ResponseJson = Json.Document(Response),
            // 获取当前页数据和分页信息
            CurrentEntities = ResponseJson[entities],
            CurrentPaging = ResponseJson[paging],
            // 递归调用获取下一页（如果还有更多数据）
            NextEntities = if CurrentPaging[hasMore] = true then FetchPage(CurrentPaging) else {}
        in
            // 合并当前页和下一页数据
            CurrentEntities & NextEntities,
    
    // 调用递归函数，从第一页开始获取
    AllEntities = FetchPage(null),
    // 将列表转换为表格
    ResultTable = Table.FromList(AllEntities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    ResultTable

关键说明 #

1. 移除SQL中的LIMIT：分页逻辑交给API的paging参数控制，避免与API自身的单页上限冲突。
2. 递归函数FetchPage：
   • 首次调用不带paging参数，API会返回第一页数据及分页信息。
   • 后续每次调用传入上一页返回的paging对象，API会自动返回下一页数据。
   • 当hasMore为false时停止递归，返回空列表。
3. 合并数据：通过&运算符将每一页的实体列表合并，最后转换为表格格式。

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