DataFormat.Error: Nie możemy przekonwertować na typ Number - przyczyny i rozwiązanie

Power Query w Excelu, Power BI i Fabric

DataFormat.Error: Nie możemy przekonwertować na typ Number - przyczyny i rozwiązanie

W skrócie

Komunikat DataFormat.Error: Nie możemy przekonwertować na typ Number widzi każdy, kto ustawia typ liczbowy na kolumnie z eksportu, w którym ktoś wpisał tekst zamiast kwoty albo zapisał liczby z kropką dziesiętną. Zapytanie działa, ale w części komórek zamiast liczb stoi Error. Pokazujemy, jak znaleźć te komórki w dużej tabeli, jak odczytać wartość, która zawiodła, i jak dobrać naprawę do przyczyny.

Jak to wygląda w praktyce

Błąd powstaje w kroku zmiany typu, zwykle Zmieniono typ, ale nie zatrzymuje zapytania. Większość wierszy ma poprawne liczby, a w niektórych komórkach kolumny stoi napis Error. Po kliknięciu w puste miejsce takiej komórki (nie w sam napis) na dole okna pojawia się komunikat:

DataFormat.Error: Nie możemy przekonwertować na typ Number.
Szczegóły:
    do ustalenia

Angielski oryginał to DataFormat.Error: We couldn't convert to Number. W polu Szczegóły Power Query podaje dokładnie tę wartość, której nie udało się zamienić na liczbę, i od niej zaczyna się diagnoza. Na zrzucie z kursu taki błąd zostawił wpis „do ustalenia” w budżecie sklepu Nordvella Online na grudzień.

Po załadowaniu do arkusza komórka z błędem zostaje pusta, więc suma kolumny w Excelu pominie tę pozycję.

Dlaczego tak się dzieje

Zmiana typu na liczbę to konwersja tekstu, a konwersja zależy od ustawień regionalnych. W polskich ustawieniach separatorem dziesiętnym jest przecinek, więc „12,5” przechodzi, a „12.5” kończy się błędem ze Szczegółami 12.5. Sprawdziliśmy w Excelu, co Power Query przyjmuje w polskich ustawieniach, a co odrzuca:

• przechodzą: „12,5”, „ 12,5 ” ze spacjami na brzegach, „1 234,50” ze spacją tysięcy, „12,50 zł” z symbolem waluty i „15%” jako 0,15, a pusty tekst zamienia się na null,
• dają błąd: „12.5” i „1.234” z kropką, znaczniki „NA” i „do ustalenia” oraz sam myślnik.

Każda taka wartość psuje tylko swoją komórkę, dlatego zapytanie się odświeża, a problem łatwo przeoczyć. Podgląd edytora pokazuje i profiluje domyślnie pierwsze 1000 wierszy, więc błąd w wierszu numer 5000 nie pojawi się w statystykach, dopóki nie przełączysz profilowania na cały zestaw danych.

Druga konwersja nie naprawia pierwszej. Jeśli krok Zmieniono typ już zamienił „12.5” w błąd, kolejny krok z ustawieniami regionalnymi dostaje błąd zamiast tekstu i błąd zostaje, co potwierdziliśmy w Excelu. Poprawiać trzeba pierwszą konwersję albo dane przed nią.

Jak to rozwiązać krok po kroku

1. Zaznacz kolumnę z błędami, na karcie Strona główna rozwiń Zachowaj wiersze i wybierz Zachowaj błędy. Zostaną tylko wiersze z błędem. Klikaj w puste miejsce komórek z napisem Error i zapisz wartości z pola Szczegóły. Krok Zachowano błędy służy tylko do diagnozy, więc po zebraniu wartości usuń go krzyżykiem.
2. Jeśli w Szczegółach widzisz znacznik zamiast liczby, zamień go na null przed zmianą typu. Kliknij na liście Zastosowane kroki krok tuż nad Zmieniono typ, zaznacz kolumnę i na karcie Strona główna kliknij Zamienianie wartości. W polu Wartość do znalezienia wpisz NA, w polu Zamień na wpisz null, rozwiń Opcje zaawansowane, zaznacz Dopasuj do całej zawartości komórki i kliknij OK. Power Query zapyta w oknie Wstawianie kroku, czy wstawić krok w środku przepisu, potwierdź.
3. Powtórz zamianę dla każdego znacznika z listy, bo każda obejmuje jedną wartość: w naszym teście po zamianie samego „NA” wpis „do ustalenia” nadal dawał błąd. W kodzie M taki krok wygląda tak: = Table.ReplaceValue(Źródło, "NA", null, Replacer.ReplaceValue, {"Budżet"}).
4. Jeśli w Szczegółach widzisz liczby z kropką, przyczyną są ustawienia regionalne, a nie dane. Kliknij krok Zmieniono typ i w pasku formuły usuń z listy parę tej kolumny, na przykład {"Cena", type number}. Potem na ostatnim kroku kliknij ikonę typu w nagłówku kolumny, wybierz Używając ustawień regionalnych i w oknie Zmienianie typu za pomocą ustawień regionalnych wskaż typ Liczba dziesiętna i ustawienia regionalne Angielski (Stany Zjednoczone). Power Query zapisze kulturę jako trzeci argument: = Table.TransformColumnTypes(Źródło, {{"Cena", type number}}, "en-US").
5. Gdy przy zmianie typu pojawi się okno Zmień typ kolumny z pytaniem o istniejącą konwersję, wybierz Zamień bieżącą, a nie Dodaj nowy krok. Nowy krok dostałby już błędy zamiast tekstu.
6. Jeśli przyczyny nie da się usunąć, a błędna wartość oznacza brak danych, zabezpiecz konwersję w kolumnie niestandardowej: = try Number.From([Budżet]) otherwise null. Alternatywa bez kodu to kliknięcie prawym przyciskiem nagłówka kolumny, polecenie Zamień błędy i wpisanie null w polu Wartość. Nie wpisuj tam zera: zero udaje prawdziwą wartość, zaniża średnie i w dzieleniu daje dzielenie przez zero.

Jak sprawdzić, że zadziałało

Włącz na karcie Widok pole Jakość kolumn i kliknij na pasku stanu napis o profilowaniu pierwszych 1000 wierszy, żeby przełączyć go na cały zestaw danych. W naprawionej kolumnie Błąd ma pokazywać 0%. Porównaj liczbę pustych wartości z liczbą zamienionych znaczników: więcej pustych oznacza, że w źródle są braki, których nie zamieniałeś. Na koniec zestaw sumę kolumny po załadowaniu z sumą policzoną w pliku źródłowym.

Nowy znacznik w kolejnym eksporcie, na przykład „b/d”, da ten sam błąd, bo żadna zamiana go nie obejmuje. Dlatego po każdej zmianie źródła zajrzyj do Jakości kolumn, a z właścicielem eksportu ustal, że brak danych to pusta komórka, a nie tekst. Gdy dane przychodzą z systemu, który zapisuje liczby z kropką, ustaw kulturę en-US w kroku zmiany typu na stałe, a nie dopiero po pierwszym błędzie.

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

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