Jak radzić sobie z błędami w Power Query?

Z lekcji dowiesz się:

• W jaki sposób Power Query informuje o błędach?

• O najnowszych funkcjonalnościach Power Query!

• Jakie działania naprawcze Power Query wykona automatycznie?

Zagadnienie naprawy błędów omówimy na podstawie przykładowych danych z rys. 1. Są tam zawarte oczywiste błędy związane z datami.

Rys. 1. Przykładowe dane z błędami

Zaczynamy od zaczytania danych do Power Query. W tym celu ustawiamy aktywną komórkę na obszarze tabeli z rys. 1 i wybieramy polecenie Z tabeli/ zakresu z karty Dane (rys. 2).

Rys. 2. Polecenie Z tabeli/ zakresu

Otworzy się Edytor zapytań Power Query z wczytaną tabelą tShort. Na rys. 3 widać, że Power Query nie zmienił typu danych na Data, ale zostawił typ ogólny. Wynika to z błędnego zapisu dat, których Power Query nie rozpoznał (rys. 3) i potraktował je jako tekst.

Rys. 3 Ogólny typ danych w kolumnie z datami

Nam zależy, aby te wszystkie wartości zostały potraktowane jako data. W tym celu klikamy na ikonkę ABC123 przy nazwie kolumny Data i z podręcznego menu wybieramy typ danych Data (rys. 4).

Rys. 4 Zmiana typu danych na Data

Otrzymamy dane przedstawione na rys. 5, gdzie w miejscu błędnie zapisanej daty pojawił się błąd (Error).

Rys. 5 Błędy (Error) w danych

Kiedy klikniemy na jeden z tych błędów, pod tabelą wyświetli się komunikat informujący o błędzie (rys. 6).

Rys. 6 Informacje o błędach

Power Query połączony z nowych Excelem ma funkcjonalność, która pokazuje stan danej kolumny (zaznaczone strzałką na rys. 7).

Rys. 7. Pasek z informacją o stanie kolumny

Widnieje tutaj także informacja, ile dana kolumna zawiera błędów. Ponadto mamy możliwość ich usunięcia lub kilka innych opcji do wyboru. Dostępne opcje są możliwe do wybrania po kliknięciu na ikonkę wielokropku (rys. 8).

Rys. 8. Opcje dostępne w odniesieniu do danej kolumny

Jeśli nie dysponujemy nową wersją Excela, to możemy skorzystać z grupy poleceń Zachowywania wierszy (np. polecenie Zachowaj błędy) z karty Narzędzia główne (rys. 9) lub Usuwania wierszy (np. polecenie Usuń błędy) – rys. 10.

Rys. 9 Grupa poleceń Zachowywania wierszy

Rys. 10 Grupa poleceń Usuwania wierszy

Dodatkowo na karcie Przekształć możemy rozwinąć polecenie Zamienianie wartości i wybrać polecenie np. Zamienianie błędów (rys. 11).

Rys. 11 Polecenie Zamienianie błędów

Podsumowując wszystkie te możliwości (z rys. 9, 10 i 11) zawarte w jednym miejscu – oferuje nowa wersja oprogramowania Excela. Musimy obsłużyć te błędy i sprawdzić, jakie zawierają informacje. W tym celu wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. 12).

Rys. 12 Polecenie Kolumna niestandardowa

Otworzy się okno Kolumny niestandardowej, gdzie w polu Nazwa nowej kolumny wpisujemy "Errors". Następnie w polu Formuła kolumny niestandardowej użyjemy słowa kluczowego "try", czym będziemy chcieli sprawdzić wartości w kolumnie Data. Zapis formuły kolumny niestandardowej powinien wyglądać następująco: = try [Data].

Tak ustawione parametry kolumny niestandardowej zatwierdzamy przyciskiem OK (rys. 13).

Rys. 13 Okno kolumny niestandardowej

Otrzymamy nową kolumnę z rekordami przedstawioną na rys. 14. Funkcja TRY tworzy record, wyciąga informacje z kolumny Data, czy był błąd czy nie. W zależności od tego, czy komórka zawierała błąd czy nie otrzymamy wyniki True lub False.

Rys. 14 Nowa kolumna niestandardowa

Teraz możemy rozwinąć informacje z kolumny Errors na poszczególne kolumny. W tym celu klikamy na ikonkę ze strzałkami, następnie odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy kolumny jako prefiksu. W okienku znajdują się informacje, jakie kolumny powstaną po rozwinięciu (HasError, Value i Error). Rozwinięcie danych zatwierdzamy przyciskiem OK (rys. 15).

