Power Query – rewolucja w Excel 2010 i nowszych
Czym jest Power Query?
Tematem dzisiejszego wpisu jest narzędzie o nazwie Power Query. Rozwiązanie to, chociaż należy do grupy umiejętności co najmniej średnio czy nawet zaawansowanego użytkownika nie oznacza, że mechanizm nie jest dostępny również dla osób początkujących. Wręcz przeciwnie, wszystko, co musisz mieć i wiedzieć, by skorzystać z narzędzia to przede wszystkim informację w jakich wersjach Excel możesz z niego korzystać.
Dla kogo jest Power Query?
Środowisko dodatku Power Query jest dostępne we wszystkich Excel 2016 lub nowszych Windows wersjach autonomicznych i planach subskrypcji usługi Microsoft 365 na karcie Dane w grupie & Przekształcanie.
W wersji Excel niższej 2010 lub 2013 dostępny jest w ramach bezpłatnego dodatku który pobieramy z oficjalnej strony Microsoft i instalujemy na swoim komputerze. Możesz go pobrać tutaj
Oczywiście w kolejnych wersjach pakietu Office narzędzie Power Query może nieco różnić się w kolejnych wersjach. Lista różnic dostępna jest na oficjalnej stronie Microsoftu tutaj
Co zyskują użytkownicy Power Query?
Za pomocą tego narzędzia możemy importować dane z różnych źródeł, łączyć je ze sobą przekształcać i wyświetlać w postaci raportu dostosowanego do naszych potrzeb. Mówiąc językiem najbardziej prostym, zbieramy dane z przeróżnych źródeł, usuwamy niepotrzebne dane (kolumny, wiersze) łączymy potrzebne tabelki, i wyrzucamy do arkusza tylko to, co potrzebujemy.
Czyli tworząc takie zapytanie w Power Query możemy je podzielić na cztery obszary.
- Połącz
Pokaż narzędziu skąd pobrać dane (źródeł może być wiele) - Przekształcanie
Z podanych wcześniej danych źródłowych przekształć dane do przyjaznej postaci. - Łączenie
Jeżeli istnieje zależność pomiędzy różnymi źródłami danych to połącz je według określonego klucza. - Załaduj
Załaduj przygotowane wcześniej dane do arkusza.
Jako przykład użycia mechanizmu posłużę się jednym z wielu przykładów jego wykorzystania.
Pobieranie plików z całego katalogu
To jeden z najczęstszych przyczyn wykorzystania Power Query. Wyobraź sobie sytuację, że co jakiś czas generowany jest nowy plik. Taki plik musisz uwzględnić w swoim raporcie. Nieważne z jaką częstotliwością dochodzą nowe pliki czy jest to codziennie, co tydzień, co miesiąc. Chciałbyś wszystkie te dane złączyć (skleić) i przykładowo zsumować je ze sobą.
Obecnie nie ma łatwiejszego i szybszego sposobu jak wykorzystanie do tego Power Query. Żeby pobrać cały katalog zawierające pliki należy wejść we wspomnianą na początku wpisu kartę Dane. Następnie w sekcji Pobieranie i przekształcanie danych wybrać pierwszy dostępny przycisk Pobierz dane -> Z pliku -> Z folderu
Następnie program wyświetli okno dzięki któremu będzie mogli bez żadnych problemów wyklikać folder który chcielibyśmy sczytać. W moim przypadku będą to dane dotyczące znajomych z Facebooka. Pliki te pobieram systematycznie miesiąc po miesiącu.
Po kliknięciu przycisku Otwórz ujrzymy okienko, w którym mamy podgląd na wszystkie pliki w wybranym katalogu wraz z podstawowymi informacjami o nim. Między innymi znajdziemy tutaj dane dotyczące nazwy pliku, rozszerzenia daty utworzenia dokumentu, daty ostatniej modyfikacji oraz innych atrybutach, które na ten moment są ukryte. W ostatniej kolumnie możemy zauważyć ścieżkę do tego pliku. natomiast na samym dole pod listą cztery przyciski umożliwiające połączenie obecnego zapytania z innym, załadowania listy w takiej formie bezpośrednio do arkusza, oraz przycisk umożliwiający przekształceniu danych.
O połączeniach kilku zapytań to temat, któremu poświęcę w przyszłości cały wpis na blogu, który będzie kontynuacją serii o Power Query, dzisiaj skorzystamy z przycisku Załaduj. W tym miejscu chciałbym zwrócić uwagę na mały trójkąt z prawej strony przycisku który umożliwia skorzystanie z innych opcji ładowania danych natomiast na ten moment nie będę ich omawiał – zrobię to po kolejnych postach skorzystamy ze zwykłego ładowania do arkusza. Po naciśnięciu przycisku narzędzie załaduje do arkusza listę plików w wybranym katalogu.
Jeżeli jednak interesuje nas nie tyle lista plików we wskazanym folderze, ale sama zawartość arkuszy w wybranych plikach możemy zamiast ładowania bezpośredniego listy do arkusza skorzystać z przycisku Przekształć dane.
Po jego naciśnięciu ukaże nam się okno Edytora Power Query. Wraz z informacjami dobrze już Ci znanych z poprzednich zdjęć. natomiast w edytorze mamy możliwość przyjrzenia się wybranym plikom bardziej szczegółowo.
Oprócz przedstawionych wcześniej informacji dotyczących chociażby nazwy czy też ścieżki plików możemy sprawdzić inne atrybuty (zakładka Attributes). oraz możliwość podejrzenia zawartości każdego pliku (kolumna Content). Żeby dostać się do wnętrza atrybutu należy kliknąć przycisk umieszczony w prawym górnym rogu nagłówka Attributes. Rzwinie się lista pokazana poniżej.
Lista ta zawiera szereg różnych atrybutów, które możemy dodać do już istniejącego zestawienia. Możemy na przykład zobaczyć rozmiar każdego z zaimportowanych plików odhaczając wszystkie atrybuty klikają w przycisk Zaznacz wszystkie kolumny i klikając na Size, a następnie przycisk Ok
Naszym oczom ukaże się poprzednie zestawienie wraz z uwzględnieniem wybranego atrybutu Size (rozmiar)
Po naciśnięciu przycisku Zamknij i załaduj ponownie załadujemy zaktualizowane o jedną dodatkową kolumnę dane w arkuszu Excel. Zatrzymajmy się jednak przy edytorze Power Query i przyjrzyjmy się kolumnie Content. która również w prawym górnym rogu posiada przycisk dzięki któremu możemy przyjrzeć się strukturze plików które zaimportowaliśmy, w moim przypadku będzie to tylko jeden arkusz o nazwie friends który jest w każdym z zaimportowanych plików.
Jeżeli chcielibyśmy dostać się do wnętrza tych plików wybierzmy go sobie z poniższej listy. Na górze okna mamy mozliwość wyboru z listy rozwijanej domyślnego pliku przykładowego według którego będzie generowany podgląd oraz układ danych wybranego z poniższej listy nazwy arkusza. Wybrany plik mówi Excelowi jak powinien wyglądać idealny plik w katalogu. Po naciśnięciu nazwy arkusza po prawej stronie pojawi się podgląd arkusza. W moim przypadku w pierwszej kolumnie jest mój znajomy a po prawej data od kiedy każda osoba jest w kręgu moich znajomych. Po naciśnięciu przycisku Ok w arkuszu pojawi nam się zawartość wszystkich plików z wybranego katalogu z arkusz friends zgodnie z tym co zaznaczyliśmy.
Narzędzie Power Query umożliwia nam zaimportowanie danych bezpośrednio do arkusza Excel, natomias nie jest to jedyna opcja. Wejdźmy sobie na kartę Dane i wybierzmy pierwszą opcję Zapytania i połączenia. Po prawej stronie pojawi się lista zapytań. Między innymi Parametrym Przykładowy plik wybrany w poprzednim punkcie i funkcja potrzebna do zaczytania całego katalogu. Szczegółowo omówimy je sobie w kolejnych materiałach natomiast dzisiaj skupimy się na tym docelowym, ostatnim połączeniu które przed chwilą utworzyliśmy, klikając na nie prawą myszką i wybierając opcję Załaduj do… mamy możliwość zmiany domyślnej opcji ładowania danych w postaci tabeli do arkusza na przykład bezpośrednio do tabeli przestawnej. Gdy to zrobimy i potwierdzimy Excelowi o swoich zamiarach pojawi nam się okno pustej tabeli przestawnej wraz z opcjami.
Nie wiesz czym jest tabela przestawna? Zapraszam Ciebie do tego postu:
Tabele przestawne w programie Excel – czym są i do czego mogą nam się przydać – cz.1
Wybranie opcji tabeli przestawnej i odpowiedniemu rozmieszczeniu pól dobry pomysłem byłoby sprawdzenie ilości nowych znajomych w podziale na lata oraz miesiące. Czy umiesz dzięki temu stwierdzić który miesiąc obfitował w nowe Facebookowe znajomości?
Jak widzisz, przykład pokazany w dzisiejszym wpisie pozwala w sposób super szybkim i efektywnym pokazać zawartość z wielu plików odpowiednio je złączyć i pokazać w przynaznym raporcie. A czy Ty wykorzystujesz Power Query w swoich Excelowych projektach? Koniecznie daj znac w komentarzu!