Power Query - dodatek ETL do Excela
Począwszy od wersji Excela 2010 możemy skorzystać z dodatku Power Query, który ułatwia pracę z danymi. Power Query jest dodatkiem do wersji Excela 2010 i 2013. W wersji 2016 jest już wbudowany, co eliminuje konieczność dodatkowej instalacji ze strony Microsoftu. Jest to narzędzie ETL (z ang. Extract, Transform, Load), które umożliwia pobieranie danych z różnych źródeł, przekształcanie danych oraz ładowanie do hurtowni danych, modeli danych lub aplikacji raportujących. Jest to bardzo intuicyjne narzędzie, które łączy w sobie elastyczność Excela oraz możliwości języka SQL. Program zapamiętuje poszczególne kroki przygotowania kwerendy, umożliwiając łatwą edycję na każdym etapie oraz automatyzację pracy.
Power Query pozwala na pobranie danych z różnych źródeł. Mogą to być bazy danych (np. SQL Server, Microsoft Access, Oracle itp.), pliki programu Excel, pliki CSV, a także dane internetowe itp.
Bardzo ważnym etapem przygotowania kwerendy jest przekształcenie danych w taki sposób, aby odpowiadały naszym potrzebom. Dzięki wykorzystaniu okna Edytora zapytań możemy oczyścić dane z niepotrzebnych rekordów (np. usuwając rekordy posiadające wartości NULL), a także usunąć niepotrzebne kolumny (zamiast zaznaczania kolumn do usunięcia Power Query umożliwia także pozostawienie zaznaczonych kolumn, co ma znaczenie zwłaszcza wtedy, gdy chcemy usunąć wiele, a pozostawić kilka kolumn). Inne przykłady przekształcania danych obejmują zamianę nazw lub wartości w komórkach, filtrowanie i sortowanie danych, tworzenie nowych kolumn zawierających parametry lub obliczenia (patrz przykład poniżej), rozdzielanie jednej kolumny na kilka i odwrotnie.
Jeśli dana tabela danych jest połączona z inną tabelą to kolumna zawierająca link do powiązanej tabeli ma link "Wpis" lub link "Tabela". Link Wpis prowadzi do jednego powiązanego rekordu i reprezentuje relację jeden do jednego z tabelą docelową. Link Tabela prowadzi do powiązanej tabeli i reprezentuje relację jeden do wielu z tabelą docelową. Chcąc dołączyć powiązane kolumny z innej tabeli wybieramy ikonę rozwijania przy danej kolumnie, a następnie opcję rozwiń w wyskakującym okienku, gdzie wskazujemy kolumny do dołączenia.
Istotnym elementem kształtowania danych jest ich grupowanie. Power Query daje możliwości grupowania danych według dowolnych kolumn przy zastosowaniu licznych operacji, takich jak: suma, średnia, zliczanie itp.
Po zakończeniu tworzenia kwerendy dane są ładowane do programu Excel i dostępne w nowej karcie skoroszytu, zaś kwerenda zostaje automatycznie zapisana. Wyświetlenie wszystkich kwerend jest możliwe po wybraniu opcji "Zapytania i połączenia" dostępnej w karcie Dane (Excel 2016).
Power Query umożliwia łączenie wielu zapytań poprzez ich scalanie lub dołączanie. Aby połączyć jedno zapytanie z drugim należy po wybraniu odpowiedniego zapytania wybrać pozycję Scal na karcie wstążki Zapytanie. Pojawiające się okienko Scalanie pozwala na wybór zapytań i pasujących kolumn do scalania, dając duże możliwości w zakresie formy scalenia (koniunkcja, alternatywa, lewo lub prawostronne). W efekcie scalania uzyskujemy odpowiednie kolumny z pierwszego zapytania i jedną kolumnę z linkiem nawigacji do powiązanej tabeli. Po wybraniu ikony rozwijania przy tej kolumnie możemy wskazać właściwe kolumny do połączenia.
Zapytania utworzone w Power Query można załadować do modelu danych w Power Pivot. Wszelkie modyfikacje zapytania po stronie Power Query będą powodować zmodyfikowanie i odświeżenie tabeli wynikowej.