Power Query: The 2021 Definitive Beginners’ Guide

Hi, welcome to the beginners’ guide to Power Query by UpSlide.
zastanawiałeś się kiedyś czym jest Power Query? Jak to działa? Jeśli tak, to jesteś we właściwym miejscu!
Paulina, nasz ekspert Power Query z Finance 3.1, dała nam wszystkie swoje najlepsze wskazówki i rady, aby napisać ten artykuł.
tutaj nauczysz się podstaw oprogramowania, a nawet trochę poćwiczysz! Gotowy?

jeśli nie masz czasu, aby go teraz przeczytać, Pobierz wersję PDF i ciesz się nią później!

co to jest Power Query? Kiedy należy go używać?

Power Query to dodatek do programu Excel, który umożliwia użytkownikom importowanie, czyszczenie i konsolidację danych. Możesz użyć Power Query, gdy:

• przetwarzanie dużych ilości danych (gdzie Excel jest ograniczony do miliona wierszy)
• Importowanie danych z różnych źródeł (np. Excel, CSV, SharePoint, SQL i Salesforce)
• manipulowanie, czyszczenie i łączenie kilku tabel, plików, a nawet folderów!

Power Query to przyjazne dla użytkownika i potężne narzędzie nawet dla początkujących, ponieważ nie potrzebujesz doświadczenia w kodowaniu, aby rozpocząć. Co więcej, nie zmienia to oryginalnego źródła danych. Wynik jest jak migawka przekształconych danych wniesionych do Excela. Co w tym takiego wspaniałego? Cóż, ponieważ dane źródłowe nie są bezpośrednio importowane, nie obciążają one pliku Excel (tzn. nie spowalniają komputera!).

Podsumowując, Power Query jest narzędziem do analizy danych, dzięki czemu procesy ETL (Extract-Transform-Load) są zarówno prostsze, jak i skuteczniejsze.

Jaka jest różnica między zapytaniem o moc a Pivotem mocy?

Power Pivot jest często kojarzony z Power Query, ponieważ jest to Dodatek pakietu Microsoft Office, który ułatwia analizę danych. Co ciekawe, te dwa narzędzia mogą ze sobą współpracować! Power Pivot to po prostu kolejny krok transformacji danych: pozwala użytkownikom na tworzenie modeli danych z tabel wcześniej oczyszczonych przez Power Query.

chociaż podobne, Power Pivot jest bardziej-więc wykorzystywane do analizy danych, obliczeń związanych z czasem i generowania linków. Po prostu powiedział:

• Power Query pozwala na automatyczną konsolidację i ponowne przetwarzanie plików (np. standaryzację procesów raportowania lub konsolidację kilku plików o tym samym formacie).
• Power Pivot pozwala analizować bazy danych kilku milionów linii poprzez wyodrębnianie zagregowanych informacji zgodnie z regułami ustalonymi przez firmę.

jaka jest różnica między Power Query A Power BI?

podstawowa technologia zapytań Power Query jest również zawarta w usłudze Power BI.

interfejs oprogramowania Power BI Desktop pozwala korzystać z trzech technologii (Power Query, Power Pivot i Power View). Dzięki usłudze Power BI możesz wykonywać wszystkie etapy łańcucha analizy danych, od wyodrębniania danych po tworzenie wizualizacji na interaktywnym pulpicie nawigacyjnym, który możesz udostępniać i analizować.

> przeczytaj nasz artykuł o tym, jak eksportować Power BI do Excela

dlaczego Power Query jest świetną alternatywą dla Excela VBA?

Power Query może łatwo zastąpić VBA (Visual Basic for Applications), ponieważ umożliwia:

• przetwarzaj tabele po prostu klikając przyciski. Brak umiejętności kodowania potrzebne!
• wizualizuj swoje operacje krok po kroku bez uruchamiania pojedynczego makra.
• łatwo edytuj kolejność kroków różnych kroków poprzez przeciąganie i upuszczanie! Możesz także dodawać i usuwać niepotrzebne kroki za pomocą jednego kliknięcia.

(zobacz część 2-Sekcja 3: Jak rozpocząć pracę z zapytaniem o moc?)

