V niektorých prípadoch potrebujete stiahnuť dáta z webu alebo z webovej aplikácie. Samotné stiahnutie tabuľky z webu pozná už asi každý, ale problém nastáva, keď sú dáta rozdelené na viacerých stránkach, a chcete ich zozbierať a spojiť dokopy zo všetkých z nich. Najmä ak neviete, na koľkých stránkach sú všetky dáta. Preto sa teraz pozrieme, ako stiahnuť stránkované dáta do Power BI a Excelu cez Power Query.

Prečo potrebujeme stiahnuť stránkované dáta

Ako zdroj dát tentokrát použijeme dáta zo systému RASFF, čiže Systému rýchleho varovania pre potraviny a krmivá, ktorý som použil už v staršom článku pri analýze bezpečnosti potravín. Tieto dáta zbiera EÚ, ale nie sú úplne voľne dostupné. Ministerstvo zemědelství ČR ich však pravidelne publikuje na svojom webe, konkrétne na tejto adrese:

https://www.bezpecnostpotravin.cz/kategorie/hlaseni-v-systemu-rasff.aspx

Zobrazí to zhruba takúto tabuľku, so zoznamom hlásení z každého týždňa:

Keď však prelistujete na koniec stránky, tak zistíte, že celá tabuľka nie je na jednej stránke, ale je rozdelená na viacero stránok:

…a samozrejme neviete vopred, koľko ich je. Môžete síce prejsť až na poslednú stránku, ale počet stránok samozrejme časom narastá. A my by sme chceli, aby v našom výslednom riešení nebol nakódovaný natvrdo. Napríklad preto, že chceme nastaviť automatickú aktualizáciu dát v reporte, prípadne aj s inkrementálnou aktualizáciou dát (detailný postup viď napr. na tomto webinári), aby si to každý týždeň potiahlo nové dáta. Preto musíme náš kód pripraviť tak, aby postupne iteroval na ďalšiu a ďalšiu stránku, až príde na poslednú z nich. A tam skončí.

Analýza stránkovaných dát na webstránke

Na takúto úlohu je najvhodnejšie použiť Power Query. A v rámci neho použiť techniku stránkovania, ktorá sa často používa vtedy, keď chcete stiahnuť stránkované dáta, či už do Excelu, Power BI, alebo do iného softvéru, kde sa nachádza Power Query.

Najprv si však zanalyzujeme, ako táto zdrojová webová stránka zobrazuje prvú, druhú, tretiu až N-tú stránku s dátami. Keď sa pohráme s menu na prechod na ďalšie stránky, tak zistíme, že takto sa dá dostať k jednotlivým stránkam dát:

• k prvej stránke s dátami sa dostaneme cez URL:
  https://www.bezpecnostpotravin.cz/kategorie/hlaseni-v-systemu-rasff.aspx
• k druhej stránke s dátami s dostaneme cez URL:
  https://www.bezpecnostpotravin.cz/kategorie/hlaseni-v-systemu-rasff.aspx?start=30
• k tretej stránke s dátami s dostaneme cez URL:
  https://www.bezpecnostpotravin.cz/kategorie/hlaseni-v-systemu-rasff.aspx?start=60
• …a tak ďalej.

Po zovšeobecnení URL vyššie a pohraní sa s URL parametrom “start” zistíme, že na tejto stránke sa ku N-tej stránke s dátami vieme dostať cez takéto URL:

https://www.bezpecnostpotravin.cz/kategorie/hlaseni-v-systemu-rasff.aspx?start=XXX

…kde XXX je číslo, ktoré zistíme ako výsledok vzorca ((N * 30) – 1), pretože na 1 stránke je zobrazených 30 záznamov. Toto číslo sa v prípade tohto webu dá zmeniť cez ďalší URL parameter s názvom “count”, ak chcete na 1 zavolanie vrátiť viac záznamov naraz, ale to teraz nemusíme riešiť.

