Podsumowanie: Dowiedz się, jak ta wspaniała funkcja Excela i Power BI zwana Power Query pomoże ci zautomatyzować proces importowania, przekształcania i czyszczenia danych, aby zaoszczędzić mnóstwo czasu w twojej pracy.
Poziom umiejętności: Początkujący
Tutorial wideo
Pobierz przykładowe pliki
Pliki CSV, których używam w filmie są dostępne do pobrania poniżej. Pliki należy rozpakować z pliku zip.
CSV Order Files For Power Query Overview.zip (337.0 KB)
Wprowadzenie do Power Query
W tym poradniku przedstawiam wprowadzenie do Power Query. Dowiesz się, dlaczego jest to moje nowe ulubione narzędzie Excela do pracy z danymi oraz jak może pomóc zautomatyzować procesy i zaoszczędzić czas!
Maszyna Danych Power Query
Oglądałem program telewizyjny o tym, jak powstają różne rzeczy, i wyjaśniali, jak działa maszyna do odkładania w fabryce ciastek.
Podstawy działania maszyny depozytorowej są następujące:
- Dodajesz do niej składniki.
- Zmieniasz niektóre ustawienia.
- I w magiczny sposób powstają ciastka (ciasteczka, pączki, biszkopty), które są gotowe do pieczenia.
Po ustawieniu pokręteł, proces można powtarzać w kółko, aby za każdym razem stworzyć idealne ciastka. Robisz się głodny…? 🙂
Power Query działa w bardzo podobny sposób!
- Dodajesz swoje źródła danych (tabele Excel, pliki CSV, tabele baz danych, strony internetowe, itp.)
- Naciskasz przyciski w oknie Power Query Editor, aby przekształcić dane.
- Wysyłasz dane do arkusza lub modelu danych (PowerPivot), który jest gotowy do tabel przestawnych lub raportowania.
Power Query jest jak maszyna, ponieważ po skonfigurowaniu zapytania, proces może być powtarzany za pomocą kliknięcia przycisku (odświeżenie) za każdym razem, gdy dane ulegną zmianie.
Jeśli używałeś makr do przekształcania danych, możesz myśleć o tym jako o znacznie łatwiejszej alternatywie dla VBA, która NIE wymaga kodowania.
Wspólne zadania związane z danymi ułatwione
Czy pracujesz z danymi, które zostały wyeksportowane z systemu ewidencji? Może to być księga główna, księgowość, ERP, CRM, Salesforce.com lub dowolny system raportowania zawierający dane.
Jeśli tak, to prawdopodobnie spędzasz dużo czasu na przekształcaniu lub zmianie kształtu danych, aby stworzyć dodatkowe raporty, tabele przestawne lub wykresy.
Te transformacje danych mogą obejmować takie zadania jak:
- Usuwanie kolumn, wierszy, pustych miejsc
- Konwersja typów danych – tekst, liczby, daty
- Podziel lub połącz kolumny
- Sortuj & filtrowanie kolumn
- Dodaj kolumny obliczone
- Agreguj lub podsumuj dane
- Wyszukaj & zamień tekst
- Upivotuj dane, aby użyć ich w tabelach przestawnych
Czy któreś z tych zadań brzmi znajomo? Jeśli tak, to prawdopodobnie są też nudne, powtarzalne i czasochłonne 🙂 Uwierz mi, spędziłem większą część mojej kariery wykonując te zadania i próbując znaleźć szybsze sposoby na ich wykonanie.
Na szczęście Power Query ma przyciski, które automatyzują wszystkie te zadania!
Przegląd wstążki Power Query
Począwszy od Excela 2016 dla Windows, Power Query został w pełni zintegrowany z Excelem. Znajduje się teraz na karcie Dane na wstążce w grupie Uzyskaj & Przekształć.
W Excelu 2010 i 2013 dla Windows Power Query jest darmowym dodatkiem. Po jego zainstalowaniu karta Power Query będzie widoczna na wstążce Excela.
Używasz przycisków na karcie Dane lub Power Query, aby uzyskać dane źródłowe. Dane mogą być przechowywane w plikach Excel, csv, Access, bazie danych SQL, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, strony internetowe i wiele innych.
Po określeniu skąd pochodzą dane, używasz okna Power Query Editor do przekształcenia danych.
Przyciski w oknie Power Query Editor pozwalają przekształcać dane.
Pomyśl o niektórych zadaniach, które wykonujesz wielokrotnie przeglądając przyciski na powyższym obrazku. Za każdym razem, gdy naciskasz przycisk, Twoje działania (kroki) są rejestrowane i możesz szybko zastosować je ponownie, gdy otrzymasz nowe dane, odświeżając zapytanie.
Po wykonaniu kroków, możesz wyprowadzić dane do tabeli w swoim skoroszycie Excel, klikając przycisk Zamknij & Załaduj.
Możesz również zmodyfikować istniejące zapytania i odświeżyć tabele wyjściowe ze zmianami lub zaktualizowanymi danymi.
Przykłady transformacji danych
Oto kilka przykładów tego, co Power Query może zrobić z Twoimi danymi.
Unpivot Data for Pivot Tables
Moją ulubioną cechą Power Query jest zdolność do unpivot data. Jest to technika używana w celu przygotowania danych do użycia w tabeli przestawnej. Jest to również określane jako normalizowanie danych, aby uzyskać je w formacie tabelarycznym.
Dane mogą zacząć wyglądać tak jak poniżej.
A ty chcesz, aby wynik końcowy wyglądał tak.
Power Query może to zrobić za pomocą kilku przycisków i przygotować dane do użycia w tabeli przestawnej.
Tutaj znajduje się artykuł i wideo na temat tego jak dokładnie rozpiąć dane za pomocą Power Query.
Sprawdź mój artykuł na temat struktury danych źródłowych dla tabeli przestawnej jeśli nie wiesz dlaczego Twoje dane muszą wyglądać w ten sposób dla tabeli przestawnej.
Załącz (połącz) tabele w Power Query
Funkcja Append w Power Query pozwala na łączenie wielu tabel (układanie ich pionowo) aby stworzyć jedną dużą tabelę. Może to zrobić z wieloma tabelami w jednym pliku, lub może pobrać dane z wielu różnych plików/źródeł.
Powiedzmy, że masz folder, który zawiera pliki CSV lub Excel z danymi raportu dla każdego miesiąca. Wrzuć wszystkie te pliki do Power Query, a wypluje on jedną ładną tabelę, którą możesz wykorzystać do tworzenia tabel przestawnych i wykresów.
Jeśli dane w tych raportach muszą być również przekształcone (usunięte wiersze, podzielone kolumny, niepodzielone, itp.), to Power Query może to zrobić w tym samym procesie.
Po skonfigurowaniu, wszystko co musisz zrobić, to nacisnąć przycisk odświeżania każdego miesiąca, gdy nowy plik zostanie dodany do folderu, a wiersze zostaną dodane do tabeli wyjściowej.
Jak niesamowite to jest! 🙂
Merge Tables – A VLOOKUP Alternative
Power Query ma możliwość łączenia lub scalania tabel. Może to być używane jako alternatywa dla formuł VLOOKUP lub INDEX/MATCH.
Powiedzmy, że masz tabelę danych zawierającą rekordy sprzedaży i używasz VLOOKUP, aby uzyskać informacje o produkcie na podstawie nazwy sprzedanego produktu. Informacje o grupie produktów znajdują się w innej tabeli na innym arkuszu lub w innym skoroszycie.
Używanie formuł VLOOKUP jest świetne, ale często może oznaczać dodanie tysięcy formuł do skoroszytu. Co zwiększa rozmiar pliku i czas obliczeń.
Power Query pozwala bardzo szybko i łatwo połączyć dwie tabele za pomocą kilku kliknięć. Wykorzystuje on złączenia SQL, więc możesz nawet wykonywać bardziej zaawansowane złączenia, takie jak wewnętrzne, zewnętrzne, lewe, prawe, pełne i antyzłącza.
Tworzenie własnych funkcji
Power Query został zaprojektowany tak, że NIE musisz znać się na kodowaniu, aby go używać. Jest on bardzo łatwy w użyciu, ponieważ można po prostu klikać przyciski i stosować filtry tak jak w Excelu.
Jednakże Power Query może być zaprogramowany do tworzenia własnych funkcji. Daje to nieograniczone możliwości przekształcania danych w dowolny sposób.
Oparte jest to na języku M, a większość funkcji jest bardzo podobna do pisania formuł w Excelu. To również sprawia, że jest bardziej przyjazny dla użytkownika i łatwiej jest nauczyć się kodu.
Ten nowy język i zestaw funkcji oznacza, że jest wiele do nauczenia się, ale uważam, że jest to zabawna i wymagająca część. Dodatkowo, pracodawcy w przyszłości na pewno będą poszukiwać pracowników z umiejętnościami Power Query.
Power Query zapisuje Twoje kroki & Automatyzuje procesy
Power Query nie tylko ułatwia wszystkie te zadania, ale również zapisuje Twoje kroki, więc NIE musisz ich wykonywać w kółko. Dzięki temu zaoszczędzisz mnóstwo czasu, jeśli przygotowujesz te same dane każdego dnia, tygodnia czy miesiąca.
Doskonale radzi sobie również z błędami. Jeśli struktura danych źródłowych ulegnie zmianie, Power Query poinformuje Cię, w którym kroku nastąpiła zmiana i pozwoli Ci ją naprawić. To sprawia, że utrzymanie danych jest łatwe i nie trzeba całkowicie przerabiać procesu, gdy coś się zmieni.
Możesz użyć Power Query, aby przygotować dane do użycia w tabelach przestawnych, wykresach i raportach pulpitu nawigacyjnego. Jest to kluczowy krok w procesie podsumowywania i analizowania danych.
Maszyna Power Query & Power BI
No cóż, nie jest w stanie dokładnie zrobić ciasteczek, ale Power Query jest całkiem niesamowitym narzędziem! Dzięki niemu zaoszczędzisz mnóstwo czasu podczas przekształcania danych.
Power Query jest tylko jednym z elementów pakietu produktów Power BI (Business Intelligence) firmy Microsoft.
Jeśli wrócimy do analogii z pieczeniem ciasteczek w fabryce, możesz pomyśleć o Power Query jako o pierwszym kroku na linii montażowej. Kiedy ciasteczka są już uformowane, musimy je upiec (tabele przestawne, PowerPivot), a następnie zapakować do prezentacji (Power View, Power Map, wykresy, pulpity itp.).)
Można myśleć o Excelu jako o fabryce, w której znajdują się wszystkie te narzędzia. Najciekawsze jest to, że przed Excelem rysuje się bardzo świetlana przyszłość! Wszystkie te postępy w technologii pomogą nam wykorzystać nasze dane na nowe sposoby, zaoszczędzić czas i zaimponować naszym szefom 🙂 🙂
Jak uzyskać Power Query?
Drugą miłą częścią jest to, że Power Query jest teraz wbudowany w Excela, począwszy od Excela 2016 dla Windows. Jeśli korzystasz z Excela 2010 lub 2013 to Power Query jest darmowym dodatkiem.
Mam dedykowaną stronę, która pomoże Ci określić czy masz odpowiednią wersję Excela, aby uzyskać Power Query. Znajdują się tam również kompletne instrukcje instalacji oraz link do pobrania.
Kompletny przewodnik po instalacji Power Query
Aby dać Ci wyobrażenie o znaczeniu tego narzędzia, Power Query zostało w pełni zintegrowane z programem Excel w Excelu 2016 dla Windows i znajduje się na karcie Dane na wstążce.
Jest ono również znane jako Get & Transform, chociaż termin Power Query jest najbardziej powszechny.
Dodatkowe zasoby
Ten artykuł dostarczył przeglądu podstaw Power Query, który powinien pomóc w zrozumieniu niektórych głównych funkcji. Power Query posiada wiele funkcji i jest wiele do nauczenia się.
W najbliższych tygodniach będę udostępniał kolejne artykuły i filmy. Oto kilka materiałów, które pomogą Ci zacząć.
Jak obrócić dane za pomocą Power Query + Samouczek wideo
Darmowy webinar szkoleniowy na temat narzędzi Power Tools
Już teraz prowadzę darmowy webinar szkoleniowy na temat wszystkich narzędzi Power Tools w Excelu. Obejmuje to Power Query, Power Pivot, Power BI, tabele przestawne, makra & VBA i wiele innych.
Nazywa się to The Modern Excel Blueprint. Podczas webinaru wyjaśniam, czym są te narzędzia i jak można je dopasować do Twojego przepływu pracy.
Dowiedzą się Państwo również, jak stać się bohaterem Excela w Państwa organizacji, czyli osobą, na której wszyscy polegają, jeśli chodzi o pomoc w zakresie Excela i zabawne projekty.
Webinarium odbywa się w różnych dniach i godzinach. Kliknij poniższy link, aby się zarejestrować i zarezerwować miejsce.
Kliknij tutaj, aby zarejestrować się na bezpłatny webinar