Liczby z kropką dziesiętną dają błąd w polskim Excelu - jak wczytać je z CSV i API
W skrócie
W polskim Excelu Power Query traktuje przecinek jako separator dziesiętny, więc liczby z kropką z plików CSV i API, na przykład 12.5, przy zmianie typu na liczbę kończą się błędem DataFormat.Error.
Zmiana typu bez wskazanej kultury czyta tekst według ustawień regionalnych pliku albo systemu. Według polskich 12.5 nie jest liczbą, za to pasuje do daty, a tekst 12,5 według kultury amerykańskiej daje 125.
Ustaw typ opcją Używając ustawień regionalnych z kulturą Angielski (Stany Zjednoczone) albo dopisz en-US w Table.TransformColumnTypes. Nie zamieniaj kropek ręcznie, bo psuje to liczby z separatorem tysięcy.
Eksport z systemu księgowego, plik CSV od dostawcy albo odpowiedź API zapisują liczby z kropką dziesiętną, a polski Excel oczekuje przecinka. W Power Query kropka i przecinek w liczbach decydują, czy kolumna zamieni się w liczby, w błędy, czy w zupełnie inne wartości. Pokazujemy, co dokładnie się dzieje, jak wczytać takie dane poprawnie i czego nie robić. Każdy przykład sprawdziliśmy w polskim Excelu.
Jak to wygląda w praktyce
Po imporcie pliku CSV i zmianie typu kolumny na liczbę dziesiętną część komórek albo cała kolumna pokazuje Error. Po kliknięciu w puste miejsce takiej komórki zobaczysz na dole komunikat:
DataFormat.Error: Nie możemy przekonwertować na typ Number.
Szczegóły:
12.5
Angielski oryginał: DataFormat.Error: We couldn't convert to Number. W szczegółach stoi wartość, która nie przeszła. Ten sam błąd dają w kolumnie niestandardowej Number.FromText("12.5") i Number.From("12.5") oraz Number.FromText("1.234", "pl-PL"), czyli każda konwersja tekstu z kropką bez wskazanej kultury albo z kulturą polską.
Zmiana typu w Power Query to konwersja tekstu na liczbę według kultury, czyli zestawu reguł zapisu liczb i dat danego kraju (ang. locale). Jeśli krok nie wskazuje kultury, Power Query używa ustawień regionalnych pliku albo systemu. W polskich ustawieniach separatorem dziesiętnym jest przecinek, więc tekst 12,5 zamienia się w liczbę 12,5, a 12.5 w błąd. Krok Zmieniono typ, który Power Query dodaje automatycznie po imporcie CSV, nie wskazuje kultury.
Dwie pułapki, które sprawdziliśmy w Excelu:
Kultura amerykańska czyta przecinek jako separator tysięcy: Number.FromText("12,5", "en-US") zwraca 125, bez żadnego błędu. Kulturę en-US ustawiaj więc tylko dla kolumn, które naprawdę mają kropkę dziesiętną.
W polskich ustawieniach tekst 12.5 pasuje do daty: zmiana typu takiej kolumny na Data daje 12 maja bieżącego roku zamiast błędu.
Liczba zapisana w JSON bez cudzysłowów, jak pole mid z kursem w API NBP, trafia do Power Query od razu jako liczba, więc ustawienia regionalne nie mają znaczenia (sprawdziliśmy to na wartości 4.2512). Problem dotyczy API, które podaje liczby w cudzysłowach, czyli jako tekst.
Jak to rozwiązać krok po kroku
Sprawdź zapis liczb w źródle, na przykład w podglądzie okna importu. Kropka dziesiętna (12.5, 1250.75, także 1,234.56 z przecinkiem tysięcy) to zapis amerykański, przecinek dziesiętny z kropką tysięcy (1.234,56) to zapis niemiecki.
Usuń automatyczny krok Zmieniono typ krzyżykiem na liście Zastosowane kroki, jeśli zmienił kolumnę z kropkami w błędy. Nowa konwersja musi działać na oryginalnym tekście, a nie na komórkach z błędem. Typy pozostałych kolumn ustawisz ponownie.
Kliknij ikonę typu w nagłówku kolumny i wybierz ostatnią pozycję listy, Używając ustawień regionalnych. Tę samą opcję znajdziesz pod prawym przyciskiem na nagłówku, w menu Zmień typ.
W oknie Zmienianie typu za pomocą ustawień regionalnych ustaw Typ danych na Liczba dziesiętna, a Ustawienia regionalne na Angielski (Stany Zjednoczone) i kliknij OK. Power Query doda krok Zmieniono typ z ustawieniami regionalnymi, w którym kultura jest trzecim argumentem Table.TransformColumnTypes, na przykład "en-US".
Liczby w zapisie niemieckim (1.234,56) wczytasz tak samo, wybierając kulturę Niemiecki (Niemcy), czyli de-DE: w naszym teście dała 1234,56.
Nie zamieniaj kropek na przecinki przez Zamień wartości. Dla 12.5 to zadziała, ale 1,234.56 po zamianie da 1,234,56 i błąd, a kultura en-US odczyta tę wartość poprawnie jako 1234,56.
Jeśli API podaje liczby w cudzysłowach, w kolumnie niestandardowej użyj Number.FromText([kurs], "en-US") albo zmień typ z ustawieniami regionalnymi jak wyżej. Tę samą kulturę możesz podać w funkcji Number.From, na przykład Number.From("12.5", "en-US") zwraca 12,5.
Ikona przy nazwie kolumny otwiera listę typów. Ostatnia pozycja, Używając ustawień regionalnych, rozwiązuje problem dat i liczb zapisanych w formacie innego kraju.
Jak sprawdzić, że zadziałało
Pełny wzorzec w kodzie M, sprawdzony w polskim Excelu na tekście w formacie CSV ze średnikiem jako ogranicznikiem:
let
Źródło = Csv.Document("Sklep;Kwota#(lf)Gdańsk;12.5#(lf)Kraków;1250.75", [Delimiter = ";"]),
Nagłówki = Table.PromoteHeaders(Źródło),
Typy = Table.TransformColumnTypes(Nagłówki, {{"Kwota", type number}}, "en-US")
in
Typy
Kolumna Kwota zawiera liczby 12,5 i 1250,75, a ich suma to 1263,25. Przy prawdziwym pliku w miejscu tekstu stoi File.Contents ze ścieżką. Po zmianie typu włącz na karcie Widok pole Jakość kolumn: w kolumnie nie może być błędów. W Profilu kolumny sprawdź wartości minimalne i maksymalne, bo liczba wyraźnie większa od reszty, na przykład 125 zamiast 12,5, zdradza kulturę en-US zastosowaną do polskiego zapisu. Na koniec porównaj sumę z sumą w systemie, z którego pochodzi eksport.
W polskich ustawieniach regionalnych separatorem dziesiętnym jest przecinek. Power Query zamienia więc 12,5 na liczbę, a 12.5 traktuje jak tekst, którego nie da się przekonwertować, i zgłasza DataFormat.Error: Nie możemy przekonwertować na typ Number. Kultura en-US odczytuje 12.5 poprawnie.
Dlaczego po ustawieniu en-US wartość 12,5 zmieniła się w 125?
Kultura amerykańska traktuje przecinek jako separator tysięcy, więc tekst 12,5 czyta jak 125, bez żadnego błędu. Kulturę en-US ustawiaj tylko dla kolumn z kropką dziesiętną, a kolumny w polskim zapisie zostaw z kulturą polską.
Czy mogę zmienić ustawienia regionalne całego skoroszytu?
Możesz, w Opcjach zapytania, w grupie Bieżący skoroszyt, w pozycji Ustawienia regionalne. Zmiana obejmie jednak wszystkie konwersje bez wskazanej kultury w tym pliku, także kolumny z polskim przecinkiem, w których 12,5 zamieni się w 125. Bezpieczniej wskazać kulturę tylko w kolumnach, które jej potrzebują.
Czy liczby z API NBP też trzeba konwertować?
Nie, jeśli API zapisuje je w JSON bez cudzysłowów, jak pole mid w tabeli kursów NBP. Power Query wczytuje takie wartości od razu jako liczby, niezależnie od ustawień regionalnych. Konwersji z kulturą en-US wymagają tylko liczby podane w cudzysłowach, czyli jako tekst.
Komentarze (0)
Brak komentarzy...