Power BI分页实现：如何通过NextPageLink从API拉取全量数据

要实现自动遍历所有分页，直接用Power Query的递归函数循环请求NextPageLink即可，直到返回结果中没有下一页链接为止。以下是修改后的完整M代码：

let
    // 定义递归函数，用于遍历所有分页
    GetAllPages = (url as text) as table =>
        let
            // 请求当前页数据
            Source = Json.Document(Web.Contents(url)),
            // 提取当前页的Items列表和下一页链接
            CurrentItems = Source[Items],
            NextLink = Source[NextPageLink],
            // 转换当前页Items为表格
            CurrentTable = Table.FromList(CurrentItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            ExpandedItems = Table.ExpandRecordColumn(CurrentTable, "Column1", {"currencyCode", "tierMinimumUnits", "reservationTerm", "retailPrice", "unitPrice", "armRegionName", "location", "effectiveStartDate", "meterId", "meterName", "productId", "skuId", "availabilityId", "productName", "skuName", "serviceName", "serviceId", "serviceFamily", "unitOfMeasure", "type", "isPrimaryMeterRegion", "armSkuName"}, {"Items.currencyCode", "Items.tierMinimumUnits", "Items.reservationTerm", "Items.retailPrice", "Items.unitPrice", "Items.armRegionName", "Items.location", "Items.effectiveStartDate", "Items.meterId", "Items.meterName", "Items.productId", "Items.skuId", "Items.availabilityId", "Items.productName", "Items.skuName", "Items.serviceName", "Items.serviceId", "Items.serviceFamily", "Items.unitOfMeasure", "Items.type", "Items.isPrimaryMeterRegion", "Items.armSkuName"}),
            // 判断是否有下一页，有则递归调用并合并数据，无则返回当前页
            Result = if NextLink <> null then Table.Combine({ExpandedItems, GetAllPages(NextLink)}) else ExpandedItems
        in
            Result,
    // 调用递归函数，从初始API地址开始
    AllPagesData = GetAllPages("https://prices.azure.com/api/retail/prices"),
    // 转换字段类型（和你原来的类型保持一致）
    ChangedType = Table.TransformColumnTypes(AllPagesData,{{"Items.currencyCode", type text}, {"Items.tierMinimumUnits", Int64.Type}, {"Items.reservationTerm", type any}, {"Items.retailPrice", type number}, {"Items.unitPrice", type number}, {"Items.armRegionName", type text}, {"Items.location", type text}, {"Items.effectiveStartDate", type datetime}, {"Items.meterId", type text}, {"Items.meterName", type text}, {"Items.productId", type text}, {"Items.skuId", type text}, {"Items.availabilityId", type any}, {"Items.productName", type text}, {"Items.skuName", type text}, {"Items.serviceName", type text}, {"Items.serviceId", type text}, {"Items.serviceFamily", type text}, {"Items.unitOfMeasure", type text}, {"Items.type", type text}, {"Items.isPrimaryMeterRegion", type logical}, {"Items.armSkuName", type text}})
in
    ChangedType

关键说明： #

• GetAllPages是核心递归函数，接收当前请求URL作为参数，返回当前页加后续所有页的合并表格
• 每次请求后先提取当前页的Items和NextPageLink，如果NextPageLink不为空，就递归调用自己获取下一页数据，再和当前页数据合并
• 类型转换部分保留了你原来的设置，确保字段类型一致

注意：首次运行时数据量可能很大（Azure价格数据条目非常多），请耐心等待加载完成，也可以考虑添加筛选条件减少请求数据量（比如在初始URL后加?$filter=armRegionName eq 'eastus'之类的筛选器）。

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