Power Query wykrywa typ na podstawie pierwszych 200 wierszy - błędny typ kolumny

Power Query w Excelu, Power BI i Fabric

Power Query wykrywa typ na podstawie pierwszych 200 wierszy - błędny typ kolumny

W skrócie

W Power Query wykrywanie typów danych działa przy każdym imporcie pliku CSV, tekstowego albo arkusza i przy jednorodnych danych trafia. Kłopot pojawia się w długich plikach, w których nietypowe wartości są dopiero w dalszej części: kolumna dostaje typ dopasowany do początku danych, a nie do całości. Pokazujemy, jak to rozpoznać, skąd się bierze i jak ustawić typy, które wytrzymają cały plik.

Jak to wygląda w praktyce

Objawy zależą od tego, co kryje się za 200. wierszem. Pierwszy przypadek jest cichy. Kolumna Rabat % ma w nagłówku ikonę 123, czyli typ Liczba całkowita, bo na początku pliku są same całe wartości. Rabat 2,5 z dalszej części pliku zamienia się w 2, a 7,9 w 8. Żadna komórka nie pokazuje błędu, tylko suma się nie zgadza: w naszym teście 200 zer oraz wartości 2,5 i 7,9 dały sumę 10 zamiast 10,4.

Drugi przypadek widać, ale często dopiero po załadowaniu, bo podgląd w edytorze obejmuje pierwsze 1000 wierszy. Kolumna z datami dostaje typ Data, a dalsze wpisy w rodzaju „brak” albo „do ustalenia” zamieniają się w Error z komunikatem DataFormat.Error: Nie możemy przeanalizować danych wejściowych dostarczonych jako wartość typu Date. (ang. We couldn't parse the input provided as a Date value.). W szczegółach błędu stoi wartość, której nie udało się zamienić, na przykład brak. Tekst w kolumnie liczbowej daje DataFormat.Error: Nie możemy przekonwertować na typ Number. (ang. We couldn't convert to Number.). Po załadowaniu do arkusza wiersz z błędem zostawia pustą komórkę.

Dlaczego tak się dzieje

Dla źródeł bez struktury Power Query domyślnie sam ustala nagłówki i typy kolumn. Dokumentacja Microsoft opisuje to wprost: typy są wnioskowane tylko z pierwszych 200 wierszy, a gdy dane po 200. wierszu różnią się od początku, Power Query może wybrać zły typ. Dokumentacja dodaje, że błędny typ nie zawsze daje błąd. Czasem wartości są po prostu niepoprawne, co utrudnia wykrycie problemu.

Oba przypadki sprawdziliśmy w polskim Excelu. Konwersja na Int64.Type przy ułamkach nie zgłasza błędu: 2,5 daje 2, 2,7 daje 3, a 7,9 daje 8, więc część ułamkowa przepada przez zaokrąglenie do liczby całkowitej. Konwersja tekstu, który nie jest datą ani liczbą, kończy się błędem tylko w tej jednej komórce, a reszta kolumny wygląda poprawnie. Przy imporcie CSV i przy łączeniu plików z folderu o próbce decyduje pole Wykrywanie typu danych w oknie importu, z ustawieniem Na podstawie pierwszych 200 wierszy.

Jak to rozwiązać krok po kroku

1. Na karcie Widok zaznacz pola Jakość kolumn, Rozkład kolumn i Profil kolumny. Potem kliknij na pasku stanu napis Profilowanie kolumn w oparciu o następującą liczbę pierwszych wierszy: 1000 i wybierz Profilowanie kolumn w oparciu o cały zestaw danych. Dopiero wtedy pasek jakości pokaże błędy z dalszych wierszy.
2. Sprawdź typ każdej kolumny po ikonie w nagłówku i porównaj go z tym, co wiesz o danych. Rabaty, kwoty, kursy i ilości w kilogramach z ikoną 123 to sygnał ostrzegawczy, nawet jeśli żadna komórka nie pokazuje błędu, bo ułamki mogły zostać zaokrąglone po cichu.
3. Popraw typy w kroku Zmieniono typ: kliknij go i w pasku formuły zamień na przykład {"Rabat %", Int64.Type} na {"Rabat %", type number}, czyli Liczba dziesiętna. Zatwierdź klawiszem Enter. Kolumnom, w których obok liczb albo dat mogą trafić się wpisy tekstowe, zostaw na razie typ Tekst, zamień takie wpisy, na przykład na null, i dopiero potem ustaw typ docelowy.
4. Przy imporcie pliku CSV albo przy łączeniu plików z folderu ustaw w polu Wykrywanie typu danych opcję Na podstawie całego zestawu danych, żeby Power Query obejrzał wszystkie wiersze. Możesz też wybrać Nie wykrywaj typów danych i nadać typy samodzielnie.
5. Jeśli wolisz zawsze ustawiać typy samodzielnie, wyłącz automatyczne wykrywanie. W oknie Opcje zapytania, w części Globalne, w grupie Ładowanie danych, wybierz Nigdy nie wykrywaj nagłówków i typów kolumn dla źródeł bez struktury. Dla jednego pliku odznacz w części Bieżący skoroszyt pole Wykrywaj nagłówki i typy kolumn dla źródeł bez struktury.
6. Po ustawieniu typów znajdź pozostałe błędy konwersji. Zaznacz kolumnę, na karcie Strona główna rozwiń Zachowaj wiersze i wybierz Zachowaj błędy. Zostaną tylko wiersze z błędami, a kliknięcie w puste miejsce komórki z napisem Error pokaże komunikat i wartość, której nie udało się zamienić. Gdy poprawisz przyczynę, usuń krok Zachowaj błędy z listy kroków.

Jak sprawdzić, że zadziałało

Przy profilowaniu na całym zestawie danych pasek Jakość kolumn pokazuje 0% błędów w każdej kolumnie z ustawionym typem, a profil obejmuje wszystkie wiersze pliku. W kolumnach liczbowych porównaj jedną sumę z systemem źródłowym: w naszym teście zmiana z Liczba całkowita na Liczba dziesiętna przywróciła sumę 10,4 zamiast 10. Po załadowaniu do Excela sprawdź w okienku Zapytania i połączenia liczbę załadowanych wierszy i porównaj ją z liczbą wierszy w źródle.

Żeby problem nie wrócił, traktuj automatyczny krok Zmieniono typ jako propozycję do przejrzenia, a nie gotowy wynik. Dokumentacja Microsoft zaleca, żeby przy źródłach bez struktury zawsze jawnie definiować typy kolumn. Gdy do folderu trafia plik z innego okresu albo systemu, przejrzyj jakość kolumn na całym zestawie danych po pierwszym odświeżeniu.

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