JAK SI VYTVOŘIT DATUMOVOU TABULKU?
Pokud jste potřebovali v modelu využít kalendářní data, buď jste museli využít Time Intelligence anebo si vytvořit vlastní tabulku s datumy. Pojďme se podívat na to, jak si ji tedy můžeme vyrobit.
V článku o Time intelligence jsme zmínili důvod, proč je rozumnější vypínat funkci Auto date/time (Časové měřítko) a vytvořit si namísto toho vlastní tabulku představující časovou hierarchii.
Možností, jak si v Power BI vytvořit datumovou tabulku, neboli DateKey je nespočet. V tomto článku si pro příklad představíme tři vybrané způsoby, jak toho lze dosáhnout:
- V Power Query jednoduchou metodou,
- v Power Query vlastní funkcí,
- pomocí jazyka DAX.
DATEKEY V POWER QUERY (Jednoduchá metoda)
Pro tuto metodu budeme potřebovat získat celkem 3 dotazy. Vše by šlo udělat dotazem jedním ale pro přehlednost to rozděluji na separátní. Kdy 2 dotazy slouží jen pro získání nejvyššího a nejnižšího datumu v datech. V této ukázce jde o obchodní data, která jsou ukládána do tabulky Sales a datum transakce je držen ve sloupci Date. Pojďme tedy na to!
Přejděte do Power Query volbou Transform data (Transformovat data) na záložce Home (Domů).
Nyní budeme potřebovat vytvořit tři samostatné dotazy. První dva nám vrátí nejnižší a nejvyšší datum, které určí rozsah samotné DateKey.
Na horním panelu na záložce Home (Domů) zvolte New Source (Nový datový zdroj) a následně Blank Query (Prázdný dotaz):
Do pole dotazu vložte (a upravte dle svých dat) následující:
= List.Min(Tabulka[Datumový sloupec])
Celý dotaz zkopírujte a upravte z “Min” na “Max”:
= List.Max(Tabulka[Datumový sloupec])
Tím jsme získaly první dva dotazy, které pro získání DateKey potřebujete.
Dále si vytvořené dotazy přejmenujte na minDate a maxDate. Kdykoli se nyní změní minimální či maximální hodnota zdrojového datumového sloupce, změní se také dynamicky výstupní hodnota příslušného dotazu.
Alternativou k předchozím dvěma dotazům je vytvořit si dva samostatné parametry minDate a maxDate, jejichž hodnotu definujete ručně. Minimální a maximální datum si tak určíte zcela libovolně, bez ohledu na existující data. Jak tvoříme parametry se můžete dočíst v článku ZDE.
Vyrobíme si třetí dotaz, který vyrobíme opět jako prázdný. Z něj již vytvoříme samotnou DateKey za použití obou předchozích dotazů (popř. parametrů) minDate a maxDate. Do pole dotazu vložte následující:
= {Number.From(minDate)..Number.From(maxDate)}
Power Query náš dotaz přeloží a vyrobí v číselné formě seznam datumů od minimálního po maximální. Abychom mohli změnit datový typ na datum, musíme nejprve převést seznam na tabulku. To provedeme kliknutím na To Table (Na tabulku) v levém horním rohu. (Pokud bychom chtěli rovnou vyrobit seznam datumů, tak bychom mohli použít funkci List.Dates(), tuto funkci záměrně ale nyní neukazujeme, protože by mohla být příliš matoucí.)
Okno, které se Vám následně zobrazí stačí potvrdit volbou Ok.
Nyní již standardním způsobem změníme datový typ sloupce na datum.
Abychom v reportech mohli pracovat s časovou hierarchií, rozšíříme tabulku o sloupce Year, Quarter, Month, Week atp. To provedeme následujícím způsobem:
Označíme nově vytvořený datumový sloupec, přejmenujeme ho na “Date” a na kartě Add Column (Nový sloupec) zvolíme Date (Datum). Následně z rozbalené nabídky zvolíme Year (Rok). Postup opakujeme pro každý nový sloupec časové hierarchie, který v rámci naší DateKey plánujeme používat.
Výsledná tabulka může vypadat například takto:
Nyní již máme DateKey kompletně vytvořenou. Před tím, než opustíme Power Query, doporučujeme ještě ulehčit modelu tím, že vypneme načítání veškerých dotazů, které v modelu neplánujeme dále používat. V tomto případě se jedná o dotazy minDate a maxDate. Vypneme je přes pravé tlačítko myši odškrtnutím volby Enable Load (Povolit načítání).
Nyní můžete opustit Power Query a v sekci Model vytvořit relaci přes datumový sloupec s příslušnou tabulkou (popř. tabulkami).
DATEKEY v Power Query (PŘES VLASTNÍ FUNKCI)
Využít custom function (vlastní funkci) znamená, že využijete buďto svůj kód zabalený jako funkci nebo kód někoho jiného, kdo již za vás celý algoritmus připravil.
Jednu takovou funkci pro vytvoření DateKey máme volně k dispozici na našem GitHub. Můžete si ji tak jednoduše zkopírovat, libovolně upravit a používat pro své vlastní potřeby. Zkopírovanou funkci stačí pouze vložit do nově vytvořeného dotazu v Power Query přes volbu Advanced Editor (Rozšířený editor).
Jakmile potvrdíte Advanced Editor (Rozšířený editor) volbou Done (Hotovo), zbývá doplnit parametry start_date, end_date a přes volbu Choose Column (Zvolit sloupec) zvolit sloupec z tabulky, která obsahuje obecné datumy svátků ve formátu 1.1., 1.5., 8.5,… Velikonoce nejsou obecným svátkem, protože je potřeba pro každý rok dopočítat, což můžeme například TOUTO funkcí. Stiskem tlačítka Invoke (Vyvolat) spustíte funkci, která vytvoří tabulku DateKey.
Jak si můžete všimnout, vytvořená tabulka obsahuje informaci o tom, zda je dané datum víkendem či svátkem:
DATEKEY VYTVOŘENÁ POMOCÍ JAZYKA DAX
DateKey můžete vytvořit také přímo v modelu například pomocí DAX funkce CALENDAR.
CALENDAR(<start_date>, <end_date>)
Podobně jako při tvorbě DateKey přes Power Query, můžeme se i zde rozhodnout, zda bude tabulka dynamická a tedy vycházející z minimální a maximální hodnoty již existujícího datumového sloupce, anebo zda její rozsah určíme napevno (konkrétními datumy).
Na kartě Modeling (Modelování) zvolte New table (Nová tabulka).
V prvním (dynamickém) případě by funkce vypadala následovně:
= CALENDAR (MINX (Tabulka, [Datumový sloupec]), MAXX (Tabulka, [Datumový sloupec]))
a ve druhém takto:
= CALENDAR (DATE (2015, 1, 1), DATE (2022, 12, 31))
V článku článku o Time Intelligence jsme již zmínili, že pokud máte v Options (Možnosti) v sekci Data Load – Time intelligence (Načtení dat – Časové měřítko) povolenou funkci Auto date/time (Časové měřítko), automaticky se s každým datumovým sloupcem vytvoří také časová hierarchie Year, Quarter, Month, Day.
Pokud funkci Auto date/time (Časové měřítko) povolenou nemáte (což doporučujeme) a přesto byste chtěli používat časovou hierarchii, můžete funkci CALENDAR obalit např. funkcí ADDCOLUMNS. Ta Vám umožní nadefinovat si další sloupce DateKey tabulky:
DAX kód pak může vypadat následovně:
Na obrázku výše si můžete všimnout dvou parametrů. První z nich (cs-CZ) říká, že daný sloupec bude zobrazován v češtině. Stejným způsobem bychom definovali výstup v angličtině (en-US), němčině (de-DE) atp.
Parametr u DateofWeekNumber nám umožňuje výběr ze tří možných číslování dní v týdnu:
- Sunday (1) – Saturday (7)
- Monday (1) – Sunday (7)
- Monday (0) – Sunday (6)
Na levém panelu v sekci Data si můžeme prohlédnout výslednou DateKey tabulku:
