Ultimátna časová tabuľka v Power Query

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.

Č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]), 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]), 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! 🙂

4 komentárov k “Ultimátna časová tabuľka v Power Query

  • 20. novembra 2018 at 13:13
    Permalink

    ja som nasiel nieco na nete a pouzivam aj pre iny financny rok

    let
    Source = List.Dates,
    #”Invoked FunctionSource” = Source(#date(2014, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2014,1,1))+730, #duration(1, 0, 0, 0)),
    #”Converted to Table” = Table.FromList(#”Invoked FunctionSource”, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Added Index” = Table.AddIndexColumn(#”Converted to Table”, “Index”, 1, 1),
    #”Renamed Columns” = Table.RenameColumns(#”Added Index”,{{“Column1”, “Date”}}),
    #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}}),
    #”Added Custom” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date])),
    #”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Month Number”, each Date.Month([Date])),
    #”Added Custom2″ = Table.AddColumn(#”Added Custom1″, “Day”, each Date.Day([Date])),
    #”Added Custom3″ = Table.AddColumn(#”Added Custom2″, “Day Name”, each Date.ToText([Date],”ddd”)),
    #”Added Custom4″ = Table.AddColumn(#”Added Custom3″, “Month Name”, each (Date.ToText([Date],”MMMM”))),
    #”Added Custom5″ = Table.AddColumn(#”Added Custom4″, “Quarter Number”, each Date.QuarterOfYear([Date])),
    #”Added Custom6″ = Table.AddColumn(#”Added Custom5″, “FY Year”, each if[Month Number] <=3 then [Year]-1 else [Year]),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "FY Month Number", each if [Month Number] <=3 then [Month Number]+9 else
    [Month Number]-3),
    #"Added Custom8" = Table.AddColumn(#"Added Custom7", "FY Quarter Number", each if [Month Number] <=3 then [Quarter Number]+3 else
    [Quarter Number]-1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom8",{{"FY Year", Int64.Type}})
    in
    #"Changed Type1"

    Odpovedať
    • 20. novembra 2018 at 18:54
      Permalink

      Ani to nie je zlé. Len to má drobnú chybičku krásy s tým pripočítavaním 730 dní (2 roky) na začiatku. V prestupných rokoch to asi nebude fungovať úplne správne…

      Ja som tiež chcel dať aj fiškálnu verziu stĺpcov do tej tabuľky, ale nakoniec som od toho upustil, pretože každá firma to má inak, a veľakrát fiškálny rok nezačína ani na začiatku niektorého z mesiacov, ale na niektorom z dátumov (napr. 19.5.). Tak kto chce, ten si to tam už dorobí podľa seba 🙂 Ale ešte porozmýšľam nad aktualizáciou, a možno raz bude verzia 2.0, keď vymyslím nejaký nekomplikovaný spôsob, ako to pôjde spraviť cez konfiguračné premenné na začiatku, ako to je teraz s tými rokmi. Každopádne vďaka za tip 😉

      Odpovedať
  • 4. marca 2019 at 15:16
    Permalink

    Jak se dá deklarovat, aby se data načetla odlišně od SK názvů dnů a měsíců, tedy např. CS (Január -> Leden) případně Január -> January atd.? Ať to nemusím prohnat vazbou přes pomocné konverzní tabulky?
    Děkuji.

    Odpovedať
    • 7. marca 2019 at 20:05
      Permalink

      Nájdite si v skripte riadok, kde je použitá funkcia Date.MonthName([Dátum]). Do tej funkcie potom pridajte druhý parameter s jazykom, v ktorom chcete mať tie mesiace. Čiže pre češtinu to bude “cs-CZ”. Vo výsledku bude teda funkcia zapísaná takto:

      Date.MonthName([Dátum], “cs-CZ”)

      Odpovedať

Pridaj komentár

Vaša e-mailová adresa nebude zverejnená.