Power Query jest kolejnym po PowerPivot dodatkiem do Excela, który służy zaawansowanej analizie danych. Łączy w sobie zalety Excela i języka SQL, otwierając przed użytkownikami nowe możliwości. Można go pobrać zarówno do Excela 2013, jak i 2010. Do czego służy? W skrócie: do odnajdywania, przekształcania i wzbogacania danych podczas wstępnej obróbki, którą „nagrywa” się w postaci kroków. Nasze zapisane działania można później łatwo odtworzyć, co umożliwia automatyzację pracy.

Wymagania i instalacja Power Query

Zanim jednak przejdziemy do jego omawiania, powiedzmy coś o jego wymaganiach. Do jego działania niezbędna jest przeglądarka internetowa Internet Explorer 9 – dość istotna informacja, biorąc pod uwagę że w dzisiejszych czasach internauci korzystający z tego oprogramowania należą raczej do mniejszości. W związku z tym wymagany jest system operacyjny Windows Vista lub nowszy (wynika to z wymagań sprzętowych Internet Explorera 9)

Jeżeli posiadamy już wyżej wspomniane oprogramowanie, pozostaje nam pobranie dodatku Power Query z oficjalnej strony Microsoftu i jego instalacja. Jej efektem będzie pojawienie się w programie Excel nowej zakładki:

 

Jak pozyskiwać dane w Power Query?

Po lewej stronie nowej zakładki POWER QUERY znajdują się opcje odpowiedzialne za pozyskiwanie danych z bardzo różnych źródeł.

W większości źródła z których możemy pobierać dane pokrywają się z tym co jest dostępne w programie Excel czy też PowerPivot, aczkolwiek znajdziemy tutaj także kilka wyjątkowych smaczków.

Klikając w pierwszą ikonę – Z sieci Web – otwieramy okno w które wpisujemy adres URL w którym znajdują się interesujące nas informacje. Może to być na przykład adres dowolnego hasła w Wikipedii, w którym znajduje się jakaś tabela.

 

Po kliknięciu przycisku OK przechodzimy do okna w którym możemy zdefiniować sposób dostępu do zasobów sieci Web: określamy, czy pobieramy dane jako anonimowy użytkownik, czy też używamy jednego ze sposobów identyfikacji (poświadczenie systemu Windows, interfejs API czy też konto organizacyjne):

 

Ponieważ każdy może korzystać z Wikipedii, wybieramy anonimowy sposób dostępu. Po kliknięciu Połącz otworzy nam się okno z tabelami które możemy pozyskać z danej strony:

 

Łatwo zauważyć, iż tabele nie są od razu w poprawnej formie – pierwsza kolumna często powtarza nazwę tabeli, a pierwszy wiersz jest powtórzeniem nagłówków. W takiej sytuacji klikamy przycisk Edytuj w celu otworzenia Edytora zapytań, gdzie możemy modyfikować naszą tabelę przed dodaniem jej do skoroszytu Excela.

Edytor zapytań jest bardzo rozbudowanym narzędziem, któremu poświęcony zostanie osobny artykuł. Nam do usunięcia wiersza oraz/lub kolumny wystarczą opcje Usuń kolumny oraz usuń wiersze:

• Funkcja Usuń kolumny usuwa zaznaczone (usuń kolumny) lub niezaznaczone (Usuń inne kolumny) przez użytkownika kolumny.

• Funkcja Usuń wiersze pozwala użytkownikowi na usuniecie określonej liczby wierszy z początku tabeli (usuwanie pierwszych wierszy), końca tabeli (usuwanie ostatnich wierszy), usunięcie określonej liczby wierszy naprzemiennie (co drugi wiersz) zaczynając od dowolnego wiersza tabeli (opcja Usuwanie naprzemiennych wierszy) czy też usunięcie pustych (Usuń puste wiersze).

Do naszych celów może być również przydatny przycisk Użyj pierwszego wiersza jako nagłówków, który zastępuje obecne w tabeli nagłówki wartościami z pierwszego wiersza.

Po przeprowadzaniu zmian w importowanych danych klikamy Załaduj i zamknij, aby zamieścić je w skoroszycie Excela.