Rys. 15 Rozwijanie danych

Otrzymamy rozwinięte dane przedstawione na rys. 16.

Rys. 16 Rozwinięte dane

W pierwszym wierszu kolumny HasError mamy informację logiczną FALSE, wynika to z tego, że ten wiersz nie zawierał błędu. W efekcie w kolumnie Value występuje powtórzona wartość z kolumny Data. W drugim wierszu (gdzie jest Error) otrzymaliśmy w kolumnie HasError wartość logiczną TRUE, natomiast w kolumnie Value nie mamy podanej tej błędnej wartości tylko wartość null. W tym przypadku w kolumnie Error mamy więcej szczegółów (Record), a mianowicie informację o tym błędzie.

Teraz będziemy chcieli skupić się na analizie błędów. W tym celu zaznaczamy kolumnę Data i rozwijamy polecenie Zachowaj wiersze (punkt 2 na rys. 17) z karty Narzędzia główne (punkt 1), następnie wybieramy polecenie Zachowaj błędy (punkt 3).

Rys. nr 17 – polecenie Zachowaj błędy

Otrzymamy dane zawierające tylko 2 wiersze z błędami przedstawione na rys. nr 18.

Rys. 18 Dane z błędami

Kolumna Value przy analizie błędów nic nie wnosi, więc możemy ją zaznaczyć i skasować za pomocą klawisza Delete. Kolumna Error zawiera informacje o błędzie (Record), tą samą, która pojawiała się na żółtym tle po kliknięciu na słowo Error w danych. Informacje te możemy sobie odpowiednio rozwinąć klikając na ikonkę ze strzałkami obok nazwy kolumny Error. Pojawi się okienko, w którym możemy wybrać jakie kolumny nas interesują. Zaznaczamy wszystkie dane i zatwierdzamy przyciskiem OK (rys. 19).

Rys. 19 Rozwijanie danych w kolumnie Error

Otrzymamy szczegółowe informacje o błędzie, w kolumnie Reason mamy informację, jaki to typ błędu. W kolumnie Message mamy wiadomość, a w kolumnie Detail wartość z komórki z błędem (rys. 20).

Rys. 20 Rozwinięte informacje o błędzie

Na początku danych mamy kolumnę ID sprzedaży, która pozwoli zlokalizować te błędy. Gdybyśmy jednak takiej kolumny nie mieli, to musielibyśmy dodać kolumnę z indeksem, żeby łatwo zlokalizować, gdzie błąd wystąpił. Taką tabelę z analizą błędów możemy zaczytać do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. 21).

Rys. 21 Polecenie Zamknij i załaduj do

W Excelu otworzy się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i miejsce wstawienia danych – Nowy arkusz. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. 22).

Rys. 22 Okno Importowania danych

Otrzymamy dane wstawione do Excela do nowego arkusza, przedstawione na rys. 23.

Rys. 23 Dane zaimportowane do Excela

Jak widać powyżej kolumna Data jest pusta, wynika to z tego, że jeśli w Power Query w komórce jest błąd (Error) to do Excela zostaje zaimportowana pusta komórka.

Powyżej została przedstawiona sytuacja, kiedy Power Query znajdzie błędy w pierwszym tysiącu wierszy. Teraz zajmiemy się sytuacją, kiedy błędy będą występować dopiero po przekroczeniu pierwszego tysiąca wierszy.

Teraz zaczytamy dane do Power Query z dłuższej tabeli, która ma ponad 19 tysięcy wierszy. Ustawiamy aktywną komórkę w obrębie tabeli, następnie wybieramy polecenie Z tabeli/ zakresu z karty Dane (jak na rys. 2).

Otworzy się edytor zapytań Power Query z wczytaną tabelą tSprzedaż przedstawioną na rys. 24.

Rys. 24 Wczytane dane

Możemy zauważyć, że w kolumnie Data Power Query nie wykrywa błędów, które celowo umieściliśmy dopiero po 1000 danych. Wynika to z faktu, że Power Query w swoich statystykach (określaniu typów danych) bierze pod uwagę tylko 1000 pierwszych wierszy (rys. 25).

Rys. 25 Ilość wierszy uwzględniana przy profilowaniu danych

Power Query określił typ danych dla kolumny Data jako Data i godzina, więc teraz zmienimy typ danych na Datę. Klikamy na ikonkę z kalendarzem i z podręcznego menu wybieramy typ Data (rys. 26).

Rys. 26 Zmiana typu danych w kolumnie Data

Wyświetli nam się komunikat, w którym musimy potwierdzić zmianę typu danych za pomocą przycisku Zamień bieżącą (rys. 27).