jednak Power Query nie może zastąpić VBA, jeśli chodzi o interakcję z interfejsem Excela, automatyzację procesów, generowanie nowych plików, wysyłanie wiadomości e-mail itp.

czy Microsoft Power Query jest darmowy? Czy jest pytanie o moc dla komputerów Mac?

tak. Power Query jest darmowym rozszerzeniem programu Excel. To kolejna główna zaleta dodatku.
(przeczytaj następne pytanie, aby dowiedzieć się, jak można go uzyskać na pulpicie programu Excel)

Zapytanie o moc dla komputerów Mac? Tak, ważna wiadomość: Edytor zapytań jest teraz kompatybilny z programem Excel dla komputerów Mac! Ale integracja jest w tej chwili dość ograniczona, ponieważ można tylko zmienić ścieżkę pliku i odświeżyć istniejące zapytania.

część 2: Rozpocznij z zapytaniem o moc!

jak pobrać Zapytanie o moc?

Power Query jest dostępny dla programu Excel od 2010 roku.

oto jak dodać Zapytanie o moc zgodnie z wersją Excela*:

• dobra wiadomość, nie musisz pobierać zapytania o moc dla programu Excel 2019, 2016 ani w Office 365. Można go znaleźć na wstążce „dane” pod przyciskiem „Pobierz & Przekształć”.
• w przypadku programów Excel 2013 i 2010 należy pobrać dodatek Power Query. Pojawi się w nowej dedykowanej karcie o nazwie „Power Query” (w programie Excel 2010 musisz mieć licencję Professional Plus z licencją Software Assurance).

*oto jak sprawdzić wersję programu Excel:

przejdź do pliku > konto > o programie Excel. Następnie pojawi się okno. Przeczytaj dwie pierwsze liczby w nawiasie. W poniższym przykładzie wersja programu Excel to 2016.

aby dowiedzieć się więcej, oto kolejny szczegółowy artykuł na temat instalacji i aktywacji Power Query w programie Excel.

Jak aktywować Power Query w Excelu?

w zależności od wersji programu Excel (patrz powyższe pytanie: Gdzie jest Power Query w programie Excel?) wystarczy kliknąć przycisk o nazwie „Pobierz dane” w sekcji „uzyskaj & Przekształć” w karcie „Dane” dla programu Excel 2016 i innych. W przypadku wersji programu Excel sprzed 2013, po prostu przejdź do wstążki Power Query.

należy pamiętać, że po wejściu do interfejsu zapytania o moc nie będzie już można wchodzić w interakcje z programem Excel. Jeśli chcesz wrócić do programu Excel, musisz zamknąć Edytor zapytań o moc.

jak działa Power Query?

w programie Excel dostęp do następujących funkcjonalności masz tylko po kliknięciu na ” zapytania & „:

wstążka danych: aby wyszukać źródła danych (wstążka „Power Query” dla Excela 2010 i 2013).

zapytania & panel połączeń: aby szybko sprawdzić wszystkie połączenia (zapytania) wykonane.

po uruchomieniu Power Query, lądujesz na edytorze Power Query, który składa się z 4 odrębnych elementów:

główna wstążka: umożliwia tworzenie zapytań od podstaw dzięki swoim przyciskom (jeśli znasz język M, Możesz również uzyskać dostęp do zaawansowanego edytora, aby dostosować je ręcznie).

zapytania: aby śledzić wszystkie połączenia ze źródłami danych i strumieniami.

podgląd tabeli danych: aby zwizualizować wynik danych po określonym kroku.

Ustawienia zapytań( zastosowane kroki): aby przejść przez wszystkie wykonane kroki. Power Query automatycznie rejestruje wszystkie dokonane przekształcenia.

• w sekcji 4 dowiesz się, jak działa Power Query, przekształcając tabelę danych.
• w sekcji 5 robi się jeszcze ciekawiej, ponieważ dowiesz się, jak postępować z danymi z różnych źródeł i łączyć zapytania.

jak zacząć z zapytaniem o moc?

wyobraź sobie następującą sytuację:

