Power Query dátumová tabuľka pre Power BI

Nižšie je Power Query skript na vytvorenie dynamickej dátumovej tabuľky v Power BI. Tento skript vám vygeneruje dátumovú tabuľku s rôznymi typmi dátumových stĺpcov cez dátum, rok, mesiac, deň, týždeň, aj relatívnymi poľami pre rozšírené možnosti výpočtov. 

Skript Dátumovej Tabuľky

let
Source = "Power Query Date Table Created by BlueNumbers.com" ,
#"** Note - DateTable by BlueNumbers.com" = Source,
#"** Note - Dates configuration" = "Dates configuration",
#"** SetUp Guide: StartDate and Length" = "SET-UP GUIDE: STARTDATE AND LENGTH. StartDate is the first date of your table. Change it manually. Length is the number of days in your table. The recommendation is to use the dynamic function Today to create a flexible End Date, and then add the number of days you want to include in the future (For example Today+1 to include just today, and Today+1+365 to include dates even for next year). US AND ISO WEEKS. The difference between US Weeks and ISO Weeks is that US Weeks start on Sunday, while ISO Weeks start on Monday. You may delete the steps for creating the weeks (US/ISO) that you do not want. RELATIVE DAYS, WEEKS, MONTHS AND YEARS. Relative Dates are calculated as the difference between the Date in the Date Table and Today. For example, the Relative Day for Today will be 0, for yesterday -1, for tomorrow +1, and for the day after tomorrow +2. The calculation is similar for Relative Years, Months, US Weeks, and ISO Weeks.",
StartDate = #date(2024, 1, 1),
Today = DateTime.Date(DateTime.LocalNow()),
#"Length of Calendar in Days" = Duration.Days(Today - StartDate)+1+365 /* Note: Use Today+1 to always have the EndDate for today, and use Today+1+365 to include dates for even for next year. */ + 0,
#"** Note - Creating Date Table" = "Script for creating Date Table with Base Date Column",
#"Create Date List" = List.Dates(StartDate, #"Length of Calendar in Days", #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(#"Create Date List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Rename Column Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type Date" = Table.TransformColumnTypes(#"Rename Column Date",{{"Date", type date}}),
#"** Note - Adding New Columns" = #"Changed Type Date",
#"Add Year Column" = Table.AddColumn(#"** Note - Adding New Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Add Month Column" = Table.AddColumn(#"Add Year Column", "Month", each Date.Month([Date]), Int64.Type),
#"Add Day Column" = Table.AddColumn(#"Add Month Column", "Day", each Date.Day([Date]), Int64.Type),
#"Add DateNumeric Column" = Table.AddColumn(#"Add Day Column", "DateNumeric", each ([Year]*10000) + ([Month]*100) + [Day], Int64.Type),
#"Add YearMonth Column" = Table.AddColumn(#"Add DateNumeric Column", "YearMonth", each [Year]*100+[Month], Int64.Type),
#"Add Day Name" = Table.AddColumn(#"Add YearMonth Column", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Add US Day of Week" = Table.AddColumn(#"Add Day Name", "US Day in Week", each Date.DayOfWeek([Date], Day.Sunday)+1, Int64.Type),
#"Add ISO Day of Week" = Table.AddColumn(#"Add US Day of Week", "ISO Day in Week", each Date.DayOfWeek([Date], Day.Monday)+1, Int64.Type),
#"Add US Week of Year" = Table.AddColumn(#"Add ISO Day of Week", "US Week of Year", each Date.WeekOfYear([Date], Day.Sunday), Int64.Type),
#"Add ISO Week of Year" = Table.AddColumn(#"Add US Week of Year", "ISO Week of Year", each Date.WeekOfYear([Date], Day.Monday), Int64.Type),
#"Add Month Name" = Table.AddColumn(#"Add ISO Week of Year", "Month Name", each Date.MonthName([Date]), type text),
#"Add Relative Year" = Table.AddColumn(#"Add Month Name", "Relative Year", each [Year] - Date.Year(DateTime.LocalNow()), Int64.Type),
#"Add Relative Month" = Table.AddColumn(#"Add Relative Year", "Relative Month", each [Month] + 12*[Year] - Date.Month(DateTime.LocalNow()) - 12* Date.Year(DateTime.LocalNow()),Int64.Type),
#"Add Relative Day" = Table.AddColumn(#"Add Relative Month", "Relative Day", each Number.From([Date] - DateTime.Date(DateTime.LocalNow())), Int64.Type),
#"Add Relative US Week" = Table.AddColumn(#"Add Relative Day", "Relative US Week", each Number.RoundDown(Number.RoundDown(([Relative Day]-[US Day in Week]+7)/7)), Int64.Type),
#"Add Relative ISO Week" = Table.AddColumn(#"Add Relative US Week", "Relative ISO Week", each Number.RoundDown(([Relative Day]-[ISO Day in Week]+7)/7), Int64.Type),
Result = #"Add Relative ISO Week"
in
Result