Ak teda chceme zozbierať dáta zo všetkých stránok, tak potrebujeme niekoľko- (možno veľa-)krát zavolať posledne uvedené URL s rôznym parametrom “start”, a potom výsledky spojiť do 1 veľkej tabuľky.

Koľkokrát to však potrebujeme zavolať, keď ani nevieme, na koľkých stránkach sú rozdelené dáta? To si musíme zistiť tiež. V momente písania tohto článku bolo na tej webstránke dokopy cca. 800 záznamov. Skúsime teda zavolať naše URL s vyšším parametrom “start”, napr. s hodnotou 3000, aby sme videli, čo to spraví, keď budeme chcieť neexistujúce dáta. Vráti to stránku bez tabuľky:

Takže takto rozpoznáme, že sme došli až do konca. A celé riešenie bude spočívať v tom, že postupne budeme sťahovať dáta z ďalšej a ďalšej stránky, až dokým to nevráti prázdnu tabuľku. Tam náš cyklus ukončíme, čiastkové tabuľky zo všetkých stránok zlúčime do jednej veľkej tabuľky, a máme náš výsledok.

Ako stiahnuť stránkované dáta cez Power Query

Začneme teda s tým, že si spravíme funkciu v Power Query, ktorá bude riešiť sťahovanie dát z jednej konkrétnej stránky. Nazveme ju “vratJednuStrankuDat”, a bude mať takýto skript v jazyku M:

(url as text) as nullable table =>
let
    Zdroj = Web.Contents("https://www.bezpecnostpotravin.cz", [RelativePath=url]),
    dataTabulka = Web.Page(Zdroj){[Caption = "Články v kategorii:"]}?,
    dalsieURL = "/kategorie/hlaseni-v-systemu-rasff.aspx?start=" & Text.From(Value.FromText(Text.AfterDelimiter(url, "start=")) + 30),
    data = if dataTabulka <> null then dataTabulka[Data] else null   
in
    data meta [dalsiaStrankaURL = dalsieURL]

Táto funkcia stiahne dáta z URL, uvedeného v parametri s názvom “url”, ako tabuľku. Klauzula “nullable” v jej deklarácií hovorí o tom, že môže vrátiť aj nič, ako hodnotu null. Funkciou Web.Contents stiahneme obsah daného URL, funkciou Web.Page ho rozparsujeme, a na tom istom riadku dáme z výsledku vrátiť tabuľku z toho riadku, kde je v stĺpci “Caption” uvedená hodnota “Články v kategorii:”, ak taká vôbec existuje.

Následne si v ďalšom riadku vypočítame URL adresu pre ďalšiu stránku s dátami. Spravíme to tak, že si z aktuálneho URL vylámeme hodnotu parametra “start”, a pripočítame k nej hodnotu 30.

Na ďalšom riadku povieme, že ak to našlo tabuľku s výsledkami na stránke, tak ju má vrátiť, inak má vrátiť hodnotu null.

V poslednom riadku dáme vyhodnotiť posledný alias s názvom “data” – čiže vrátime tabuľku s dátami, ak sme nejaké našli. Pridáme k nej však metaúdaje – pole s názvom “dalsiaStrankaURL”, ktoré bude obsahovať URL ďalšej stránky s dátami, ktoré sme vypočítali vyššie. Zmysel toho uvidíte o chvíľu.

Túto funkciu si otestujeme s nejakým vzorovým parametrom, napr. s jedným z hore uvedených URL pre konkrétnu stránku s dátami, a keď to funguje, tak môžeme prejsť k ďalšiemu kroku.

Iterovanie cez viaceré stránky a spojenie výsledkov

V ďalšom kroku sa potrebujeme precykliť/preiterovať cez všetky stránky, stiahnuť z nich dáta našou funkciou “vratJednuStrankuDat”, a dať ich dohromady. Využijeme na to vzorovú funkciu Table.GenerateByPage, ktorú nájdeme v dokumentácii k Power Query, a ktorá má takýto kód:

