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?

see https://gorilla.bi/power-query/list-generate-api-calls/ and similar — horseyride, Nov 10 '22 at 11:33

score 1 · Accepted Answer · answered Nov 10 '22 at 12:47

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"

Thank you @David, but without `and [Offset] < 500 ` it runs forever — Francesco Mantovani, Nov 10 '22 at 13:19
1h has passed and PowerQuery is still iterating. Is that normal? — Francesco Mantovani, Nov 10 '22 at 14:15
You are retrieving > 100k rows from the web in 100 row chunks so it is going to take a long time... — Davide Bacci, Nov 10 '22 at 14:32
100.000 rows / chunks of 100 = 1000 queries. If it takes 1 second for each query we just need 1000 seconds to parse the whole API. So in 16 minutes it should be done. There's something fishy here... — Francesco Mantovani, Nov 10 '22 at 14:55
So strange David, after several hours it fails with this error: https://snipboard.io/CLYxwZ.jpg . I will try to pull the data with Python and I will let you know — Francesco Mantovani, Nov 12 '22 at 19:52
Hi David, I could query the API through Python (https://stackoverflow.com/questions/74438621/how-to-export-azure-prices-rest-api-to-csv) , as result a CSV is produced with 455.800 rows and is executed in 1:15h . PowerQuery shouldn't fail under 1 millions rows so I wander if the problem is a timeout. Is there any way I can increase the timeout for the query? — Francesco Mantovani, Nov 15 '22 at 14:40
I'm not sure to be honest. Have you tried changing [data][NextPageLink] <> null to [data][NextPageLink] <> "". You need to see how the [NextPageLink] actually looks on the last page when there is no further link. — Davide Bacci, Nov 15 '22 at 14:50

score 0 · Answer 2 · answered Aug 14 '23 at 13:40

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"

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

2 Answers2

Linked