Pri funkciách Time Intelligence v jazyku DAX potrebujete vždy použiť časovú tabuľku, aby fungovali správne. Tú viete zohnať z dátového skladu, či si ju vyrobiť. Dá sa vyrobiť v Power Query aj v PowerPivote, ale v Power Query je to má jednu obrovskú výhodu – viete ju ľahko a opakovane použiť v ďalších reportoch. A preto sa teraz pozrieme, ako sa takáto ultimátna časová tabuľka dá spraviť.

Časová tabuľka je nevyhnutná súčasť každého dátového modelu. Takmer každý jeden človek však zápasí s tým, kde ju zohnať, resp. ako ju vyrobiť. A keď ju už vie vyrobiť, tak sa pýta, či je lepšie spraviť ju v Exceli, v Power Query či v PowerPivote. A keďže sa ma na to pýta snáď každý jeden človek, tak som pre vás vyrobil hotové riešenie pre Power Query, ktoré som nazval “Ultimátna časová tabuľka”. Ktorú za pár sekúnd viete použiť aj vy.

Aktualizácia 13.10.2021: Najnovšiu verziu ultimátnej časovej tabuľky nájdete na tomto odkaze.

Časovú tabuľku viete vytvoriť aj v PowerPivote, napr. cez funkcie CALENDAR či GENERATESERIES, tak ako som to písal v článku o sekvenčných tabuľkách. Má to však tú nevýhodu, že keď v nej máte desiatky stĺpcov (a kto nemá, že?), tak v ďalšom dátovom modeli si tú šarádu musíte zopakovať znova, a prekopírovať vzorce pre všetky stĺpce jeden po druhom. A to sa takmer nikomu nechce.

Druhé riešenie je vytvoriť časovú tabuľku v Power Query. A začať tak, ako som tiež písal v článku o sekvenčných tabuľkách – vytvorením dátumového stĺpca so postupnosťou dátumov, a dopočítaním všetkých odvodených stĺpcov. Prvý krok je relatívne ťažký pre úplných začiatočníkov, ale ostatné sú jednoduché. Prečo to však robiť dokola vo viacerých reportoch, keď si môžete prekopírovať celý M skript za celú tabuľku naraz do iného reportu?

A práve preto je tu táto ultimátna časová tabuľka pre Power Query. Je to takýto chrumkavý skript v jazyku M (dá sa stiahnuť aj ako textový súbor z tohto odkazu):

// ultimátna časová tabuľka, stiahnutá z webu www.powerpivot.sk