jesteś kontrolerem finansowym firmy technologicznej sprzedającej Komputery i smartfony. Obecnie działasz w 3 krajach: Francji, WIELKIEJ BRYTANII i Chinach. Każdego miesiąca tworzysz skonsolidowany raport dla swojego menedżera, zawierający kluczowe wskaźniki efektywności, takie jak całkowity przychód, przychód według kraju lub przychód według produktu. Aby to zrobić, każdy kierownik kraju – Dennis, Douglas i Karen-wysyła Ci plik.

Problem: każdy plik zawiera listę sprzedaży w nominale własnym kraju. Przeliczanie ich na Euro jest zbyt czasochłonne, a następnie konsolidacja ich w sprawozdaniu końcowym.

co by było, gdybyś mógł zautomatyzować proces i obliczyć raport w ciągu kilku minut, używając tylko Power Query?

czy to nie byłoby super? Cóż, z zapytaniem o moc, możesz! Zacznijmy od przygotowania pierwszej tabeli 🙂

najpierw potrzebujesz danych. Pobierz nasz przykład Power Query i zapisz go na swoim lokalnym serwerze.

następnie wykonaj następujące proste kroki:

• rozpakuj plik i zapisz go na lokalnym serwerze.
• teraz otwórz nowy arkusz w programie Excel i przejdź do karta Dane > Pobierz dane > z pliku > ze skoroszytu, jak pokazano poniżej.

(jeśli masz program Excel w wersji 2013 lub 2010, po prostu przejdź do karty Power Query)

• przejdź do ścieżki, w której zapisałeś właśnie pobrane rozpakowane pliki i wybierz plik Excel o nazwie „kursy wymiany”.

• kliknij Przekształć dane.

być może zauważyłeś, że niektóre zastosowane kroki pojawiły się już w okienku ustawień zapytań. Są one wykonywane automatycznie.

teraz poćwiczmy trochę, zaczynając od czyszczenia tabeli danych:

• usuń górne 3 wiersze, przejdź do Usuń wiersze > usuń górne wiersze i wpisz „3”. Natychmiast zobaczysz transformację na podglądzie tabeli danych, a także w zastosowanych krokach (Ustawienia zapytań).

• pozbądź się pustych wierszy: Usuń wiersze > Usuń puste wiersze (jak pokazano poniżej).

• Promuj swój pierwszy wiersz jako nagłówek: kliknij „użyj pierwszego wiersza jako nagłówków”. Jak być może zauważyłeś, Power Query automatycznie dodał krok w celu zmiany typu niektórych danych (wartości w pierwszej kolumnie są teraz sformatowane jako daty).

• Usuń puste kolumny: kliknij „Wybierz kolumny” i odznacz kolumny 7, 8, 9 i 10.

dobrze ci idzie! Jeszcze trzy kroki i wszystko gotowe!

• : Wybierz kolumnę „kurs wymiany”, a następnie na karcie Przekształć przejdź do kolumny Unpivot > Unpivot inne kolumny (możesz również wybrać pięć innych kolumn i wybrać „kolumny Unpivot”).

• Zmień nazwę kolumn: kliknij komórki nagłówka, wprowadź „datę” w pierwszej, „walutę” w drugiej i „kurs” w trzeciej.

• Zastosuj filtr: podobnie jak w programie Excel, kliknij menu rozwijane kolumny ” Data „i wybierz”Dec-18”.

• na karcie Strona główna kliknij Zamknij i załaduj, aby załadować tabelę końcową w nowym arkuszu.

Dobra robota! Ukształtowałeś swoją pierwszą tabelę danych.

przejdźmy dalej Z Power Query

(Re)wyobraź sobie następującą sytuację:

dzięki swoim umiejętnościom Power Query jesteś obecnie starszym kontrolerem finansowym firmy technologicznej. Teraz musisz skonsolidować dane z grudnia 2018 r., które wysłali Ci menedżerowie krajów.

Problem: każdy plik zawiera listę sprzedaży w nominale własnym kraju. Przeliczanie ich na Euro jest czasochłonne, a tym bardziej konsolidacja ich w sprawozdaniu końcowym.

co, jeśli można zautomatyzować proces i obliczyć raport w ciągu kilku minut tylko za pomocą Power Query?