W ten sposób w arkuszu powstaje tabela z wskazanymi przez użytkownika danymi. Oprócz zwykłych opcji tabeli dostępnych w zakładce PROJEKTOWANIE mamy także do dyspozycji kartę ZAPYTANIE. Możliwości zawartych tam funkcji będą tematem późniejszych artykułów dotyczących Power Query.

Przy używaniu danych z sieci Web należy oczywiście zwrócić uwagę na jakość naszych danych. Wikipedia, mimo jej popularna, nie jest zbyt oczywiście zbyt wiarygodnym źródłem. W poszukiwaniu danych w Internecie warto zwrócić na publiczne instytucje, np. GUS czy też Eurostat. Mimo iż wiele z zawartych tam danych można ściągnąć w postaci Excela, to wciąż zdarzają się tabele które są dostępne jedynie na ich stronach internetowych. W takich przypadkach Power Query może oszczędzić nam żmudnej pracy.

Kolejna ikona w obszarze Pobieranie danych zewnętrznych w zakładce POWER QUERY to przycisk pobierania danych Z pliku, po naciśnięciu którego oczom użytkownika ukazuje się lista formatów z których możemy pobrać dane:

 

O ile pobieranie danych z innego pliku Excela, pliku CSV, XML czy też tekstowego nie jest niczym nowym, to miłym udogodnieniem jest pobieranie metadanych i linków dotyczących plików w folderze – do tej pory aby uzyskać listę plików w folderze niezbędne było korzystanie z wiersza poleceń. Teraz wystarczy wybrać opcje Pobieranie danych zewnętrznych > Z pliku > Z folderu aby uzyskać listę zawartości wskazanego folderu wraz z metadanymi.

Kolejna opcja pobierania danych w zakładce POWER QUERY – Z platformy Azure dotyczy obliczeniowej platformy chmury Microsoftu. Tutaj też Power Query ma szersze możliwości pozyskiwania danych niż Excel bez tego dodatku bądź PowerPivot. W przeciwieństwie do nich, oprócz pobierania danych z Bazy SQL Azure Marketplace oraz z witryny Azure Marketplace w Power Query jest możliwość korzystania z danych zawartych w HDInsight, magazynu obiektów blob oraz z magazynu tabel Azure Marketplace.

Pod czwartą ikonką w obszarze Pobieranie danych zewnętrznych – Z innych źródeł kryje się pozyskiwanie danych z takich usług i źródeł jak:

• SharePoint
• Odata
• Hadoop
• HDFS
• Active Directory
• Microsoft Exchange
• Dynamics CRM Online
• Facebook
• SAP BusinessObjects BI Universe
• Salesforce
• ODBC

W dobie niezwykłej popularności facebooka interesujące wydaje się pobieranie danych z tego serwisu.

Możliwość pozyskania danych dotyczących popularności prowadzonego fanpage’a pozwoliłaby specjalistom od social media przeprowadzenia własnych analiz. Niestety, zapytania zaimplementowane do dodatku PowerQuery pozwalają jedynie na pobranie danych odnośnie swojego profilu (takie jak polubione książki, zespoły muzyczne, fanpage czy też znajomi).

Warto zauważyć, iż żeby pozyskać takie dane musimy korporacji Microsoft udostępnić nasze dane – o pozwolenie program pyta się za pierwszym pobraniem danych z serwisu Facebook.

Aby pobrać informacje na temat prowadzonej strony, należy własnoręcznie napisać zapytanie korzystając z Facebook Graph API, co wymaga jednak specjalistycznej wiedzy. Posiadając takie umiejętności, można dokonać tego samego bez użycia programu Excel.

 

Uwaga: Jeżeli raz udostępniliśmy nasze z portalu Facebook dane korporacji Microsoft, będą one dostępne do czasu kiedy ręcznie usuniemy ich aplikację z listy aplikacji którym udostępniamy swoje dane. Aby to zrobić, należy wejść w ustawienia Facebooka, wybrać Aplikacje, a następnie usunąć Microsoft Power BI.

Ostatnią ikoną w obszarze Pobieranie danych zewnętrznych jest Ostatnio używane źródła. Klikając w nią możemy jeszcze raz połączyć się z jednym z serwisów z którego w ostatnim czasie pobieraliśmy dane.