// The getNextPage function takes a single argument and is expected to return a nullable table
Table.GenerateByPage = (getNextPage as function) as table =>
    let        
        listOfPages = List.Generate(
            () => getNextPage(null),            // get the first page of data
            (lastPage) => lastPage <> null,     // stop when the function returns null
            (lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
        ),
        // concatenate the pages together
        tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
        firstRow = tableOfPages{0}?
    in
        // if we didn't get back any pages of data, return an empty table
        // otherwise set the table type based on the columns of the first page
        if (firstRow = null) then
            Table.FromRows({})
        else        
            Value.ReplaceType(
                Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
                Value.Type(firstRow[Column1])
            )

Táto funkcia robí nasledovné:

• na vstupe očakáva funkciu s jedným parametrom, ktorá buď vracia dáta pre jednu stránku alebo hodnotu null,
• vytvorí zoznam pomocou funkcie List.Generate, ktorého položky budú tabuľky. Tabuľky s dátami z konkrétnych stránok. Najprv zavolá funkciu na získanie počiatočnej stránky (funkcia getNextPage bola zadaná cez parameter, a tam využijeme neskôr našu funkciu “vratJednuStrankuDat”). V každom cykle/iterácii použije výsledok z predchádzajúceho volania funkcie, na získanie dát pre ďalšiu iteráciu, resp. z ďalšej čiastkovej stránky. A potom pokračuje v generovaní zoznamu dovtedy, dokým táto funkcia nevráti hodnotu null. Ak táto funkcia vráti null, tak sa generovanie zoznamu ukončí.

Použitie funkcie Table.GenerateByPage

A to ešte len začne byť zaujímavé, keď si ukážeme, ako použiť túto funkciu. Vytvoríme si teda nový prázdny dotaz v Power Query, do ktorého najprv vložíme premennú s počiatočným URL, potom kód pre hore uvedenú funkciu Table.GenerateByPage, a potom cez alias nazvaný “vyhodnot” túto funkciu zavoláme:

let
    pociatocneURL = "/kategorie/hlaseni-v-systemu-rasff.aspx?start=0",
    // The getNextPage function takes a single argument and is expected to return a nullable table
    Table.GenerateByPage = (getNextPage as function) as table =>
        let        
            listOfPages = List.Generate(
                () => getNextPage(null),            // get the first page of data
                (lastPage) => lastPage <> null,     // stop when the function returns null
                (lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
            ),
            // concatenate the pages together
            tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
            firstRow = tableOfPages{0}?
        in
            // if we didn't get back any pages of data, return an empty table
            // otherwise set the table type based on the columns of the first page
            if (firstRow = null) then
                Table.FromRows({})
            else        
                Value.ReplaceType(
                    Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
                    Value.Type(firstRow[Column1])
                ),
    vyhodnot = Table.GenerateByPage(
        (predchadzajuciVysledok) => 
        let
            // vygenerujeme hodnotu parametra
            // ak parameter "predchadzajuciVysledok" je null, tak toto je nasa prva stranka s datami, ...
            // ...a teda vrat pociatocneURL, inak vrat URL z metadatoveho pola "dalsiaStrankaURL" z predchadzajucehoVysledku
            dalsieURL = if (predchadzajuciVysledok = null) then pociatocneURL else Value.Metadata(predchadzajuciVysledok)[dalsiaStrankaURL]?,
            // if NextLink was set to null by the previous call, we know we have no more data
            stranka = if dalsieURL <> null then vratJednuStrankuDat(dalsieURL) else null
        in
            stranka
    )
in
    vyhodnot

Pri volaní funkcie Table.GenerateByPage si všimnite, že používame in-line funkciu s jedným parametrom, ktorý obsahuje predchádzajúci čiastkový výsledok. Čiže výsledok predchádzajúceho volania funkcie “vratJednuStrankuDat”, ktorý obsahuje tabuľku s dátami na danej webovej stránke, s metadátovým poľom “dalsiaStrankaURL”. Ak je parameter “predchadzajuciVysledok” rovný hodnote null, tak vieme, že ideme sťahovať dáta z prvej stránky (lebo viď hore “cyklus” pre generovanie zoznamu, kde sa volá táto funkcia s parametrom null).

Podľa toho vygenerujeme premennú “dalsieURL”, ktorá bude obsahovať buď hodnotu premennej “pociatocneURL”, alebo URL z metadátového poľa “dalsiaStrankaURL” z predchadzajucehoVysledku (viď funkcia “vratJednuStrankuDat”, ktorá okrem vracania tabuľky s dátami z danej stránky, vráti aj metadátové pole “dalsiaStrankaURL” s URL adresou pre ďalšiu stránku dát). No a potom na základe toho, či to ďalšie URL je null-ové alebo nie, spravíme o, že ak nie je null-ové, tak zavoláme funkciu “vratJednuStrankuDat” pre toto URL (ktorá vráti dáta z ďalšej stránky a tie sa zároveň uložia do výsledného zoznamu tabuliek generovaného funkciou List.Generate vyššie). A ak je ďalšie URL null-ové, tak vráti null, ako reprezentáciu prázdnych dát.

Táto podmienka by tam nemusela v tomto prípade vôbec byť a mohli by sme volať priamo funkciu “vratJednuStrankuDat”, ale nechal som vám to tam kvôli tomu, ak by ste používali dátový zdroj, ktorý po poslednej stránke dát už nevracia URL pre ďalšiu stránku s dátami. Čo je relatívne bežný jav pri sťahovaní stránkovaných dát cez protokol OData.

Vo výsledku, keď si to všetko pospájame, tak to funguje tak, že zavoláme funkciu Table.GenerateByPage, ktorá vygeneruje zoznam s tabuľkami, kde 1 tabuľka sú dáta z 1 stránky. Pri jeho generovaní volá funkciu “getNextPage” zo svojho parametra, ktorú sme v aliase “vyhodnot” zadali ako in-line funkciu, popísanú vyššie. Najprv to stiahne dáta z prvej stránky, a potom dokola volá funkciu “vratJednuStrankuDat” s URL parametrom ďalšej stránky, kde sa nachádzajú ďalšie dáta. A pokračuje to až dovtedy, dokým funkcia “vratJednuStrankuDat” nevráti hodnotu null, čím sa generovanie zoznamu s tabuľkami ukončí. Na ďalších riadkoch sa tieto tabuľky spoja do jednej veľkej tabuľky, a to je vrátené ako výsledok.

Keď to takto máme, tak stačí skript v dotaze odkliknúť, a dostaneme takýto krásny výsledok:

Potom stačí “Dotaz1” premenovať na výsledný názov tabuľky, a načítať do Power BI, Excelu, alebo iného softvéru, kde sa nachádza Power Query. A máte to hotové.

Na záver

Pri tejto technike si však dajte pozor na jednu vec. Vopred si otestujte, ako sa na vašom dátovom zdroji bude správať funkcia, ktorá vracia dáta z jednej stránky. Pretože ak už neexistuje ďalšia stránka s dátami, tak má vrátiť hodnotu null. Ak ju nevráti, tak sa vám môže pokojne stať, že tento skript pôjde donekonečna, a nikdy neskončí. A zabijete tým nielenže veľa času, ale budete musieť zostreliť Power Query (alebo rovno celý Power BI, Excel a pod., ak neviete ako na to). A čo je najhoršie – môže si to všimnúť admin tej webovej stránky, že voláte tisíce až milióny dotazov v krátkom čase, a úplne vám zablokovať prístup na stránku, aby ste ju nepreťažovali. Takže si najprv poctivo otestujte tú funkciu, a až potom to skúste zreťaziť podľa postupu vyššie. Tak ušetríte veľa nepríjemností nielen sebe, ale aj ľuďom na druhej strane s vašim dátovým zdrojom.

Takto sa teda dajú stiahnuť stránkované dáta do Power BI a Excelu cez Power Query. Na prvý pohľad to vyzerá ako šialene komplikovaný kód, ale keď nad tým trocha viac pouvažujete, tak pochopíte tú logiku. A budete vedieť stiahnuť stránkované dáta cez Power Query celkom ľahko 🙂