W poniższym krótkim artykule będę chciał zademonstrować dlaczego warto jest się zainteresować PQ. Nie będę dokładnie się rozwodził nad każdym z kroków – spróbuj przećwiczyć owy przykład a być może tak jak i ja nie będziesz sobie wyobrażał dalszego życia bez PQ 😉
Mamy taki oto przykładowy plik CSV.
Co miesiąc musimy zaimportować ten plik do Excela i wykonać następujące czynności:
- Chcemy usunąć kolumnę telefon, gdyż jest ona niepotrzebna
- Przy zaimportowaniu należy w kolumnie pensja zamienić kropki na przecinki, aby Excel potraktował dane z tej kolumny jako liczby
- Chcemy zmienić nazwiska tak, aby były pisane WIELKIMI LITERAMI
- Dla owych pensji chcemy obliczyć pensję netto (tj. bez podatku – tutaj dla uproszczenia założymy, że pensja netto to pensja pomniejszona o 17% podatku dochodowego aby nie zaciemniać idei ćwiczenia – nie będziemy się bawić w obliczanie ZUSów i składek zdrowotnych)
- Zaokrąglijmy pensję netto do dwóch miejsc po przecinku.
W pierwszym kroku na karcie Dane wybieram polecenie Z pliku tekstowego/CSV.
Ewentualnie to samo polecenie znajdę pod przyciskiem Pobierz dane -> Z pliku -> Z pliku tekstowego/CSV.
W następnym kroku wskazuję odpowiedni plik, z którego chcę zaimportować dane (np. 01_demonstracja_pq.csv).
Pojawi się okno dialogowe z ustawieniami importu. W górnej części okna należy zwrócić uwagę na dwie rzeczy: pierwsza to pochodzenie pliku. Jeśli w przykładowych danych zamiast polskich liter widzimy „krzaczki” to znaczy, że należy wybrać inny sposób kodowania znaków. W naszym przypadku jest wszystko OK (plik ma kodowanie 65001: Unicode (UTF-8)). Czasami pliki mogą mieć kodowane polskie znaki w standardzie 1250: Środkowoeuropejski (Windows). Druga rzecz warta zwrócenia uwagi to ogranicznik (czyli jakim znakiem w pliku tekstowym są oddzielane od siebie kolumny). Najczęściej jest to Średnik, ale czasami może być to przecinek, lub nawet jakiś inny niestandardowy znak. Jeśli wszystko jest OK, to należy kliknąć w przycisk Przekształć dane (w starszych wersjach PQ ten przycisk ma nazwę Edytuj).
Załaduje się edytor Power Query. Wtedy główne okno programu Excel jest zablokowane do czasu, aż nie opuścimy edytora PQ.
Po prawej stronie mamy listę zastosowany kroków:
Widzimy tutaj pierwszy krok: Źródło – czyli tutaj kryje się informacja jaki plik został zaimportowany. Następny krok: Nagłówki o podwyższonym poziomie wykonał za nas edytor PQ. Na podstawie analizy struktury pliku PQ domyślił się, że pierwszy wiersz pliku CSV jest wierszem nagłówkowym tabeli dlatego też w podglądzie danych mamy takie kolumny jak l.p., Imię, Nazwisko, Pensja, Telefon a nie Column1, Column2, Column3, Column4, Column5. Trzeci krok Zmieniono typ oznacza że edytor PQ ustawił odpowiedni typ dla poszczególnych kolumn (np. kolumna l.p. ma typ liczbowy co widać po ikonce 123 a kolumna Imię ma typ tekstowy co widać po ikonce ABC).
Dość często ten krok jest usuwany, gdyż nie zawsze edytor PQ poprawnie ustawi typ danych dla wszystkich kolumn. Ale w naszym przypadku możemy zostawić tak jak jest. Zwróć uwagę na kolumnę Pensja która ma typ tekstowy a nie liczbowy. Jest to spowodowane złym separatorem dziesiętnym (kropka zamiast przecinka).
Wracając do okienka zastosowane kroki: przy każdym kroku gdy na niego najedziemy po lewej stornie pojawi się symbol X który oznacza, że można dany krok usunąć. W Power Query nie ma czegoś takiego jak Ctrl + Z. Jeśli wykonamy jakąś czynność i efekt jej działania nie spełni naszych oczekiwań to po prostu należy ten krok usunąć i spróbować daną czynność wykonać ponownie. Przy niektórych krokach znajduje się ikonka koła zębatego, która pozwala na zmianę ustawień danego kroku. Np. po kliknięciu w koło zębate przy kroku Źródło możemy zmienić plik, na którym będą wykonywane przekształcenia
(jeśli kliknąłeś w koło zębate, to kliknij w przycisk Anuluj, aby przejść do dalszej części ćwiczenia)
Przejdźmy w końcu do konkretów!
Po tym krótkim wprowadzeniu do interfejsu edytora PQ zajmijmy się tym, co jest najważniejsze – doprowadźmy nasze dane do porządku.
Po pierwsze – usuńmy kolumnę Telefon. Tutaj sprawa jest prosta: wystarczy na nagłówku kolumny Telefon kliknąć prawym przyciskiem myszy i wybrać opcję Usuń.
Drugim krokiem będzie doprowadzenie do porządku Pensji. W tym celu klikam na nagłówku kolumny Pensja prawym przyciskiem myszy i wybieram opcję Zamień wartości.
Pojawi się okno dialogowe Zamienianie wartości w którym to w polu Wartość do znalezienia wpisuję kropkę a w polu Zamień na wpisuję przecinek i klikam w przycisk OK.
Jeśli w kolumnie Pensja znajdowałby się jeszcze jakiś symbol waluty np. € warto ponownie wykonać ten sam krok w celu usunięcia niepożądanych znaków, tak aby została „goła” liczba.
Mając „gołą liczbę” w kolumnie Pensja możemy zmienić typ wartości w niej przechowywanych z Tekst na Liczba dziesiętna. W tym celu na nagłówku kolumny Pensja klikamy lewym przyciskiem myszy na ABC i wybieramy Odpowiedni typ danych.
Teraz zajmijmy się zamianą nazwisk tak, aby były pisane WIELKIMI LITERAMI. W tym celu klikam lewym przyciskiem myszy na nagłówku kolumny Nazwisko tak aby była zaznaczona. Następnie na wstążce klikam na kartę Przekształć i wybieram polecenie Format -> Wielkie litery.
Ostatni krok to dodanie kolumny Pensja netto. W tym celu kliknij lewym przyciskiem myszy na nagłówku kolumny Pensja, aby ją zaznaczyć a następnie na karcie Dodaj kolumnę wybierz polecenie Standardowy – > Mnożenie.
W oknie dialogowym Pomnóż wpisz wartość 0,83 i kliknij w przycisk OK (równie dobrze moglibyśmy najpierw utworzyć kolumnę o nazwie Podatek, gdzie pensję brutto pomnożylibyśmy przez 17% a potem utworzyli nową kolumnę, która odjęłaby od siebie te dwie wartości, ale myślę że nie ma sensu komplikować ćwiczenia).
Wskazówka: zauważ, że na kartach Przekształć i Dodaj kolumnę znajduje się sporo zdublowanych poleceń. Różnica między poleceniami na tych kartach jest taka, że wybierając przekształcenie z karty Przekształć dokonamy przekształcenia na aktualnie zaznaczonej kolumnie (utracimy dane pierwotne). Natomiast wybierając polecenie z karty Dodaj kolumnę na podstawie aktualnie wybranej kolumny zostanie utworzona nowa kolumna z wybranym przekształceniem.
Powstała nowa kolumna o nazwie Mnożenie. Zmieńmy jej nazwę na Pensa netto. W tym celu na nagłówku kolumny mnożenie kliknij prawym przyciskiem myszy i wybierz opcję Zmień nazwę a następnie wpisz Pensja netto i naciśnij klawisz Enter.
Zauważ, że nasze pensje netto są podane z dokładnością do 4 miejsc po przecinku. Zaokrąglijmy liczby do pełnych groszy czyli do 2 miejsc po przecinku. Kliknij lewym przyciskiem myszy w nagłówek kolumny Pensja netto a następnie na karcie Przekształć wybierz polecenie Zaokrąglenie -> Zaokrąglenie…
Następnie w oknie dialogowym Zaokrąglenie w polu Miejsca dziesiętne wpisz 2 i kliknij w przycisk OK.
W końcu wykonaliśmy klika przekształceń. Teraz zaimportujmy nasze dane do Excela. W tym celu na karcie Narzędzia główne kliknij w górną część przycisku Zamknij i załaduj. Dane zostaną załadowane do nowego arkusza.
Nie zapomnij zapisać skoroszytu, aby nie utracić efektów pracy!
Ręczna edycja pliku CSV
Aby wykonać kolejne ćwiczenie, należy nasz plik CSV wyedytować np. usunąć kilka rekordów. W tym celu w Eksploratorze systemu Windows kliknij na pliku 01_demonstracja_pq.csv prawym przyciskiem myszy i wybierz Edytuj.
Otworzy się program notatnik – zaznacz kilka wierszy i naciśnij klawisz BackSpace. Jeśli czujesz się na siłach, możesz dopisać kilka rekordów zachowując oryginalny format. Następnie z menu Plik wybierz polecenie Zapisz i zamknij notatnik
Ponowne wykorzystanie zapisanych przekształceń
A teraz coś pięknego. Załóżmy, że za miesiąc musisz dokonać ponownego przekształcenia danych. W tym celu najprościej będzie skopiować utworzony miesiąc temu skoroszyt programu Excel do innego pliku (najprościej otwierasz plik wybierasz Plik->Zapisz jako i zapisujesz skoroszyt pod inna nazwą).
I teraz mamy dwa scenariusze: jeśli plik w nowymi danymi (w sensie plik CSV, który importujemy) znajduje się w tej samej lokalizacji, to wystarczy na tabelce kliknąć prawym przyciskiem myszy, wybrać opcję Odśwież i do tabeli zostaną załadowane nowe dane z wykonanymi przekształceniami (aby sprawdzić, jak to działa otwórz plik CSV w notatniku i usuń kilka pierwszych rekordów lub dodaj jakieś nowe rekordy).
Po odświeżeniu zostaną załadowane nowe dane ale będą wykonane wcześniej zdefiniowane przekształcenia (usunięta kolumna telefon, nowa kolumna pensja netto, nazwiska będą zapisane WIELKIMI LITERAMI).
Wykonanie przekształceń dla innego pliku
Załóżmy, że dane do nowego raportu znajdują się z pliku o innej nazwie bądź w innej lokalizacji. Ale nic straconego. Wystarczy wykonać kilka czynności. Najpierw otwórz plik zawierający zapisane nasze przekształcenia. Następnie zapisz ten plik pod inną nazwą (Plik->Zapisz jako). W kolejnym kroku kliknij na tabeli z danymi. Po prawej stronie powinien się pojawić panel Zapytania i połączenia (jeśli się nie pojawia to na karcie Dane należy kliknąć w przycisk Zapytania i połączenia)
Następnie kliknij prawym przyciskiem myszy na naszym zapytaniu i wybierz polecenie Edytuj.
Załaduje się edytor Power Query. Po prawej stronie w panelu Zastosowane kroki przy pierwszym kroku Źródło kliknij w ikonkę koła zębatego
Pojawi się znane wcześniej okno dialogowe. Należy wskazać inny plik z danymi, kliknąć w przycisk OK aby wrócić do głównego okna Edytora PQ. W edytorze PQ załadują się nowe dane. Wystarczy teraz tylko na karcie Narzędzia główne wybrać polecenie Zamknij i załaduj. W tabeli w arkuszu Excela pojawią się zaktualizowane dane.