Wartości #N/D i #DZIEL/0! z Excela jako błędy w Power Query

Power Query w Excelu, Power BI i Fabric

W skrócie

Komórki z wartościami #N/D albo #DZIEL/0! w źródłowym skoroszycie mają w Power Query napis Error. Komunikat brzmi DataFormat.Error: Nieprawidłowa wartość komórki „#N/A” albo „#DIV/0!”, z angielskimi kodami błędów.
Power Query czyta wartości komórek zapisane w pliku, a dla komórki z formułą jest to jej wynik. Błąd formuły staje się błędem pojedynczej komórki: reszta wiersza wczytuje się normalnie, ale suma takiej kolumny kończy się błędem.
Znajdź wiersze poleceniem Zachowaj błędy, a potem zamień błędy na null albo usuń wiersze, zależnie od tego, co błąd oznacza. Najtrwalej jest naprawić formuły w źródle, na przykład funkcją JEŻELI.BŁĄD.

Błąd #N/D w Power Query, a obok niego #DZIEL/0!, pojawia się przy wczytywaniu skoroszytów z formułami: arkuszy z WYSZUKAJ.PIONOWO, wskaźnikami marży albo realizacji planu. Pokazujemy, jak taki błąd wygląda w edytorze, dlaczego komunikat podaje angielski kod i jak zdecydować między zamianą błędu, usunięciem wiersza a naprawą formuły w źródle. Zachowanie poleceń sprawdziliśmy w polskim Excelu na skoroszycie z marżami sklepów Nordvelli.

Jak to wygląda w praktyce

W arkuszu źródłowym część komórek pokazuje #N/D, na przykład gdy WYSZUKAJ.PIONOWO nie znalazło kodu, albo #DZIEL/0!, gdy marżę liczono przy zerowej sprzedaży. Po wczytaniu skoroszytu do Power Query te same komórki mają napis Error, a reszta wiersza wygląda normalnie. Kliknięcie w puste miejsce takiej komórki, obok napisu Error, pokazuje pod podglądem komunikat DataFormat.Error: Nieprawidłowa wartość komórki „#N/A”. albo DataFormat.Error: Nieprawidłowa wartość komórki „#DIV/0!”. W angielskim Excelu ten sam komunikat brzmi Invalid cell value '#N/A'.

Power Query podaje kody błędów w wersji angielskiej, choć w polskim arkuszu widzisz #N/D i #DZIEL/0!. Skutki wychodzą dalej: w naszym teście suma kolumny Marża % zakończyła się tym samym błędem, a zmiana typu kolumny na liczbę błędu nie usunęła. Wiersze z błędem wyglądają przy tym na kompletne, bo pozostałe kolumny, takie jak Sklep i Sprzedaż, mają poprawne wartości.

Dlaczego tak się dzieje

Power Query czyta wartości komórek zapisane w pliku skoroszytu. Dla komórki z formułą jest to jej wynik, więc gdy formuła zwraca błąd, Power Query dostaje błąd i zamienia go na błąd pojedynczej komórki z przyczyną DataFormat.Error. Kod błędu pochodzi z pliku, w którym błędy zapisane są w wersji angielskiej, stąd #N/A i #DIV/0! zamiast #N/D i #DZIEL/0! z polskiego interfejsu. Tak samo przychodzą inne błędy formuł: w naszym teście komórka z błędem #VALUE! dała komunikat z kodem „#VALUE!”.

To błąd na poziomie komórki, a nie całego zapytania. Zapytanie działa, ale operacja, która musi odczytać wartość z błędem, na przykład suma albo średnia kolumny, przejmuje ten błąd. Dlatego jeden #N/D w kolumnie wystarczy, żeby suma w kroku grupowania też pokazała błąd zamiast liczby.

Jak to rozwiązać krok po kroku

Zaznacz kolumnę z błędami i na karcie Strona główna wybierz Zachowaj wiersze, Zachowaj błędy. Zostaną tylko wiersze z błędem w tej kolumnie: w naszym teście dwa z pięciu, sklepy Nordvella Wrocław i Nordvella Kraków Galeria. Zanotuj, czego dotyczą, i usuń krok Zachowano błędy krzyżykiem, bo służy tylko do diagnozy.
Jeśli chcesz zachować treść błędu, zanim go zamienisz, na karcie Dodaj kolumnę kliknij Kolumna niestandardowa i wpisz formułę let t = try [#"Marża %"] in if t[HasError] then t[Error][Message] else null. W wierszach z błędem pokaże komunikat, na przykład Nieprawidłowa wartość komórki „#N/A”., a w pozostałych null.
Gdy błąd oznacza brak danych, na przykład marżę, której nie da się policzyć, zamień go na wartość pustą. Kliknij prawym przyciskiem nagłówek kolumny, wybierz Zamień błędy i w oknie Zamienianie błędów wpisz w polu Wartość null. Formuła kroku to Table.ReplaceErrorValues(#"Nagłówki o podwyższonym poziomie", {{"Marża %", null}}). Nie wpisuj odruchowo 0, bo zero zaniżyłoby średnią marżę bez śladu w danych.
Gdy wiersz z błędem nie powinien trafić do wyniku wcale, zaznacz kolumnę i na karcie Strona główna wybierz Usuń wiersze, Usuń błędy. W naszym teście zostały 3 wiersze z 5. To polecenie usuwa całe wiersze, razem ze sprzedażą zapisaną w innych kolumnach.
Najtrwalsza poprawka jest w źródle. Gdy #N/D oznacza brakujący kod w słowniku, uzupełnij słownik. Gdy błąd jest spodziewany, na przykład dzielenie przez zero przy sklepie bez sprzedaży, otocz formułę funkcją JEŻELI.BŁĄD, na przykład =JEŻELI.BŁĄD(C2/B2;""). Pusty tekst zwrócony przez taką formułę Power Query zamienia na null przy zmianie typu kolumny na liczbę, co sprawdziliśmy w teście.
Po poprawce w źródle zapisz skoroszyt i odśwież zapytanie. Power Query czyta plik zapisany na dysku, więc zmiana widoczna tylko w otwartym, niezapisanym skoroszycie nie trafi do wyniku.

Okno Zamienianie błędów w Power Query z wartością null wpisaną w polu Wartość — Okno Zamienianie błędów. Wartość z pola Wartość zastąpi błędy w zaznaczonych kolumnach: null zostawia komórkę pustą, a wpisanie 0 zamieniłoby błąd na zero.

Jak sprawdzić, że zadziałało

Włącz na karcie Widok opcję Jakość kolumn: pod nagłówkiem poprawionej kolumny udział błędów powinien wynosić 0%. Sprawdź też, czy suma albo średnia kolumny liczy się bez błędu, na przykład w kroku Grupowanie według, i porównaj liczbę wierszy z poprzednim wynikiem. Zamiana błędów nie zmienia liczby wierszy, a usunięcie błędów ją zmniejsza: w naszym teście z 5 do 3.

Żeby problem nie wrócił, zostaw w zapytaniu krok zamiany błędów tylko tam, gdzie błąd jest spodziewany, a pozostałe błędy traktuj jako sygnał do poprawki w źródle. Krok Zamienione błędy obejmie przy każdym odświeżeniu wszystkie przyszłe błędy w tej kolumnie, także te, które oznaczałyby nowy problem w danych.

Poczytaj więcej
Lekcja 9 bezpłatnego kursu Power Query: Błędy w zapytaniach i profilowanie danych
Cały bezpłatny kurs Power Query w Excelu, Power BI i Fabric z Copilotem

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Baner szkolenia Microsoft Excel - Power Query w JSystems z edytorem Power Query na ekranie laptopa

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

Autorzy

Tekst: Redakcja JSystems

Najczęściej zadawane pytania

Dlaczego Power Query pokazuje #N/A, skoro w Excelu widzę #N/D?

Plik skoroszytu zapisuje kody błędów w wersji angielskiej, a polski Excel tylko wyświetla je po polsku. Power Query czyta wartość z pliku, więc w komunikacie Nieprawidłowa wartość komórki widzisz kod #N/A albo #DIV/0!. Szukając rozwiązania, korzystaj z obu wersji kodu.

Czy zmiana typu kolumny na liczbę usunie błędy #N/D?

Nie. W naszym teście po zmianie typu na liczbę komórka nadal zawierała błąd DataFormat.Error z kodem #N/A. Błędy trzeba zamienić, usunąć albo naprawić w źródle, a typ ustawić niezależnie od tego.

Kiedy zamienić błąd na null, a kiedy usunąć cały wiersz?

Zamień na null, gdy wiersz zawiera inne potrzebne dane, a błąd oznacza brak jednej wartości, na przykład nieznaną marżę przy znanej sprzedaży. Usuń wiersz tylko wtedy, gdy cały jest bezużyteczny, bo polecenie Usuń błędy kasuje go razem z poprawnymi wartościami w innych kolumnach.

Czy inne błędy formuł Excela Power Query traktuje tak samo?

Tak. Komórka, w której formuła zwróciła błąd, przychodzi jako błąd DataFormat.Error z kodem w wersji angielskiej. W naszym teście komórka z błędem #VALUE! dała komunikat Nieprawidłowa wartość komórki z tym kodem, więc postępujesz tak samo jak przy #N/D.