Stránkování v Power Query

tl;dr stránkování je způsob, jak rozdělit data na menší části (stránky). Díky tomu můžeme data přenášet efektivněji. Tahle technika se často používá při práci s API a získávání většího množství dat. V Power Query můžeme pracovat se stránkovanými zdroji pomocí jednoduchých seznamů nebo využít funkce jako List.Generate().

Potíž

Představte si, že si objednáváte burger v novém fastfoodu. Nejste si však jistí, jestli mají v nabídce bezlepkové (GF) a veganské produkty. Zeptáte se tedy číšníka, jestli by vám mohl doporučit něco z menu, co by vám vyhovovalo. Číšník s radostí začne vyjmenovávat všechny bezlepkové možnosti, ale nezastaví se tam a pokračuje dál, dokud nevyjmenuje všechno, co je na menu. Po pěti minutách jeho vyčerpávajícího přednesu mu řeknete, že si stejně vyberete tu první možnost, protože si zbytek už ani nepamatujete. Nicméně byste si k jídlu ještě rádi dali něco k pití, a tak se zeptáte na limonády, které mají dnes k dispozici. Číšník opět vesele začne vyjmenovávat všechny možné nápoje, včetně kávy, čaje a podobně, aniž by vzal v úvahu, že jste chtěli slyšet jen limonády...

Doufám, že tento příběh je jen smyšlený, ale být takto zahlceni možnostmi by bylo opravdu frustrující. Stejně jako si nechceme užít takovou zkušenost v restauraci, nechceme, aby něco podobného zažila i naše data. Místo toho, abychom dostali miliony řádků, které nepotřebujeme, můžeme si vybrat jen konkrétní část obsahu a dostat přesně tu.

Této metodě, která nám to umožňuje, se říká stránkování.

Stránkování

Stránkování je technika, která rozděluje data na menší části (stránky). Často se používá při práci s velkými datovými soubory a API. Díky tomu usnadňuje zpracování datových toků, urychluje přenos dat a zlepšuje škálovatelnost.

Tento článek se zaměřuje především na stránkování v souvislosti s API.

Stránky

Každá stránka obsahuje omezený počet položek. Klient si může vyžádat pouze jednu stránku, nebo pokračovat v požadavcích na další stránky, dokud nezíská dostatek dat, případně celý dataset. Při práci s API se můžete setkat s různými typy stránkování.

Nebudeme si ukazovat všechny typy stránkování, jenom ty základní, pokud si chcete přečíst více mrkněte sem nebo sem.

Typy stránkování

Offset & Limit

Tato technika se točí kolem parametrů offset & limit. Offset určuje počáteční pozici v datasetu a limit nastavuje maximální počet položek, které budou zahrnuty na jedné stránce.

Liší se podle konkrétního API, ale může být pojmenována i jinak, například startAt & maxResults.

GET /api/posts?offset=30&limit=10

Tento požadavek by přeskočil prvních 30 položek a vrátil dalších 10.

Token-based

Když si vyžádáte data pro první stránku, odpověď obsahuje jak data, tak token, který odkazuje na další stránku. Tento token pak použijete k získání následujících stránek a pokračujete v procesu, dokud nedosáhnete poslední stránky (ta je často označena polem isLast, nastaveným na hodnotu true nebo false). Tato metoda zajišťuje, že se žádná data nevynechají, a zároveň není potřeba vytvářet vlastní smyčku pro načtení všech stránek.

Požadavek

GET /api/items?limit=5

Odpověď

{"data": ["item1", "item2", "item3", "item4", "item5"], "next_token": "abc123"}

Poždavak pro další stránku

GET /api/items?limit=5&token=abc123

Odpověď

{"data": ["item6", "item7", "item8", "item9", "item10"], "next_token": "def456"}

Time-based

Jak už název napovídá, tato metoda využívá čas a datum k řízení stránkování. V závislosti na implementaci můžete často zadat parametry start_timestamp a end_timestamp, kterými definujete přesné časové rozmezí pro každý požadavek na data.

