Usuń duplikaty zostawia nie ten wiersz - jak zachować najnowszy rekord

Power Query w Excelu, Power BI i Fabric

Usuń duplikaty zostawia nie ten wiersz - jak zachować najnowszy rekord

W skrócie

Usuń duplikaty w Power Query a najnowszy rekord: z tym problemem spotkasz się przy tabelach z historią zmian, takich jak cennik, adresy klientów czy statusy zamówień. Chcesz jeden wiersz na produkt, ten z najpóźniejszą datą, a polecenie zostawia ten, który akurat stoi wyżej, i nawet tego nie gwarantuje. Pokazujemy, dlaczego sortowanie przed usunięciem duplikatów nie wystarcza, oraz trzy sposoby, które wybierają najnowszy wiersz niezależnie od kolejności danych. Każdy sprawdziliśmy w polskim Excelu na historii cen produktów Nordvelli.

Jak to wygląda w praktyce

Masz tabelę zmian cen z kolumnami Kod produktu, Data zmiany i Cena netto. Zaznaczasz kolumnę Kod produktu, na karcie Strona główna rozwijasz Usuń wiersze i wybierasz Usuń duplikaty. Zostaje po jednym wierszu na produkt, ale z nieaktualną ceną. W naszym teście w Excelu, na danych zapisanych chronologicznie, polecenie zostawiło dla BEX-1021 cenę 19,99 z 5 stycznia, choć od 1 kwietnia obowiązywała cena 22,99, a dla KEL-1005 cenę 2849 zamiast 2699.

Pierwszy odruch to sortowanie malejąco po dacie przed usunięciem duplikatów. W podglądzie zwykle wygląda to dobrze, dlatego problem łatwo uznać za rozwiązany. Dokumentacja Microsoft opisuje dokładnie ten przypadek i zaznacza, że taka operacja może wyglądać na działającą, ale to zachowanie nie jest gwarantowane.

Dlaczego tak się dzieje

Usuń duplikaty zapisuje krok Table.Distinct, który porównuje wartości w zaznaczonych kolumnach i z każdej grupy identycznych wartości zostawia jeden wiersz. Którego, tego nie ustala na podstawie daty ani żadnej innej kolumny. Opis funkcji w polskim Excelu mówi wprost, że „nie ma gwarancji, który konkretny duplikat zostanie zachowany” (ang. there's no guarantee which specific duplicate will be preserved), bo Power Query czasem przenosi operacje do źródła danych albo pomija kroki, które uzna za zbędne.

Sortowanie przed tym krokiem nie zmienia sytuacji. Sekcja dokumentacji Microsoft o zachowaniu sortowania podaje przykład posortowania sprzedaży tak, żeby największa transakcja sklepu była pierwsza, i usunięcia duplikatów: wynik może się zgadzać, ale nie musi. Wybór najnowszego rekordu musi więc wynikać z reguły zapisanej w zapytaniu, a nie z kolejności wierszy.

Jak to rozwiązać krok po kroku

1. Policz najpóźniejszą datę dla każdego klucza. Kliknij prawym przyciskiem zapytanie z historią cen, wybierz Odwołanie i nazwij nowe zapytanie Ostatnie_zmiany. Na karcie Strona główna kliknij Grupowanie według, grupuj po Kod produktu, nową kolumnę nazwij Data zmiany, wybierz operację Maksimum i kolumnę Data zmiany. Wynik ma jeden wiersz na produkt.
2. Dociągnij resztę wiersza scaleniem po dwóch kolumnach. W zapytaniu Ostatnie_zmiany kliknij Scal zapytania, wybierz tabelę z historią cen i zaznacz klawiszem Ctrl kolumny Kod produktu i Data zmiany w tej samej kolejności w obu tabelach. Rozwiń kolumnę z tabelami i zaznacz Cena netto. W teście wynik to BEX-1021 z ceną 22,99 z 1 kwietnia i KEL-1005 z ceną 2699 z 10 lutego.
3. Sprawdź remisy. Gdy produkt ma dwie zmiany tego samego dnia, scalenie zwróci oba wiersze. W teście z dodatkową korektą ceny KEL-1005 z 10 lutego wyszły 3 wiersze dla 2 produktów. Jeśli po rozwinięciu wierszy jest więcej niż po grupowaniu, potrzebujesz drugiego kryterium, na przykład numeru wersji albo godziny zmiany.
4. Jeden krok z remisem w zestawie: Table.Max. W oknie Grupowanie według wybierz operację Wszystkie wiersze i nazwij kolumnę Najnowszy. W pasku formuły zamień each _ razem z opisem typu tabeli na each Table.Max(_, {"Data zmiany", "Wersja"}), type record. Table.Max zwraca cały wiersz z największą wartością według kryteriów, a druga kolumna rozstrzyga remis. Rozwiń kolumnę Najnowszy ikoną w nagłówku. W teście z remisem dla KEL-1005 została korekta 2649 z wersją 3.
5. Ranga, gdy potrzebujesz też poprzednich wersji. Zamiast Table.Max wpisz each Table.AddRankColumn(_, "Ranga", {{"Data zmiany", Order.Descending}, {"Wersja", Order.Descending}}, [RankKind = RankKind.Ordinal]), type table, rozwiń kolumnę i odfiltruj Ranga równą 1. Ranga 2 wskaże poprzednią cenę. W teście filtr na 1 dał te same wiersze co Table.Max.
6. Szybka poprawka istniejącego zapytania: bufor po sortowaniu. Jeśli zostajesz przy Usuń duplikaty, kliknij prawym przyciskiem krok Posortowano wiersze, wybierz Wstaw krok po i wpisz = Table.Buffer(#"Posortowano wiersze"). Opis Table.Distinct zaleca bufor, jeśli usuwanie duplikatów ma działać przewidywalnie. W teście sortowanie, bufor i usunięcie duplikatów zostawiły 22,99 i 2699. Remisów ta metoda nie rozstrzyga, a bufor zatrzymuje składanie zapytań do bazy.

Jak sprawdzić, że zadziałało

Policz wiersze: wynik powinien mieć tyle wierszy, ile jest różnych kluczy. Liczbę różnych kodów pokaże napis Odrębne pod nagłówkiem Kod produktu w tabeli źródłowej, gdy na karcie Widok zaznaczysz Rozkład kolumn i przełączysz profilowanie na cały zestaw danych. Wybierz dwa, trzy produkty z najdłuższą historią i porównaj datę w wyniku z najpóźniejszą datą w źródle. Nawrotom zapobiega reguła wpisana w zapytanie (maksimum, Table.Max albo ranga), a w danych, w których zdarzają się dwie zmiany jednego dnia, stałe drugie kryterium, takie jak numer wersji.

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