Rys. 27 Komunikat o zmianie typu danych

Nawet po zmianie typu danych na Data, Power Query nie wykrywa błędów w danych (rys. 28).

Rys. 28 Dane po zmianie typu w kolumnie Data

Kolumna Data nadal wyświetla się na zielono i mamy w niej informację, że wszystkie wiersze są prawidłowe (rys. 29).

Rys. 29 Informacja o kolumnie

Wiemy, że nasze dane zawierają błędy. Teraz zaczytamy dane do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (jak na rys. 21). W Excelu otworzy się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela oraz wskazujemy miejsce wstawienia danych – Nowy arkusz. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (jak na rys. 22).

Otrzymamy dane zaimportowane do Excela. Co istotne przy importowaniu danych do Excela, Power Query przeanalizuje wszystkie wiersze i znajdzie błędy. Informacje o błędach będziemy mieć zawartą w panelu bocznym Zapytań i połączeń (zaznaczone strzałką na rys. 30).

Rys. 30 Dane zaimportowane do Excela z pokazaną ilością błędów

Gdyby panel (okno) Zapytań i połączeń nie był widoczny, możemy go uruchomić za pomocą polecenia Zapytania i połączenia z karty Dane (rys. 31).

Rys. 31 Uruchamianie okna Zapytań i połączeń

Możemy kliknąć na link z błędami (zaznaczony strzałką na rys. 30), żeby Power Query z tej dużej głównej tabeli wyciągnął informacje o błędach. Otworzy się Power Query z tabelą zawierającą tylko wiersze z błędami (rys. 32).

Rys. 32 Wyciągnięte wiersze z błędami

Power Query sam automatycznie wykonał kilka czynności, mianowicie wykrył niezgodności typów, dodał kolumnę z indeksem, użył polecenia Zachowaj błędy i zmienił kolejność kolumn, aby mieć nr indeksu na początku danych. Te czynności widać w Ustawieniach zapytania, w Zastosowanych krokach (rys. 33).

Rys. 33 Zastosowane kroki

Tego zapytania nie będziemy już zaczytywać do Excela. Ważne jest, aby pamiętać, że Power Query wykrywa błędy tylko w pierwszym tysiącu wierszy i warto sprawdzić, czy dalej nie ukrył się jakiś błąd. Power Query sprawdza całość danych dopiero przy ładowaniu ich do Excela.

Przejdziemy teraz na arkusz Porównanie w naszym pliku do tego zadania, gdzie będziemy chcieli zrobić proste porównanie sprzedaży z roku 2019 i 2020. Przykładowe dane do tego zadania przedstawione zostały na rys. 34.

Rys. 34 Przykładowe dane do zadania

Takie porównanie w Excelu zrobilibyśmy za pomocą funkcji JEŻELI.BŁĄD Zapis funkcji wyglądał by następująco:

=JEŻELI.BŁAD([@2020]/[@2019]-1;"")

Funkcja ta sprawdzałaby obliczenia. Dla pierwszego wiersza obliczenia zwróciłyby błąd, wtedy otrzymalibyśmy pusty ciąg tekstowy. Podobna sytuacja wydarzy się w ostatnim wierszu, ponieważ nie ma jeszcze danych za grudzień 2020 i wtedy funkcja zwróci pusty ciąg tekstowy (dzielenie tekstu przez liczbę).

Podsumowując w dwóch przypadkach otrzymaliśmy pustą komórkę, więc teraz sprawdzimy jak w tej sytuacji zadziała Power Query. Zaczytujemy dane do Power Query za pomocą polecenia Z tabeli/ zakresu z karty Dane (jak na rys. 2).

Otworzy nam się edytor zapytań Power Query z wczytaną tabelą tPorównanie (rys. 35).

Rys. 35 Dane zaczytane z Excela

Powinniśmy dodatkowo zmienić typ danych w kolumnie Porównaj, aby dane były w postaci procentowej. W tym celu klikamy na ikonkę 1.2 przy nazwie kolumny i z podręcznego menu wybieramy typ Wartość procentowa (rys. 36).

Rys. 36 Zmiana typu danych

Pojawi się okienko Zmiany typu kolumny, którą musimy zatwierdzić przyciskiem Zamień bieżącą.

Otrzymamy dane po zmianie typu przedstawione na rys. 37.

Rys. 37 Dane po zmianie typu na wartość procentową

Kiedy zaczytaliśmy dane z formułą stworzoną w Excelu puste komórki zostały zastąpione wartościami null. Sprawdzimy co się stanie, kiedy takie obliczenie wykonamy w Power Query. W tym celu dodamy nową kolumnę, czyli wybieramy polecenie Kolumna niestandardowa z karty Dodaj kolumnę (rys. 38).

Rys. 38 Polecenie Kolumna niestandardowa

Otworzy się okno Kolumny niestandardowej, gdzie w polu Nazwa nowej kolumny wpisujemy 2020/2019, następnie wpisujemy formułę kolumny niestandardowej. W formule tej będziemy chcieli podzielić dane z roku 2020 przez dane z 2019 i odjąć wartość 1. Zapis powinien wyglądać następująco:

=[2020]/[2019]-1

Tak przygotowane parametry kolumny niestandardowej zatwierdzamy przyciskiem OK (rys. 39).

Rys. 39 Okno kolumny niestandardowej

Otrzymamy dane z nową kolumną niestandardową przedstawione na rys. 40.

Rys. 40 Dane z nową kolumną niestandardową

Jak widać na rysunku powyżej obliczenia w Power Query wyszły takie same jak w tych z Excela, ale mamy w danych dwa inne wyniki – Infinity, (nieskończoność) i Error (błąd). Obliczenie z pierwszego wiersza zwróciło wynik Infinity, ponieważ Power Query rozumie takie pojęcie jak nieskończoność (Excel nie). W ostatnim wierszu otrzymaliśmy błąd, ponieważ w Power Query również nie możemy dzielić wartości tekstowej przez liczbę.

Po kliknięciu na Error otrzymamy dokładną informację o błędzie przedstawioną na rys. 41.

Rys. 41 Informacja o błędzie

Nasze zadanie polega na tym, aby Power Query obsłużył te wyniki. W tym celu zmodyfikujemy zapis formuły kolumny niestandardowej. Klikamy na koło zębate obok nazwy kroku Dodano kolumnę niestandardową w Zastosowanych krokach. Otworzy się ponownie okno kolumny niestandardowej, gdzie przed poprzednią formułę dołożymy słowo kluczowe try. Tak zmienioną formułę zatwierdzamy przyciskiem OK (rys. 42).

Rys. 42 Okno kolumny niestandardowej (zmiana w formule)

Otrzymamy kolumnę z rekordami przedstawioną na rys. 43, z informacją czy wystąpił błąd czy też nie (true lub false).

Rys. 43 Kolumna z rekordami

Powyższe rekordy możemy sobie rozwinąć klikając na ikonkę ze strzałkami obok nazwy kolumny. Pojawi się okienko, w którym zaznaczamy wszystkie kolumny i odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy kolumny jako prefiksu. Rozwinięcie to zatwierdzamy przyciskiem OK (rys. 44).

Rys. 44 Rozwijanie danych (rekordów)

Otrzymamy rozwinięte dane przedstawione na rys. 45.

Rys. 45 Rozwinięte dane

Nie chcemy jednak wykorzystać takiego sposobu, dlatego usuwamy ostatni krok z Zastosowanych kroków (Rozwinięty element 2020/2019) za pomocą klawisza Delete.

Chcemy, aby Power Query za każdym razem, kiedy napotka błąd, wykonywał określoną czynność. Dlatego po raz kolejny przechodzimy do edycji kroku Dodano kolumnę niestandardową za pomocą koła zębatego. Otworzy się okno Kolumny niestandardowej, gdzie w formule musimy użyć innego słowa kluczowego otherwise. Zapis formuły powinien wyglądać następująco:

= try [2020]/[2019]-1

Otherwise "brak danych"

Tak zmienioną formułę kolumny niestandardowej zatwierdzamy przyciskiem OK (rys. 46).

Rys. 46 Zmiany w formule kolumny niestandardowej

Otrzymamy kolumnę niestandardową przedstawioną na rys. 47, gdzie zamiast błędu otrzymujemy zapis "brak danych".

Rys. 47 Kolumna niestandardowa po zmianach w formule

Użyte słowo kluczowe "try" działa na zasadzie funkcji JEŻELI.BŁĄD w Excelu, natomiast słowo "otherwise" pełni rolę drugiego argumentu powyższej funkcji. Załadujemy teraz powyższe dane do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne. W Excelu otworzy się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako tabela, a następnie wybieramy miejsce wstawienia danych – Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. 48).

Rys. 48 Okno importowania danych

Otrzymamy dane przedstawione na rys. 49. Załadowaliśmy dane głównie po to, aby sprawdzić, jak Excel poradzi sobie z "Infinity" z Power Query.

Rys. 49 Dane zaimportowane do Excela

Jak łatwo zauważyć w Excelu otrzymaliśmy błąd liczby (#LICZBA!). Wynika to z tego, że Excel nie zna pojęcia nieskończoność. Możemy również zauważyć, że zniknęło formatowanie kolumny Porównaj, które nałożyliśmy w Power Query. Należy zapamiętać, że formatowanie z Power Query nie przenosi się do Excela, więc musimy bezpośrednio w Excelu samodzielnie nałożyć odpowiednie formatowanie. Zaznaczamy kolumny Porównaj i 2020/2019, następnie wybieramy formatowanie Procentowe w grupie poleceń Liczba na karcie Narzędzia główne (rys. 50).

Rys. 50 Zmiana formatowania liczbowego

Otrzymamy sformatowane dane przedstawione na rys. 51.

Rys. 51 Sformatowane dane

Podsumowując, nie chcemy otrzymać błędu liczby w Excelu po zaimportowaniu danych. Zależy nam na poradzeniu sobie z nieskończonością w Power Query, więc wracamy do naszego zapytania tPorównanie klikając dwukrotnie na jego nazwę w oknie Zapytań i połączeń (rys. 52).

Rys. 52 Okno Zapytań i połączeń

Otworzy się edytor zapytań z danymi, które importowaliśmy do Excela (jak na rys. 47). Zajmiemy się teraz przekształceniem zapisu Infinity na zapis, który Excel będzie w stanie zrozumieć.

Po kliknięciu na komórkę z Infinity, pod tabelą pojawi się znak nieskończoności, który zaznaczamy i kopiujemy za pomocą skrótu klawiszowego Ctrl+C (będzie potrzebny później). W pierwszym kroku wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. 53).

Rys. 53 Polecenie Kolumna warunkowa

Otworzy się okno Dodawania kolumny warunkowej, gdzie w polu Nazwa nowej kolumny wpisujemy Prawidłowe (punkt 1 na rys. 54). Następnie w polu Nazwa kolumny wybieramy wartości z kolumny 2020/2019 (punkt 2), w polu Operator zostawiamy domyślne działanie równa się (punkt 3), w polu Wartość wklejamy skopiowany wcześniej znak nieskończoności za pomocą skrótu klawiszowego Ctrl+V (punkt 4).

Jeśli wartość z kolumny 2020/2019 równa się nieskończoność to wtedy chcemy otrzymać zapis "brak" – wpisany w polu Wartość wyjściowa (punkt 5). Jeśli otrzymujemy wartość inną niż nieskończoność, to chcemy otrzymać wartości z kolumny 2020/2019, czyli w polu W przeciwnym razie wybieramy kolumnę 2020/2019 (punkt 6).

Tak ustawione parametry kolumny warunkowej zatwierdzamy przyciskiem OK. Analogicznie możemy wykonać zapis dla ujemnej nieskończoności, wystarczy dodać nową klauzulę.

Rys. 54 Kroki dodawania kolumny warunkowej

Otrzymamy dane z nową kolumną warunkową przedstawione na rys. 55.

Rys. 55 Dane z kolumną warunkową

Zapisowi testów logicznych możemy się przyjrzeć w pasku formuły, gdzie zamiast znaku nieskończoności mamy zapis #infinity. Podsumowując, to co zapisaliśmy w interfejsie użytkownika, Power Query przełożył na kod języka M. Ważne jest, że dzięki użyciu kolumny warunkowej, otrzymaliśmy zapis brak zamiast Infinity (rys. 56).

Rys. 56 Zapis formuły dla kolumny warunkowej

Możemy teraz usunąć kolumnę 2020/2019, aby po zaimportowaniu danych do Excela nie pokazywał nam się błąd liczby. Zaznaczamy kolumnę i usuwamy ją za pomocą klawisza Delete. Po raz kolejny importujemy powyższe dane do Excela za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. 57).

Rys. 57 Polecenie Zamknij i załaduj

W Excelu nie otworzy się okno Importowania danych, bo już ustawione zostało miejsce docelowe. Dane odświeżą się automatycznie. Musimy teraz sformatować kolumnę Prawidłowe na procenty (jak na rys. 50). Otrzymamy dane przedstawione na rys. 58.

Rys. 58 Sformatowane dane

Pokazaliśmy, jak radzić sobie z obsługą błędów w Power Query oraz jak poradzić sobie z występującymi w Power Query pojęciami, które nie są zrozumiałe dla Excela (Infinity).

Polecamy także:

• Jak wyznaczyć wysokość premii w zależności od obrotu?

• Jak przedstawić wyniki sprzedaży na wykresie panelowym?

Adam Kopeć