Trudne i czasochłonne zadania w zwykłym Excelu, z łatwością ( i znajomością formuł oczywiście 😉 ) można osiągnąć w Power Query. Zaprezentuję, jak łatwo można zbudować tabelę słownikową Kalendarz o określonej długości, przy pomocy Power Query i kilku niestandardowych formuł języka M. Kalendarz taki przyda się każdemu, kto tworzy tabelę w modelu danych Excel z użyciem Power Pivot lub w modelu danych aplikacji Power BI.

Krok 1. Formuła List.Dates

Tworzę nowe, puste zapytanie poprzez Dane > Nowe zapytanie > Z innych źródeł > Puste zapytanie.

 

Będę wprowadzać kolejne formuły języka M, które pozwolą mi na zbudowanie kalendarza. Dlatego poprzez zakładkę Widok uruchamiam Pasek formuły.

 

W pasku wpisuję formułę List.Dates (uwaga na wielkość znaków, język M jest językiem typu case sensitive). Następnie wciskam Enter, dzięki czemu na ekranie pojawia opis działania funkcji, przykład oraz miejsce na podanie jej kolejnych argumentów.

Jako start podaję początek roku 2017, Count  – ilość kolejnych wygenerowanych dat, w tym przypadku 100 oraz step czyli odstęp pomiędzy datami – 1 dzień.

 

Po wyborze przycisku Wywołaj została wygenerowana formuła oraz lista dat jak poniżej, na dole screenu wydać również, że liczba wygenerowanych elementów przez funkcję to 100.

 

Nie satysfakcjonuje mnie jednak ta ilość dat – chciałbym, by była to liczba dynamiczna, czyli od początku roku do dziś. W tym celu w formule zamieniam liczbę 100 na Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2017,1,1)). Widać również po zaakceptowaniu zmian w funkcji, że liczba wygenerowanych elementów się zmieniła na 300 (oczywiście liczba elementów będzie inna w Twoim pliku, zależna od tego kiedy wykonasz to ćwiczenie 😉 ).

 

Przekształcam utworzoną listę na tabelę Przekształć > Do tabeli.

 

Krok 2: Budowa dodatkowych kolumn

Dodaję kolumnę Indeksu, która będzie numerowała kolejne wiersze w tabeli Dodaj kolumnę > Dodaj kolumnę indeksu > Od 1. Plus zmieniam nazwę pierwszej kolumny na Data.

 

Następnie dodaję kolejne kolumny z kolejnymi wymiarami daty poprzez Dodaj kolumnę > Dodawanie kolumny niestandardowej. Zaczynam od roku jak na załączonym obrazku. Można je też wyklikać z interfejsu na karcie Dodaj kolumnę. Po prawej stronie widoczne są kolumny, które mogę wykorzystać w tworzeniu kolejnych. Zmiany w tworzonej tabeli wprowadzam przyciskiem OK. Tworzę kolejne kolumny jak poniżej:

Numer Miesiąca =Date.Month([Data])

Dzień =Date.Day([Data])

Nazwa dnia =Date.ToText([Data],”ddd”)

Nazwa miesiąca =Date.ToText([Data],”MMM”)

Nazwa kwartału =Date.QuarterOfYear([Data])

 

Poniżej widok z wszystkimi utworzonymi kolumnami.

 

Chciałbym stworzyć kolumnę z informacją o skróconym roku (2 ostatnie znaki) oraz o numerze kwartału tego roku. W tym celu duplikuję kolumnę Rok Prawy przycisk myszy > Duplikuj kolumnę.

 

Aby ze zduplikowanej kolumny wydzielić dwa ostatnie znaki, muszę zmienić jej typ na tekstowy prawy przycisk myszy > Zmień typ > Tekst.

 

Następnie by wydzielić znaki wybieram prawy przycisk myszy > podziel kolumny > według liczy znaków > 2 > Powtarzalne > OK.

 

Wyniku tej operacji dostaję dwie kolumny, usuwam tę z wartościami ‘20’ a dla drugiej zmieniam nazwę na Skrócony Rok. Następnie dodaję nową kolumnę poprzez Dodaj kolumnę > Dodawanie kolumny niestandardowej. Poniżej formuła oraz nazwa tworzonej kolumny, która będzie dawała informację o roku i jego kolejnym kwartale.

 

Krok 3. Uporządkowanie i zmiana typu danych

Następnie zmieniam kolejność kolumn oraz ich formaty, by uporządkować nieco dane. Niemal wszystkie kolumny zmieniam na format tekstowy (Przekształć > Dowolna kolumna > Typ danych > Tekst), nazwę dnia tygodnia zmieniam na dwuliterową poprzez wyodrębnienie znaków (Przekształć  > Kolumna tekstu > Wyodrębnij > Pierwsze znaki , w przypadku poniedziałku i niedzieli muszę ręcznie poprzez zamień wartości z menu podręcznego kolumny zamienić kodowanie).

Pobierz plik Excel i wstaw swoje dane

Tu możesz pobrać Kalendarz w Power Query i wstawić swoje dane.