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

Time:11-12

I want to pull all Azure resource prices from the official enter image description here

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"
  • Related