W dzisiejszym poście zajmiemy się błędem, który pojawia się przy pobieraniu danych, kiedy wcześniej nałoży się filtr. Załóżmy, że mamy w przykładowych danych zakres, który nie jest tabelą. Zaznaczamy ten zakres i będziemy chcieli go nazwać — po prostu NazwanyZakres (w miejscu oznaczonym strzałką na rys. nr 1).
Nazwanie zakresu pozwala nam wrócić do niego z każdego miejsca w arkuszu (z każdej komórki) rozwijając okno, w którym nazywaliśmy ten zakres (rys. nr 2).
Kiedy mamy nazwany zakres możemy za pomocą polecenia Z tabeli/zakresu z karty Dane pobrać go do Power Query (rys. nr 3).
W takim przypadku Power Query nie ma żadnych problemów, pobiera dane prawidłowo. Otworzy nam się Edytor zapytań z wczytanym zaznaczonym zakresem (rys. nr 4).
Problem pojawia się jeśli na ten nazwany zakres w Excelu nałożymy filtr, a następnie za pomocą polecenia Z tabeli/zakresu z karty Dane wczytamy ten zakres do Power Query. Otworzy nam się edytor zapytań Power Query, ale z błędem (rys. nr 5).
Błąd wynika z tego, że Power Query nie odczytuje zakresu danych tylko próbuje odczytać filtr, który jest nałożony na ten zakres (rys. nr 6).
Możemy przyjrzeć się dokładniej funkcji Excel.CurrentWorkbook, gdzie widzimy, że przy nazwie filtru jest błąd, ponieważ Power Query próbuje pobrać tabelę, której nazwa nie istnieje (rys. nr 7).
Jako tester lubię sobie czasem poeksperymentować, poszukać sposobu na obejście tego błędu. W innym pliku rozwijamy polecenie Pobierz dane (punkt 2 na rys. nr 8) z karty Dane (punkt 1), następnie rozwijamy polecenie Z pliku (punkt 3) i wybieramy Ze skoroszytu (punkt 4).
Otworzy nam się okno Importowanie danych, gdzie wybieramy nasz plik klikając na niego dwa razy (rys. nr 9).
Otworzy nam się okno Nawigator, gdzie możemy podejrzeć zawartość skoroszytu, czyli jego poszczególne elementy (rys. nr 10).
Jeśli zaznaczymy sobie cały plik Excela i wciśniemy przycisk Edytuj, to zobaczymy wszystkie elementy i zakresy z tego pliku. Otworzy nam się edytor zapytań Power Query z listą tych wszystkich elementów. Tutaj Power Query użyje innej funkcji Excel.Workbook (rys. nr 11).
W naszych danych nie interesują nas arkusze, czyli odfiltrujemy kolumnę Kind. W tym celu klikamy na trójkąt przy nazwie kolumny Kind i odznaczamy checkbox przy nazwie Sheet (czyli arkusz). Filtr zatwierdzamy przyciskiem OK (rys. nr 12).
Otrzymamy tabelę tylko ze zdefiniowanymi nazwami przedstawioną na rys. nr 13.
Interesuje nas jeszcze kolumna Hidden, która pokazuje które elementy są ukryte. Filtr nałożony na dane jest ukryty, natomiast nazwany zakres jest widoczny. Przy pobieraniu danych Power Query sięga niestety po tą tabelę, która jest ukryta (rys. nr 14).
W takim rozwiązaniu Power Query nie ma problemu i poprawnie odczytuje dane.
Jak możemy obejść ten błąd przy standardowym pobieraniu danych do Power Query?
Wyłączamy filtr w naszym nazwanym zakresie. Jest on niewidoczny ale dalej się tam znajduje. Stworzyliśmy tabelę pomocniczą z zakresem do filtrowania i właśnie na nią nakładamy ten filtr za pomocą skrótu klawiszowego Ctrl+Shift+L (rys. nr 15).
Teraz jeśli pobierzemy dane z tego zakresu za pomocą polecenia Z tabeli/zakresu z karty Dane, otworzy nam się edytor zapytań Power Query z wczytanym zakresem (rys. nr 16).
W kroku Źródło w Zastosowanych krokach możemy podejrzeć sobie funkcję użytą przez Power Query. Możemy zauważyć, że dalej pojawia nam się błąd mimo, że dane zostały wczytane (rys. nr 17).
Sposobem na zlikwidowanie błędu jest zmiana właściwości zakresu w VBA (rys. nr 18).
Wynika z tego, że funkcja Power Query źle odwołuje się do zakresu i stąd wynika problem (zły zapis zakresu – pojedynczy cudzysłów).
Podsumowując celem tego filmu było pokazanie, że Power Query nie zawsze chce zwracać dane, których potrzebujesz. Przede wszystkim w sytuacji kiedy nakładasz filtr na nazwany zakres.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
