Liczby z kropką dziesiętną dają błąd w polskim Excelu - jak wczytać je z CSV i API

Power Query w Excelu, Power BI i Fabric

Liczby z kropką dziesiętną dają błąd w polskim Excelu - jak wczytać je z CSV i API

W skrócie

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ą.

Dlaczego tak się dzieje

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

1. 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.
2. 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.
3. 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.
4. 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".
5. 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.
6. 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.
7. 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.

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.

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