Jak korzystać z dodatku Power Query w programie Excel?
Dodatek Power Query programu Excel jest używany do wyszukiwania źródeł danych, nawiązywania połączeń ze źródłami danych, a następnie kształtowania danych zgodnie z naszymi wymaganiami dotyczącymi analizy. Gdy skończymy już kształtować dane zgodnie z naszymi potrzebami, możemy również udostępniać nasze ustalenia i tworzyć różne raporty przy użyciu większej liczby zapytań.
Kroki
Zasadniczo istnieją 4 kroki, a kolejność tych 4 kroków w dodatku Power Query jest następująca:
- Połącz: Najpierw łączymy się z danymi, które mogą znajdować się gdzieś, w chmurze, w usłudze lub lokalnie.
- Transform: Drugim krokiem byłaby zmiana kształtu danych zgodnie z wymaganiami użytkownika.
- Połącz: na tym etapie wykonujemy kilka kroków transformacji i agregacji oraz łączymy dane z obu źródeł w celu utworzenia połączonego raportu.
- Zarządzaj: To scala i dołącza kolumny w zapytaniu z pasującymi kolumnami w innych zapytaniach w skoroszycie.
Dodatek Power Query do programu Excel udostępnia wiele niezwykle zaawansowanych funkcji.
Załóżmy, że mamy dane dotyczące zakupów z ostatnich 15 lat w 180 plikach. Teraz zarządzanie organizacją wymagałoby konsolidacji danych przed ich analizą. Kierownictwo może przyjąć jedną z następujących metod:
- Otwierali wszystkie pliki i kopiowali je i wklejali do jednego pliku.
- Z drugiej strony mogą skorzystać z mądrego rozwiązania, jakim jest stosowanie formuł, gdyż jest ono podatne na błędy.
Niezależnie od wybranej metody, wiąże się to z dużą ilością ręcznej pracy, a po kilku miesiącach pojawiłyby się nowe dane o sprzedaży dla dodanego czasu. Będą musieli ponownie wykonać to samo ćwiczenie.
Jednak dodatek Power Query może pomóc im nie wykonywać tej żmudnej i powtarzalnej pracy. Zrozummy to zapytanie dotyczące programu Excel na przykładzie.
Przykład
Załóżmy, że mamy pliki tekstowe w folderze z danymi sprzedaży i chcemy uzyskać te dane w naszym pliku Excel.
Jak widać na poniższym obrazku, w folderze mamy dwa typy plików, ale chcemy uzyskać dane tylko plików tekstowych w pliku excel.
Aby zrobić to samo, kroki będą następujące:
Krok 1: Najpierw musimy pobrać dane z dodatku Power Query, abyśmy mogli wprowadzić wymagane zmiany w danych, aby zaimportować je do pliku programu Excel.
W tym celu wybieramy opcję „Z folderu” z menu „Z pliku” po kliknięciu na polecenie „Pobierz dane” z grupy „Pobierz i przekształć” w zakładce „Dane” .
Krok 2: Wybierz lokalizację folderu, przeglądając.
Kliknij „OK”
Krok 3: Otworzy się okno dialogowe zawierające listę wszystkich plików w wybranym folderze z nagłówkami kolumn: „Zawartość”, „Nazwa”, „Rozszerzenie”, „Data dostępu”, „Data modyfikacji”, „Data utworzenia”, „Atrybuty” i „Ścieżka folderu”.
Dostępne są 3 opcje, tj. Połącz , Załaduj i Przekształć dane .
- Połącz : Ta opcja służy do przejścia do ekranu, na którym możemy wybrać dane do połączenia. Krok edycji jest pomijany dla tej opcji i nie daje nam żadnej kontroli nad tym, które pliki połączyć. Funkcja Połącz powoduje konsolidację każdego pliku w folderze, co może prowadzić do błędów.
- Załaduj: Ta opcja po prostu załaduje tabelę, jak pokazano powyżej na obrazku, do arkusza programu Excel zamiast rzeczywistych danych w plikach.
- Przekształć dane: W przeciwieństwie do polecenia „Połącz” , jeśli użyjemy tego polecenia, możemy wybrać, które pliki mają zostać połączone, tj. Możemy połączyć tylko jeden typ pliku (to samo rozszerzenie).
Tak jak w naszym przypadku, chcemy łączyć tylko pliki tekstowe (.txt); wybierzemy polecenie „Przekształć dane” .
Po prawej stronie okna widzimy „Zastosowane kroki”. Na razie wystarczy wykonać jeden krok, czyli pobrać szczegóły plików z folderu.
Krok 4: Istnieje kolumna o nazwie „Rozszerzenie”, w której widzimy, że wartości w kolumnie są zapisane w obu przypadkach, tj. Wielkie i małe litery.
Musimy jednak zamienić wszystkie wartości na małe litery, ponieważ filtr rozróżnia oba. Aby zrobić to samo, musimy zaznaczyć kolumnę, a następnie wybrać „Małe litery” z menu polecenia „Format” .
Krok 5: Będziemy filtrować dane za pomocą kolumny „Rozszerzenie” dla plików tekstowych.
Krok 6: Musimy teraz połączyć dane dla obu plików tekstowych przy użyciu pierwszej kolumny „Treść”. Klikniemy na ikonę umieszczoną po prawej stronie nazwy kolumny.
Krok 7: Otworzy się okno dialogowe oznaczone jako „Połącz pliki” , w którym musimy wybrać separator jako „Tab” dla plików tekstowych (pliki z rozszerzeniem „.txt”) i możemy wybrać podstawę do wykrywania typu danych. I kliknij „OK”.
Po kliknięciu „OK” , w oknie „Power Query” otrzymamy połączone dane plików tekstowych .
W razie potrzeby możemy zmienić typ danych kolumn. W kolumnie „Przychody” zmienimy typ danych na „Waluta”.
Możemy zobaczyć kroki zastosowane do danych za pomocą zapytania zasilania po prawej stronie okna.
Po wprowadzeniu wszystkich wymaganych zmian w danych, możemy załadować dane do arkusza programu Excel za pomocą polecenia „Zamknij i wczytaj do ” w grupie „Zamknij” w zakładce „Strona główna” .
Musimy wybrać, czy chcemy załadować dane jako tabelę czy połączenie. Następnie kliknij „OK”.
Teraz możemy zobaczyć dane jako tabelę w arkuszu.
I okienko „Zapytania ze skoroszytu” po prawej stronie, którego możemy używać do edycji, powielania, scalania, dołączania zapytań i do wielu innych celów.
Dodatek Power Query do programu Excel jest bardzo przydatny, ponieważ widzimy, że w ciągu kilku minut załadowano 601 612 wierszy.
Rzeczy do zapamiętania
- Dodatek Power Query nie zmienia oryginalnych danych źródłowych. Zamiast zmieniać oryginalne dane źródłowe, rejestruje każdy krok wykonywany przez użytkownika podczas łączenia lub przekształcania danych, a gdy użytkownik zakończy kształtowanie danych, pobiera dopracowany zestaw danych i przenosi go do skoroszytu.
- W dodatku Power Query jest rozróżniana wielkość liter.
- Konsolidując pliki w podanym folderze musimy upewnić się, że korzystając z kolumny „Rozszerzenie” musimy wykluczyć pliki tymczasowe (posiadające rozszerzenie „.tmp”, a nazwa tych plików zaczyna się od znaku „~”) jako Dodatek Power Query może również importować te pliki.
