Microsoft w pewnym momencie dostrzegł, że zostaje w tyle jeśli chodzi o narzędzia pozwalające na przekształcanie danych. Użytkownicy Excela narzekali na problemy z importowaniem plików z innych źródeł (tekstowych, z www, z SAPa) itp. Pobranie nawet prostego pliku tekstowego często wymagało monotonnych napraw (kropek na przecinki, dat etc.). Co gorsza – czynności te były wykonywane za każdym razem, gdy pojawiała się potrzeba zaimportowania nowych danych (chyba, że ktoś opanował do perfekcji sztukę tworzenia makr).
Odpowiadając więc na potrzeby użytkowników Microsoft przygotował dla nas dodatek, który nazwał pierwotnie Power Query. Czym jest Power Query? Otóż książkowo jest to narzędzie z grupy ETL (Extract, Transform, Load). Przyjrzyjmy się dokładniej temu skrótowi i zobaczmy jak jego rozwinięcie przekłada się na działanie dodatku:
Power Query pozwala na sięganie do różnych źródeł danych. Powiedzieć, że jest ich „dużo” byłoby pewnie dla Excela obraźliwe – liczba źródeł jest ogromna!!
Poczynając od najprostszych – możesz za pomocą Power Query dostać się do tabeli lub zakresu, który znajduje się w Excelu. Możesz podłączyć się do pliku tekstowego lub zewnętrznej bazy danych (np. Oracle lub SQL Server). Możesz z użyciem Power Query sięgnąć do danych znajdujących się w internecie lub swojej skrzynki mailowej (np. by utworzyć statystykę – z kim ile maili wymieniłeś w ostatnim miesiącu). Możesz również z łatwością sięgać do danych dostępnych w internecie. Na poniższym filmiku znajdziesz wskazówki jak pobierać zawsze aktualne dane (kursy walut) ze strony Narodowego Banku Polskiego.
Analogicznie możesz konsolidować dane, które znajdują się w wielu arkuszach w tym samym pliku.
Gdy już sięgniesz po dane będziesz mógł bez trudu je odświeżać! Jeśli zatem zmienią się Twoje dane, lub dołożysz nową zakładkę w pliku Excela, lub dołożysz nowy plik do folderu lub system transakcyjny wpisze nowe rekordy do bazy danych – klikasz odśwież i masz aktualne dane w Excelu!
A jeśli ktoś będzie pracował na pliku, z którego chcesz pobrać dane? Nie ma problemu! Excel pobierze najbardziej aktualne dane (z poprzedniego) zapisu i NIE nakrzyczy na Ciebie komunikatem o tym, że ktoś na tym pliku pracuje…
A może nie mieścisz się w Excelu z danymi? Pobierasz dane z bazy danych lub wielu plików i przekraczasz 1 mln wierszy? (Excel ma ich dokładnie 1 048 576 w jednym arkuszu począwszy od wersji MS Office 2007). Tym też nie musisz się martwić – Power Query pobierze więcej danych i pozwoli Ci utworzyć z nich tabelę przestawną, bez wkładania danych do arkusza. Zobacz jak można pobrać 6 mln (!!) rekordów z Accessa do Excela:
Ok, to już dostałeś się do danych. Podłączyłeś się przez Power Query do folderu lub do bazy danych czy też swojej skrzynki mailowej…
Co się zazwyczaj dzieje dalej w scenariuszach biznesowych? Najczęściej trzeba dane „naprawić”. Co to znaczy naprawić? Zwykle przy projektach spotykamy się z problemami związanymi z nieprawidłowym formatem liczb, dat, poprzestawianymi kolumnami, zapivotowanymi danymi (zobacz filmik poniżej, żeby zrozumieć czym są dane zapivotowane). Czasem potrzebujesz pobrać tylko cząstkę danych z bazy – np. masz dane sprzedażowe z ostatnich 10 lat a chcesz pobrać dane tylko o sprzedaży weekendowej. Co więcej data jest niepoprawnie zapisana (np. z kropkami)…
Power Query daje Ci możliwość wyklikania funkcji. To znaczy, że nie musisz wiedzieć jak nazywa się funkcja, żeby z niej skorzystać. Brzmi niesamowicie dla wyjadaczy Excelowych… Przykładowo – chcesz dowiedzieć się jakim dniem tygodnia jest dana data? W Excelu musisz wiedzieć jaka funkcja pozwala na realizację tego zadania (pssst wiesz jaka :)?). W Power Query wystarczy, że zaznaczysz kolumnę z datami i powiesz programowi, że chcesz poznać nazwę dnia tygodnia. I gotowe!
Rezultat:
Takich smaczków w Power Query jest dużo – zazwyczaj udaje nam się wykonać w 100% zadanie nie korzystając z pisania własnych funkcji w języku, którym komunikuje się Power Query (a ten język nazywa się M).
W kroku Transform może dane dowolnie przekształcać, grupować, filtrować, sortować… Zobacz np. jak odpivotować dane w Excelu:
I jak szybko posprzątać dane i utworzyć raport:
Bardzo ciekawe zastosowanie Power Query do grupowania danych zostało opisane na blogu dashboards.pl – sprawdź ten artykuł: Top N wartości po podłączeniu do bazy danych.
Podsumowując – sięgnąłeś do danych, dokonałeś odpowiednich przekształceń, czas zatem wykorzystać je do raportu, wykresu, pulpitu menadżerskiego etc.
Power Query daje Ci możliwość – przeniesienia danych do Excela lub utworzenia połączenia z danymi. Dzięki temu omijasz ich wklejenie w arkusz co drastycznie zmniejsza rozmiar pliku i przyspiesza jego działanie (i oczywiście omija ograniczenia liczby wierszy w Excelu).
Gdy już przygotujesz wszystkie działania – możesz dane w dowolnym momencie odświeżyć – Power Query wykona wówczas wszystkie kroki, które raz pokazałeś mu jak powinny wyglądać (jak sięgnąć do danych, jak je przekształcić, co z nimi dalej zrobić).
Power Query jest dodatkiem, który zmienia zasady gry w Excelu. Pozwala w prosty sposób wykonać czynności, które do tej pory zarezerwowane były wyłącznie dla mistrzów Excela i deweloperów VBA. Jest to największe usprawnienie w Excelu od czasów pojawienia się prawdziwej tabeli (Narzędzia główne/Formatuj jako tabelę).
Power Query pozwala na wykonanie działań, które do tej pory były wykonywane skomplikowanymi makrami a co więcej – radzi sobie z nimi szybciej niż nawet bardzo dobrze napisane makro.
Takie pytanie słyszę najczęściej gdy pokaże kilka sztuczek w Power Query. Jeśli pracujesz z najnowszą wersją Office – powinieneś widzieć Power Query na wstążce Dane (Microsoft nazwał go „Pobieranie i przekształcanie danych” i zastąpił starszą grupę służącą do pobierania danych zewnętrznych).
W ciut starszej wersji dodatek ten również znajduje się na wstążce Dane, jednak widoczny jest dopiero za starszymi kreatorami.
W wersji 2013 i 2010 również możesz skorzystać z Power Query, musisz jednak go dodatkowo doinstalować. Dodatek jest bezpłatny!!! Pobrać możecie go spod tego linka: Pobierz Power Query.
Podsumowując – kto pozna Power Query zdobędzie przewagę przy analizowaniu i przetwarzaniu danych, zaoszczędzi ogrom czasu i zwiększy swoją satysfakcję z pracy.
W artykule skupiliśmy się tylko na zastosowaniu Power Query w Excelu, jednak ten dodatek wykorzystywany jest również w Power BI do pobierania i przekształcania danych. Jeśli zatem nauczysz się jak pracować z nim w Excelu – bez problemu poradzisz sobie z pracą jednym z ciekawszych narzędzie od Microsoftu ostatnich lat – Power BI.
A jak my możemy Wam pomóc z poznaniem Power Query?
Subskrybujcie nasz kanał: youtube.com/imperiumszkoleniowe – publikujemy tam poradniki z Power Query.Możecie też obejrzeć wersję wideo zapoznającą z Power Query:
Jeśli chcecie szybko nauczyć się Power Query i poznać więcej ciekawych zastosowań – sprawdźcie nasze szkolenia otwarte: Szkolenia Power Query lub zapytajcie o szkolenie zamknięte dla pracowników Waszej firmy.
Do zobaczenia na szkoleniu Power Query lub Power BI!
Autor: Imperium Szkoleniowe.