Power BI pagination :: How to pull all data from API using "NextPageLink"

Question

I want to pull all Azure resource prices from the official Azure Retail Prices overview.

With Power BI I can easily retrieve the first 100 records from the URL https://prices.azure.com/api/retail/prices this way:

let
    Source = Json.Document(Web.Contents("https://prices.azure.com/api/retail/prices")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded Items" = Table.ExpandListColumn(#"Converted to Table", "Items"),
    #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"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"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Items1",{{"BillingCurrency", type text}, {"CustomerEntityId", type text}, {"CustomerEntityType", type text}, {"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}, {"NextPageLink", type text}, {"Count", Int64.Type}})
in
    #"Changed Type" 

But then the page ends with:

"NextPageLink": "https://prices.azure.com:443/api/retail/prices?$skip=100","Count": 100

How can I make Power BI click on that link and pass to the next page... and so on... till there are no more pages?

Answer 1

are you able to refresh the dataset in Power BI Service where the dataset contains API calls using skip and offset method? when I used it in PBI Desktop everything works as expected but when published to service and tried refreshing it..it errors out with error as "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed"

Answer 2

Here you go:

I have only retrieved the first 500 rows. To retrieve everything, delete the following: and [Offset] < 500

let
    Query1 = let
    
        
a = 

List.Generate( () => [ Offset = 0, data = pagingFunction( 100 ) ],
                  each [data][NextPageLink] <> null and [Offset] < 500, 
                  each [ data = pagingFunction( [Offset] ),
                         Offset = [Offset] + 100 ], 
                  each [data]
),

    pagingFunction = (offset) =>
    let
        Source = Json.Document(Web.Contents("https://prices.azure.com/api/retail/prices?$skip="& Number.ToText( offset ))),
        Convert = Table.FromRecords({Source}),
        #"Removed Other Columns" = Table.SelectColumns(Convert,{"Items", "NextPageLink"}),
        #"Expanded Items" = Table.ExpandListColumn(#"Removed Other Columns", "Items"),
        #"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {"currencyCode", "tierMinimumUnits", "retailPrice", "unitPrice", "armRegionName", "location", "effectiveStartDate", "meterId", "meterName", "productId", "skuId", "availabilityId", "productName", "skuName", "serviceName", "serviceId", "serviceFamily", "unitOfMeasure", "type", "isPrimaryMeterRegion", "armSkuName"}, {"Items.currencyCode", "Items.tierMinimumUnits", "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"})
    in
        #"Expanded Items1"
, b= Table.FromList({a})

in a,
    #"Converted to Table" = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Items.currencyCode", "Items.tierMinimumUnits", "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", "NextPageLink"}, {"Items.currencyCode", "Items.tierMinimumUnits", "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", "NextPageLink"})
in
    #"Expanded Column1"