Przytnij nie usuwa wszystkich spacji - spacje w środku tekstu i niewidoczne znaki

Power Query w Excelu, Power BI i Fabric

Przytnij nie usuwa wszystkich spacji - spacje w środku tekstu i niewidoczne znaki

W skrócie

Przytnij w Power Query nie usuwa spacji, których się spodziewasz, gdy dane pochodzą ze stron WWW, plików PDF, wiadomości e-mail albo eksportów z innych systemów. Nazwa sklepu wygląda tak samo jak w słowniku, a scalanie i filtry traktują ją jak inną wartość. Pokazujemy, które znaki usuwa polecenie Przycięcie, a których nie, jak zobaczyć znaki niewidoczne i jak je usunąć. Każde zachowanie sprawdziliśmy w polskim Excelu na nazwach sklepów Nordvella.

Jak to wygląda w praktyce

Kolumna Sklep po kroku Przycięty tekst nadal zawiera dwie wersje tej samej nazwy. Na liście filtra „Nordvella Wrocław” występuje dwa razy, tabela przestawna pokazuje sklep w dwóch wierszach, a scalanie ze słownikiem sklepów daje null. W teście w Excelu tekst z twardą spacją między wyrazami, w języku M zapisany jako Nordvella#(00A0)Wrocław, miał tę samą długość (17 znaków) i ten sam wygląd co zwykłe „Nordvella Wrocław”, a porównanie obu wartości zwróciło false. Scalenie tej nazwy ze słownikiem dało zero dopasowań, a po usunięciu znaków niewidocznych jedno.

Dlaczego tak się dzieje

Polecenie Przycięcie z menu Format na karcie Przekształć (na karcie Dodaj kolumnę nazywa się Przytnij i tworzy nową kolumnę) wywołuje funkcję Text.Trim. Jej opis mówi, że domyślnie usuwa wiodące i końcowe znaki odstępu, czyli działa wyłącznie na brzegach tekstu. W polskim Excelu sprawdziliśmy:

• spacje, tabulatory i znaki nowego wiersza na brzegach są usuwane,
• twarda spacja (U+00A0, w języku M #(00A0)) na brzegach jest usuwana, ale w środku tekstu zostaje,
• podwójna spacja w środku zostaje: Nordvella Wrocław po przycięciu nadal ma 18 znaków,
• spacja o zerowej szerokości (U+200B, #(200B)) zostaje nawet na brzegach: „Wrocław” otoczony tym znakiem miał po przycięciu 9 znaków zamiast 7.

Polecenie Wyczyść (Text.Clean) usuwa znaki kontrolne, na przykład znak nowego wiersza ze środka tekstu, ale w teście nie usunęło ani twardej spacji, ani U+200B. Twarda spacja trafia do danych między innymi przy kopiowaniu ze stron WWW i PDF-ów, o czym piszemy w lekcji o czyszczeniu tekstu.

Jak to rozwiązać krok po kroku

1. Zobacz, jakie znaki są w tekście. Dodaj kolumnę niestandardową z formułą Text.Combine(List.Transform(Text.ToList([Sklep]), each Text.From(Character.ToNumber(_))), ","). Pokaże kod każdego znaku: zwykła spacja to 32, twarda spacja 160, spacja o zerowej szerokości 8203. W teście tekst a#(00A0)b#(200B)c dał 97,160,98,8203,99. Usuń tę kolumnę po diagnozie.
2. Zamień twardą spację w oknie Zamienianie wartości. Zaznacz kolumnę, na karcie Strona główna kliknij Zamienianie wartości, rozwiń Opcje zaawansowane i zaznacz Zamień przy użyciu znaków specjalnych. Kliknij w polu Wartość do znalezienia, wybierz Wstaw znak specjalny i Spacja nierozdzielająca, a w polu Zamień na wpisz zwykłą spację. Według dokumentacji Microsoft opcje zaawansowane są dostępne tylko w kolumnach tekstowych.
3. Usuń U+200B formułą. Lista znaków specjalnych ma tylko tabulator, powrót karetki, nowy wiersz, ich połączenie i spację nierozdzielającą, więc spację o zerowej szerokości usuń w kolumnie niestandardowej: Text.Replace([Sklep], Character.FromNumber(8203), ""). Obie zamiany zmieścisz w jednej formule: Text.Trim(Text.Replace(Text.Replace([Sklep], Character.FromNumber(8203), ""), Character.FromNumber(160), " ")). W teście zamieniła ona tekst z U+200B na początku, twardą spacją w środku i spacją na końcu dokładnie na „Nordvella Wrocław”.
4. Zredukuj podwójne spacje w środku. Formuła Text.Combine(List.Select(Text.Split([Sklep], " "), each _ <> ""), " ") dzieli tekst na wyrazy, pomija puste fragmenty powstałe z kolejnych spacji i skleja wyrazy pojedynczą spacją, przy okazji usuwając spacje z brzegów. W teście „Nordvella Warszawa Centrum” zamieniło się w „Nordvella Warszawa Centrum”.
5. Przycinaj na końcu. Kolejność ma znaczenie: w teście przycięcie tekstu z U+200B i spacją na początku, a dopiero potem usunięcie U+200B, zostawiło wiodącą spację, a odwrotna kolejność dała czysty „Wrocław”. Najpierw usuń i zamień znaki niewidoczne, potem redukuj spacje i przycinaj.
6. Złóż wszystko w jedną funkcję. Gdy problem dotyczy kilku kolumn albo kilku zapytań, utwórz puste zapytanie Czysc_tekst z kodem funkcji podanym niżej, w części o zapobieganiu nawrotom, i wywołuj je przez Wywołaj funkcję niestandardową na karcie Dodaj kolumnę. Funkcja obsługuje puste komórki: w teście zwróciła null dla null, a tekst z U+200B, podwójną twardą spacją, podwójną spacją i znakiem nowego wiersza zamieniła na „Nordvella Warszawa Centrum”.

Jak sprawdzić, że zadziałało

Po czyszczeniu na karcie Widok zaznacz Rozkład kolumn i przełącz profilowanie na cały zestaw danych: liczba wartości odrębnych w kolumnie Sklep powinna spaść do liczby prawdziwych sklepów, w kursie do 15. Kolumna z kodami znaków z pierwszego kroku nie powinna już zawierać 160 ani 8203, a okno Scalanie powinno pokazać pełną zgodność ze słownikiem.

Żeby problem nie wracał, zostaw czyszczenie jako stały krok zaraz po źródle, bo każdy nowy eksport ze strony WWW albo z PDF-a może przynieść te same znaki. Najwygodniej zrobić to funkcją Czysc_tekst, którą wklejasz do Edytora zaawansowanego pustego zapytania:

(t as nullable text) as nullable text =>
    if t = null then null
    else Text.Combine(
        List.Select(
            Text.Split(
                Text.Replace(Text.Replace(Text.Clean(t), Character.FromNumber(8203), ""),
                    Character.FromNumber(160), " "),
                " "),
            each _ <> ""),
        " ")

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