none
Loop through pages using /M RRS feed

  • Question

  • I have a power query created that connects to a vendor created API.

    Note in the underlined/bold portion, the API accepts a page and records per page parameter.  In the example below, it is page 0 and 50 records per page.  The problem is that I can only run this for one page at a time.  If I raise the records per page number to a high value, like 99999, the request times out.  I need to loop or iterate through the pages and combine the results into a single set. 

    Any feedback is appreciated.

    let
        Source = Xml.Tables(Web.Contents("https://api.vendorname.com/v1/timecards/modifiedsince/2014-12-31/page/0/50")),
       #"Changed Type" = Table.TransformColumnTypes(Source,{{"RecordCount", Int64.Type}, {"PageIndex", Int64.Type}, {"PageSize", Int64.Type}, {"PageCount", Int64.Type}}),
       List = #"Changed Type"{0}[List],
        Timecard = List{0}[Timecard],
        #"Changed Type1" = Table.TransformColumnTypes(Timecard,{{"OriginalId", Int64.Type}, {"Id", Int64.Type}, {"CreatedDate", type datetime}, {"CreatedUserId", Int64.Type}, {"ModifiedDate", type datetime}, {"ModifiedUserId", Int64.Type}, {"Submit", type logical}, {"SubmitDate", type datetime}, {"Row", Int64.Type}, {"TimecardDate", type datetime}, {"HoursAdjust", Int64.Type}, {"HoursBreak", Int64.Type}, {"HoursDay", Int64.Type}, {"ApprovedStatus", type text}, {"DateApproved", type datetime}, {"ProcessId", Int64.Type}, {"ProcessDate", type datetime}, {"BillableId", Int64.Type}, {"BillableDate", type datetime}, {"PayableId", Int64.Type}, {"InvoicedDate", type datetime}, {"BillRateBase", Int64.Type}, {"BillRateMult", Int64.Type}, {"PayRateBase", Int64.Type}, {"PayRateMult", Int64.Type}}),
        #"Expand User" = Table.ExpandTableColumn(#"Changed Type1", "User", {"Id", "LoginName"}, {"User.Id", "User.LoginName"})
    in
        #"Expand User"

    Wednesday, January 14, 2015 2:55 AM

Answers

All replies