Czyli Power Query w akcji!

Zadanie na dziś jest… dość skomplikowane.

Pracownicy naszej firmy zapisują się (lub są zapisywani) na szkolenia. Wszystkie te dane mamy zgromadzone w tabeli. Dysponujemy danymi pracownika, nazwą szkolenia, jego datą rozpoczęcia i zakończenia.

Zdarzają się błędy przypisań: pracownik może być zapisany na szkolenia nakładające się na siebie. Chcemy stworzyć mechanizm, który będzie wyłapywał te błędy.

Pracę taką, niestety, musimy wykonywać co najmniej raz w miesiącu. Przyda się zatem rozwiązanie z kategorii co-zrobić-aby-się-nie-narobić. Power Query przyjdzie nam z pomocą. Oto formatka:

Formatka

Do dzieła!

Logika rozwiązania

Pomysł na rozwiązanie tego zadania poddał mi członek mojej grupy na Facebooku – Krzysiek. Poszczególne kroki są następujące:

1. Określenie konkretnych dni (dat), w których mają odbywać się szkolenia.
2. Zliczenie, ile razy występuje dany dzień dla danego pracownika (powinien wystąpić raz!).
3. Wyfiltrowanie wszystkich nieprawidłowości: dat występujących więcej niż raz dla pracownika.
4. Przyporządkowanie nazw szkoleń kolidującym datom.

Będzie grupowanie, scalanie zapytań… Ale po kolei.

Ładowanie danych do Power Query

Dane w formatce znajdują się w tabeli o nazwie tbSzkolenia. Należy załadować je do Power Query, czyli:

1. Ustawiamy się w dowolnym miejscu tabeli.
2. Dane/ Z tabeli/zakresu.

Power Query samo zmieni typy importowanych danych – niekoniecznie tak, jakbyśmy tego chcieli. Kolejnym krokiem będzie więc zmiana typów danych:

• Data od i Data do → na datę
• ID pracownika → tekst

Ok. Po takiej kosmetyce możemy przejść do sedna sprawy, czyli do tworzenia listy dat szkolenia.

Tworzenie listy dat szkolenia

Na początku potrzebujemy wyodrębnić konkretne dni (daty), w których odbywa się szkolenie. W zależności od czasu jego trwania mogą to być dwie daty, trzy czy nawet więcej. Efekt ten osiągniemy, tworząc listę, której początkiem będzie Data od, a końcem Data do.

W tym celu dodamy sobie kolumnę niestandardową, czyli w edytorze Power Query: Dodaj kolumnę/ Kolumna niestandardowa, a w niej następująca formuła:

={Number.From([Data od])..Number.From([Data do])}

Gdy ją dodamy, otrzymamy taki efekt:

Kolumna z listą dat

Kolejnym krokiem będzie rozwinięcie wpisów listy do osobnych wierszy. W tym celu klikamy w strzałeczki w nagłówku kolumny Niestandardowe i wybieramy opcję Rozwiń do nowych wierszy:

Rozwijanie listy do nowych wierszy

Po zmianie typu na datę, nowa kolumna wygląda tak:

Kolumna dat

Tutaj można od razu usunąć niepotrzebne kolumny, czyli Datę od i Datę do.

Wykrywanie kolidujących ze sobą dat szkoleń

Teraz będziemy liczyć, ile razy każda data występuje dla jednego pracownika. Jeśli dane są idealne, każda data powinna występować tylko raz. Ponieważ oczywiście tak nie jest, chcemy wychwycić takie, które występują więcej niż raz. W tym celu:

Pogrupujemy dane wg pracownika i daty i zliczymy, ile jest takich rekordów. Czyli:

1. Klikamy prawym przyciskiem myszy na nazwie kolumny np. ID pracownika i wybieramy Grupuj.
2. Ustawiamy grupowanie zaawansowane.
3. Wybieramy poziomy grupowania (ID, pracownik Niestandardowe).
4. Określamy, co ma wyświetlać kolumna grupująca – ma zliczać wiersze.

Całość wygląda tak:

Zliczanie dni poprzez grupowanie

Teraz przyszła kolej na to, byśmy odrzucili prawidłowe rekordy, czyli takie, w których data występuje tylko raz. Wykorzystamy więc filtrowanie kolumny Liczność tak, aby pokazać wartości większe niż 1:

Filtrowanie błędnych danych

Scalanie: przyporządkowanie nazw szkoleń

Ponieważ podczas grupowania uciekły nam nazwy szkoleń, chcemy je teraz przyporządkować. Zrobimy to, wykorzystując mechanizm scalania zapytań.

Zrobię to trochę na łatwiznę, przez co może niezbyt intuicyjnie. Ale zobaczysz, że po delikatnej zmianie kodu będzie miało to sens. Zanim jednak zacznę scalać, wyjaśnię, co z czym chcę scalić.

Chciałabym scalić obecny widok z widokiem, który miałam prawie na początku, czyli w kroku, gdzie wyodrębniłam dni szkoleń (może to być krok z usuwaniem kolumn). Dla większej czytelności zmienię nazwę tego kroku na ListaSzkoleń.

Nadchodzi nieintuicyjny moment.

Scalę teraz te kroki: obecny i ListaSzkoleń, korzystając z mechanizmu scalania zapytań. Robię tak, abym mogła wyklikać to z menu, a nie tak, bym musiała pisać kod ręcznie. Kod podmienię za chwilę. 🙂

Czyli będąc w ostatnim kroku (u mnie nazywa się on Przefiltrowano wiersze), z menu Narzędzia główne wybieram Scal zapytania.

Zapytanie, w którym jestem (u mnie nazywa się ono Szkolenia), będę scalać z samym sobą, wg klucza ID pracownika-Niestandardowe (Niestandardowe to nazwa kolumny z datami). O tak:

Scalanie kroków/zapytań

Teraz trzeba podmienić nazwę drugiego scalanego zapytania, na naszą ListaSzkoleń. To już robimy w pasku formuły najnowszego kroku. Uzyskujemy taki wpis:

= Table.NestedJoin(#"Przefiltrowano wiersze", {"ID pracownika", "Niestandardowe"}, #"ListaSzkoleń", {"ID pracownika", "Niestandardowe"}, "Przefiltrowano wiersze", JoinKind.LeftOuter)

A wynik scalenia jest następujący:

Wynik scalenia

Teraz, z tabel w kolumnie Przefiltrowano wiersze chcemy wyodrębnić nazwy szkoleń. Znów użyjemy do tego celu strzałek wyodrębniania w nagłówku kolumny (pamiętaj o odznaczeniu opcji Użyj oryginalnej nazwy kolumny jako prefiksu):

Wyodrębnianie nazw szkoleń

To w zasadzie tyle.

Na koniec można jeszcze ładnie posortować dane według pracownika i daty oraz usunąć kolumnę Liczność. A żeby było już w ogóle pięknie – można też zmienić nazwę Niestandardowe na Data (w sumie można było to zrobić wcześniej, ale zapomniałam :)).

Otrzymujemy taki piękny efekt:

Wynik zapytania

Całość możemy załadować do Excela jako tabela, albo jako tabela przestawna. Ta ostatnia będzie ciekawie wyglądać:

Wynik w tabeli przestawnej

Jak dodamy do tego bajerki w stylu fragmentatorów – możemy w fajny sposób filtrować np. pracowników :).

Hope it helps.

Na koniec mam do Ciebie prośbę: pomożesz mi dotrzeć z tym artykułem do szerszego grona? Jeśli zechcesz udostępnić ten artykuł na Facebooku – będę Ci bardzo wdzięczna! Bardzo się starałam, aby ten artykuł był dla Ciebie na tyle wartościowy, by wart był wzmianki ;). Oczywiście śmiało podeślij go innym osobom mailem lub Messengerem. Będzie mi bardzo miło:)

Powiązane produkty

• Power Query – wstęp dla laików – Celem tej lekcji jest wprowadzenie w magiczny świat Power Query wszystkich tych, którzy jeszcze nie mieli do czynienia z tym cudownym narzędziem. Coś tam kiedyś słyszeli, coś widzieli, ale jeszcze nic samodzielnie nie robili.
• Kurs Excel w codziennej pracy – mający na celu uporządkować Twoją wiedzę na temat Excela. Jest świetny zarówno dla osób, które chcą uporządkować swoją wiedzę, zdobywaną do tej pory „po omacku”, jak i takich, które dopiero zaczynają swoją przygodę z Excelem.

Plik do pobrania:

MalinowyExcel Rejestr szkoleń dw.xlsx

I film na Youtubie, gdzie pokazuję powyższe rozwiązanie: