Text.Contains i filtry tekstowe rozróżniają wielkość liter - jak to wyłączyć

Power Query w Excelu, Power BI i Fabric

Text.Contains i filtry tekstowe rozróżniają wielkość liter - jak to wyłączyć

W skrócie

Na ten problem trafiasz, gdy filtrujesz nazwy sklepów, produktów albo kontrahentów zapisane raz wielkimi, raz małymi literami. W Power Query Text.Contains rozróżnia wielkość liter, więc filtr po słowie „galeria” nie znajdzie sklepu zapisanego jako „NORDVELLA KRAKÓW GALERIA”. Pokazujemy, które funkcje przyjmują porównywarkę, jak poprawić krok filtra w pasku formuły i jak sprawdzić, że objął wszystkie warianty zapisu.

Jak to wygląda w praktyce

Wybierasz w menu kolumny Filtry tekstu, potem Zawiera, wpisujesz fragment nazwy i dostajesz mniej wierszy, niż się spodziewasz. Nie ma komunikatu ani błędu, po prostu część danych znika z wyniku. To samo dzieje się w kolumnie niestandardowej z funkcją Text.Contains: dla wierszy zapisanych inną wielkością liter zwraca false.

Sprawdziliśmy to w polskim Excelu na tabeli z trzema sklepami: „Nordvella Kraków Galeria”, „NORDVELLA KRAKÓW GALERIA” i „Nordvella Wrocław”. Warunek z fragmentem „Galeria” zostawił 1 wiersz, chociaż sklep w galerii występuje w tych danych w dwóch zapisach. Z porównywarką bez rozróżniania wielkości liter zostały 2 wiersze.

Text.Contains("Nordvella Kraków Galeria", "galeria")
// false

Text.Contains("Nordvella Kraków Galeria", "galeria", Comparer.OrdinalIgnoreCase)
// true

Dlaczego tak się dzieje

Bez trzeciego argumentu Text.Contains, Text.StartsWith i Text.EndsWith porównują tekst znak po znaku z rozróżnianiem wielkości liter. Opis Text.EndsWith w polskim Excelu mówi to wprost: „We wskazaniu jest uwzględniana wielkość liter”. Ten sam opis i opisy dwóch pozostałych funkcji podają, że trzecim, opcjonalnym argumentem jest funkcja porównująca (porównywarka, ang. comparer). Wbudowane są trzy: Comparer.Ordinal (dokładnie, z wielkością liter), Comparer.OrdinalIgnoreCase (bez wielkości liter) i Comparer.FromCulture (według reguł wskazanego języka).

Porównanie znakiem równości działa tak samo: w naszym teście "Online" = "ONLINE" dało false. Filtry tekstowe z menu kolumny zapisują warunek w języku M, więc dziedziczą tę samą zasadę. Wielkość liter ma znaczenie także w nazwach: kolumna „sklep” to inna kolumna niż „Sklep”.

Jak to rozwiązać krok po kroku

1. Kliknij na liście Zastosowane kroki krok filtra (dla filtrów z menu kolumny ma nazwę Przefiltrowano wiersze) i spójrz na pasek formuły. Jeśli go nie widać, zaznacz na karcie Widok opcję Pasek formuły.
2. Odszukaj w warunku funkcję Text.Contains, Text.StartsWith albo Text.EndsWith i dopisz trzeci argument po szukanym tekście, na przykład each Text.Contains([Sklep], "galeria", Comparer.OrdinalIgnoreCase). Zatwierdź klawiszem Enter.
3. Jeśli warunek porównuje znakiem równości, na przykład each [Kanał] = "Online", zamień go na each Comparer.Equals(Comparer.OrdinalIgnoreCase, [Kanał], "Online"). W naszym teście ta forma zwróciła true dla pary „Online” i „ONLINE”.
4. Gdy filtrujesz według kilku wartości naraz, użyj listy: each List.Contains({"Online", "Telefon"}, [Kanał], Comparer.OrdinalIgnoreCase). Jedna lista zastępuje kilka warunków połączonych słowem or i od razu ignoruje wielkość liter.
5. W kolumnie niestandardowej (karta Dodaj kolumnę, Kolumna niestandardowa) zasada jest ta sama: = Text.Contains([Sklep], "galeria", Comparer.OrdinalIgnoreCase). Porównywarka radzi sobie z polskimi literami: Text.Contains("NORDVELLA ŁÓDŹ", "łódź", Comparer.OrdinalIgnoreCase) zwróciło w naszym teście true, tak samo jak wersja z Comparer.FromCulture("pl-PL", true).
6. Jeśli funkcja, której używasz, nie przyjmuje porównywarki, sprowadź oba teksty do małych liter: Text.Contains(Text.Lower([Sklep]), Text.Lower("GALERIA")). Wynik jest ten sam, tylko zmiana obejmuje obie strony porównania.
7. Przed filtrem przytnij kolumnę (Przekształć, Format, Przycięcie). Opis Text.Contains zastrzega, że wszystkie znaki są traktowane dosłownie, więc porównywarka nie zrówna „DR” z „ DR ” ze spacjami.

Jak sprawdzić, że zadziałało

Porównaj liczbę wierszy przed poprawką i po niej, na przykład w profilu kolumny po przełączeniu profilowania na cały zestaw danych. Dokładniejszy test: dodaj dwie tymczasowe kolumny niestandardowe, jedną z formułą = Text.Contains([Sklep], "galeria"), drugą z tą samą formułą i porównywarką. Wiersze, w których kolumny się różnią, to dokładnie te, które wcześniej filtr pomijał. Po sprawdzeniu usuń obie kolumny.

Wartości null nie psują takiego filtra. Według opisu funkcji Text.Contains zwraca dla null wartość null, a w naszym teście wiersz z null po prostu nie przeszedł przez filtr, bez błędu. Jeśli takie wiersze mają zostać, użyj warunku each [Sklep] = null or Text.Contains([Sklep], "galeria", Comparer.OrdinalIgnoreCase). Żeby problem nie wracał, dopisuj porównywarkę od razu przy każdym nowym filtrze tekstowym na danych wpisywanych ręcznie.

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