Bezpłatny kurs Power Query: pierwsze kroki w Excelu i łączenie plików z folderu

W skróciePower Query w Excelu otwierasz z karty Dane, a jego najmocniejszy pierwszy krok to połączenie wszystkich plików z folderu w jedną tabelę. Pokazujemy, gdzie jest edytor, z czego składa się jego okno i co robią zapytania pomocnicze, które Excel tworzy przy łączeniu plików.

Gdzie jest Power Query w Excelu i jak zacząć

W Excelu dla Windows (Microsoft 365, 2021, 2019, 2016) Power Query siedzi na karcie Dane. Cała praca zaczyna się od przycisku Pobierz dane, który rozwija listę źródeł pogrupowanych w kategorie: pliki, bazy danych, Azure, Fabric i Power Platform, usługi online i inne źródła. Zanim zaczniesz, utwórz nowy, pusty skoroszyt i zapisz go, na przykład jako Nordvella_raport_sprzedazy.xlsx w folderze z danymi.

Na dole tej samej listy są trzy pozycje, do których jeszcze wrócimy: Uruchom edytora dodatku Power Query otwiera edytor bez tworzenia nowego zapytania (to samo robi skrót Alt+F12), Ustawienia źródeł danych pozwala zmienić ścieżkę albo poświadczenia źródła, a Opcje dodatku Query to ustawienia Power Query, w tym poziomy prywatności, o których piszemy w lekcji o błędach.

Jeśli dane już leżą w otwartym skoroszycie, nie musisz ich importować z pliku. Kliknij dowolną komórkę tabeli i na karcie Dane wybierz Z tabeli/zakresu. Zwykły zakres Excel najpierw zamieni na tabelę, a potem otworzy go w edytorze. Takie zapytanie odczytuje dane funkcją Excel.CurrentWorkbook, więc każda zmiana w tabeli trafi do wyniku po odświeżeniu. To wygodny sposób na słowniki i parametry, które użytkownicy mają edytować w arkuszu.

Na samej górze listy Pobierz dane Microsoft testuje nowe okno wyboru źródła, oznaczone jako Pobierz dane (wersja zapoznawcza). Prowadzi do tych samych łączników, tylko z wyszukiwarką i kategoriami w jednym oknie. W tym kursie korzystamy z klasycznych menu, bo są dostępne w każdej wersji Excela od 2016.

Interfejs edytora Power Query

Edytor Power Query to osobne okno, które otwiera się nad Excelem. Do tych samych pięciu obszarów będziemy wracać w każdym kroku, więc najpierw krótka mapa. Poniżej edytor tuż po połączeniu plików z folderu, który za chwilę zbudujemy.

1. Wstążka ma cztery karty. Strona główna zawiera najczęstsze operacje (usuwanie wierszy i kolumn, scalanie, dołączanie, nowe źródło, zamknięcie i załadowanie). Przekształć zmienia istniejące kolumny w miejscu. Dodaj kolumnę zostawia oryginał i dokłada nową kolumnę z wynikiem. Widok włącza profilowanie danych, edytor zaawansowany i zależności zapytań.
2. Panel Zapytania pokazuje wszystkie zapytania w skoroszycie, pogrupowane w foldery. Liczba w nawiasie przy nagłówku to liczba zapytań.
3. Pasek formuły pokazuje kod M zaznaczonego kroku. Jeśli go nie widzisz, włącz go na karcie Widok polem Pasek formuły.
4. Podgląd danych to pierwsze wiersze wyniku. Ikona przy nazwie kolumny pokazuje typ danych (ABC to tekst, 123 to liczba całkowita, 1.2 to liczba dziesiętna, kalendarz to data), a strzałka obok nazwy otwiera sortowanie i filtry.
5. Ustawienia zapytania zawierają nazwę zapytania i listę Zastosowane kroki. Kliknięcie kroku pokazuje dane w tym miejscu przepisu, krzyżyk przy kroku go usuwa, a ikona koła zębatego otwiera okno z ustawieniami kroku. Prawy przycisk na kroku daje dalsze polecenia: Wstaw krok po (nowa operacja w środku przepisu), Przenieś przed i Przenieś po (zmiana kolejności, możliwa też przeciąganiem) oraz Usuwaj do końca, który kasuje zaznaczony krok i wszystkie po nim.