Nastavenie Dátumovej Tabuľky

Skopírujte Skript do Power Query Editora
- Skopírujte skript priamo do Power Query Editora (do prázdneho dotazu - Blank Query). Odporúča sa nazvať dotaz "Date Table" alebo "Date".

Nastavte Parameter StartDate
- Pridajte váš začiatočný dátum tabuľky do parametra StartDate. Tento dátum označuje začiatok vašej dátumovej tabuľky.

Zachovajte Nezmenený Parameter Today
- Uistite sa, že parameter Today zostane nezmenený. Tento parameter dynamicky vypočíta Koncový Dátum na základe aktuálneho dátumu.

Nastavte Parameter Length
- Prispôsobte parameter Length na určenie trvania vašej dátumovej tabuľky:
- Použite +1 na dynamické nastavenie dnešného dátumu ako Koncový Dátum
- Použite +1 + 365 na rozšírenie rozsahu dátumov o 365 dní (1 rok) do budúcnosti.

Zvoľte si medzi ISO a US Týždňami
- US Týždne: Začínajú v nedeľu (Americký štandard)
- ISO Týždne: Začínajú v pondelok. Je to aj Slovenský, aj Európsky štandard.
- Podľa vašich preferencií odstráňte kroky na vytvorenie týždňov (US/ISO), ktoré nechcete vo vašej dátumovej tabuľke.

Použitie Relatívnych Dní, Týždňov, Mesiacov a Rokov
- Ak potrebujete špeciálne filtre dátumov, môžete použiť relatívne dátumy. Záporné čísla v relatívnych dátumoch označujú historické obdobie, 0 označuje aktuálne obdobie a kladné čísla označujú budúce obdobie.

Výpočty relatívnych dátumov sú nasledovné:
- Relative Year (Relatívny Rok): Rok z poľa Date - Aktuálny Rok
- Relative Month (Relatívny Mesiac): Mesiac z poľa Date - Aktuálny Mesiac
- Relative Day (Relatívny Deň): DPole Date - Aktuálny Dátum
- Relatívny US/ISO Týždeň: Týždeň z poľa Date - Aktuálny Týždeň

Príklad: Ak potrebujete vytvoriť filter pre posledné 3 dni, dnes a zajtra, vyfiltrujete tieto hodnoty v poli Relative Day (Relatívny deň)
0 pre dnešok
+1 pre zajtra
-1, -2 a -3 pre posledné 3 historické dni

Skript je voľne na použitie
Poskytnutý skript môžete voľne použiť a upraviť pre vlastné potreby. Skript môžete použiť aj pre komerčné použitie.

Skript je skopírovaný zo stránky BlueNumbers.com. Tím Expert Power BI sa podieľa aj na tvorbe stránok BlueNumbers.com a BlueNumbers.sk

Zdroj: http://bluenumbers.com/power-query-date-table-script-for-power-bi.html