teraz zobaczymy, jak skonsolidować różne źródła danych i połączyć je z tabelą, którą przygotowaliśmy w poprzedniej sekcji.

po pierwsze, zaimportujmy cały folder, w tym trzy różne pliki źródłowe:

• jeśli nadal jesteś w Power Query przejdź do folderu New source > File >.

(jeśli z powrotem w programie Excel, przejdź do Pobierz dane > z folderu)

• Wybierz folder „sales_201812”, który wcześniej pobrałeś i zapisałeś. Następnie kliknij OK.

powinno pojawić się nowe okno.

następnie musisz scalić zapytania:

• wybierz „Połącz & Edytuj” w rozwijanym menu (u dołu nowego okna).

• Wybierz przykładową tabelę „Dec-18” (należy pamiętać, że wszystkie arkusze muszą być nazwane w ten sam sposób).

• w pierwszej kolumnie możesz sprawdzić, czy wszystkie źródła są uwzględnione (kliknij menu rozwijane), a następnie kliknij OK.

jesteś teraz w Edytorze zapytań o moc.

• przejdź do zakładki Przekształć > Zamień wartości.

• teraz zastąp”€”na ” EUR”.

• przejdź do zakładki Strona główna >Scalanie zapytań.

pojawi się nowe okno:

1. wybierz kolumnę „Waluta”.
2. Wybierz plik „kursy walut” z rozwijanego menu.
3. wybierz kolumnę „Waluta”.
4. kliknij OK.

• teraz musisz wyświetlić dane o kursie wymiany, które właśnie pobrałeś:
  1. wybierz kolumnę „kurs wymiany”.
  2. kliknij przycisk Rozwiń.
  3. Sprawdź tylko „Oceń”.
  4. odznacz ” Użyj oryginału…”.
  5. kliknij OK.

Gratulacje! Po prostu załadowałeś inne źródło danych, połączyłeś zapytania i uzyskałeś odpowiedni kurs wymiany. Koniec z VLOOKUPEM lub indeksem + mecz!

aby zakończyć, utworzymy kolumnę „Cena jednostkowa EUR”, aby przeliczyć wszystkie ceny na Euro:

• przejdź do zakładki Dodaj kolumnę > Niestandardowa Kolumna .

• pojawi się nowe okno. Napiszmy wzór ceny w Euro:
  1. Nazwij to „cena jednostkowa EUR”.
  2. Kliknij dwukrotnie” Cena jednostkowa lokalna waluta ” (lub wybierz ją, a następnie kliknij Wstaw).
  3. wpisz „/”.
  4. Kliknij dwukrotnie „Oceń”.
  5. kliknij OK.

• gdy pojawi się kolumna, kliknij na jej symbol typu (tuż przed nazwą) i wybierz „Liczba dziesiętna”.

musimy zrobić to samo, aby wygenerować kolumnę „Revenue”:

• przejdź do zakładki Dodaj kolumnę > Niestandardowa Kolumna .
• pojawi się nowe okno. Wpisz wzór przychodu:
  1. nazwij go „przychodem”.
  2. Kliknij dwukrotnie” Cena jednostkowa EUR ” (lub wybierz ją, a następnie kliknij Wstaw).
  3. wpisz „*”.
  4. Kliknij dwukrotnie na „Ilość”.
  5. kliknij OK.

• ponownie zmień typ kolumny na”Liczba dziesiętna”.

aby zakończyć, wyeksportujmy go do nowej tabeli danych w programie Excel!

• przejdź do zakładki Strona główna > Zamknij & załaduj > Zamknij & załaduj.

teraz możesz w pełni korzystać z funkcji programu Excel do analizy danych, tworzenia tabeli przestawnej itp.

Wow! Jeśli dotarłeś do końca, gratulacje! Teraz wiesz, jak działa Power Query.

teraz twoja kolej!

chcę ci to przekazać: czy zrozumiałeś wszystkie powyższe pytania dotyczące zapytania o moc?
czy pobrałeś nasze pliki z samouczkami i postępowałeś zgodnie z powyższymi wskazówkami, aby wypróbować je na własną rękę?
daj nam znać, zostawiając szybki komentarz poniżej już teraz!

aby dowiedzieć się więcej o zapytaniu o moc: