Tworzenie tabeli "kalendarza" za pomocą funkcji w Power BI

utworzone przez Adam Kopeć | mar 28, 2017 | PowerBI | 3 Komentarze

Czasami przy pracy z danymi wczytywanymi do Power BI przydaje nam się tabela z rozpisanymi na różne sposób datami, gdzie oprócz samej daty mamy jeszcze np.: kolumny, z numerem roku, miesiąca, dnia, kwartału lub połączonych tych informacji. Żeby nie musieć tworzyć takiej tabeli ręcznie różni programiści stworzyli kod funkcji w języku M, żeby zautomatyzować to zadanie.

Ja korzystam z kodu Matta Massona, ze strony (dowiedziałem się o nim od Jona Acampora):
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Który odrobinę zmodyfikowałem (głównie zmieniłem nazwy kolumn), żeby bardziej odpowiadał polskim ustawieniom.

let CreateDateTable = (DataPoczątkowa as date, DataKońcowa as date, optional Język as nullable text) as table =>
  let
    DayCount = Duration.Days(Duration.From(DataKońcowa - DataPoczątkowa)),
    Source = List.Dates(DataPoczątkowa,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Data"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Rok", each Date.Year([Data])),
    InsertQuarter = Table.AddColumn(InsertYear, "Kwartał", each Date.QuarterOfYear([Data])),
    InsertMonth = Table.AddColumn(InsertQuarter, "Miesiąc", each Date.Month([Data])),
    InsertDay = Table.AddColumn(InsertMonth, "Dzień", each Date.Day([Data])),
    InsertDayInt = Table.AddColumn(InsertDay, "DataLiczba", each [Rok] * 10000 + [Miesiąc] * 100 + [Dzień]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "NazwaMiesiąca", each Date.ToText([Data], "MMMM", Język), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MiesiącKalendarz", each (try(Text.Range([NazwaMiesiąca],0,3)) otherwise [NazwaMiesiąca]) & " " & Number.ToText([Rok])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "KwartałKalendarz", each "K" & Number.ToText([Kwartał]) & " " & Number.ToText([Rok])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "NrDniaTygodnia", each Date.DayOfWeek([Data],Day.Monday)+1),
    InsertDayName = Table.AddColumn(InsertDayWeek, "NazwaDniaTygodnia", each Date.ToText([Data], "dddd", Język), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "KoniecTygodnia", each Date.EndOfWeek([Data]), type date)
  in
    InsertWeekEnding
in
  CreateDateTable

Jak skorzystać z kodu funkcji w PowerBI?

Żeby skorzystać z tej funkcji w PowerBI musisz stworzyć najpierw puste zapytanie (karta Narzędzia główne -> rozwiń polecenie Pobierz dane -> wybierz z listy Puste zapytanie)

Następnie w edytorze zapytań wybierz polecenie Edytor zaawansowany z karty Narzędzia główne.

W oknie edytora zaawansowanego wklejasz skopiowany z początku wpisu kod, a następnie zatwierdzasz go przyciskiem Gotowe.

Teraz przy aktywnym tym zapytaniu zobaczysz okno wprowadzania parametrów funkcji.

• DataPoczątkowa – to data, od której będzie zaczynał się stworzony kalendarz
• DataKońcowa – to data, na której będzie kończył się stworzony kalendarz
• Język – parametr opcjonalny, który decyduje w jakim języku są prezentowane dane np.: nazwy miesiąca, czy dni tygodnia. Jeśli nie wypełnisz tego pola, to będą brane pod uwagę ustawienia aplikacji PowerBI. Możesz zobaczyć szczegóły tego parametru na stronie:
  [MS-LCID]: Windows Language Code Identifier (LCID) Reference

Wystarczy, że wypełnisz to okno parametrami zgodnie ze swoimi potrzebami, a następnie zatwierdzisz wybór przyciskiem Wywołaj.

Żeby edytor zapytań stworzył dla Ciebie całą tabelę danych. Pamiętaj jeszcze załadować ją do aplikacji PowerBI Desktop (polecenie Zamknij i zastosuj na karcie Narzędzia główne).

Chcesz lepiej poznać PowerBI wejdź na stronę mojego kursu:
https://exceliadam.pl/kursy/power-bi

Pozdrawiam
Adam Kopeć
Miłośnik Excela