Úvod do API v Power Query

tl;dr API je způsob jak dvě appky spolu komunikují. Díky dvěma funkcím Mka Web.Contents(), Json.Document(), můžeme API používat i v rámci Power Query. Níže je popsáno, jak na to.

Příběh

Už jste někdy byli smutní, že vaše oblíbená appka nemá nativní konektor do Power BI, takže musíte manuálně stahovat excely, ukládat je na on-premises server (nebo na Sharepoint, pokud máte štěstí), zatímco vaši softwaroví vývojáři a datoví inženýři používají nějakou zakázanou a temnou metodu, která jim umožní napřímo se na appku napojit?

Řešení

V tom případě vám představuji: API (v dnešním příkladě, JIRA REST API)—zakázaná a mystická síla, kterou vaši chrabní (možná) kolegové pravděpodobně používají. API je zkratka pro Application Programming Interface a je mostem, přes který dvě aplikace spolu dokáží komunikovat.

API je v češtině Aplikační Programové Rozhraní. Pokud byste chtěli zachovat písmenka, tak Architektura Propojení Informací (velmi volně přeloženo, jenom jako komediální vsuvka).

V Power BI nemáme vyloženě API konektor (v Power Query Online, například v Dataflows, je něco, co si na to hraje, ale je to stejně klasický Web konektor). Nicméně tento Web konektor nám bohatě stačí na to, abychom mohli s API pracovat.

Charakteristika API

API zvenku vypadá jako běžný internetový odkaz—on to totiž jeden takový odkaz je. Základem je vždy hostitel—hlavní server, například https://org.atlassian.net/. Potom máme relativní cestu nebo koncový bod—část, která hned následuje za hostitelem, například rest/api/3/search?. Dost často tyto dva komponenty stačí, nicméně máme možnost přidat ještě další, psány za koncovým bodem, například maxResults, jql, nebo fields. Tyto parametry se liší podle API, kterou používáte, nicméně základní charakteristika zápisu je stejná.

Pro lepší orientování, hostitel = host; koncový bod = endpoint

Poslední klíčová věc—autentifikace. Abychom měli klid na duši, soukromé API vyžadují nějaký způsob autentifikace. Tyto typy můžou být Basic Authentication, Bearer Token, anebo API Key (asi vám je ani nebudu překládat; v češtině se stejně používají anglické termíny). Možná už jste někdy slyšeli, že někdo omylem nechal interní API Key v nějakém veřejně publikovaném kódu. Toto je tzv. velký špatný, protože kdokoliv může mít najednou přístup do interní aplikace (pokud je veřejná z internetu). Což teda většinou nechcete. Naštěstí v rámci Power BI budete pravděpodobně publikovat pouze do vašeho tenantu, který je odříznutý od zbytku světa. Takže můžete být v klidu.

Když už známe základy, jak tuto sílu zkrotíme v Power Query?

Jak na to

(tady už budou i nějaké obrázky—určitě jste si je zasloužili po přečtení úvodu)

Web.Contents()

Web.Contents(url as text, optional options as nullable record) as binary

M funkce Web.Contents() vrací obsah stažený z vyplněné adresy (URL). Samotné funkci stačí pouze jeden parameter, nicméně je však vhodné (a někdy i nezbytné) doplnit přídavné informace. K tomu slouží druhý volitelný parametr. Tyto možnosti jsou přednastavené v rámci funkce, pro plný list doporučuji mrknout na oficiální dokumentaci.

V dnešním příkladě využijeme dvě volitelné možnosti:

• RelativePath

• Headers

Relative Path

Zadaná textová hodnota je přidána za základ URL před vytvořením požadavku

Oddělení hostitele a relativní cesty je naprosto důležité z jednoho důvodu. Dynamický zdroj. Respektive zabránění dynamického zdroje. Pokud chcete report automaticky aktualizovat v Power BI Službě (Service), je právě nutné oddělit tyto dvě části, aby Power BI vedělo, podle kterého zdroje má nastavit typ oprávnění, a ten se následovně nesmí měnit.

Tento princip funguje i na ostatní typy zdrojů a není unikátní pro Web.Contents().

Headers

Zadání této hodnoty jako záznamu poskytne další headers pro HTTP požadavek