Jeżeli na liście widocznej na powyższym obrazku klikniemy na opcję Więcej…, to otworzy nam się okno w którym również będziemy mogli włączyć używane źródło danych. Jednak w tym miejscu możemy też –  klikając w ikonę pinezki – przypiąć źródło do listy.

 

W ten sposób będzie ono wyświetlało się na górze listy wyświetlanej po kliknięciu w ikonę Ostatnio używane źródła:

 

Oprócz pobierania danych ze źródeł zewnętrznych, można rzecz jasna pobrać dane do dodatku Power Query z tabeli znajdującej się w skoroszycie Excela. W tym celu należy użyć ikony Z tabeli znajdującej się w obszarze Dane programu Excel:

 

Jeżeli klikniemy niniejszą ikonę mając aktywną komórkę jakiejkolwiek tabeli, automatycznie ta zostanie dodana do programu PowerQuery. Jeżeli użyjemy tej funkcji stojąc poza tabelą, program poprosi nas o określenie zakresu w jakim znajduje się tabela z której chcemy pobrać dane:

 

W takim przypadku należy zaznaczyć zakres komórek, który nie stanowi tabeli, tabeli przestawnej, wyniku zapytania bądź komórek chronionych. Oznacza to, iż jeżeli chcemy dodać tabelę ze skoroszytu Excela do edytora zapytań Power Query, należy już przed kliknięciem ikony Z tabeli zaznaczyć dowolną komórkę w interesującej nas tabeli.

Pod ikoną Ustawienia źródeł danych znajdującą się w obszarze Ustawienia możemy zmienić zasady na jakich łączymy się z interesującym nas źródłem danych – np. sposób autoryzacji.

 

Po kliknięciu w tą ikonę otworzy nam się lista źródeł danych w których możemy zmienić jakieś parametry (dlatego też nie znajdziemy tutaj np. folderu – jako że pobierając informacje o plikach znajdujących się w folderze nie mamy możliwości ustawić żadnych opcji)

 

Warto zwrócić uwagę na opcję wyszukiwania – podczas kiedy mamy tylko 4 źródła danych nie jest ona zbyt przydatna, ale przy ich większej liczbie pozwoli zaoszczędzić dużo czasu.

Ustawienia zmieniamy zaznaczając interesujące nas źródło i klikając Edytuj. Możemy także usunąć dane źródło, co nie łączy się z usunięciem danych pobranych do tej pory z tego źródła. Podczas kolejnego połączenia z danym źródłem użytkownik od nowa skonfiguruje ustawienia, które w ogólności ograniczają się do typu poświadczenia oraz poziomu prywatności.

 

 

Jak skorzystać z danych pobranych z pomocą Power Query?

Warto wspomnieć, iż nie musimy zamieszczać danych w arkuszu w postaci tabeli, aby z nich skorzystać. Możemy jedynie utworzyć połączenie z bazą, dzięki czemu będziemy mogli wykorzystywać interesujące nas dane w raporcie w postaci tabeli przestawnej czy też PowerView. Dodatkowe dodanie ich do modelu pozwoli na ich dalszą obróbkę i analizę w dodatku PowerPivot. Aby utworzyć połączenie i ewentualnie dodać dane do modelu, po wybraniu źródła danych i otworzeniu edytora zapytać musimy wybrać opcję Zapisz i załaduj do, a następnie zaznaczyć odpowiednie opcje.

Po wyborze sposobu pobrania danych nie ma możliwości jego zmiany – nie możemy zamieszczonej tabeli usunąć i utworzyć połączenia ze źródłem ani odwrotnie w obrębie tego samego zapytania. W tym celu musimy usunąć to zapytanie i ponownie pobrać interesujące nas dane.

Powyższy artykuł jest jedynie wstępem do tematu PowerQuery. Omówione zostały jedynie standardowe sposoby pozyskiwania źródła danych – tymczasem możemy nawet własnoręcznie napisać odpowiednie zapytanie odwołujące się do bazy danych! Dodatek ten, mimo iż jest stosunkowo mały, jest naprawdę użyteczny – szczególnie w połączeniu z PowerPivot. Ponadto jest rozwijany niemal z dnia na dzień, a aktualizacje pojawiają się naprawdę często. Już wkrótce szersze omówienie tego przydatnego narzędzia.

Dodaj komentarz o Power Query

A czy Ty korzystasz z Power Query? Znasz jakieś ciekawe zastosowania?