Usuń duplikaty nie usuwa duplikatów - wielkość liter i spacje na końcu

Power Query w Excelu, Power BI i Fabric

Usuń duplikaty nie usuwa duplikatów - wielkość liter i spacje na końcu

W skrócie

Na ten problem trafisz przy pierwszym czyszczeniu eksportu: klikasz Usuń duplikaty, a liczba wierszy prawie się nie zmienia. W Power Query Usuń duplikaty nie działa tak, jak podpowiada oko, bo porównuje tekst co do znaku. Pokazujemy, jak przebiega to porównanie, w jakiej kolejności ustawić kroki czyszczenia i jak potwierdzić wynik w profilu kolumny, na danych sprzedaży fikcyjnej sieci Nordvella z naszego kursu.

Jak to wygląda w praktyce

Zaznaczasz wszystkie kolumny, na karcie Strona główna rozwijasz Usuń wiersze i wybierasz Usuń duplikaty. Na liście Zastosowane kroki pojawia się krok Usunięto duplikaty, nie ma komunikatu ani błędu, a w danych dalej widać pary wierszy, które wyglądają tak samo. Zwykle różnią się jednym z dwóch szczegółów:

• wielkością liter: „Nordvella Wrocław” i „NORDVELLA WROCŁAW”, kod „BEX-1021” i „bex-1021”,
• spacją na końcu: „Nordvella Wrocław” i „Nordvella Wrocław ” ze spacją po ostatniej literze.

Skutek wychodzi w raporcie. Dla tabeli przestawnej to także różne wartości, więc ten sam sklep pojawia się w kilku wierszach, a jego sprzedaż rozkłada się na warianty zapisu.

Dlaczego tak się dzieje

Polecenie Usuń duplikaty zapisuje krok z funkcją Table.Distinct, która porównuje wartości dokładnie. Sprawdziliśmy to w polskim Excelu: tabela z wierszami „Nordvella Wrocław” i „NORDVELLA WROCŁAW” po usunięciu duplikatów nadal ma 2 wiersze. Tak samo zostają 2 wiersze z pary „Nordvella Wrocław” i „Nordvella Wrocław ”. Dla Power Query to różne teksty, więc krok działa poprawnie, tylko dane nie są jeszcze ujednolicone.

Table.Distinct(#table({"Sklep"}, {{"Nordvella Wrocław"}, {"NORDVELLA WROCŁAW"}}))
// wynik: 2 wiersze

Table.Distinct(#table({"Sklep"}, {{"Nordvella Wrocław"}, {"NORDVELLA WROCŁAW"}}), Comparer.OrdinalIgnoreCase)
// wynik: 1 wiersz

Gdy zaznaczysz wszystkie kolumny, znikną wyłącznie wiersze identyczne w całości. Wystarczy, że jedna kolumna różni się wielkością liter albo spacją, i wiersz zostaje. Dlatego liczy się kolejność kroków: najpierw czyszczenie tekstu, potem usuwanie duplikatów.

Jak to rozwiązać krok po kroku

1. Na karcie Widok zaznacz Pokaż odstępy. Spacje na początku i końcu tekstu staną się widoczne w siatce podglądu, więc od razu zobaczysz, które kolumny wymagają przycięcia.
2. Zaznacz kolumnę tekstową, na przykład Sklep, i na karcie Przekształć rozwiń Format, a potem wybierz Przycięcie. Power Query usunie odstępy z początku i końca każdej komórki i doda krok Przycięty tekst.
3. Przy tej samej kolumnie jeszcze raz rozwiń Format i ujednolić zapis: dla nazw wybierz Zamień pierwszą literę każdego wyrazu na wielką, dla kodów Wielkie litery, tak jak w kursie dla kolumny Kod produktu. Każda operacja doda osobny krok, na przykład Tekst pisany wielkimi literami.
4. Jeśli krok Usunięto duplikaty już istnieje, nie twórz go od nowa. Kliknij na liście Zastosowane kroki krok, który go poprzedza, i dodaj przycięcie oraz zmianę wielkości liter. Power Query zapyta w oknie Wstawianie kroku, czy na pewno wstawić krok w środku zapytania. Potwierdź, a potem kliknij ostatni krok, żeby zobaczyć wynik.
5. Gdy kroki czyszczenia stoją wyżej, zaznacz kolumny, które razem identyfikują wiersz, albo wszystkie kolumny (kliknij nagłówek pierwszej i naciśnij Ctrl+A). Na karcie Strona główna rozwiń Usuń wiersze i wybierz Usuń duplikaty.
6. Jeśli wolisz nie zmieniać zapisu w danych, a tylko porównywać bez wielkości liter, dopisz w pasku formuły funkcję porównującą (porównywarkę, ang. comparer). Table.Distinct(#"Przycięty tekst", Comparer.OrdinalIgnoreCase) porównuje tak wszystkie kolumny, a Table.Distinct(#"Przycięty tekst", {{"Sklep", Comparer.OrdinalIgnoreCase}, {"Kod produktu", Comparer.OrdinalIgnoreCase}}) tylko wskazane. Zapis z jedną porównywarką dla listy kolumn, {{"Sklep", "Kod produktu"}, Comparer.OrdinalIgnoreCase}, kończy się błędem Expression.Error: Określone kryteria unikatowości są nieprawidłowe. (ang. The specified distinct criteria is invalid.).
7. Pamiętaj, że porównywarka nie usuwa spacji i nie poprawia zapisu. W naszym teście z pary „NORDVELLA WROCŁAW” i „Nordvella Wrocław” został zapis wielkimi literami, bo stał wyżej. Do raportów lepiej więc ujednolicić tekst krokami z punktów 2 i 3, a porównywarkę traktować jako dodatkowe zabezpieczenie.

Jak sprawdzić, że zadziałało

Porównaj liczbę wierszy przed krokiem Usunięto duplikaty i po nim. Na karcie Widok włącz Rozkład kolumn i Profil kolumny, a na pasku stanu kliknij napis o profilowaniu pierwszych 1000 wierszy i przełącz na Profilowanie kolumn w oparciu o cały zestaw danych. W kursie po tych krokach kolumna Sklep ma dokładnie 15 wartości odrębnych, tyle, ile sklepów, a liczba wierszy spada z 9256 do 9250. Jeśli wartości odrębnych jest więcej niż obiektów w rzeczywistości, w kolumnie zostały warianty zapisu.

Żeby problem nie wrócił z kolejnym plikiem, trzymaj kroki przycięcia i zmiany wielkości liter nad krokiem Usunięto duplikaty. Przy każdym odświeżeniu przejdą przez nie także nowe dane.

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