调整Power BI分页API调用实现Service刷新时数据获取异常排查

核心问题 #

调整后的代码仅能获取60天数据，根源在于分页递归逻辑错误：

• gather函数内重复请求初始URL获取分页链接，而非基于上一次请求的结果获取真实的历史分页地址
• 递归调用时始终传递初始的url和options，而非最新的分页参数，导致无法持续拉取更早的12个月数据

修复后的代码 #

let
    initUntil = Number.ToText(todayInSeconds),
    // 若目标为拉取12个月数据，需将此处get30DaysBefore替换为计算12个月前时间戳的函数
    initSince = Number.ToText(@get30DaysBefore(todayInSeconds)),

    url = "https://graph.facebook.com/v15.0/",
    baseOptions = [
        RelativePath = "XXXXXXXXX/insights",
        Query = [
            metric = metrics,
            period="day",
            since=initSince,
            until=initUntil,
            access_token=metaPageToken
        ]
    ],
    initReq = Json.Document(Web.Contents(url, baseOptions)),
    initData = initReq[data],

    // 辅助函数：解析分页URL，转换为Web.Contents可接受的静态URL+参数格式
    parsePagingUri = (uri as text) as record =>
        let
            parts = Uri.Parts(uri),
            staticUrl = "https://" & parts[Host],
            relativePath = parts[Path],
            queryParams = parts[Query]
        in
            [Url = staticUrl, Options = [RelativePath = relativePath, Query = queryParams]],

    gather = (data as list, currentUri as text, i as number) =>
        let
            // 解析分页链接，构造合规的请求参数
            pagingParams = parsePagingUri(currentUri),
            newReq = Json.Document(Web.Contents(pagingParams[Url], pagingParams[Options])),
            newData = newReq[data],
            updatedData = List.Combine({data, newData}),
            // 检查是否还有下一页，且递归次数未超限
            hasPrevious = Record.HasFields(newReq[paging], {"previous"}),
            check = if not hasPrevious then updatedData else if i > 20 then updatedData else @gather(updatedData, newReq[paging][previous], i+1)
        in
            check,

    // 初始化递归逻辑
    outputList = if Record.HasFields(initReq[paging], {"previous"}) then @gather(initData, initReq[paging][previous], 1) else initData
// 后续数据处理逻辑...
in
    outputList

额外注意事项 #

• 时间范围参数检查：确认get30DaysBefore函数是否为误保留，若要拉取12个月数据，需替换为计算12个月前时间戳的对应函数
• 权限与配置：确保Power BI Service端已设置正确的数据源隐私权限，且metaPageToken具备拉取12个月Insights数据的API权限
• 递归次数限制：当前代码限制递归20次，若12个月数据需要更多分页请求，可适当调高该数值（需注意Meta API的调用频率限制）

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