Json Pagination

Anonymous · ‎06-01-2020

Hi all,

Very new to Power BI but having some issues getting data via JSON, the first page is imported but subsequent pages are ignored. I have followed a couple of tutorials but can't seem to find the issue here:

https://cloudposapi.citruslime.com/swagger/ui/index

let
    Source = Json.Document(Web.Contents("https://cloudposapi.citruslime.com/api/CustomerOrder", [Headers=[Authorization="xx"]])),
    BaseUrl = "https://cloudposapi.citruslime.com/api/CustomerOrder",
    EntitiesPerPage = 1000,
 
    GetJson = (Url) =>
        let RawData = Web.Contents(Url),
            Json = Json.Document(RawData)
        in  Json,
    
    GetTotalEntities = () =>
        let Json = GetJson(BaseUrl),
            Total = Json[total]
        in  Total,

    GetPage = (Index) =>
        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),
            Url   = BaseUrl & "&" & Skip,
            Json  = GetJson(Url),
            Value = Json[data]
        in  Value,

    GetUrl = (Index) =>
        let Skip  = "skip=" & Text.From(Index * EntitiesPerPage),
            Url   = BaseUrl & "&" & Skip
        in  Url,
  
    EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
   
 URLs  = List.Transform(PageIndices, each GetUrl(_)),
 Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Uid", "ClientRef", "Closed", "DateCreated", "TypeAsString", "Type", "Comment", "CustomerID", "ShipToID", "Deposit", "Tax", "Total", "LastUpdated", "DueDate", "Taxable", "SalesRepID", "ReferenceNumber", "ShippingChargeOnOrder", "ChannelTypeAsString", "ChannelType", "CheckedIn", "CustomerName", "OrderLines"}, {"Uid", "ClientRef", "Closed", "DateCreated", "TypeAsString", "Type", "Comment", "CustomerID", "ShipToID", "Deposit", "Tax", "Total", "LastUpdated", "DueDate", "Taxable", "SalesRepID", "ReferenceNumber", "ShippingChargeOnOrder", "ChannelTypeAsString", "ChannelType", "CheckedIn", "CustomerName", "OrderLines"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Total", Currency.Type}})
in
    #"Changed Type"

dax · ‎06-02-2020

Hi @Anonymous ,

I can't test this in my environment because of the authentication. You could check each step to see which step can't work. By the way, you also could @ super users for more suggestions.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi