Power Query obcina zera wiodące w kodach i numerach - jak je zachować

Power Query w Excelu, Power BI i Fabric

Power Query obcina zera wiodące w kodach i numerach - jak je zachować

W skrócie

Gdy w Power Query zera wiodące znikają z kodów produktów, numerów klientów albo kodów pocztowych, problem wychodzi zwykle dopiero przy scalaniu ze słownikiem albo przy porównaniu z systemem źródłowym. Kolumna wygląda porządnie, tylko kod 00123 stał się liczbą 123. Pokazujemy, który krok usuwa zera, jak ustawić typ, żeby przetrwały, i jak je odtworzyć, gdy już przepadły.

Jak to wygląda w praktyce

Po imporcie pliku CSV albo arkusza kolumna z kodami ma w nagłówku ikonę 123, czyli typ Liczba całkowita, a wartości są wyrównane do prawej. Kod 00123 wyświetla się jako 123, a 04567 jako 4567. Na liście Zastosowane kroki, zaraz po kroku Nagłówki o podwyższonym poziomie, stoi krok Zmieniono typ, którego nikt świadomie nie dodawał.

Skutki wychodzą w kolejnych krokach. Scalenie z katalogiem, w którym kody są tekstem z zerami, nie znajduje par: w naszym teście tekst 123 nie pasuje do 00123 i kolumny dociągnięte ze słownika zostają puste. Długie numery, na przykład 26-cyfrowy numer rachunku, w typie Liczba całkowita w ogóle się nie mieszczą i kończą się błędem Expression.Error: Liczba znajduje się poza zakresem wartości typu 64-bitowa liczba całkowita. (ang. The number is out of range of a 64 bit integer value.).

Dlaczego tak się dzieje

Dla źródeł bez struktury, czyli plików CSV, tekstowych i arkuszy Excela, Power Query domyślnie sam wykrywa typy kolumn. Według dokumentacji Microsoft przegląda w tym celu pierwsze 200 wierszy i dodaje dwa kroki: promowanie nagłówków i zmianę typu. Kolumna złożona z samych cyfr dostaje typ liczbowy, a liczba nie przechowuje zer na początku. W polskim Excelu Number.From("00123") zwraca 123, tak samo jak krok z Int64.Type dla tej kolumny.

Zera przepadają w chwili konwersji, dlatego późniejsza zmiana typu na Tekst ich nie przywraca. Sprawdziliśmy to: dwa kroki po sobie, najpierw Int64.Type, potem type text, dają tekst 123, a nie 00123. Bywa też, że zer nie ma już w źródle. Gdy w arkuszu widzisz 00123, ale komórka zawiera liczbę 123 z formatem niestandardowym 00000, Power Query odczyta 123, bo pobiera wartość komórki, a nie jej wygląd. Komórka z tekstem 00123 przychodzi jako tekst razem z zerami.

Jak to rozwiązać krok po kroku

1. Znajdź krok, który zmienia typ kolumny z kodami. Klikaj kolejne pozycje na liście Zastosowane kroki i patrz na ikonę w nagłówku kolumny. Zwykle jest to Zmieniono typ zaraz po Nagłówki o podwyższonym poziomie.
2. Kliknij ten krok i w pasku formuły zamień typ kolumny z kodami na tekstowy, na przykład {"Kod klienta", Int64.Type} na {"Kod klienta", type text}. Zatwierdź klawiszem Enter. Kod przejdzie z pliku jako 00123, bo konwersja na tekst nie rusza znaków.
3. Gdy Zmieniono typ jest ostatnim krokiem, ten sam efekt da kliknięcie ikony typu w nagłówku i wybór Tekst. W oknie Zmień typ kolumny wybierz Zamień bieżącą, żeby poprawić istniejący krok. Przycisk Dodaj nowy krok dołożyłby konwersję na tekst za zamianą na liczbę, czyli już po utracie zer.
4. Przy imporcie CSV możesz od razu ustawić w oknie importu pole Wykrywanie typu danych na Nie wykrywaj typów danych i nadać typy samodzielnie. Dla wszystkich nowych plików wykrywanie wyłączysz 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. Ta opcja wyłącza też automatyczne promowanie nagłówków.
5. Gdy zera przepadły już w źródle, a kody mają stałą długość, odtwórz je. Na karcie Dodaj kolumnę kliknij Kolumna niestandardowa i wpisz Text.PadStart(Text.From([Kod klienta]), 5, "0"), gdzie 5 to długość kodu. Kolumnę możesz też przekształcić w miejscu jednym krokiem: = Table.TransformColumns(Źródło, {{"Kod klienta", each Text.PadStart(Text.From(_), 5, "0"), type text}}). Liczby 123 i 4567 zamienią się w tekst 00123 i 04567.
6. Dopełnianie działa tylko przy kodach jednej długości. Kody 0123 i 00123 po utracie zer są tą samą liczbą 123 i po dopełnieniu do pięciu znaków oba staną się 00123. Wtedy pewnym źródłem jest tylko eksport z kodami zapisanymi jako tekst albo słownik kodów.
7. Długie numery, jak 26-cyfrowy numer rachunku, trzymaj zawsze jako Tekst. Typ Liczba całkowita kończy się błędem przekroczenia zakresu, a Liczba dziesiętna według dokumentacji Microsoft ma precyzję najwyżej 15 cyfr.

Jak sprawdzić, że zadziałało

Po poprawce nagłówek kolumny z kodami ma ikonę ABC, czyli typ Tekst, a wartości zaczynają się od zer. Sprawdź długość kodów tymczasową kolumną niestandardową Text.Length([Kod klienta]): przy kodach pięcioznakowych każdy wiersz powinien mieć 5. Ponów scalanie ze słownikiem i upewnij się, że kolumny dociągnięte z katalogu nie mają pustych wartości. W naszym teście ten sam klucz 00123 po obu stronach daje dopasowanie, a 123 i 00123 nie dają go wcale. Po załadowaniu do arkusza kod z typem Tekst zostaje tekstem razem z zerami: komórka pokazała 00123.

Żeby problem nie wrócił, typ Tekst ustawiaj wszystkim identyfikatorom, na których nie wykonujesz obliczeń: kodom produktów i klientów, kodom pocztowym, NIP-om, numerom kont i dokumentów. Gdy dodajesz nowe źródło albo importujesz plik od nowa, sprawdź, czy na liście kroków nie pojawił się kolejny automatyczny krok Zmieniono typ.

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