API zwraca tylko pierwszą stronę wyników - stronicowanie w Power Query

Power Query w Excelu, Power BI i Fabric

API zwraca tylko pierwszą stronę wyników - stronicowanie w Power Query

W skrócie

Z paginacją API w Power Query spotkasz się, gdy pobierasz dane przez API z systemu sprzedaży, CRM albo sklepu internetowego: podgląd wygląda poprawnie, a tabela kończy się na pierwszej setce rekordów. Pokazujemy, jak rozpoznać schemat stronicowania (ang. pagination), jak pobrać wszystkie strony pętlą w języku M i jak zapisać zapytanie, żeby odświeżało się w Excelu i w usłudze Power BI. Wzorce sprawdziliśmy w polskim Excelu, także na publicznym API NBP, którego używamy w kursie.

Jak to wygląda w praktyce

Zapytanie zbudowane w oknie Z sieci Web albo funkcją Web.Contents działa bez błędu, a danych jest za mało. Rozpoznasz to po kilku sygnałach:

• liczba wierszy jest okrągła (50, 100, 1000) i nie rośnie, choć w systemie przybywa rekordów,
• w rekordzie odpowiedzi JSON obok listy danych stoją pola w rodzaju page, total, offset, next albo nextLink,
• suma kontrolna w Power Query jest niższa niż w raporcie samego systemu.

Brak komunikatu to najważniejsza wskazówka. Serwer odpowiedział poprawnie i oddał pierwszą stronę, więc Power Query nie ma czego zgłosić. Błąd DataSource.Error z kodem stanu HTTP oznacza inny problem: żądanie w ogóle się nie powiodło.

Dlaczego tak się dzieje

API oddają duże zbiory porcjami, żeby pojedyncze żądanie nie trwało zbyt długo. Spotkasz trzy schematy: numer strony (parametr w rodzaju page), przesunięcie (para w rodzaju offset i limit, czyli „pomiń 200, oddaj 100”) albo adres następnej strony podany w odpowiedzi, którego na ostatniej stronie brak. Funkcja Web.Contents wysyła jedno żądanie HTTP i zwraca jedną odpowiedź, więc o kolejnych stronach nie wie. Dokumentacja Microsoft, opisując zrywane połączenia przy dużych odpowiedziach API, zaleca sprawdzić, czy API obsługuje stronicowanie, i dzielić żądania na mniejsze porcje.

Pętlę po stronach zapisujesz w języku M. Wzorzec dla numeru strony (adres i nazwy pól są przykładowe):

let
    Strona = (nr as number) => Json.Document(Web.Contents("https://api.przyklad.pl/",
        [RelativePath = "zamowienia", Query = [page = Text.From(nr)]])),
    Strony = List.Generate(
        () => [nr = 1, dane = Strona(1)],
        each List.Count([dane][items]) > 0,
        each [nr = [nr] + 1, dane = Strona([nr] + 1)],
        each [dane][items]),
    Wszystko = List.Combine(Strony)
in
    Table.FromRecords(Wszystko)

List.Generate zaczyna od strony 1 i pobiera kolejne, dopóki drugi argument zwraca true, czyli dopóki strona zawiera rekordy. Na danych testowych z trzema stronami po dwa rekordy pętla zatrzymała się na czwartej, pustej stronie i zwróciła 6 wierszy.

Jak to rozwiązać krok po kroku

1. Obejrzyj odpowiedź API. W edytorze kliknij Record albo List, żeby zejść poziom niżej, i ustal, które pole zawiera rekordy (w API NBP to rates, we wzorcu items) oraz który schemat stronicowania stosuje API. Nazwy parametrów potwierdź w dokumentacji API.
2. Wklej wzorzec. Na karcie Strona główna rozwiń Nowe źródło, wskaż Inne źródła i wybierz Puste zapytanie. Otwórz Edytor zaawansowany, zastąp zawartość wzorcem z poprzedniej sekcji i podmień adres, ścieżkę, nazwę parametru strony oraz pola z rekordami.
3. Przesunięcie zamiast numeru strony. Funkcja Porcja = (przes as number) => Json.Document(Web.Contents("https://api.przyklad.pl/", [RelativePath = "zamowienia", Query = [offset = Text.From(przes), limit = "100"]]))[items] zwraca od razu listę rekordów. Pętla startuje od () => [p = 0, dane = Porcja(0)], sprawdza each List.Count([dane]) > 0 i przesuwa się o rozmiar strony: each [p = [p] + 100, dane = Porcja([p] + 100)]. Na 250 rekordach testowych pobrała porcje 100, 100 i 50.
4. Adres następnej strony. Pętla idzie po linkach z odpowiedzi: List.Generate(() => Odpowiedz(adresStartowy), each _ <> null, each if [next]? = null then null else Odpowiedz([next]), each [items]), gdzie Odpowiedz = (adres as text) => Json.Document(Web.Contents(adres)). Znak zapytania w [next]? obsługuje ostatnią stronę bez tego pola. Bez niego pętla kończy się błędem Expression.Error: Nie można znaleźć pola „next” w rekordzie. (ang. The field 'next' of the record wasn't found.).
5. Zmienne części adresu tylko w RelativePath i Query. Dokumentacja Microsoft podaje, że usługa Power BI w większości przypadków nie odświeża dynamicznych źródeł danych, czyli adresów składanych w trakcie działania zapytania. Wyjątkiem są opcje RelativePath i Query. Wariant z adresem następnej strony składa cały adres dynamicznie, więc jeśli API pozwala, wybierz numer strony albo przesunięcie.
6. Liczby w Query zamieniaj na tekst. Wpis Query = [top = 1] kończy się błędem Expression.Error: Nie możemy przekonwertować wartości 1 na typ Text. (ang. We cannot convert the value 1 to type Text.), dlatego we wzorcach stoi Text.From(nr).
7. Ustaw poziom prywatności przy wielu adresach. W Excelu pętla ze stałą ścieżką i numerem strony w Query (trzy żądania do API NBP) odświeżyła się bez pytań. Zapytanie pobierające kilka różnych ścieżek tego samego API zatrzymało się na komunikacie „Aby połączyć dane, potrzebne są informacje. Określ poziom prywatności dla każdego źródła danych.” (ang. Information is needed in order to combine data. Please specify a privacy level for each data source.). Wtedy na karcie Dane rozwiń Pobierz dane, wybierz Ustawienia źródeł danych, zaznacz źródło, kliknij Edytuj uprawnienia i dla publicznego API wybierz poziom Publiczne.
8. Uwzględnij limity zapytań. Przy kodzie 429 (Too Many Requests) Web.Contents według dokumentacji Microsoft sam ponawia żądanie do trzech razy, z rosnącym odstępem albo po czasie z nagłówka Retry-After. Mniej żądań wyślesz, ustawiając największy rozmiar strony, na jaki pozwala API.

Jak sprawdzić, że zadziałało

Porównaj liczbę wierszy z liczbą podaną przez API (pole w rodzaju total) albo z raportem w samym systemie. Liczbę pobranych stron zobaczysz, zmieniając na chwilę ostatnią linię zapytania na List.Count(Strony). Jeśli liczba wierszy jest dokładną wielokrotnością rozmiaru strony, sprawdź, czy ostatnia strona rzeczywiście przyszła pusta. Nawrotom zapobiega warunek końca oparty na odpowiedzi API (pusta lista, brak adresu następnej strony). Liczba stron wpisana na stałe przy rosnących danych po cichu ucina nowe rekordy.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5