Slouží k přesnějšímu definování, jakým způsobem budete s API komunikovat. Většinu headerů v Power Query nemusíme vypisovat. "Headery" jsou předurčené pole, kde každé slouží pro definování různé části požadavku. Seznam všech polí můžete najít zde. V našem případě nás zajímá pouze Authorization. Toto pole slouží k definování příhlašovacích údaju. O tomto poli si povíme více v dalším článku.

Při definování vlastního autentifikačního headeru je nezbytené nastavit v oprávnění zdrojů (pro danou URL) typ oprávnění jako Anonymous. Pokud používáte URL poprvé, Power Query se vás na to zeptá samo.

Demo

Nyní můžeme dát vše dohromady a dostaneme napříkald takový HTTP požadavek.

Web.Contents(
    "https://org.atlassian.net",
    [
        RelativePath = "rest/api/3/search?jql=assignee=currentUser()",
        Headers = [
            Authorization = "Basic xx"
        ]
    ]
)

Tip: Dejte si pozor na "/", aby jich nebylo více za sebou. Celý požadavek potom nemusí fungovat, protože si je některé API nedokáží přeložit.

V samotném Mku a jeho funkcích moc češtiny nenajdete, na rozdíl od Excelu třeba, kde se funkce překládají.

Toto je základní kostra volání API—také známo jako HTTP request (lepší termín pro googlení) a měla by pokrýt většinou API volání s metodou GET.

GET metoda je HTTP požadavek, primárně určen pro získání dat ze serveru, zatímco POST slouží k posílání dat, nicméně i skrz něj dokážete získat data, například pro autentifikaci

Přirozeně, do našeho volání můžeme přidat více možností hlavičky, jako třeba Accept, který říká serveru, jaký typ odpovědi budeme zpracovávat (nebo které rozumíme). Například, typicky bychom tuto možnost nastavili na application/json, pokud očekáváme JSON data. Nicméně i když hlavičku nenastavíme, většina API vrací jako základní typ JSON.

Zpracování obsahu

Kdybychom poslali výše uvedený požadavek, dostaneme něco takového.

Pro simulaci reálného API, používám konkrétní adresu namísto https://org.atlassian.net/

Když už víme, že většinou pracujeme s JSON odpověďmi, můžeme použít M funkci Json.Document(), abychom rozbalili jeho obsah.

Toto je typická struktura začátku JSONu. Vlastní data jsou uvnitř pole issues, zatímco ostatní pole obsahují různé metadata:

• startAt - Index první položky aktuální stránky.

• maxResults - Maximální počet položek v jedné stránce.

• total - Celkový počet položek bez ohledu na stránky.

• issues - Vlastní data.

  
  

Navigace v datech

Já vím, že jsem už několikrát zmínil stránky bez pořádného vysvětlení. Abychom však zachovali tento článek jednodušší, stránky prozatím elegantně přeskočím a budu jim věnovat vlastní článek, kde je probereme do hloubky (odkaz zde).

Prozatím nás tedy zajímají jenom vlastní data. Ty otevřeme skrz pole issues.

Pokud se vám pak lépe pracuje s tabulkou, můžete záznam rozbalit následovně.

Table.FromRecords je dobře zapamatovatelná možnost vytvoření tabulky, určitě však není nejvhodnější (pokud chceme brát v potaz konzumaci paměti). Pro detailnější porovnání způsobu vytvoření tabulky mrkněte na skvělý článek zde (sekce Extracting).

Na základě struktury našich data, můžeme dále a dále rozbalovat položky, dokud se nedostaneme na úroveň detailu, který potřebujeme. Odsud jdou však kroky jednoduše naklikat, stejně jako kdybyste pracovali s jiným datasetem, například Excelem.

let
    Zdroj = Web.Contents(
        "https://org.atlassian.net",
        [
            RelativePath = "rest/api/3/search?jql=assignee=currentUser()",
            Headers = [
                Authorization = "Basic xx"
            ]
        ]
    ),
    vemObsah = Json.Document(Zdroj),
    dostanSeKPolozkam = vemObsah[issues],
    vyrobTabulku = Table.FromRecords(dostanSeKPolozkam)
in
    vyrobTabulku

Závěr

Ukázali jsme si, jak jednoduše dokážeme získat data z API jenom za pomocí jedné funkce: Web.Contents(). Tyto kroky budou obecně fungovat pro API, které nevyžadují stránkování. Abychom ale měli v rukávu víc než jedno eso. Představíme si také tato další témata. Každé jako vlastní článek (bez specifického řazení a včetně odkazů):

• Stránkování

• Typy autentizace