W dzisiejszym poście nauczymy się wyznaczać ilość dni w miesiącu pomiędzy datami w Power Query. Odcinek ten powstał jako odpowiedź na komentarz Billa Szyszkowskiego do postu z pytaniami od widzów nr 121, poruszającego ten sam problem. Wyznaczymy ilość dni pomiędzy datami w danym miesiącu ale tym razem za pomocą Power Query. Musimy pamiętać, że kiedy w Excelu mamy daty to tak naprawdę mamy do czynienia z liczbami, bo daty w Excelu to liczby. Bill Szysz zwrócił uwagę, że czasami nie uwzględniamy tego pierwszego dnia, np. między datami 2018-01-02 a 2018-01-04 są 2 dni różnicy, ale jeśli policzymy to dokładnie to mamy 2018-01-02, 2018-01-03 i 2018-01-04, a więc 3 dni. W zależności czy liczymy ten pierwszy dzień czy nie to otrzymujemy różne wyniki. Bill Szysz zaproponował rozwiązanie tego problemu w Power Query. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.
Standardowo mamy dane umieszczone w tabeli i w Excelu od 2016 wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane, aby zaczytać dane do Power Query.
Otworzy nam się okno Edytora zapytań dla danych zaczytanych z tabeli tDaty. Musimy pamiętać, że Power Query chce zapisać dane jako daty, a my chcemy skorzystać z liczb. Klikamy ikonkę (oznaczoną zieloną strzałką na rysunku nr 3) przy nazwie kolumny Od i z podręcznego menu wybieramy polecenie Liczba całkowita.
Pojawi się nam komunikat o zmianie typu kolumny, gdzie musimy kliknąć przycisk Zamień bieżącą (rys. nr 4).
Tę samą czynność powtarzamy dla kolumny obok nazwanej Do. Klikamy ikonkę przy jej nazwie i z podręcznego menu wybieramy polecenie Liczba całkowita. Pojawi nam się komunikat o zmianie typu kolumny, gdzie klikamy przycisk Zamień bieżącą. Otrzymamy dane przedstawione na rysunku nr 5.
W kolejnym kroku wybieramy polecenie Kolumna niestandardowa (punkt nr 2 na rysunku nr 6) z karty Dodaj kolumnę.
Pojawi nam się okno Kolumna niestandardowa, w której zmienimy nazwę kolumny na Miesiąc, a następnie w okienku formuły kolumny niestandardowej wpiszemy ={[od]..[do]}. Następnie nasze ustawienia zatwierdzamy klikając przycisk OK (rys. nr 7).
Musimy tutaj pamiętać , że pod wartościami [od] i [do] kryją się liczby. Otrzymamy dane przedstawione na rysunku nr 8, gdzie dla każdego wiersza w kolumnie Miesiąc mamy listę liczb między tymi datami – zapisanymi też jako liczby.
Dla pierwszego wiersza lista ta została przedstawiona na rysunku nr 9.
W kolejnym kroku zaznaczamy kolumnę Miesiąc, klikamy prawym przyciskiem myszy na nazwę tej kolumny i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 11).
Zostanie nam jedna kolumna. Następnie klikamy ikonkę oznaczoną na rysunku nr 12 zieloną strzałką i z podręcznego menu wybieramy polecenie Rozwiń do nowych wierszy.
Otrzymamy listę wszystkich dni pomiędzy datami (rys. nr 13).
Nas interesuje konkretnie ilość dni w danym miesiącu, więc w pierwszym kroku musimy znowu zmienić typ danych na daty. Rozwijamy ikonkę przy nazwie kolumny i z podręcznego menu wybieramy polecenie Data (rys. nr 14).
Otrzymamy listę dni w postaci dat, przedstawioną na rysunku nr 15 (jej fragment).
Przy tak przygotowanych danych wybieramy polecenie Data (punkt nr 2 na rysunku nr 16) z karty Dodaj kolumnę.
Za pomocą tego polecenia możemy z daty wyciągać różne parametry, takie jak rok, kwartał, miesiąc itd. My wybieramy Rok (rys. nr 17), następnie analogicznie wybieramy Miesiąc.
Otrzymamy dane przedstawione na rysunku nr 18.
W kolejnym kroku z karty Przekształć wybieramy polecenie Data, a następnie Miesiąc i Nazwa miesiąca (po kolei kroki oznaczone na rysunku nr 19).
Kiedy korzystamy z poleceń z karty Dodaj kolumnę to powstaje nowa kolumna, jeśli zaś korzystamy z polecenia z karty Przekształć, to działamy na istniejącej kolumnie i w niej wprowadzamy zmiany. Otrzymamy dane przedstawione na rysunku nr 20.
Zaznaczamy wszystkie trzy kolumny a następnie wybieramy polecenie Grupowanie według (punkt nr 2 na rysunku nr 21) z karty Narzędzia główne.
Otworzy nam się okno Grupowanie według, gdzie wybieramy grupowanie Zawansowane (punkt nr 1 na rysunku nr 22). Automatycznie pojawią nam się trzy poziomy grupowania (punkt nr 2). Następnie nazywamy nową kolumnę Ile dni (punkt nr 3) i wybieramy operację Zlicz wiersze (punkt nr 4), która ma zliczyć dni z danego miesiąca. Nie interesuje nas jakie to były dni tylko ich ilość. Nasze parametry zatwierdzamy przyciskiem OK.
Otrzymamy pogrupowane dane według miesięcy przedstawione na rysunku nr 23.
Tak przygotowane dane chcemy załadować do Excela, więc korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 24).
Otworzy nam się okno Ładowanie do, gdzie ustawiamy parametry wstawianych danych, sposób wyświetlania danych jako Tabela i miejsce wstawienia danych jako istniejący arkusz i wybieramy konkretną komórkę, gdzie chcemy umieścić nasze dane. Parametry zatwierdzamy przyciskiem Załaduj (rys. nr 25).
Otrzymamy dane przedstawione na rysunku nr 26.
W naszych wczytanych dołożyliśmy Rok tylko dla sytuacji kiedy mamy w danych przeskok lat. Przygotowałem sobie dodatkowy wiersz, który kiedy dodam do tabeli z pierwotnymi danymi, a następnie naszą tabele z Power Query odświeżę – klikamy prawym przyciskiem myszy na dane (dowolną komórkę w tabeli zwróconej przez zapytanie Power Query), a następnie z podręcznego menu wybieramy polecenie Odśwież (rys. nr 27).
Otrzymamy przeliczone dane z uwzględnionymi miesiącami w kolejnym roku (który dołożyliśmy do tabeli i danymi bazowymi), przedstawione na rysunku nr 28.
Podsumowując Power Query świetnie sprawdza się w wyznaczaniu ilości dni między datami w danym miesiącu. Rozwiązanie jest dużo szybsze i prostsze niż w Excelu
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
