Jednou z často používaných techník pri príprave reportov je vytvorenie špeciálnych tabuliek, ktoré nám majú uľahčiť buď výpočty, alebo reporting. Dnes si ukážeme techniku, ktorá sa nazýva sekvenčné tabuľky. A ako pomocou nich vytvoriť tabuľku, kde bude v jednom stĺpci pod sebou postupnosť hodnôt zo zadaného intervalu.

Sekvenčné tabuľky sa používajú na to, aby ste si vedeli vygenerovať postupnosť hodnôt, cez ktoré potom chcete výpočtom prechádzať, alebo si tým chcete niečo zjednodušiť. Má to využitie buď pri rôznych dátumových výpočtoch, pri zbere stránkovaných dát z webstránok, či napr. pri výpočtoch zloženého úroku.

Metód je na to viacero. A keďže nie všetci pracujete s najnovším PowerPivotom, resp. Power BI, tak si ukážeme aj metódu, ako to spraviť aj v Power Query.

Ako vytvoriť sekvenčné tabuľky v Power BI a SSAS Tabulare

V najnovšom Power BI Desktope  aj SSAS Tabulare máte možnosť vytvoriť si vypočítanú tabuľku. A zároveň tam máte skratkovitú funkciu GENERATESERIES s touto syntaxou:

GENERATESERIES(začiatočnáHodnota; koncováHodnota; [krok])

Táto funkcia vráti tabuľku s jedným stĺpcom vyplneným za sebou idúcimi hodnotami od začiatočnejHodnoty po koncovúHodnotu. Parameter krok je nepovinný, a znamená veľkosť kroku v postupnosti. Ak nie je zadaný, použije sa ako krok hodnota 1.

Keď teda chceme vytvoriť vypočítanú tabuľku so stĺpcom, kde budú za sebou hodnoty od 1 do 10, tak zadáme tento vzorec:

Tabuľka = GENERATESERIES(1; 10)

Výsledná tabuľka bude vyzerať takto:

A keď chcete napr. iba párne hodnoty od 1 do 10, tak zadáte aj 3. parameter funkcie ako 2, s vhodne upravenými hodnotami 1. a 2. parametra:

Tabuľka = GENERATESERIES(2; 10; 2)

Výsledná tabuľka bude vyzerať takto:

A funguje to aj s dátumami. Nasledujúci vzorec vygeneruje sekvenciu dátumov od 1. 8. 2018 do 1. 9. 2018:

Tabuľka = GENERATESERIES(DATE(2018; 8; 1); DATE(2018; 9; 1))

Namiesto funkcie GENERATESERIES môžete v prípade dátumov použiť aj funkciu CALENDAR, s rovnakou syntaxou.

Takže takto jednoduché je to vytvoriť v Power BI, a novšom SSAS Tabulare. Čo však majú robiť tí, čo majú iba excelovský PowerPivot? Nemusíte vypĺňať bunky v Exceli potiahnutím myši, ale môžete použiť Power Query.

Ako vytvoriť sekvenčné tabuľky v Power Query

V Power Query tiež idú vytvoriť sekvenčné tabuľky, tak ako aj ľubovoľné iné tabuľky. Výhodou tohto postupu je, že si môžete vytvoriť aj dynamické tabuľky, ktorých rozsah riadkov sa prepočíta pri aktualizácii dát, podľa vašich parametrov. To ide samozrejme spraviť aj v PowerPivote, ale ten nemá taký obrovský arzenál funkcií pre prípravu dát, aký má jazyk M v Power Query. Predsa len, je určený na analýzy.

Keď si teda chcete vytvoriť sekvenčnú tabuľku v Power Query, tak si otvorte Excel alebo Power BI Desktop, a v ňom vytvorte nový Prázdny dotaz. V Exceli 2016 sa to robí tak, že kliknete v hlavnom menu na záložku Údaje, a tam na Nový dotaz => Z iných zdrojov => Prázdny dotaz:

Otvorí sa Editor Power Query, kde by ste mali mať zobrazený riadok na zadanie vzorca:

Ak ho tam nemáte, tak choďte na záložku Zobrazenie, a tam zakliknite políčko Riadok vzorcov:

Keď už teda máte zobrazený riadok na zadanie vzorca, tak doňho napíšte tento vzorec v jazyku M:

= List.Generate( () => 1, each _ < 11, each _ + 1)

Tento vzorec nám vygeneruje zoznam, čiže niečo ako jednostĺpcovú tabuľku, kde budú hodnoty od 1 do 10. Na prvý pohľad to vyzerá odstrašujúco, ale nebojte sa, detaily sa dajú celkom v pohode naučiť na kurze Power Query 🙂 Syntax tejto funkcie je takáto:

List.Generate(start as () as any, condition as (item as any) as logical, next as (item as any) as any, optional transformer as (item as any) as any) as list

Podobá sa to cyklom for v programovacích jazykoch, ale s týmto sa dá robiť oveľa viac. Prvý parameter hovorí o tom, kde začať (v našom prípade s ktorým číslom), druhý parameter definuje ukončovaciu podmienku (v našom prípade podmienka s číslom o 1 väčším ako potrebujeme), a tretí parameter hovorí o kroku (v našom prípade +1). Štvrtý parameter je nepovinný, a v tomto momente nepodstatný.

Po zadaní tohto vzorca a potvrdení Enterom to bude vyzerať takto:

Vygeneruje sa nám zoznam s hodnotami od 1 do 10, ako sme chceli. Takýto zoznam však nejde načítať ani do Excelu, ani do Power BI, lebo tam idú načítať iba dotazy, ktoré vracajú tabuľky. A aj keď toto vyzerá ako 1-stĺpcová tabuľka, tak v skutočnosti je to tak len zobrazené, ale tabuľka to nie je. Vidno to aj podľa ikonky, ktorá je zobrazená vedľa nášho dotazu, keď si šípkou rozbalíte ten skrytý panel naľavo:

Musíme ten zoznam teda prekonvertovať na tabuľku. Spravíme to tak, že klikneme v hlavnom menu na záložku Nástroje zoznamu, a tam hneď na prvú ikonu s názvom Do tabuľky:

Otvorí sa vám okno, v ktorom iba kliknite na tlačítko OK:

Následne bude vidieť v ľavom paneli, že to je už tabuľka:

Teraz už stačí prípadne pridať ďalšie stĺpce, či skombinovať to s inými tabuľkami, a po dokončení klikneme na Zavrieť a načítať (ak to chcete načítať do Excelu), alebo na Zavrieť a načítať do… (ak to chcete načítať priamo do PowerPivotu). Alebo ak ste v Power BI Desktope, tak na Zavrieť a použiť:

…a voilááá, máme to v Exceli:

Táto funkcia v jazyku M však funguje iba na číselné postupnosti. Ak by ste chceli vytvoriť postupnosť dátumov – napr. do časovej tabuľky alebo do kombinačnej tabuľky – tak budete potrebovať trochu iný postup.

Na začiatku samozrejme otvoríte nový Prázdny dotaz, rovnako ako aj v predchádzajúcom prípade. Potom kliknete v hlavnom menu na tlačítko Rozšírený editor:

Potom doňho zadáte dole uvedený skript v jazyku M:

let
StartDate = #date(2018, 8, 1),
Duration = Duration.Days(Duration.From(Date.From(DateTime.LocalNow()) – StartDate)) + 1,
Zdroj = List.Dates(StartDate, Duration, #duration(1, 0, 0, 0))
in
Zdroj

Poznámka: Ak by vám Power Query písalo, že máte vo vzorci chybu blízko toho mínuska, tak ho zmažte a napíšte na klávesnici znova. Problém je v prehliadači, ktorý nahradzuje mínuská v texte dlhými pomlčkami, podobne ako napr. MS Word.

Tento skript vytvorí zoznam dátumov od 1. 8. 2018 po dnešný dátum. Dátum 1. 8. 2018 si vytvoríte funkciou #date, dnešný dátum funkciou DateTime.LocalNow(), a zoznam s dátumami od-do pomocou funkcie List.Dates. Výsledok bude vyzerať takto:

So syntaxou si moc hlavu nelámte. Jazyk M nepatrí medzi zrovna najľahšie jazyky, a učí sa na kurze Power Query. Takže ak ho chcete zvládnuť, tak ho veľmi silno odporúčam navštíviť. Lebo vysvetliť to tu by bolo na 10 ďalších článkov, a pochopiť celý jazyk M na ďalších 50 až 100. A to už by som radšej vydal ďalšiu knihu, ale zatiaľ sa mi nechce 🙂

Výsledok samozrejme, rovnako ako v prechádzajúcom prípade, prekonvertujte na tabuľku. A nezabudnite zmeniť dátový typ toho stĺpca na Dátum, aby sa vám to správne natiahlo do Excelu, PowerPivotu či Power BI. Spravíte to tak, že v hlavnom menu kliknete na tlačítko Typ údajov, kde zvoľte položku Dátum:

A na konci kliknite na Zavrieť a načítať, resp. Zavrieť a použiť.

Takže takto sa dajú vytvárať sekvenčné tabuľky aj v jazyku DAX, aj v jazyku M. Jazyk M je na to flexibilnejší, ale je o dosť mentálne náročnejší na pochopenie v porovnaní s DAX-om. Takže ak tomu nechcete chápať, tak si ako vždy zoberte vzorec a upravte si ho na svoje hodnoty 🙂 A ak tomu chcete pochopiť, tak dôjdite na najbližší kurz Power Query. Po ňom pre vás sekvenčné tabuľky budú hračkou 🙂