Połącz wszystkie pliki z folderu w jedną tabelę

Zamiast importować każdy miesięczny plik osobno, każemy Power Query wczytać cały folder. To najważniejsza funkcja dla każdego, kto co miesiąc dostaje plik o tej samej strukturze: raz zbudowane zapytanie obejmie także pliki, które dopiero się pojawią.

1. Kliknij Dane > Pobierz dane > Z pliku > Z folderu. W oknie Przeglądaj wskaż folder C:\Dane\Nordvella\Sprzedaz_miesieczna (możesz wkleić ścieżkę w pole Nazwa folderu) i kliknij Otwórz.

2. Power Query pokaże listę plików w folderze. To jeszcze nie są dane, tylko informacje o plikach: kolumna Content zawiera zawartość pliku (Binary), a dalej są nazwa, rozszerzenie, daty i ścieżka. Nazwy tych kolumn zawsze są po angielsku, bez względu na język programu.

3. Rozwiń przycisk Połącz i wybierz Łączenie i przekształcanie danych. Pozostałe dwie opcje, Połącz i załaduj oraz Połącz i załaduj do..., ładują wynik od razu, bez otwierania edytora, co przy eksportach z wierszami tytułowymi skończyłoby się bałaganem w arkuszu.

4. Otworzy się okno Połącz pliki. Power Query bierze jeden plik jako wzór (Przykładowy plik: Pierwszy plik) i na nim pokazuje, jak odczyta wszystkie pozostałe. Sprawdź trzy ustawienia: Pochodzenie pliku to kodowanie znaków (dla polskich liter właściwe jest 65001: Unicode (UTF-8)), Ogranicznik to znak rozdzielający kolumny (u nas Średnik), a Wykrywanie typu danych decyduje, na ilu wierszach Power Query zgaduje typy kolumn. Zatwierdź OK.

5. Excel otworzy edytor Power Query. W panelu zapytań pojawi się kilka zapytań, chociaż kliknęliśmy tylko raz. To normalne i warto zrozumieć, skąd się wzięły:

• Przykładowy plik to wzorcowy plik (pierwszy z folderu),
• Parametr1 to parametr, pod który Power Query podstawia kolejne pliki,
• Przekształć przykładowy plik to kroki wykonywane na wzorcowym pliku, i to tu wprowadzimy poprawki,
• Przekształć plik to funkcja (ikona fx) zbudowana automatycznie z poprzedniego zapytania, wywoływana dla każdego pliku z folderu,
• Sprzedaz_miesieczna to zapytanie główne: lista plików, filtr ukrytych plików (krok Filtrowane pliki ukryte1, który odrzuca na przykład pliki tymczasowe), wywołanie funkcji i rozwinięcie wyników w jedną tabelę.

Zasada pracy jest prosta: zmiany, które mają dotyczyć każdego pliku osobno (usunięcie wierszy tytułowych, nagłówki, wiersz Razem), robisz w zapytaniu Przekształć przykładowy plik. Funkcja Przekształć plik przejmie je automatycznie dla wszystkich plików. Zmiany na połączonej tabeli (typy danych, duplikaty, scalanie ze słownikiem) robisz w zapytaniu Sprzedaz_miesieczna.

Ten sam mechanizm działa dla wielu arkuszy w jednym skoroszycie, na przykład gdy każdy oddział ma swoją kartę. W nawigatorze zamiast konkretnego arkusza zaznacz nazwę całego pliku (ikona folderu na górze listy) i kliknij Przekształć dane. Dostaniesz tabelę z listą arkuszy i kolumną Data, w której każdy arkusz jest osobną tabelą. Odfiltruj zbędne arkusze po kolumnie Kind albo Name i rozwiń kolumnę Data.

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

✕