GET /api/data?start_timestamp=2023-01-01T00:00:00&end_timestamp=2023-01-31T23:59:59

Kombinované typy stránkování

Různé API, můžou využívat různé typy stránkování, výjimkou není kombinace dvou a více typů dohromady. Doporučuji si tedy projít dokumentaci (pokud existuje, haha) a příklady odpovědí, abyste zjistili, jaké konkrétní možnosti máte k dispozici.

Implementace v Power Query

Teorie za námi! Pojďme si nyní projít prakticky, jak jednotlivé typy materializujeme v Power Query.

Budu využívat reálné příklady z praxe, takže se klidně můžete nechat inspirovat, pokud pracujete s podobnými aplikacemi. Doporučuji ještě mrknout na Úvod do API v Power Query, pokud jste s API předtím nepracovali.

Upozornění: Toto není návod na konkrétní API. Vybral jsem API pouze k demonstraci určitých typů stránkování, přičemž vždy může existovat optimalizovanější způsob. I tak se ale můžete inspirovat.

Offset & Limit v PQ

Příklad: JIRA REST API Cloud v3; Get Issues

V tomto příkladu zjistíme, kolik položek máme celkem, nastavíme velikost jedné stránky a poté budeme postupně načítat jednotlivé stránky.

Získej celkový počet položek

První věc, co uděláme, je vytvoření základní šablony požadavku, abychom nemuseli opakovat stejné kroky dokola.

Vlastní funkce je skvělý nástroj pro snížení opakujících se kroků a pro zajištění větší flexibility a dynamiky procesů. Více o vlastních funkcích se dozvíte zde (odkaz brzy přidám).

request = (offset as number, limit as number) =>  
    Json.Document(  
        Web.Contents(  
        "https://simvojtech.atlassian.net/",  
        [  
            RelativePath="/rest/api/3/search?jql=project=BI",  
            Query =   
                [  
                    startAt=Text.From(offset),  
                    maxResults=Text.From(limit)  
                ],  
            Headers=  
                [  
                    Authorization=basic_auth_string  
                ]  
        ]  
        )  
    )  

basic_auth_string je textový parameter, kde ukládám přihlašovací údaje

JIRA používá odlišné klíčové názvy pro offset a limit. Tyto jsou startAt a maxResults

Pro tento příklad bude naše funkce obsahovat pouze dva parametry: offset a limit.

I když bychom mohli plně parametrizovat všechno, nebudeme věci komplikovat a zaměříme se jen na části, které je potřeba měnit.

Abychom získali celkový počet položek, provedeme požadavek s minimálním množstvím potřebných dat a využijeme pole total v odpovědi.

getTotal = request(0, 0)

I když odpověď nevrátí žádná vlastní data, pořád máme přístup k poli total, které nás zajímá.

Potvrdili jsme, že máme k dispozici pole total. Nyní upravíme náš krok tak, aby přímo načetl celkový počet položek

getTotal = request(0, 0)[total]

Vypočítej celkový počet stránek

S jasně definovaným celkovým počtem položek můžeme rozhodnout, jaký maximální počet položek na stránku (limit) použijeme, a spočítat, kolik stránek bude potřeba.

Limit se liší podle jednotlivých API. V JIRA Cloud je aktuální maximální počet položek na stránku 100, takže můžeme použít jakékoli číslo od 0 do 100.

V tomto případě zvolíme 100.

pageSize = 100

Jde se počítat.

totalPages = Number.RoundDown( getTotal / pageSize )

Musíme zaokrouhlit dolů, jelikož indexujeme od 0, takže potřebujeme 3 stránky (0-100, 101-200, 201-249).

Plnění stránek

Máme pár možností, jak naplnit stránky. Můžeme si stránky napočítat a plnit ručně, případně použít existující funkci jako například List.Generate(). Ukážeme si obě.

Manuální plnění

Nejdřív pojďme vypočítat celkový počet stránek pomocí intervalu.

manualPages = {0..totalPages}

Nyní už stačí jen stránky naplnit.

populatePages = List.Transform( manualPages, each request(_*pageSize, pageSize))

V tomto kroku iteruju přes každou stránku (0, 1, 2). Pro každou stránku, voláme funkci s HTTP požadavkem (kterou jsme si dřív nadefinovali), uvnitř které definujeme offset a limit. Offset se rovná aktuální hodnotě (ukryté pod "_"), kterou vynásobíme velikostí stránky. Tudíž pro každou stránku by to vypadalo takto:

• Stránka 1 - 0*100

• Stránka 2 - 1*100

• Stránka 3 - 2*100

Limit zůstává vždy stejný, tudíž 100.

Jedná z odpovědí by vypadala následovně:

Jelikož víme, jak jednotlivé odpovědi vypadají, upravíme výsledek kroku tak, abychom získali přímo pole issues, jelikož tam se schovávájí naše vlastní data.

populatePages = List.Transform( manualPages, each request(_*pageSize, pageSize)[issues])

K dokončení transformace můžeme použít List.Combine(), který nám sloučí stránky do jedné a vrátí jednotlivé položky. Díky tomu můžeme zkontrolovat, že máme opravdu 249 jedinečných hodnot.

expandPages = List.Combine(populatePages)

V tuto chvíli můžeme provést standardní sadu kroků pro načtení a ověření naší odpovědi.

List.Generate()

List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list

Tato metoda vyžaduje jedinou funkci. Nemusíme zde ručně vytvářet jednotlivé stránky, nicméně počáteční logika může být na první pohled složitější na pochopení.

populatePagesLG = List.Generate(
    ()=> 0, 
    (page) => page <= totalPages, 
    (page) => page + 1, 
    (page) => request(page * pageSize, pageSize)
)

Co se děje ve funkci:

()=> 0
// Definice počáteční hodnoty, která nastavuje výchozí bod (první stránku)

(page)=> page < totalPages
// Podmínka, která říká, že generování pokračuje, dokud je hodnota menší než totalPages (celkový počet stránek)

(page)=> page+1
// Po každé iteraci přidáme 1 k aktuální hodnotě, což posouvá na další stránku

(page)=> request(page*pageSize, pageSize)
// Při každé iteraci spustíme HTTP požadavek s aktuálním offsetem (page*pageSize) a limitem (pageSize)

Zde jsem se rozhodl pracovat s parametrem "page", který drží aktuální hodnotu. Můžeme však použít jakýkoli název, nebo využít klíčové slovo each a pracovat s podtržítkem (_) jako aktuální hodnotou namísto "page".

Pokud preferujete práci s each, funkce by vypadala takto:

populatePagesLGEach = List.Generate(  
    ()=> 0,  
    each _ <= totalPages,  
    each _ + 1,  
    each request(_ * pageSize, pageSize)  
)

each; klíčové slovo, které slouží jako zkratka pro vytvoření anonymní funkce s jedním parametrem. Plná reprezentace each by vypadala takto: (_)=>. Hlavním účelem each je zkrátit kód a učinit ho přehlednějším; each můžete vidět v generovaných krocích po kliknutí v uživatelském rozhraní.

Pokud bychom chtěli napodobit přímý přístup k jednotlivým položkám (issues), můžeme upravit krok stejným způsobem jako u manuálního přístupu.

populatePagesLGEach = List.Generate(  
    ()=> 0,  
    each _ <= totalPages,  
    each _ + 1,  
    each request(_ * pageSize, pageSize)[issues]  
)

Nakonec můžeme naše seznamy rozbalit pomocí List.Combine() stejným způsobem.

expandPagesLGEach = List.Combine( populatePagesLGEach )

Time-based; implementace v PQ

Příklad: Power BI REST API; Get Activity Events

Tato metoda je poněkud přímočará bez nutnosti různých stránkovacích vychytávek.

Pojďmě vyrobit šablonu požadavku. V našem příkladě API pracuje s dvěma parametry:

• startDateTime

• endDateTime

Server očekává hodnotu obalenou v jednoduchých úvozovkách ('2024-11-11T00:01:00.000Z')

request = (start as text, end as text) => 
    Json.Document(
        Web.Contents(
            "https://api.powerbi.com", 
            [
                RelativePath = "v1.0/myorg/admin/activityevents", 
                Query = [
                    startDateTime = "'" & start & "'", 
                    endDateTime = "'" & end & "'"
                ], 
                Headers = [
                    Authorization = "Bearer " & temp_token
                ]
            ]
        )
    )

Toto je naše základní struktura. Inicializovali jsme dva parametry: start a end. Tyto parametry budou sloužit jako vstup pro startDateTime a endDateTime. Někteří z vás si určitě všimli, že abychom zajistili obalení hodnot v jednoduchých úvozovkách, přidáváme je přímo do definice.

temp_token je vygenerovaný a dočasný bearer token jako textový dotaz

getFilteredData = request("2024-11-11T00:01:00.000Z", "2024-11-11T23:59:59.999Z")

Tohle by u mnoha API stačilo. Naštěstí v našem případě máme možnost zavést stránkování založené na tokenu (token based), protože odpověď vypadá takto:

Token-based; implementace v PQ

Příklad: Power BI REST API; Get Activity Events; navazuje z Time-based stránkování

Jak už název napovídá, každá odpověď obsahuje token, který použijeme k načtení další stránky. V našem příkladu můžeme buď přímo použít continuationToken, nebo zvolit continuationUri. Druhá možnost však vyžaduje úpravu adresy, aby správně fungovala v Power Query. Nicméně doporučuje se použít continuationUri, poskytnutý přímo od API, pro zamezení možným problémům s DNS.

„Problémy s DNS“ znamenají, že hostitel v URL navržené pro další stránku se může lišit od hostitele použitého ve vašem původním požadavku. Tento nesoulad vyžaduje, abyste adresu převedli na preferovaného hostitele určeného vývojáři API. V minulosti se v našem příkladovém endpointu mohly při tomto převodu objevovat chyby.

Pro přístup k další stránce použijeme poskytnutou URI. Rozdělíme ji na dvě části: nový hostitel, kterého bohužel musíme zapsat staticky pro zamezení "dynamického zdroje", a relativní cesta, která může a měla by zůstat dynamická. Nakonec ještě odstraníme parametry s časovými značkami, protože již nejsou potřeba.

Vytvoříme tedy novou šablonu pro požadavek na další stránku, abychom měli čistší kód.

nextPageRequest = (uri as text) => 
    Json.Document(
        Web.Contents(
            "https://wabi-west-europe-e-primary-redirect.analysis.windows.net", 
            [
                RelativePath = Text.AfterDelimiter(uri, "/", 2), 
                Headers = [
                    Authorization = "Bearer " & temp_token
                ]
            ]
        )
    )

Text.AfterDelimeter() vrátí druhou část URI, tedy textovou část za třetím znakem "/". Indexování začíná od 0, takže hodnota 2 znamená třetí výskyt tohoto symbolu.

odstraněná část: https://wabi-west-europe-e-primar-redirect.analysis.windows.net/

výstup: v1.0/myorg/admin/activityevents?continuationToken='eyJTdGFydER...'

Nyní máme vše, co potřebujeme, a můžeme začít volat stránky:

token_based_call = 
    List.Generate(
        () => request("2024-11-11T00:01:00.000Z", "2024-11-11T23:59:59.999"), 
        each not _[lastResultSet], 
        each nextPageRequest(_[continuationToken]), 
        each _[activityEventEntities]
    )

Upozornění pro návštěvníky, kteří „nečtou a rychle kopírují“: Toto ještě není finální řešení.

Zde zase můžeme použít naši oblíbenou funkci List.Generate(). Rozebereme si, co se uvnitř ní děje:

token_based_call = 
    List.Generate(
        () => počáteční požadavek pomocí naší funkce "request" s časovými známkami,

        iteruj, dokud hodnota, která označuje poslední stránku, není TRUE,

        pomocí pokračovacího tokenu volej funkci "nextPageRequest",

        při každé iteraci ulož data z pole "activityEventEntities"
    )

Příklad funguje perfektně—akorát že vůbec. Tento konkrétní požadavek by měl vrátit 3 stránky, ale dostáváme jen 2.

Problém je, že nedostáváme data z poslední stránky. Jelikož v poslední stránce je lastResultSet nastaven jako TRUE, funkce skončí předčasně.

Existuje několik způsobů, jak to opravit. Můžete použít funkce jako List.Accumulate() nebo zkusit napsat rekurzivní funkci. Tyto možnosti ale přidají komplexitu do našeho řešení, proto vám ukážu jednodušší trik.

token_based_call = 
List.Generate(
    () => 
        [
            call = request("2024-11-11T00:01:00.000Z", "2024-11-11T23:59:59.999Z"), 
            nextUri = call[continuationUri]?, 
            isLastPage = call[lastResultSet]?
        ], 
    each not [isLastPage], 
    each 
        [
            call = nextPageRequest(nextUri), 
            nextUri = _[call][continuationUri]?, 
            isLastPage = _[call][lastResultSet]?
        ], 
    each [call][activityEventEntities]?
)

Tato metoda je inspirována @Štěpánem Rešlem a jeho knihovnou (link).

Toto je už hotovka, můžete ji zkopírovat ("nečtu, ale rychle kopíruji" návštěvníci)

V tomto řešení není tolik změn, jak by se na první pohled mohlo zdát. V podstatě pro každou iteraci vytváříme vlastní záznam, kde kontrolujeme jak data, tak i podmínku iterace. Tento přístup nám umožňuje se „zpozdit“ o jednu iteraci, což nám umožní načíst a uložit data i pro poslední stránku.

Tady je stejný kód doplněn o komentáře:

token_based_call_commented = 
    List.Generate(
        // Iniciální hodnota: vytvoř vlastní záznam
        () => 
            [
                // Iniciální volání API s časovými známkami
                call = request("2024-11-11T00:01:00.000Z", "2024-11-11T23:59:59.999Z"), 
                
                // Vem pole "continuationUri" z položky call.
                nextUri = call[continuationUri]?, 
                
                // Vem pole "lastResultSet" z položky call.
                isLastPage = call[lastResultSet]?
            ], 
        
        // Podmínka: Iteruj, dokud "isLastPage" není pravda.
        // Hodnota je brána z vytvořeného vlastního záznamu
        each not [isLastPage], 
        
        // Transform: Definice proměny stavu pro další iteraci.
        // Musíme použít stejná pole jako při iniciálním volání.
        each 
            [
                // Zavolej další stránku pomocí uri z předchozího kroku.
                call = nextPageRequest(nextUri), 
                
                // Aktualizuj "continuationUri" pomocí předchozí hodnoty pole.
                nextUri = _[call][continuationUri]?, 
                
                // Aktualizuj "lastResultSet" pomocí předchozí hodnoty pole.
                isLastPage = _[call][lastResultSet]?
            ], 
        
        // Selector: Vem data z pole ("activityEventEntities") z každé odpovědi.
        each [call][activityEventEntities]?
    )

Pokud bychom tento přístup zobrazili v tabulce, mohl by vypadat takto:

"Opožděná" odpověď

Shrnutí

V dnešním podrobném průvodci jsme se zabývali několika klíčovými metodami stránkování—offset-limit, time-based, a token-based stránkování. Tyto metody pomáhají efektivně zpracovávat velké datové sady, zejména při práci s API. Ukázali jsme si, jak implementovat každý typ v Power Query, a to pomocí vlastních funkcí a nástrojů, jako je List.Generate(), pro vytvoření flexibilních a efektivních systémů stránkování.

Seznámili jsme s různými postupy plnění stránek. Od základního manuálního přístupu až po využití dynamických funkcí jako je například List.Generate(). Též jsme si ukázali, jak řešit případné logické potíže skrz "opožděné" ukládání dat. Doufám, že nově nabyté dovednosti vám umožní propojit Power Query přímo s vaší aplikací bezbolestně.

Děkuji za přečtení a jděte si dopřát opravdový (ne imaginární) burger! 🍔