let
    pociatocnyRok = 2010,
    koncovyRok = 2018,
    pociatocnyDatum = #date(pociatocnyRok, 1, 1),
    koncovyDatum = #date(koncovyRok, 12, 31), 
    trvanie = Duration.Days(Duration.From(koncovyDatum - pociatocnyDatum)) + 1,
    Zdroj = List.Dates(pociatocnyDatum, trvanie, #duration(1, 0, 0, 0)),
    #"Konvertované na tabuľku" = Table.FromList(Zdroj, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Premenované stĺpce" = Table.RenameColumns(#"Konvertované na tabuľku",{{"Column1", "Dátum"}}),
    #"Zmenený typ" = Table.TransformColumnTypes(#"Premenované stĺpce",{{"Dátum", type date}}),
    #"Vložený rok" = Table.AddColumn(#"Zmenený typ", "Rok", each Date.Year([Dátum]), Int64.Type),
    #"Vložený štvrťrok" = Table.AddColumn(#"Vložený rok", "Štvrťrok", each Date.QuarterOfYear([Dátum]), Int64.Type),
    #"Vložený mesiac" = Table.AddColumn(#"Vložený štvrťrok", "Mesiac", each Date.Month([Dátum]), Int64.Type),
    #"Vložený deň" = Table.AddColumn(#"Vložený mesiac", "Deň", each Date.Day([Dátum]), Int64.Type),
    #"Vložený týždeň v roku" = Table.AddColumn(#"Vložený deň", "Týždeň v roku", each Date.WeekOfYear([Dátum]), Int64.Type),
    #"Premenované stĺpce1" = Table.RenameColumns(#"Vložený týždeň v roku",{{"Týždeň v roku", "Týždeň"}}),
    #"Vložený začiatok roka" = Table.AddColumn(#"Premenované stĺpce1", "Začiatok roka", each Date.StartOfYear([Dátum]), type date),
    #"Vložený koniec roka" = Table.AddColumn(#"Vložený začiatok roka", "Koniec roka", each Date.EndOfYear([Dátum]), type date),
    #"Vložený začiatok štvrťroka" = Table.AddColumn(#"Vložený koniec roka", "Začiatok štvrťroka", each Date.StartOfQuarter([Dátum]), type date),
    #"Vložený koniec štvrťroka" = Table.AddColumn(#"Vložený začiatok štvrťroka", "Koniec štvrťroka", each Date.EndOfQuarter([Dátum]), type date),
    #"Vložený začiatok mesiaca" = Table.AddColumn(#"Vložený koniec štvrťroka", "Začiatok mesiaca", each Date.StartOfMonth([Dátum]), type date),
    #"Vložený koniec mesiaca" = Table.AddColumn(#"Vložený začiatok mesiaca", "Koniec mesiaca", each Date.EndOfMonth([Dátum]), type date),
    #"Vložený názov mesiaca" = Table.AddColumn(#"Vložený koniec mesiaca", "Názov mesiaca", each Date.MonthName([Dátum], "sk-SK"), type text),
    #"Vložené dni v mesiaci" = Table.AddColumn(#"Vložený názov mesiaca", "Dni v mesiaci", each Date.DaysInMonth([Dátum]), Int64.Type),
    #"Vložený začiatok týždňa" = Table.AddColumn(#"Vložené dni v mesiaci", "Začiatok týždňa", each Date.StartOfWeek([Dátum]), type date),
    #"Vložený koniec týždňa" = Table.AddColumn(#"Vložený začiatok týždňa", "Koniec týždňa", each Date.EndOfWeek([Dátum]), type date),
    #"Vložený týždeň v mesiaci" = Table.AddColumn(#"Vložený koniec týždňa", "Týždeň v mesiaci", each Date.WeekOfMonth([Dátum]), Int64.Type),
    #"Vložený deň v týždni" = Table.AddColumn(#"Vložený týždeň v mesiaci", "Deň v týždni", each Date.DayOfWeek([Dátum], Day.Monday) + 1),
    #"Vložený deň v roku" = Table.AddColumn(#"Vložený deň v týždni", "Deň v roku", each Date.DayOfYear([Dátum]), Int64.Type),
    #"Vložený názov dňa" = Table.AddColumn(#"Vložený deň v roku", "Názov dňa", each Date.DayOfWeekName([Dátum], "sk-SK"), type text),
    #"Pridané vlastné" = Table.AddColumn(#"Vložený názov dňa", "Prestupný rok", each Date.IsLeapYear([Dátum])),
    #"Zmenený typ1" = Table.TransformColumnTypes(#"Pridané vlastné",{{"Prestupný rok", type logical}, {"Deň v týždni", Int64.Type}}),
    #"Stĺpce so zmeneným poradím" = Table.ReorderColumns(#"Zmenený typ1",{"Dátum", "Rok", "Štvrťrok", "Mesiac", "Názov mesiaca", "Deň", "Deň v týždni", "Názov dňa", "Týždeň", "Začiatok roka", "Koniec roka", "Začiatok štvrťroka", "Koniec štvrťroka", "Začiatok mesiaca", "Koniec mesiaca", "Dni v mesiaci", "Začiatok týždňa", "Koniec týždňa", "Týždeň v mesiaci", "Deň v roku"})
in
    #"Stĺpce so zmeneným poradím"

Tento skript si stačí okopírovať do svojho reportu a bum – za 5 sekúnd máte celú prácu hotovú.

Tento skript generuje časovú tabuľku pre komplet všetky dni v rokoch 2010 – 2018. Ak chcete iný rozsah rokov, tak na začiatku skriptu v prvých riadkoch zmeňte čísla rokov, a máte to vybavené.

A ako to použiť?

Otvorte si nový dotaz v Power Query typu “Prázdny dotaz”, resp. dajte si načítať dáta z dátového zdroja typu “Prázdny dotaz”. Keď sa vám otvorí Power Query, tak v ňom kliknite v hlavnom menu na tlačítko “Rozšírený editor”:

Následne sa otvorí takéto okno:

V ňom zmažte všetok text, namiesto neho tam prilepte hore uvedený skript (a prípadne si na jeho začiatku zmeňte čísla rokov), a stlačte tlačítko Hotovo:

V Power Query sa vytvorí nový dotaz z tohto M skriptu, ktorý vám vygeneruje takúto tabuľku:

Čo je naša ultimátna časová tabuľka. Na obrázku síce vidíte len zopár stĺpcov, ale sú tam všetky stĺpce ako Rok, Mesiac, Deň, Týždeň, či začiatky a konce období. Momentálne je tam 21 stĺpcov.

Dotaz/tabuľku si potom premenujte, napr. na názov Čas.

Potom dáte vľavo hore “Zavrieť a načítať”, resp. “Zavrieť a použiť”, a máte to hotové. A to je všetko. Úplne všetko. Takto rýchlo. Svokra by bola prekvapená, ako ste to zvládla.

A keď teraz chcete mať rovnakú časovú tabuľku v inom reporte, tak si tam rovnakým postupom skopírujte tento skript. Rýchle, ľahké a elegantné. Teda až na ten skript, ale ak pracujete v jazyku M, tak je to jednoduchá vec.

Ak by vám ten skript nefungoval, tak si ho skúste stiahnuť ako textový súbor z tohto odkazu.

Takže takto sa dnes robia veci – rýchlo a ľahko, ak už viete ako na to. Šírte toto slovo, a užívajte si svoju novú ultimátnu časovú tabuľku. Power Query zdar! 🙂

Michal Chmelár

Autor, tréner a expert na PowerPivot, Power BI a jazyk DAX. Založil som tento web, aby som pomohol dostať PowerPivot a Power BI do širšieho povedomia, a aby som ľuďom ukázal, že aj komplexné analytické problémy idú riešiť jednoducho. Po nociach vzývam Majstra Yodu a tajne plánujem ovládnutie vesmíru.