Z początkiem roku słowo budżet pojawia się na ustach każdego analityka pewnie jakieś kilkanaście razy dziennie. Teoretycznie budżet na nowy rok powinien być już gotowy, ale w praktyce… no cóż: różnie z tym bywa ;). Dlatego dziś, w temacie budżetowania pokażę Ci, jak bardzo w budżetowaniu może pomóc Power Query. Konkretnie dowiesz się, jak połączyć dane z wielu plików Excela w jedno i po drodze sprytnie je przekształcić. 

Załóżmy, że w naszej firmie pierwszym etapem budżetowania jest zaprognozowanie przez handlowców ile jakich produktów sprzedadzą konkretnym klientom. 

Jako analitycy przygotowaliśmy handlowcom formatkę. Aby łatwiej ją było uzupełniać, wygląda ona następująco: 

Formatka

Zatem mamy informację o kliencie oraz, w osobnych kolumnach, o konkretnych kategoriach produktów. Na skrzyżowaniu odpowiednich handlowiec uzupełnia prognozowane ilości. 

Całość znajduje się w pliku nazwanym jako ID oraz Imię i nazwisko handlowca, w arkuszu Dane. 

Tych plików jest tyle ile handlowców i naszym zadaniem teraz jest połączenie ich w jedno zestawienie, przekształcenie do prawidłowego zakresu danych i zwrócenie do tabeli przestawnej. 

I tutaj całe na biało wkracza Power Query, które zrobi 98% tej pracy 😉.  

Zaczynamy! 

1. Łączenie wielu plików Excela z folderu 

Podłączmy się zatem do osobnego folderu, w którym znajdują się pliki od handlowców. W tym celu, będąc w Excelu, wybierz Dane → Pobierz dane → Z pliku → Z folderu i wybierz odpowiedni folder. 

Otrzymasz następujące okienko, z którego wybierz następnie Połącz → Łączenie i przekształcanie danych: 

Podłączanie do folderu

Pojawi się kolejne okienko, Nawigator, w którym wybierz arkusz, który ma podlegać połączeniu. U mnie będzie to Dane: 

Wybór arkusza

W Power Query otrzymamy kilka zapytań, z czego interesujące będą dla nas dwa z nich: 

1. Przekształć przykładowy plik – czynności, które są wykonywane dla każdego pliku z folderu 
2. REP – zapytanie główne, którego wynik zwrócimy do tabeli przestawnej 

Co do zasady pliki są połączone, jednak jeszcze wymagają następujących przekształceń: 

1. Zmiana układu danych (unpivot): kategorie produktów powinny znaleźć się w osobnej kolumnie, a ich wartości również w osobnej. Zatem zamiast czterech kolumn: Batony, Żelki, Wafle, Czekolady, powinniśmy mieć: Kategoria i Wartość. 
2. Wyodrębnienie danych z nazwy pliku: ID_REP i REP_Name 
3. Usunięcie przyrostka .xlsx z nazwy pliku, a tym samym imienia i nazwiska handlowca 

To do roboty! 

2. Przekształcanie plików na prawidłowy (tabelaryczny) zakres danych 

Aby tego dokonać, skorzystamy z funkcji unpivotowania, czyli anulujemy przestawianie kolumn w zakresie danych. 

Możemy tego dokonać w zapytaniu REP lub w zapytaniu Przekształć przykładowy plik. Ja wykorzystam to drugie. 

Będąc w tym zapytaniu, zaznacz dwie pierwsze kolumny, kliknij prawym przyciskiem myszy i wybierz opcję Anuluj przestawianie innych kolumn: 

Unpivot

Efekt jest następujący: 

Unpivot wynik

Dostaliśmy dwie nowe kolumny Atrybut i Wartość, czyli dokładnie to, co chcieliśmy. Ja tylko zmieniłabym nazwę kolumnie Atrybut na Kategoria. Można to zrobić bezpośrednio w pasku formuły – śmiało. 

3. Podział kolumn i zamiana wartości w Power Query 

Teraz przyszedł czas na powrót do zapytania głównego, gdzie na dzień dobry dostajemy błąd: 

Błąd w zapytaniu głównym

Błąd ten wiąże się z nazwami kolumn, które wcześniej były, a teraz ich nie ma (Batony itp.). Najprościej więc usunąć krok, który generuje ten błąd (Zmieniono typ). 

Teraz możemy śmiało przystąpić do podziału kolumny Source.Name na ID_REP i REP. W tym celu: 

1. Kliknij krok Usunięto inne kolumny1 
2. Zaznacz kolumnę Source.Name 
3. Kliknij prawym przyciskiem myszy i wybierz opcję: Podziel kolumny → Według ogranicznika 
4. Następnie wybierz podział najdalej z lewej strony:

Podział kolumny w Power Query

Automatycznie wygenerował się również krok zmiany typów, który z powodzeniem możesz teraz usunąć (będziemy jeszcze zmieniać typy danych na końcu). 

W kroku Podzielono kolumnę według ogranicznika możesz również zmienić nazwę utworzonych kolumn na ID_REP i REP (w pasku formuły): 

Wynik podziału kolumny

Teraz przyszła pora na pozbycie się przyrostka .xlsx w kolumnie REP. W tym celu: 

1. Zaznacz tę kolumnę 
2. Z menu podręcznego wybierz Zamień wartości 
3. W okienku zamiany wpisz, co na co chcesz zamienić:

Zamiana wartości

Na koniec zapytania (przejdź do ostatniego kroku) nadamy kolumnom typ danych, a zatem: 

1. Zaznacz wszystkie kolumny 
2. Wybierz Przekształć → Wykryj typ danych 
3. Sprawdź. czy zaproponowane typy są OK. Jeśli nie – dokonaj odpowiednich modyfikacji 

Tak wygląda zapytanie po zmianach: 

Gotowe zapytanie

Jeśli wszystko jest OK, to teraz śmiało możesz załadować zapytanie REP do Excela, a konkretnie do tabeli przestawnej! 

Oto efekt przykładowego raportu: 

Wynikowa tabela przestawna

To, co jeszcze przydałoby się tutaj zrobić, to z pewnością podział danych budżetowych na miesiące. Handlowcy tego nie prognozowali, ale jako analitycy sami już sobie z tym poradzimy. 

Ale to w kolejnym artykule 😉. Aby go nie przegapić, zapisz się na mój bezpłatny mailing poniżej (albo z pop-upu, który pojawia się na tej stronie). 

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