How to combine sources into one?

Question

I have an issue when Calling a REST endpoint. The resulting data set is too large for the endpoint to return (I get an HTTP 500 error). I can split the Query up in pieces, e.g. by month. How do I perform multiple calls to the endpoint - one for each month I want to return, and then combine them into one table?

Unfortunately, the REST endpoind doesn't support ODATA queries, so I cannot page through the result set.

let

Source1 = Json.Document(Web.Contents("https://someurl?theapi" & "&q=Date>='2019-01-01' AND Date<='2019-01-31'")),
Source2 = Json.Document(Web.Contents("https://someurl?theapi" & "&q=Date>='2019-02-01' AND Date<='2019-02-28'")),
Table1= Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Table2= Table.FromList(Source2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
CompositeTable= Table.NestedJoin(Table2, {"Datum"}, Table1, {"Datum"}, "CompositeTable", JoinKind.LeftOuter)

in CompositeTable

I want to have the result sets from both queries merged into "CompositeTable"

David David · Accepted Answer · 2019-06-12T13:43:20

There's a great blog post by Mark Tiedemann that suggests a solution. I've applied this method a dozen times and it works flawlessly for any paginated API I encountered.

Mark's elegant solution is to query the first page, extract the number of total results from it and then call the GetPage function for all remaining pages and combine all pages using the List.Union function.

For your use case, I would use a start and end date instead of items/page and total items. For each month in between start and end date, call the function that queries this month only, and combine the results. To give you an idea, something like this:

let 
    BaseUrl         = "https://someurl?theapi&",
    StartDate       = #date(2019,01,01),
    EndDate         = #date(2019,05,31),

    GetJson = (Url) =>
        let Json = Json.Document(Web.Contents(Url))
        in  Json,

    GetPage = (Index) =>
        let Start = "Date>=" & Text.From(Date.StartOfMonth(Index)),
            End   = "Date<=" & Text.From(Date.EndOfMonth(Index)),
            Url   = BaseUrl & "&q=" & Start " AND " & End,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,

    PageIndices = { LIST OF ALL MONTHS },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

How to combine sources into one?

1 Answers