Bezpłatny kurs Power Query: scalanie i dołączanie zapytań - WYSZUKAJ.PIONOWO bez formuł

W skrócieScalanie dokleja kolumny z innej tabeli jak WYSZUKAJ.PIONOWO, a dołączanie dokleja wiersze pod spodem. Łączymy sprzedaż z katalogiem produktów, wybieramy jeden z sześciu rodzajów sprzężenia, korzystamy z dopasowania rozmytego i dołączamy listę nowych sklepów.

Scal zapytania, czyli WYSZUKAJ.PIONOWO w Power Query

Eksport sprzedaży zna tylko kod produktu. Nazwę, kategorię i markę mamy w osobnym skoroszycie Nordvella_slowniki.xlsx. W Excelu ciągnęlibyśmy je funkcją WYSZUKAJ.PIONOWO albo X.WYSZUKAJ, w Power Query robi to scalanie zapytań.

Zaimportuj słowniki

1. Będąc w edytorze, na karcie Strona główna rozwiń Nowe źródło > Plik i wybierz Skoroszyt programu Excel. Wskaż plik Nordvella_slowniki.xlsx.

2. W oknie Nawigator zaznacz pole Wybierz wiele elementów i zaznacz tabele tProdukty oraz tSklepy. Ikona przy nazwie odróżnia tabelę Excela (tProdukty, tSklepy) od całego arkusza (Produkty, Sklepy). Jeśli dane w źródle są sformatowane jako tabela, zawsze wybieraj tabelę: jej granice rosną razem z danymi, a arkusz potrafi wciągnąć puste wiersze i notatki obok danych. Kliknij OK.

Scal sprzedaż z katalogiem produktów

3. Wróć do zapytania Sprzedaz_miesieczna. Na karcie Strona główna kliknij Scal zapytania. Strzałka obok przycisku ma dwie opcje: Scal zapytania dokłada kolumny do bieżącego zapytania, a Scal zapytania jako nowe tworzy nowe zapytanie i zostawia oba źródłowe bez zmian.

4. W oknie Scalanie kliknij nagłówek Kod produktu w górnej tabeli, z listy poniżej wybierz tProdukty i kliknij w niej nagłówek Kod produktu. Power Query od razu policzy, ile wierszy znalazło parę.

Ten komunikat to najszybszy test jakości danych. Gdyby pokazał na przykład 9180 z 9250, wiedziałbyś od razu, że 70 wierszy ma kody, których nie ma w katalogu, i trzeba je wyjaśnić, zanim ktokolwiek zobaczy raport.

5. Rozwiń listę Rodzaj sprzężenia. Domyślne Lewe zewnętrzne jest właściwe w większości przypadków: zostawia wszystkie wiersze sprzedaży i dokłada dane produktu tam, gdzie znalazł parę.

Dwa rodzaje sprzężeń warto zapamiętać szczególnie. Wewnętrzne po cichu wyrzuca wiersze bez pary, więc sprzedaż produktu, którego zapomniano dodać do katalogu, zniknie z raportu bez żadnego ostrzeżenia. Lewe anty działa odwrotnie i zwraca wyłącznie wiersze bez pary, co czyni z niego gotowy test: zapytanie, które powinno zwrócić zero wierszy, a jeśli zwraca cokolwiek, w danych jest problem.

6. Pole Użyj dopasowywania rozmytego w celu wykonania scalenia pozwala łączyć wartości, które różnią się literówkami albo wielkością liter. Po jego zaznaczeniu w Opcjach dopasowywania rozmytego ustawisz próg podobieństwa i tabelę przekształceń. Przy kodach produktów nie jest potrzebne, przydaje się przy nazwach firm i adresach wpisywanych ręcznie. Kliknij OK.

Rozwiń dociągnięte kolumny

7. Na końcu tabeli pojawi się kolumna tProdukty z wartościami Table. W każdej komórce siedzi cały pasujący wiersz katalogu. Kliknij ikonę z dwiema strzałkami w nagłówku tej kolumny, odznacz (Zaznacz wszystkie kolumny), zaznacz Nazwa produktu, Kategoria, Marka i Stawka VAT, a na dole odznacz Użyj oryginalnej nazwy kolumny jako prefiksu.

Scalanie zadziała tylko wtedy, gdy klucze w obu tabelach mają ten sam typ danych. Kod jako tekst w jednej tabeli i jako liczba w drugiej nie zostanie dopasowany. Przy scalaniu po dacie oba pola muszą mieć typ Data, a nie Data/godzina.

Dołączanie zapytań: wiersze pod spodem zamiast kolumn obok

Scalanie dokłada kolumny. Dołączanie (ang. append) dokleja wiersze jednej tabeli pod drugą. Łączenie plików z folderu, które zrobiliśmy w lekcji 1, to właśnie dołączanie, wykonane automatycznie dla wszystkich plików. Ręcznie dołączasz zapytania wtedy, gdy masz dwie lub kilka tabel o tej samej strukturze z różnych źródeł.

Przykład: Nordvella otwiera dwa nowe sklepy, a ich dane przychodzą jako krótka lista od działu ekspansji, osobno od głównego słownika.

1. Na karcie Strona główna kliknij Wprowadź dane. W oknie Tworzenie tabeli możesz wpisać albo wkleić tabelę (Ctrl+V z Excela czy Worda). Jeśli wklejasz z nagłówkami, Power Query sam przeniesie pierwszy wiersz do nagłówków. Nazwij tabelę tSklepy_nowe i kliknij OK.

2. Zanim dołączysz tabele, wyrównaj typy kolumn. W naszym słowniku kolumna Otwarty od przyszła z Excela jako liczba (numer seryjny daty), a w nowej tabeli to tekst. Zaznacz kolumnę w zapytaniu tSklepy, na karcie Przekształć wybierz Typ danych: Data. Power Query zapyta, co zrobić z istniejącym krokiem zmiany typu:

Wybierz Zamień bieżącą. Lista kroków zostanie krótsza i czytelniejsza. Zrób to samo z kolumną Otwarty od w zapytaniu tSklepy_nowe.

3. Zaznacz zapytanie tSklepy, rozwiń strzałkę przy Dołącz zapytania i wybierz Dołącz zapytania jako nowe. W oknie Dołączanie jako drugą tabelę wybierz tSklepy_nowe. Opcja Co najmniej trzy tabele pozwala dołączyć dowolną liczbę tabel naraz.

Jeśli nazwa kolumny w jednej tabeli różni się choćby spacją albo wielkością litery, Power Query nie połączy jej z odpowiednikiem, tylko utworzy osobną kolumnę z pustymi wartościami dla wierszy z drugiej tabeli. To dobry sygnał kontrolny: po dołączeniu sprawdź, czy nie pojawiły się kolumny, których się nie spodziewasz. Power Query nadaje nowemu zapytaniu nazwę Dołącz1, którą widać na zrzucie. Zmień ją od razu na czytelną, my użyliśmy Sklepy_wszystkie.

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

✕