Jak korzystać z dodatku Power Query w programie Excel

Microsoft Excel oferuje kilka narzędzi i funkcji do manipulowania danymi, a Power Query jest jednym z najlepszych. To narzędzie do analizy biznesowej pozwala importować dane z różnych źródeł oraz w razie potrzeby łatwo je przekształcać i manipulować w programie Excel. Zasadniczo eliminuje powtarzalne zadania i może pomóc zmniejszyć wysiłek i zaoszczędzić czas.

Główną zaletą dodatku Power Query jest to, że do korzystania z niego nie jest wymagana żadna specjalistyczna wiedza ani wiedza z zakresu kodowania. Przyjrzyjmy się, jak można go używać do manipulowania danymi w programie Microsoft Excel.

Dostęp do dodatku Power Query

Dodatek Power Query jest dostępny we wszystkich wersjach programu Microsoft Excel, począwszy od programu Excel 2010. Od programu Excel 2016 został on wbudowany bezpośrednio w aplikację.

W programie Excel 2016 i nowszych

Uruchom nowy arkusz programu Excel i kliknij zakładkę „Dane” na pasku menu.

Z opcji na karcie „Dane” kliknij opcję „Pobierz dane” w lewym górnym rogu pod paskiem menu.

Zawiera wszystkie narzędzia i opcje dodatku Power Query służące do importowania i przekształcania danych.

W Excelu 2013 i 2010

W przypadku wersji programu Excel 2013 i 2010 dodatek Power Query jest dostępny jako bezpłatny dodatek, który można pobrać z witryny internetowej firmy Microsoft.

Przejdź do strony pobierania dodatku Power Query i kliknij przycisk „Pobierz”, aby rozpocząć pobieranie narzędzia.

Po kliknięciu przycisku „Pobierz” pojawi się kilka opcji, spośród których możesz wybrać odpowiednią w zależności od systemu.

Po wybraniu odpowiedniej opcji kliknij przycisk „Pobierz”, aby pobrać narzędzie.

Korzystanie z narzędzia Power Query

Po otwarciu arkusza programu Excel dostęp do narzędzia Power Query można uzyskać z karty „Dane”, a następnie opcji „Pobierz dane”.

Importowanie danych

Po kliknięciu opcji „Pobierz dane” zostaną wyświetlone różne źródła, z których można importować dane. Należą do nich skoroszyty programu Excel, pliki tekstowe lub CSV, pliki XML i JSON . Oprócz tego możesz importować dane z internetowych baz danych, takich jak między innymi SQL Server i Microsoft Access. Inne źródła, z których możesz importować dane, to Microsoft Azure i usługi online, takie jak Salesforce i Facebook.
Aby zaimportować dane, kliknij dowolną opcję, np. „Z pliku”, „Z bazy danych”, „Z platformy Azure”, „Z usług online” lub „Z innych źródeł”.

Podczas importowania danych Excel wyświetli wyskakujące okienko z podglądem danych, które zostaną załadowane. Kliknij przycisk „Załaduj” na dole, aby zakończyć importowanie danych.

Teraz zobaczysz dane w arkuszu programu Excel i możesz zastosować do nich różne przekształcenia.

Składniki edytora Power Query

Aby w razie potrzeby przekształcić zaimportowane dane, potrzebujesz edytora Power Query. Po kliknięciu przycisku „Pobierz dane” kliknij opcję „Uruchom edytor Power Query”.

Spowoduje to uruchomienie „Edytora Power Query”, który składa się z sześciu głównych komponentów. U góry znajdziesz „Wstążkę Edytora zapytań”, która zawiera różne polecenia w różnych zakładkach.

Poniżej „Wstążki Edytora zapytań” po lewej stronie znajduje się „Lista zapytań”, która pokazuje wszystkie zapytania ze skoroszytu. W środku będzie także sekcja „Podgląd danych”, która pokaże wszystkie przekształcenia zastosowane do danych.

„Pasek formuły” umożliwia edycję kodu M kroku transformacji. Wszystkie transformacje są rejestrowane i pojawiają się jako kroki w obszarze „Zastosowane kroki”.

Sekcja „Właściwości” umożliwia nadanie zapytaniom nazw.

Stosowanie przekształceń

Do danych zaimportowanych w edytorze Power Query można zastosować różne przekształcenia. Należą do nich tworzenie tekstu, przycinanie, transpozycja i inne.

Transformacje tekstu

Tekst można przekształcić na wielkie lub małe litery po zaimportowaniu go do Edytora.

W edytorze Power Query przejdź do karty „Przekształć” u góry, a zobaczysz kilka opcji, takich jak „Transpozycja”, „Zamień wartości” itp.

Opcja „Format” znajduje się pośrodku, obok opcji „Podziel kolumnę”. Kliknij go, aby wyświetlić dostępne opcje formatowania.

Kliknij dowolną opcję, np. „małe litery” lub „WIELKIE”, aby przekształcić tekst w wybranej kolumnie na małe lub wielkie litery. Podobnie kliknięcie innych opcji spowoduje odpowiednie przekształcenie tekstu.

Opcja „Format” umożliwia także usunięcie wszystkich białych znaków za pomocą opcji „Przytnij”. Kliknięcie przycisku „Przytnij” spowoduje usunięcie wszystkich dodatkowych białych znaków z tekstu.

Dzielenie kolumn

Oprócz przekształcania tekstu Edytor Power Query umożliwia dzielenie kolumn na różne sposoby.

Po zaimportowaniu danych do edytora Power Query kliknij nagłówek kolumny, aby zaznaczyć całą kolumnę.

Następnie kliknij przycisk „Podziel kolumnę” po lewej stronie przycisku „Formatuj”. Otrzymasz listę opcji, które pozwalają podzielić wybraną kolumnę na różne sposoby.

Aby podzielić kolumnę według ograniczników, kliknij odpowiednią opcję. Spowoduje to wyświetlenie wyskakującego okienka podziału według ogranicznika, w którym możesz wybrać ogranicznik, taki jak przecinek, dwukropek, znak równości itp.

Kliknij przycisk „OK”, aby dowolnie podzielić kolumnę. Zobaczysz, że kolumna została podzielona.

Transpozycja danych

Dzięki opcji „Transpozycja” użytkownicy mogą przełączać dane z wierszy do kolumn i odwrotnie. Aby to zrobić, najpierw zaimportuj dane do edytora Power Query, jak wyjaśniono wcześniej.

Po załadowaniu danych przejdź do zakładki „Przekształć” na górze, gdzie znajdziesz opcję „Transponuj”.

Kliknij opcję „Transponuj”, aby przekonwertować wiersze na kolumny.

Łączenie zapytań

Dodatek Power Query umożliwia łatwe łączenie wielu zestawów danych przy użyciu opcji „Scal” i „Dołącz”.

Korzystanie z opcji scalania

Operacja Scal umożliwia utworzenie nowego zapytania poprzez połączenie istniejących zapytań.

Najpierw zaimportuj dane do arkusza programu Excel z pliku, bazy danych lub innych źródeł. W takim przypadku nie trzeba ładować danych do edytora Power Query, ale konieczne będzie zaimportowanie wielu zestawów danych.

Pod opcjami importowania danych zobaczysz inną opcję „Połącz zapytania”. Wskaż kursorem tę opcję, a dostępne będą dwie opcje – Dołącz i Scal.

Kliknięcie przycisku „Scal” wyświetli nowe wyskakujące okienko, w którym możesz wybrać zbiory danych, które mają zostać scalone.

Wybranie zestawów danych spowoduje wyświetlenie podglądu. W lewym dolnym rogu możesz wybrać sposób scalania zbiorów danych, zanim klikniesz przycisk „OK”.

Korzystanie z opcji dołączania

Opcja „Dołącz” umożliwia utworzenie nowej tabeli poprzez połączenie wierszy poprzednich zapytań.

Wykonaj tę samą procedurę, co powyżej, aby dodać zestawy danych do arkusza Excel, a następnie przejdź do opcji „Dołącz” w sekcji „Połącz zapytania”.

W wyskakującym okienku wybierz tabele, dla których chcesz połączyć dane, a następnie kliknij przycisk „OK”. Użytkownicy mogą łączyć dane z dwóch tabel lub z trzech lub więcej tabel.

Połączone dane pojawią się w oknie Edytora Power Query, skąd możesz je zaimportować do arkusza za pomocą przycisku „Zamknij i załaduj” znajdującego się w lewym górnym rogu.

Ładowanie danych do arkusza

Po zakończeniu wszystkich operacji w edytorze Power Query konieczne będzie załadowanie danych do arkusza programu Excel.

Istnieje kilka sposobów załadowania przekształconych danych do arkusza programu Excel, na przykład do wykresu przestawnego, tabeli przestawnej, tabeli lub połączenia dla zapytania. Kliknij opcję „Zamknij i załaduj” w lewym górnym rogu, a zobaczysz dwie opcje – „Zamknij i załaduj” oraz „Zamknij i załaduj do”.

Kliknięcie drugiej opcji wyświetli różne opcje ładowania danych do arkusza.

Excel umożliwia wybranie lokalizacji, np. komórki w istniejącym arkuszu lub nowego arkusza, który zostanie utworzony automatycznie. Dostępna jest również opcja „Dodaj te dane do modelu danych”.

Korzystanie z formuł i funkcji

Power Query umożliwia także korzystanie z formuł i funkcji podobnych do arkuszy programu Excel. Wymaga to dodania niestandardowych kolumn, w których można dodawać formuły i funkcje.

Uruchom edytor Power Query z karty „Pobierz dane” i przejdź do karty „Dodaj kolumnę” u góry.

Po lewej stronie zobaczysz swoje zapytania. Wybierz jedną, klikając ją, a „Kolumna niestandardowa” stanie się aktywna. Utwórz nową kolumnę, klikając opcję „Kolumna niestandardowa”.

W oknie dialogowym tworzenia kolumny niestandardowej podaj nazwę kolumny.

W sekcji „Niestandardowa formuła kolumny” dodaj formułę służącą do tworzenia kolumny. Na przykład użyj formuły takiej jak [First Name]&""&[Last Name]. Edytor Power Query sprawdzi, czy w formule nie występują błędy.

Jeśli nie ma żadnych błędów, kliknij przycisk „OK”, a edytor utworzy kolumnę.

Aby skorzystać z funkcji, powtarzaj kroki, aż pojawi się wyskakujące okienko „Kolumna niestandardowa”. W sekcji „Niestandardowa formuła kolumny” dodaj funkcję, taką jak Text.Upper([Full Name]), która spowoduje utworzenie wszystkich nazw pisanych wielkimi literami.

Aby zakończyć dodawanie kolumny, kliknij przycisk „OK”, aby utworzyć kolumnę z nazwami pisanymi wielkimi literami.

To wszystko, co musisz wiedzieć, aby rozpocząć korzystanie z dodatku Power Query. Narzędzie to sprawia, że niezwykle łatwo jest przekształcać dane w programie Microsoft Excel według potrzeb, dzięki czemu można analizować i wyciągać wnioski przy minimalnym wysiłku. Można go używać do łączenia różnych zbiorów danych, zmiany ich formatowania i wykonywania innych czynności. W edytorze możesz nawet używać funkcji i formuł programu Excel, co czyni go jeszcze bardziej użytecznym.