W tym odcinku zobaczysz jak stworzyć prosty kalendarz z dowolnym wyborem daty początkowej i końcowej. Bezproblemowo wyodrębnisz z daty: numer dnia tygodnia, nazwę dnia, numer miesiąca, kwartał roku oraz rok.
Na poniższym obrazku w zielonej tabelce został stworzony docelowy kalendarz na podstawie zakresu dat, który jest wskazany w niebieskiej tabeli.
Wczytywanie tabeli z datami do edytora Power Query
Stwórz w Excelu tabelkę z datami, które będzie zawierać datę początkową i końcową. Następnie wczytaj ją do edytora Power Query.
- Zaznacz tabelę z datami
- Przejdź do zakładki „Dane”
- Opcja „Z tabeli”
- zaznacz „Moja tabela ma nagłówki”
- kliknij „Ok”
Zmiana typu danych
Teraz zmienimy typ danych w kolumnach [data początkowa] i [data końcowa] na liczbę całkowitą. Każda data w Power Query ma swój unikatowy numer seryjny. Przykładowo nr 44562 oznacza datę 01.01.2022. Aby stworzyć zakres dat dla wyznaczonego przedziału, będziemy tą liczbę zwiększać o jeden, aż do momentu uzyskania liczby 44926, która oznacza naszą datę końcową.
- Zaznacz kolumny: [data początkowa] i [data końcowa]
- Kliknij PPM na dowolną zaznaczoną kolumnę
- Wybierz opcję „Zmień typ”
- „Liczba całkowita”
Tworzenie listy dat ze wskazanego zakresu
Przekształciliśmy nasze daty do formatu liczbowego. Teraz możemy stworzyć listę dat podając datę początkową i końcową w odpowiednim formacie. W ten sposób zostaną wygenerowane wszystkie oczekiwane daty.
- Dodaj kolumnę niestandardową
- Przejdź do zakładki „Dodaj kolumnę”
- Wybierz polecenie „Kolumna niestandardowa”
- pojawi się okno z ustawieniami kolumny niestandardowej
- W polu nazwa nowej kolumny wpisz: „Data”
- W pasku formuły wpisz:
{[data początkowa]..[data końcowa]}Powyższa funkcja stworzy listę dat z podanego zakresu w formacie danych typy List. W tym formacie każda data przechowywane jest w osobnej komórce. W następnym etapie w łatwy sposób wyodrębnimy je do nowych wierszy.
Powstała nowa kolumna [data], w której wygenerowane daty są w formacie typu List. Na dole ekranu widać je w formacie seryjnym
Rozwijanie wygenerowanych dat do nowych wierszy
Teraz te docelowe daty wyodrębnimy do nowych wierszy.
- Kliknij w ikonę strzałek obok kolumny [Data]
- Wybierz opcję „Rozwiń do nowych wierszy”
Zmiana typu danych
Przekonwertujemy seryjny numer na datę w formacie: dd-mm-rrrr
- Zaznacz kolumny: [data początkowa], [data końcowa], [data]
- Kliknij PPM w dowolną zaznaczoną kolumnę
- Wybierz opcję: „Zmień typ”
- „Data”
Wyodrębnianie roku
- Zaznacz kolumnę [data]
- Przejdź do zakładki „Dodaj kolumnę”
- Wybierz opcję „Data”
- Następnie „Rok”
- „Rok”
Wyodrębnianie kwartału
- Zaznacz kolumnę [Data]
- Przejdź do zakładki „Dodaj kolumnę”
- Wybierz opcję „Data”
- Następnie „Kwartał”
- „Kwartał Roku”
Wyodrębnianie numeru miesiąca
- Zaznacz kolumnę [Data]
- Przejdź do zakładki „Dodaj kolumnę”
- Wybierz opcję „Data”
- Następnie „Miesiąc”
- „Miesiąc”
Wyodrębnianie numeru dnia miesiąca
- Zaznacz kolumnę [Data]
- Przejdź do zakładki „Dodaj kolumnę”
- Wybierz opcję „Data”
- Następnie „Dzień”
- „Nazwa dnia”
Wyodrębnianie nazwy dnia
- Zaznacz kolumnę [Data]
- Przejdź do zakładki „Dodaj kolumnę”
- Wybierz opcję „Data”
- Następnie „Dzień”
- „Nazwa dnia”
Wyodrębnianie numeru dnia tygodnia
- Zaznacz kolumnę [Data]
- Przejdź do zakładki „Dodaj kolumnę”
- Wybierz opcję „Data”
- Następnie „Dzień”
- „Dzień tygodnia”
Zmiana numeracji dni tygodnia
W obecnej numeracji dni tygodnia, poniedziałek zaczyna się od 0. Zmienimy numerację w taki sposób, aby ten dzień zaczynał się od 1.
- Na liście zastosowane kroki kliknij w krok „Wstawiono dzień tygodnia”
- W pasku formuły zmodyfikuj tą części formuły:
Date.DayOfWeek([data]), Int64.Typena
Date.DayOfWeek([data]) +1, Int64.Typeza kolumną [data] została wpisana wartość +1, która zwiększy każdy numer dnia tygodnia o 1.
Stworzyliśmy nasz kalendarz. Finalnie wygląda tak:
Załadowanie zapytania do nowego arkusza
Załadujemy kalendarz do nowego arkusza w Excelu.
- Przejdź do zakładki „Narzędzia Główne”
- Wybierz opcję „Zamknij i załaduj”
Kalendarz został załadowany do nowego arkusza