Filtr pustych wartości nie działa - null, pusty tekst i spacja w Power Query

Power Query w Excelu, Power BI i Fabric

Filtr pustych wartości nie działa - null, pusty tekst i spacja w Power Query

W skrócie

Na ten problem trafiasz, gdy filtr pustych komórek zostawia część pustych wierszy, a Wypełnij, W dół zostawia dziury. W Power Query null a pusty tekst to dwie różne wartości, a komórka z samą spacją wygląda tak samo jak obie. Pokazujemy, jak rozpoznać każdy wariant, jak sprowadzić je do jednej postaci i jak napisać warunek, który obejmie wszystkie trzy.

Jak to wygląda w praktyce

Klikasz strzałkę w nagłówku kolumny i wybierasz Usuń puste, a część pozornie pustych wierszy zostaje. Na liście wartości w tym samym filtrze widać dwie osobne pozycje: (null) i (puste). Pierwsza to brak wartości, druga to tekst o długości zero. Komórka z samą spacją jest trzecim przypadkiem: nie jest ani null, ani pustym tekstem, więc Usuń puste jej nie usuwa. Zobaczysz ją po zaznaczeniu na karcie Widok opcji Pokaż odstępy.

Ten sam podział psuje inne kroki. W dół z menu Wypełnij uzupełnia tylko null. W naszym teście kolumna z wartościami „Północ”, pusty tekst i null po wypełnieniu zawierała „Północ” i dwa puste teksty, bo pusty tekst został skopiowany w miejsce null. Grupowanie także rozdziela warianty: kolumna z null, pustym tekstem i spacją daje trzy grupy zamiast jednej.

Dlaczego tak się dzieje

W języku M null oznacza brak wartości, pusty tekst "" to wartość typu tekst o długości zero, a " " to tekst o długości jeden. Sprawdziliśmy w polskim Excelu, że null = "" i "" = " " zwracają false, a dopiero Text.Trim(" ") = "" zwraca true.

Warianty biorą się ze źródeł. Pusta komórka w tabeli Excela trafiła w naszym teście do Power Query jako null, a pole bez wartości na końcu wiersza pliku CSV jako pusty tekst. Spacje dokłada zwykle system źródłowy albo ręczna edycja. Polecenie Usuń puste według dokumentacji Microsoft usuwa wartości null i puste, a tekst ze spacją nie jest pusty, więc zostaje. Na tabeli z czterema wierszami (nazwa sklepu, null, pusty tekst i spacja) trzy warunki dają takie wyniki:

Table.SelectRows(Źródło, each [Sklep] <> null)
// zostają 3 wiersze: nazwa, pusty tekst i spacja

Table.SelectRows(Źródło, each [Sklep] <> null and [Sklep] <> "")
// zostają 2 wiersze: nazwa i spacja

Table.SelectRows(Źródło, each [Sklep] <> null and Text.Trim([Sklep]) <> "")
// zostaje 1 wiersz: nazwa sklepu

Jak to rozwiązać krok po kroku

1. Otwórz filtr kolumny i sprawdź, czy na liście wartości są pozycje (null) i (puste). Potem zaznacz na karcie Widok opcję Pokaż odstępy, żeby zobaczyć komórki z samymi spacjami. Tak ustalisz, z którymi wariantami masz do czynienia.
2. Zaznacz kolumnę i na karcie Przekształć rozwiń Format, a potem wybierz Przycięcie. Komórki z samymi spacjami staną się pustym tekstem, a null zostanie null, bo przycięcie wartości null zwraca null.
3. Na karcie Strona główna kliknij Zamienianie wartości. W oknie zostaw puste pole Wartość do znalezienia, w polu Zamień na wpisz null, rozwiń Opcje zaawansowane, zaznacz Dopasuj do całej zawartości komórki i kliknij OK.
4. Sprawdź w pasku formuły nowy krok Zamieniono wartość. Powinien mieć postać = Table.ReplaceValue(#"Przycięty tekst", "", null, Replacer.ReplaceValue, {"Sklep"}), gdzie #"Przycięty tekst" to nazwa poprzedniego kroku. Jeśli zamiast Replacer.ReplaceValue widzisz Replacer.ReplaceText, popraw to i zatwierdź klawiszem Enter. W naszym teście ta formuła zamieniła pusty tekst i przyciętą spację na null.
5. Dopiero teraz filtruj: strzałka w nagłówku kolumny i Usuń puste. Wszystkie trzy warianty mają już jedną postać, więc filtr obejmie każdy z nich. Jeśli wolisz jeden krok w kodzie bez zamiany, użyj warunku each [Sklep] <> null and Text.Trim([Sklep]) <> "" z ostatniego przykładu powyżej.
6. Wypełnianie w dół (Przekształć, Wypełnij, W dół) rób po zamianie pustego tekstu na null. Dopiero wtedy wypełnią się wszystkie puste komórki, a pusty tekst nie rozleje się w dół na kolejne wiersze.

Jak sprawdzić, że zadziałało

Kliknij na liście Zastosowane kroki krok Zamieniono wartość i otwórz filtr kolumny. Na liście wartości nie powinno już być pozycji (puste), a wszystkie puste komórki mają trafić do (null). Dla pewności dodaj na końcu zapytania tymczasową kolumnę (karta Dodaj kolumnę, Kolumna niestandardowa) z formułą poniżej i obejrzyj jej rozkład w profilu kolumny. W naszym teście formuła poprawnie rozpoznała wszystkie cztery przypadki. Po czyszczeniu i filtrze kolumna pomocnicza powinna zawierać tylko „wartość”. Potem ją usuń.

= if [Sklep] = null then "null"
  else if [Sklep] = "" then "pusty tekst"
  else if Text.Trim([Sklep]) = "" then "same spacje"
  else "wartość"

Żeby problem nie wrócił z kolejnym plikiem, trzymaj kroki przycięcia i zamiany pustego tekstu na null tuż po ustawieniu typów, przed filtrami, grupowaniem i wypełnianiem w dół.

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