Liczby z Power Query lądują w Excelu jako tekst - SUMA zwraca zero

Power Query w Excelu

W skrócie

Liczby z Power Query lądują w Excelu jako tekst, gdy kolumna w zapytaniu ma typ Tekst albo typ Dowolny z wartościami tekstowymi. SUMA zwraca wtedy 0, a CZY.TEKST zwraca PRAWDA.
Power Query zapisuje do arkusza wartości w takim typie, jaki mają w zapytaniu. Ręczna zamiana tekstu na liczby w arkuszu nie pomaga na stałe, bo odświeżenie nadpisuje tabelę danymi z zapytania.
Ustaw kolumnie typ Liczba dziesiętna albo Liczba całkowita w edytorze Power Query przed Zamknij i załaduj, a liczby w formacie innego kraju zmień z ustawieniami regionalnymi. Potem sprawdź ikony typów w nagłówkach.

Liczby z Power Query lądują w Excelu jako tekst wtedy, gdy w samym zapytaniu są tekstem: tabela wygląda dobrze, a SUMA pod kolumną kwot pokazuje 0. Najczęściej chodzi o kolumnę, której ktoś nadał typ Tekst, albo o kwotę wyciętą z dłuższego opisu. Pokazujemy, jak rozpoznać tekst w arkuszu, dlaczego poprawka w arkuszu nie wystarcza i gdzie naprawić to na stałe.

Jak to wygląda w praktyce

Po kliknięciu Zamknij i załaduj tabela w arkuszu wygląda poprawnie, ale formuła =SUMA(A2:A3) pod kolumną z kwotami zwraca 0. Tak było w naszym teście w polskim Excelu: kolumna z typem Tekst i wartościami 10 i 20 dała sumę 0. Funkcja =CZY.TEKST(A2) zwróciła PRAWDA, a =CZY.LICZBA(A2) zwróciła FAŁSZ, czyli w komórce jest tekst, a nie liczba.

Zielony trójkąt w rogu komórki to znak, którym Excel oznacza liczby zapisane jako tekst, na przykład wpisane z apostrofem. Przy tabeli z zapytania nie warto na nim polegać: w naszym teście reguła sprawdzania błędów Excela oznaczyła wpis z apostrofem, a komórek tekstowych załadowanych z Power Query nie oznaczyła. Pewniejszym testem są SUMA i CZY.TEKST. Drugi objaw pojawia się po odświeżeniu: liczby poprawione ręcznie w arkuszu znów stają się tekstem.

Dlaczego tak się dzieje

Power Query ładuje do arkusza wartości w takim typie, jaki mają w zapytaniu. Kolumna z typem Tekst (ikona ABC w nagłówku) trafia do komórek jako tekst, nawet gdy zawiera same cyfry. Kolumna z typem Dowolny (ikona ABC123) zapisuje każdą wartość osobno: w naszym teście liczby z takiej kolumny dały w arkuszu poprawną sumę, a wartości tekstowe z innej kolumny Dowolny dały 0. Liczba staje się więc tekstem w arkuszu wtedy, gdy jest tekstem już w zapytaniu.

Ręczna poprawka nie przetrwa, bo tabela z zapytania jest nadpisywana przy każdym odświeżeniu. Sprawdziliśmy to: komórkę z tekstem 10 zamieniliśmy w arkuszu na liczbę, a po odświeżeniu znów był w niej tekst. W kursie pokazujemy drugą stronę tej zasady: formaty liczb ustawione w arkuszu, na przykład złote i procenty, odświeżanie zachowuje, bo Power Query aktualizuje dane, a nie wygląd tabeli. Typ danych trzeba więc ustawić w zapytaniu, a format w arkuszu.

Jak to rozwiązać krok po kroku

Otwórz edytor skrótem Alt+F12 i w panelu zapytań po lewej kliknij zapytanie, które ładuje tabelę. Listę zapytań w skoroszycie zobaczysz też w okienku Zapytania i połączenia, które otwierasz przyciskiem o tej nazwie na karcie Dane.
Sprawdź ikonę w nagłówku kolumny z liczbami. ABC oznacza Tekst, a ABC123 Dowolny. Kliknij ikonę i wybierz Liczba dziesiętna dla kwot albo Liczba całkowita dla sztuk. Power Query zamieni tekst na liczby i doda krok Zmieniono typ.
Typ liczbowy w polskich ustawieniach regionalnych przyjmuje spacje tysięcy i dopisek waluty: w naszym teście tekst 1 234,50 zł zamienił się w 1234,5. Nie przejdzie natomiast zapis amerykański 12.50, który daje błąd DataFormat.Error: Nie możemy przekonwertować na typ Number. (ang. We couldn't convert to Number.). Takie kolumny zmień poleceniem Używając ustawień regionalnych z kulturą źródła, na przykład Angielski (Stany Zjednoczone).
Jeśli po zmianie typu w kolumnie zostają komórki z napisem Error, zaznacz kolumnę, na karcie Strona główna rozwiń Zachowaj wiersze i wybierz Zachowaj błędy. Zobaczysz wartości, których nie udało się zamienić. Popraw je w źródle albo krokiem zamiany wartości, a krok kontrolny usuń.
Kolumnom typu Dowolny, które mają trafić do modelu danych albo do Power BI, też ustaw typ liczbowy. W naszym teście model danych Excela nadał kolumnie Dowolny z samymi liczbami typ tekstowy.
Zamknij edytor przyciskiem Zamknij i załaduj. Ręczne poprawki z arkusza możesz pominąć, bo i tak zastąpią je dane z zapytania. Format liczb, na przykład złote, ustaw w arkuszu, bo ten przetrwa odświeżanie.

Jak sprawdzić, że zadziałało

Po załadowaniu formuła =CZY.LICZBA(A2) ma zwracać PRAWDA, a SUMA pod kolumną oczekiwaną kwotę. W naszym teście kolumna z typem Liczba dziesiętna i wartościami 10,5 i 20 dała sumę 30,5. W edytorze kolumny z liczbami mają w nagłówku ikonę 1.2 albo 123, a nie ABC ani ABC123. Na karcie Dane kliknij Odśwież wszystko (Ctrl+Alt+F5) i sprawdź, czy wynik się utrzymuje.

Żeby problem nie wrócił, typy ustawiaj w zapytaniu, a nie w arkuszu. Ostatnim krokiem zapytania, które ładujesz do arkusza, niech będzie zmiana typu wszystkich kolumn. Jeśli zapytanie zasila tabelę przestawną, odśwież ją po zmianie typu i porównaj sumy z wynikiem formuły SUMA.

Tabela załadowana z Power Query do arkusza Excela z kwotami sprzedaży i budżetu w złotych oraz realizacją budżetu w procentach — Tabela załadowana z zapytania do arkusza jako liczby: sprzedaż i budżet w złotych, realizacja budżetu w procentach, wszystkie wartości wyrównane do prawej.

Poczytaj więcej
Lekcja 6 bezpłatnego kursu Power Query: Ładowanie wyniku i automatyczne odświeżanie w Excelu
Cały bezpłatny kurs Power Query w Excelu, Power BI i Fabric z Copilotem

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

Baner szkolenia Microsoft Excel - Power Query w JSystems z edytorem Power Query na ekranie laptopa

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

Autorzy

Tekst: Redakcja JSystems

Najczęściej zadawane pytania

Dlaczego SUMA zwraca 0 dla kolumny załadowanej z Power Query?

Bo w komórkach jest tekst, a SUMA pomija wartości tekstowe w zakresie. Tak się dzieje, gdy kolumna w zapytaniu ma typ Tekst. Sprawdź to funkcją CZY.TEKST, a potem ustaw w edytorze Power Query typ Liczba dziesiętna i odśwież tabelę.

Czy mogę zamienić tekst na liczby bezpośrednio w arkuszu?

Możesz, ale tylko do następnego odświeżenia. Tabela z zapytania jest przy odświeżeniu nadpisywana i w naszym teście poprawiona ręcznie komórka znów zawierała tekst. Trwała naprawa to typ liczbowy ustawiony w Power Query.

Dlaczego po zmianie typu na Liczba dziesiętna część komórek pokazuje Error?

Te wartości nie dają się odczytać jako liczba w bieżących ustawieniach regionalnych, na przykład zapis 12.50 z kropką w polskim Excelu. Zmień typ z ustawieniami regionalnymi źródła albo popraw takie wartości przed zmianą typu. Wiersze z błędami znajdziesz poleceniem Zachowaj błędy.

Czy kolumna typu Dowolny też trafi do arkusza jako tekst?

Zależy od wartości. W naszym teście liczby z kolumny Dowolny trafiły do arkusza jako liczby, a wartości tekstowe jako tekst. W modelu danych ta sama kolumna z liczbami dostała typ tekstowy, dlatego bezpieczniej ustawić typ każdej kolumnie przed załadowaniem.