API. Постраничный запрос данных.

Довольно часто встречаются API, которые отдают данные постранично. С учётом того, что в Power Query отсутствует цикл типа do while, приходится искать альтернативные способы загрузки всех страниц.

Для примера рассмотрим гипотетический API, который отдает данные с ограниченным количеством строк на одной странице (в одном запросе).

В запросе используются параметры: $count, $top и $skip. Данные возвращаются в формате JSON.

Задача: получить все страницы с данными и преобразовать их в единую таблицу для работы в Power BI.

Алгоритм:

1. Отправляем запрос со следующими параметрами : $count=true и $top=0. Этот запрос определяет, сколько строк нам нужно будет получить (но на самом деле сами данные запрос не получает).

2. В зависимости от того, сколько строк с данными существует всего и сколько строк мы хотим (или ограничены лимитом) получить на одной странице, мы можем вычислить, сколько страниц нам нужно получить (если у нас есть 1650 строк, и мы хотим получить 1000 строк на странице, нам придется запросить 2 страницы).

3. Затем мы можем создать список индексов страниц, которые мы хотим получить (например, если мы хотим получить 2 страницы, индексы { 0, 1 }) и сопоставить индексы с фактическими страницами, отправив запросы для каждой страницы с параметрами $skipи $top.

GetPage(0) --> skip = 0 * 1000 = 0, top = 1000[пропустить 0 строк, вернуть первые 1000], 

GetPage(1) --> skip = 1 * 1000 = 1000, top = 1000[пропустить первые 1000 строк, вернуть следующие 1000] и т. д.).

4. В результате у нас будет список страниц (каждая страница содержит набор строк). Чтобы создать единый список всех строк, мы должны слить списки страниц. Наконец, мы должны преобразовать список в таблицу.

Решение:

let 
    BaseUrl         = "https://fake-odata-api.com/v1/Entities?",
    Token           = "F4K3-T0K3N-D0NT-U5E-L0L",
    EntitiesPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

Статья подготовлена по материалам блога Mark Tiedemann